This topic describes the causes of a high active thread count on an ApsaraDB RDS for MySQL instance and provides solutions.
Background information
The number of active threads (also known as active connections) is a key metric for measuring the workload of a MySQL instance. On a healthy instance, the number of active threads is typically below 10. For high-specification, high-QPS instances, this number can be 20 or 30. If the count reaches several hundred or even thousands, it indicates that SQL queries are accumulating and response times increase. In severe cases, the instance may stop responding and can no longer process SQL requests.
Check active threads
The ApsaraDB RDS console provides several methods for viewing the number of active threads:
-
Monitoring and Alerts
On the Monitoring and Alerts page in the console, click the Standard Monitoring tab, and then click Standard View to view the active thread metrics for the instance.
-
Database Autonomy Service (DAS)
On the page of the console, click the Dashboard tab to view the session connection status. If the number of threads is too high, this indicates that instance sessions are blocked.
Slow SQL accumulation
-
Symptom
If monitoring shows an increase in active threads, first run the
show processlist;command to check for slow SQL queries. A large number of queries that scan too many rows often cause the number of active threads to rise.You can view information about slow SQL on the page in the console.
-
Resolution
Use the SQL throttling feature or terminate sessions to reduce the impact of slow SQL queries.
Table cache issues
-
Symptom
If the table cache is insufficient, a large number of SQL statements remain in the
Opening tablestate. This commonly occurs when QPS is high or when you have a large number of tables. -
Resolution
Increase the values of the table_open_cache parameter (no instance restart required) and the table_open_cache_instances parameter (instance restart required).
Metadata lock (MDL) issues
-
Symptom
When a metadata lock (MDL) occurs, a large number of SQL queries enter the
Waiting for table metadata lockstate. During theprepareandcommitphases, a DDL statement must acquire an MDL. If the table has uncommitted transactions or slow SQL queries, the DDL operation is blocked. This, in turn, blocks other SQL queries and eventually increases the number of active threads. -
Resolution
To resolve this issue, terminate the uncommitted transactions, slow SQL queries, or the running DDL statement.
Row lock conflicts
-
Symptom
Row lock conflicts are indicated by an increase in the innodb_row_lock_waits and innodb_row_lock_time metrics.
On the page in the console, click the Dashboard tab to view the monitoring items in row lock.
-
Resolution
Run the
show engine innodb status;command to check whether a large number of sessions are in theLock waitstate. If so, row lock contention is severe. To reduce contention, optimize hot-spot updates, reduce transaction sizes, and commit transactions promptly.