ApsaraDB RDS:Troubleshoot slow SQL statements on an ApsaraDB RDS for MySQL instance

Troubleshoot slow SQL statements caused by SQL exceptions

• Cause and symptom

  SQL exceptions may occur due to various issues, such as an unsuitable schema design, missing indexes, and an excessively large number of rows that need to be scanned.

  You can log on to the ApsaraDB RDS console and choose SQL Explorer and Audit in the left-side navigation pane. Then, you can view information about the executed SQL statements. The information includes the time that is required to execute each slow SQL statement and the number of times that each slow SQL statement is executed.

• Solution

  Optimize the SQL statements that you want to execute based on your business scenario. For more information, see SQL optimization.

Troubleshoot slow SQL statements caused by instance limits

• Cause and symptom

  In most cases, your RDS instance reaches its maximum performance due to the following reasons:

  • Your workloads continue to increase. However, the storage capacity is not scaled.
  • The physical host on which your RDS instance resides ages. This decreases the performance of your RDS instance.
  • The amount of data continues to increase, and the data structure changes. As a result, the speed at which ApsaraDB RDS executes some SQL statements becomes low.

  You can log on to the ApsaraDB RDS console and choose Monitoring and Alerts in the left-side navigation pane. Then, on the Standard Monitoring tab, you can click Resource Monitoring to view the resource usage of your RDS instance. If the values of all resource usage metrics are close to 100%, your RDS instance has reached its maximum performance.

• Solution

  We recommend that you use SysBench to benchmark the maximum performance of your RDS instance. In most cases, the queries per second (QPS) and the transactions per second (TPS) do not exceed the maximum performance even if you run complex queries. For more information, see Test guidelines.

  If your RDS instance has reached its maximum performance, we recommend that you upgrade the instance. For more information, see Change the specifications of an ApsaraDB RDS for MySQL instance.

Troubleshoot slow SQL statements caused by version upgrades

• Cause and symptom

  When you upgrade your RDS instance, the query plans for SQL statements may change. The following join types that are supported by query plans are sorted in descending order based on efficiency: system, const, eq_ref, ref, fulltext, ref_or_null, index_merge, unique_subquery, index_subquery, range, index, and all. For more information, see the official MySQL documentation.

  If your application frequently resends query requests that specify range and index joins but ApsaraDB RDS processes these query requests at low speeds, a number of SQL statements are parallelized. In this case, your application releases threads at low speeds. As a result, the connections in the connection pool are depleted. This affects all the workloads on your RDS instance.

  You can log on to the ApsaraDB RDS console and choose Monitoring and Alerts in the left-side navigation pane. Then, on the Standard Monitoring tab, you can click Resource Monitoring to view the connections to your RDS instance.

• Solution

  Analyze the index usage and the number of rows that need to be scanned. The analysis is based on the specified query plans. Then, estimate query efficiency, reconstruct SQL statements, and adjust indexes based on the analysis results. This allows you to improve query efficiency. For more information, see SQL optimization.

Troubleshoot slow SQL statements caused by unsuitable parameter settings

• Cause and symptom

  If the settings of the innodb_buffer_pool_instances and join_buffer_size parameters are unsuitable, ApsaraDB RDS executes SQL statements at low speeds.

  You can log on to the ApsaraDB RDS console and choose Parameters in the left-side navigation pane. Then, on the Edit History tab, you can view the reconfiguration histories of these parameters.

• Solution

  Reconfigure these parameters based on your business scenario.

Troubleshoot slow SQL statements caused by the expiration of cached entries

• Cause and symptom

  The system cache can bear a large number of queries. However, Alibaba Cloud does not guarantee a cache hit ratio of 100%. If the cached entries expire, a large number of queries are routed to your RDS instance. This decreases the query performance of your RDS instance.

  You can log on to the ApsaraDB RDS console and choose Monitoring and Alerts in the left-side navigation pane. Then, on the Standard Monitoring tab, you can click Resource Monitoring to view the cache hit ratio, QPS, and TPS of your RDS instance.

• Solution

  Use the thread pool, fast query cache, and automatic SQL throttling features to increase the query performance of your RDS instance. For more information, see Thread Pool, Fast query cache, and Automatic SQL throttling.

Troubleshoot slow SQL statements caused by batch operations

• Cause and symptom

  If a large number of operations are performed to import, delete, and query data, ApsaraDB RDS executes SQL statements at low speeds.

  You can identify slow SQL statements based on disk usage, SQL logs, or slow query statistics. For example, you can check the size of each binary log file. In normal cases, the size per binary log file is 500 MB. If the size of a binary log file exceeds 500 MB, you can check whether exceptions occur.

  You can log on to the ApsaraDB RDS console and choose Monitoring and Alerts in the left-side navigation pane. Then, on the Standard Monitoring tab, you can click Resource Monitoring to view the disk usage and input/output operations per second (IOPS) of your RDS instance. On this tab, you can also click Engine Monitoring to view the TPS of your RDS instance.

  
• Solution

  Perform batch operations during off-peak hours. Otherwise, split each batch operation into multiple requests and separately submit these requests.

Troubleshoot slow SQL statements caused by unclosed transactions

• Cause and symptom

  If a task suddenly slows down, but the CPU utilization and IOPS usage are normal and the number of active sessions continues to increase, some transactions are not closed.

• Solution

  Check for locks that cause conflicts between transactions. Then, terminate the SQL statements that are included in the transactions.

Troubleshoot slow SQL statements caused by scheduled tasks

• Cause and symptom
  If the loads on your RDS instance change regularly over time, scheduled tasks may be configured.

  Note You can view the monitoring information on the Standard Monitoring tab of the Monitoring and Alerts page.
• Solution

  Adjust the time when scheduled tasks are run. We recommend that you run scheduled tasks during off-peak hours.

Summary

Use the recommended features to troubleshoot the issues that cause slow SQL statements. For more information, see the following topics:

• View the resource, engine, and deployment metrics of an ApsaraDB RDS for MySQL instance
• View details about the slow query logs of an ApsaraDB RDS for MySQL instance
• Use the SQL Explorer feature on an ApsaraDB RDS for MySQL instance
• DAS overview