MySQL Active Thread Count Optimization
The active thread count or active connection count is an important metric for assessing the load status of MySQL. Generally, a healthy instance should maintain an active connection count of less than 10. For high-spec and high QPS instances, the active connection count may reach 20-30. If the active connection count exceeds several hundred or even thousands, SQL queue buildup may occur, and MySQL's response time may slow down, potentially leading to instance crashes or an inability to handle additional SQL requests.
Checking Active Connection Count
You can view the active connection count using the following SQL statement.
| Returned Parameter | Description |
|---|---|
| Threads_connected | Number of open connections |
| Threads_running | Number of active connections, typically much lower than the connected count |
Optimization Suggestions for Active Thread Count Issues
Slow SQL Queue Buildup
If you find that the active thread count is increasing, you should first execute the show processlist command to check for slow queries. Slow queries that scan too many rows can lead to an increase in active connections, thereby causing performance issues. If performance-related issues need to be resolved, it is recommended to terminate the relevant sessions to reduce the impact.
Table Cache Issues
In high-traffic scenarios or those involving a large number of tables, the table cache may be insufficient, resulting in numerous SQL statements being in the "Opening table" state. This significantly increases query latency and server load and may lead to performance degradation or server crashes.
You can increase the table cache size by raising the table_open_cache parameter to reduce the number of SQL statements in the "Opening table" state and improve query performance. However, care should be taken as excessively increasing this parameter can consume server memory and potentially lead to performance issues. Therefore, adjustments to this parameter should be made cautiously to achieve optimal performance and stability.
Metadata Lock (MDL) Issues
When MDL locks occur, a large number of SQL statements may be in the "Waiting for table metadata lock" state. During the DDL prepare and commit phases, DDL statements need to acquire MDL locks. If there are uncommitted transactions or slow SQL on the table, it will block DDL operations. Since DDL operations can also block other SQL operations, this may lead to an increase in the active thread count.
It is recommended to terminate uncommitted transactions, slow SQL, or currently executing DDL operations.
Row Lock Conflicts
You can check for the relevant status with the following SQL statement.
Row lock conflicts manifest as an increase in the Innodb_row_lock_waits and Innodb_row_lock_time status indicators, leading to a rise in the active thread count.
You can also execute the show engine innodb status command to check for a large number of sessions in the "Lock wait" state. If there are many, it indicates serious row lock conflicts, which need to be optimized through methods such as hotspot updates, reducing transaction sizes, and timely transaction commits to avoid row lock conflicts.