All Products
Search
Document Center

ApsaraDB RDS:Troubleshoot the issues that cause an excessive number of active threads on an ApsaraDB RDS for MySQL instance

Last Updated:Sep 11, 2023

This topic describes how to troubleshoot the issues that cause an excessive number of active threads on an ApsaraDB RDS for MySQL instance.

Background information

The number of active threads or the number of active connections is an important metric that is used to measure the loads on your RDS instance. In most cases, the number of active threads or connections on a healthy RDS instance is less than 10. If the RDS instance provides high specifications and high queries per second (QPS), the number may increase to 20 or 30. If the number exceeds 100 or 1,000, the speed at which the RDS instance responds to queries is low due to an SQL query pile-up. In severe cases, the RDS instance does not respond and stops processing SQL queries.

View the number of active threads

You can log on to the ApsaraDB RDS console and use one of the following methods to view the number of active threads:

  • Monitoring and Alerts

    In the left-side navigation pane, click Monitoring and Alerts. On the Standard Monitoring tab of the page that appears, click Standard View to view the number of active threads.

  • DAS

    In the left-side navigation pane, choose Autonomy Service > Dashboard. On the page that appears, click the Performance Trends tab to view the number of active threads. If the number is excessively large, some sessions are blocked.

Troubleshoot piled-up slow SQL queries

  • Symptom

    If the number of active threads increases, you can execute the SHOW PROCESSLIST statement to check whether slow SQL queries exist. If a large number of SQL queries require ApsaraDB RDS to scan an excessively large number of rows, the number of active threads may increase.

    You can choose Autonomy Service > Slow Query Logs to view the information about slow SQL queries.

  • Solution

    Enable the SQL throttling feature or terminate sessions. This mitigates the impact of slow SQL queries. For more information, see SQL throttling.

Troubleshoot table cache issues

  • Symptom

    If your RDS instance encounters excessively high QPS or processes a large number of tables, a large number of SQL queries switch to the Opening table state due to an insufficient table cache size.

  • Solution

    Increase the values of the table_open_cache and table_open_cache_instances parameters. The reconfiguration of the table_open_cache parameter does not require a restart of the RDS instance. However, the reconfiguration of the table_open_cache_instances parameter requires a restart of the RDS instance.

Troubleshoot metadata locking issues

  • Symptom

    In the Prepare and Commit phases, DDL statements need to acquire metadata locks on tables. If the tables are involved in uncommitted transactions or slow SQL queries, the DDL statements are blocked. This in turn blocks more SQL queries. All the blocked SQL queries switch to the Waiting for table metadata lock state. As a result, the number of active threads increases.

  • Solution

    Abort all the uncommitted transactions, slow SQL queries, and ongoing DDL statements.

Troubleshoot row lock conflicts

  • Symptom

    If the values of the Innodb_row_lock_waits and Innodb_row_lock_time metrics are large, row lock conflicts may occur.

    In the left-side navigation pane, choose Autonomy Service > Dashboard. On the page that appears, click the Performance Trends tab. Then, you can view the metrics in the Row Lock section.

  • Solution

    Execute the SHOW ENGINE INNODB STATUS; statement to check whether a large number of sessions are in the Lock wait state. If a large number of sessions are in the Lock wait state, severe row lock conflicts occur. In this case, mitigate row lock conflicts by using all suggested methods. For example, you can optimize hot data updates, reduce transaction sizes, and reduce the time that is required to commit transactions.