Why is my query running slow in Amazon RDS for MySQL?

Last updated: 2021-10-27

I'm trying to troubleshoot slow running queries in Amazon Relational Database Service (Amazon RDS) for MySQL. Why is this happening and how can I improve query performance?

Short description

To improve query performance, consider the following factors:

  • Resource utilization (such as CPU, memory, and storage)
  • Workload analysis
  • Query tuning and monitoring

Resolution

Resource utilization (such as CPU, memory, and storage)

To understand the root cause of any database performance issues, check all the server-wide resources that your instance is using. You can monitor your workload and survey when the query performance was normal compared to when the query began to take too long to run.

Use Amazon CloudWatch metrics to monitor these resources over a period of time that include days when performance was considered normal. You can also view performance metrics in the Amazon RDS console to monitor database performance.

You can also check your instance status to identify any other active or scheduled processes that might be impacting database performance. In the Amazon RDS console, check the events that occurred while your database was performing poorly.

Workload analysis

To analyze the workload contributing to resource consumption, use Performance Insights. Performance Insights will provide a graphic analysis of all your queries and any waits that are contributing to increased resource consumption.

Performance Insights uses workload as its main metric instead of using the number of vCPUs for an instance. If your current workload exceeds the vCPU limit, then your server is overloaded. If your server is overloaded, check the queries that are contributing to your workload and identify ways to optimize your queries. Then, consider modifying your instance class.

Your Performance Insights workload can also be broken down into wait events. Investigate the top resource consuming waits by slicing the DB load by the number of wait events. The thicker color bands in the load chart indicate the wait types contributing the most to the workload. For more information, see Monitoring DB load with Performance Insights on Amazon RDS.

You can also use the slow query log (enabled in your custom parameter group) to identify slow running queries.

You can then use the Amazon CloudWatch metrics to check if the amount of work done on your instance has increased. For example:

  • Database connections: The number of client sessions that are connected to the DB instance.
  • Network receive throughput (MB/second): The rate of network traffic to and from the DB instance.
  • Write and read throughput: The average number of megabytes read from or written to disk per second.
  • Write and read latency: The average time for a read or write operation in milliseconds.
  • IOPS (read and write): The average number of disk read or write operations per second.
  • Free storage space (MB): The amount of disk space not currently used by the DB instance.

The latency metrics indicate the amount of time taken to complete read or write disk I/O operation. The correlation of the latency metrics with either increased database connections or throughput metrics could indicate workload as the reason for slow query execution. For more information about identifying usage factors, see How do I view what is using storage in an Amazon RDS DB instance that's running MySQL?

You can also use Enhanced Monitoring to retrieve the list of operating systems involved in your workload and the underlying system metrics. By default, the monitoring interval for Enhanced Monitoring is 60 seconds. It's a best practice to set this to 1-5 second intervals for more granular data points.

Query optimization

After the long-running query has been identified from a slow query log or Performance Insights, consider ways to improve your query performance. To tune a query, consider the following approaches:

  • To find the states where the most time is spent, profile your slower queries. For more information, see SHOW PROFILE statement on the MySQL website.
  • Run the SHOW FULL PROCESSLIST command along with Enhanced Monitoring. When used together, you can review the list of operations that are currently being performed on the database server.
  • Use the SHOW ENGINE INNODB STATUS command to obtain nformation about transaction processing, waits, and deadlocks.
  • Find any blocking queries and resolve the blocking. For more information, see Why was a query to my Amazon RDS for MySQL DB instance blocked when there is no other active session?
  • Publish MySQL logs to Amazon CloudWatch. Logs are rotated every hour to maintain the 2% of the allocated storage space threshold. They are then purged if they're more than two weeks old or if their combined size exceeds the 2% threshold.
  • Set an Amazon CloudWatch alarm so that you can monitor your resource usage and be alerted whenever thresholds are exceeded.
  • Find the execution plan for the query and check whether the query is using appropriate indexes. You can optimize your query using the EXPLAIN plan and review details about how MySQL runs the query.
  • Keep your query statistics updated with the ANALYZE table statement. Query optimizers can sometimes choose poor execution plans because of outdated statistics. This can lead to poor performance of a query because of inaccurate cardinality estimates of both table and indexes.
  • MySQL 8.0 now uses an EXPLAIN ANALYZE statement. The EXPLAIN ANALYZE statement is a profiling tool for your queries that shows you where MySQL spends time on your query and why. With EXPLAIN ANALYZE, MySQL plans, instruments, and runs the query while counting rows and measuring the time spent at various points of the execution plan. When the query completes, EXPLAIN ANALYZE will print the plan and its measurements instead of the query result.
  • In MySQL version 8, be aware that lock waits are listed in the Performance Schema of the data_lock_waits table. For example:
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;

For more information, see Using InnoDB transaction and locking information on the MySQL website.