How do I troubleshoot a slow query and improve its performance in Amazon RDS for MySQL?

7 minute read

I want to troubleshoot a slow query and improve its performance in Amazon Relational Database Service (Amazon RDS) for MySQL.

Short description

In Amazon RDS, the following issues can cause slow query performance:

Workload and resource utilization issues, such as inadequate indexing and inefficient buffer pool use
Inefficient query execution plan
Resource contention
Blocking transactions

To resolve these issues, review Amazon CloudWatch metrics, Performance Insights, Database Insights, and Enhanced Monitoring to identify performance bottlenecks. Then, resolve the bottleneck issue and optimize your query performance.

Resolution

Important: Performance Insights will reach its end of life on June 30, 2026. You can upgrade to the Advanced mode of Database insights before June 30, 2026. If you don't upgrade, then DB clusters that use Performance Insights will default to the Standard mode of Database Insights. Only the Advanced mode of Database Insights will support execution plans and on-demand analysis. If your clusters default to the Standard mode, then you might not be able to use these features on the console. To turn on the Advanced mode, see Turning on the Advanced mode of Database Insights for Amazon RDS and Turning on the Advanced mode of Database Insights for Amazon Aurora.

Note: If you receive errors when you run AWS Command Line Interface (AWS CLI) commands, then see Troubleshooting errors for the AWS CLI. Also, make sure that you're using the most recent AWS CLI version.

Monitor your resource and database performance

To troubleshoot your query performance, review the Amazon CloudWatch metrics to determine the cause of the issue. To determine when a query increases use of a specific resource or decreases DB performance, use the CloudWatch console or AWS CLI to monitor the following metrics:

DatabaseConnections
NetworkReceiveThroughput
WriteThroughput
ReadThroughput
WriteLatency
ReadLatency
WriteIOPS
ReadIOPS
FreeStorageSpace
BurstBalance

If your DB performance is poor, then check the RDS DB instance status for active or scheduled processes that might affect performance. Also, review your Amazon RDS events for events that can affect DB performance.

Review your workload and resource utilization

If your query performance is slow, then review the other queries in your workload to see if they affect your query performance. To identify the queries that you must optimize, you can turn on Advanced mode of Database Insights for Amazon RDS or Amazon Aurora.

If your instance restarts, then your DB instance might lose cached data and can slow query performance. To prevent this cold cache issue, configure the following parameters to accelerate the warming-up buffer pool after a restart:

innodb_buffer_pool_dump_at_shutdown
innodb_buffer_pool_load_at_startup
innodb_buffer_pool_dump_pct

To optimize query performance, it's a best practice to monitor how much your DB instance utilizes the InnoDB buffer pool. For more information, see Buffer pool on the MySQL website. To monitor the status of the InnoDB buffer pool, review the following database counters of Performance Insights:

For the number of logical read requests, review the Innodb_buffer_pool_read_requests counter.
For the number of logical reads that InnoDB can't satisfy from the buffer pool and had to read directly from the disk, review the Innodb_buffer_pool_reads.
Use Innodb_buffer_pool_hit_ratio for the percentage of reads that InnoDB can satisfy from the buffer pool.
Review Innodb_buffer_pool_usage for the percentage of the InnoDB buffer pool that contains data pages.

To identify slow-running queries, you can also turn on slow_query_log in your parameter group, and then publish the logs to CloudWatch Logs.

Optimize your query performance

To optimize your query performance, run the following commands to based on the needs of your query execution plan. For more information, see EXPLAIN output format on the MySQL website.

Use EXPLAIN to optimize your queries

To view details about your query performance and why the query might be delayed, run the EXPLAIN command. For more information, see Optimizing queries with EXPLAIN on the MySQL website.

To determine whether your query uses an index, run the EXPLAIN query. In the EXPLAIN output, review the table names, keys that are in use, and the number of rows that the query scanned. For more information, see EXPLAIN statement on the MySQL website. Review the output, and then take the following actions:

If the output doesn't show the keys in use, then create an index on the columns in the WHERE clause.
If the table has the required indexing, then make sure that the table statistics are current. For more information, see The INFORMATION_SCHEMA STATISTICS table on the MySQL website.

Use ANALYZE TABLE to update your query statistics

If your table statistics aren't current, then the query can have poor performance. To update your query statistics, run the ANALYZE TABLE command. For more information, see ANALYZE TABLE statement on the MySQL website.

Use EXPLAIN ANALYZE to see how your queries allocate time

To determine what part of query execution is slow, run the EXPLAIN ANALYZE query to see how MySQL allocates time on your query. When the query completes, the EXPLAIN ANALYZE query prints the plan and its measurements. For more information, see Obtaining information with EXPLAIN ANALYZE on the MySQL website. You can also use SHOW PROFILE to profile your slower queries and find the status where the session spends the most time. For more information, see SHOW PROFILE statement on the MySQL website.

Use SHOW FULL PROCESSLIST and Enhanced Monitoring to review operations

Run the SHOW FULL PROCESSLIST command to view the list of operations that are performed on the database server. You can also use Enhanced Monitoring to review this list. For more information, see SHOW PROCESSLIST statement on the MySQL website.

Check the history list length

The InnoDB transaction system maintains Multi-Version Concurrency Control (MVCC). If your workload demands multiple open or long-running transactions, then expect a high-history list length on the database. It's a best practice to avoid open or long-running transactions on the database. For more information, see The InnoDB history list length increased significantly.

If you don't monitor your history list length size, then your performance decreases over time. High-history list length can also cause high-resource utilization, slow and inconsistent SELECT performance, and an increase in storage.

Note: Long-running transactions aren't the only cause of history list length spikes. If purge threads can't match changes on the database, then history list length remains high. In extreme cases, you can also experience a database outage.

The SHOW ENGINE INNODB STATUS command shows information about transaction processing, wait events, and deadlocks. For more information see, SHOW ENGINE statement on the MySQL website. Run the SHOW ENGINE INNODB STATUS query to check your history list length:

SHOW ENGINE INNODB STATUS;

Example output:

\------------ TRANSACTIONS ------------Trx id counter 26368570695  Purge done for
 trx's n:o < 26168770192 undo n:o < 0 state: running but idle History list length 1839

To use Performance Insights to check the length of your history list, complete the following steps:

Open Amazon RDS console.
In the navigation pane, choose Performance Insights, and then select the database that you want to view metrics for.
Choose Metrics.
On the Metrics dashboard page, choose Custom dashboard.
Choose Add widget, and then select the Trx Rseg History Len metric.
Choose Add widget.

Note: If Data Manipulation Language (DML) writes cause the history list length to increase, then ask your database administrator to end the write queries.

Resolve blocked queries

If your query runs for an extended amount of time, then a different query might be blocking your query. In MySQL 8.0, you can find lock waits in the performance schema of the data_lock_waits table. For more information, see Using InnoDB transaction and locking information on the MySQL website. Run the following query to identify blocking transactions:

SELECT
  r.trx\_id waiting\_trx\_id,    
  r.trx\_mysql\_thread\_id waiting\_thread,      
  r.trx\_query waiting\_query,    
  b.trx\_id blocking\_trx\_id,    
  b.trx\_mysql\_thread\_id blocking\_thread,    
  b.trx\_query blocking\_query    
FROM       performance\_schema.data\_lock\_waits w    
INNER JOIN information\_schema.innodb\_trx b    
  ON b.trx\_id = w.blocking\_engine\_transaction\_id    
INNER JOIN information\_schema.innodb\_trx r    
  ON r.trx\_id = w.requesting\_engine\_transaction\_id;