ApsaraDB RDS:Slow SQL statements in ApsaraDB RDS for MySQL

SQL exceptions

• Causes and symptoms

  SQL exceptions can be caused by many factors, such as inefficient table schema design, missing indexes, or an excessive number of rows to scan.

  On the SQL Explorer page, you can view the execution duration and frequency of slow SQL statements.

• Solution

  Optimize your SQL statements based on your business requirements. For more information, see SQL optimization.

Instance bottlenecks

• Causes and symptoms

  An instance may reach a performance bottleneck for several reasons:

  • Continuous growth in business volume without scaling up the instance.

  • Hardware aging, which causes performance degradation.

  • A constant increase in data volume and changes in data structures can cause previously fast SQL statements to become slow.

  You can go to the Monitoring and Alerts page in the console, click the Standard Monitoring tab, and check the resource usage of the instance in the Resource Monitoring section. If resource utilization metrics are close to 100%, your instance may have reached a performance bottleneck.

• Solution

  Running a benchmark test is a good way to determine whether your instance has a bottleneck. For example, you can use SysBench to perform a benchmark test. The queries per second (QPS) and transactions per second (TPS) in complex scenarios rarely exceed the benchmark values.

  If you confirm that the instance has reached a bottleneck, we recommend that you upgrade the instance specifications. For more information, see Change instance specifications.

Version upgrades

• Causes and symptoms

  Upgrading an instance version can change the SQL execution plan. Join types in an execution plan are ranked from most to least efficient: system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > all. For more information, see the official MySQL documentation.

  If a SQL request slows down after its join type changes to range or index, and the application repeatedly retries the request, the number of parallel SQL queries can increase. This slows the release of application threads, which can exhaust the connection pool and affect the entire service.

  You can go to the Monitoring and Alerts page in the console, click the Standard Monitoring tab, and check the number of connections for the instance in the Resource Monitoring section.

• Solution

  Analyze the execution plan to check index usage and the number of rows scanned to estimate query efficiency. Reconstruct SQL statements or adjust indexes to improve query performance. For more information, see SQL optimization.

Improper parameter settings

• Causes and symptoms

  Improper settings for parameters such as innodb_buffer_pool_instances and join_buffer_size can degrade performance.

  You can go to the Parameters page in the console and click the Edit History tab to view the parameter modification history of the instance.

• Solution

  Adjust the relevant parameters to suit your business scenario.

Cache stampede

• Causes and symptoms

  A cache can handle a large number of queries, but the cache hit ratio is not guaranteed to be 100%. A cache stampede occurs when multiple cache entries expire simultaneously, routing a large volume of queries to the database and causing performance degradation.

  You can go to the Monitoring and Alerts page in the console, click the Standard Monitoring tab, and check metrics such as the cache hit ratio, QPS, and TPS in the Engine Monitoring section.

• Solution

  Use features such as Thread Pool, Fast Query Cache, and Automatic SQL throttling to improve performance.

Batch operations

• Causes and symptoms

  Large-scale data import, deletion, or query operations can slow the execution of SQL statements.

  You can identify the corresponding statements by checking disk space, using SQL Explorer and Audit, or reviewing slow query logs. For example, check the size of the binary log (binlog) files. A single binlog file is typically 500 MB. If a file exceeds this size, check for anomalies.

  You can also go to the Monitoring and Alerts page in the console, click the Standard Monitoring tab, and check metrics such as disk space, IOPS, and transactions in the Resource Monitoring and Engine Monitoring sections.

  In this example, the File_size of a mysql-bin file reaches 4,297,859,854 (approximately 4 GB), which is significantly beyond the normal range and indicates an anomaly.

• Solution

  Perform large-scale operations during off-peak hours, or split them into smaller batches to be executed sequentially.

Unclosed transactions

• Causes and symptoms

  An unclosed transaction is often the cause if a task suddenly slows down while CPU and IOPS utilization remain low and the number of active sessions continuously increases.

• Solution

  Inspect the locks that are causing transaction conflicts and terminate the corresponding SQL statements.

Scheduled tasks

• Causes and symptoms

  If the instance load shows a regular pattern of changes over time, a scheduled task might be the cause.

  Note

  You can view the relevant monitoring information on the Standard Monitoring tab of the Monitoring and Alerts page.

• Solution

  Adjust the execution time of the scheduled task. We recommend running it during off-peak hours.

Summary

You can identify slow SQL statements on an ApsaraDB RDS instance by using the following methods:

• View monitoring information

• View slow query log details

• Use SQL Explorer and Audit

These ApsaraDB RDS features help you quickly identify and automatically resolve issues caused by slow SQL statements.