How do I troubleshoot a query that runs slow and improve my query performance in Amazon RDS for MySQL?
I want to troubleshoot a query that runs slow and improve my query performance in Amazon Relational Database Service (Amazon RDS) for MySQL.
Resolution
To troubleshoot a query that runs slow and improve your query performance, complete the following steps:
Check your resource utilization
To monitor your resource utilization and identify when your query performance decreases, take the following actions:
- To monitor your resources over a specified time period, use Amazon CloudWatch metrics.
- To monitor your database performance, use the Amazon RDS console to view your performance metrics.
- To identify active or scheduled processes that might impact your database performance, check your instance status. Make sure that you check the Amazon RDS events that occurred when your database performance was poor.
Check the workload that contributes to resource consumption
To check the workload that contributes to resource consumption, use Performance Insights on Amazon RDS. If your current workload exceeds the vCPU limit, then your server is overloaded. If your server is overloaded, then check the queries that contribute to your workload and optimize your queries. Then, modify your instance class.
To investigate the resource that consumes the most wait events, slice the DB load by the number of wait events. Thicker color bands on the load chart indicate the wait types that contribute the most to your workload. For more information, see Monitoring DB load with Performance Insights on Amazon RDS.
To identify slow queries, turn on slow_query_log in your parameter group. To check if your instance workload has increased, use CloudWatch metrics to check the following:
- 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 the disk per second.
- Write and read latency: The average time for a read or write operation in milliseconds. Correlation of latency metrics with either increased database connections or throughput metrics might indicate the workload as the cause of slow queries. For more information, see How do I troubleshoot an RDS for MySQL or MariaDB instance that shows storage full?
- IOPS (read and write): The average number of disk read or write operations per second.
- Free storage space (MB): The amount of disk space that's not currently used by the DB instance.
- Burst Balance(%) - The percent of General Purpose SSD (gp2) burst-bucket I/O credits available
To view the list of operating systems (OS) in your workload and system metrics, use Enhanced Monitoring. By default, the interval for Enhanced Monitoring is 60 seconds. It's a best practice to set the interval between 1 and 5 seconds for more detailed data points.
Check if your queries have an index or does full table scans
If your query has an index or does full table scans, then your query runs slow.
To check if your query uses an index, run the EXPLAIN query. For more information, see EXPLAIN Statement on the MySQL website. In the EXPLAIN output, check the table names, the keys used, and the number of rows that are scanned for the query. If the output doesn't show any keys in use, then create an index on the columns used in the WHERE clause. If the table has the indexing required, then check that the table statistics are up to date. For more information, see The INFORMATION_SCHEMA STATISTICS Table on the MySQL website. When the statistics are up to date, the query optimizer uses the most selective indexes with the correct cardinality. As a result, your query performance improves.
Check the history list length
InnoDB uses multi-version concurrency control (MVCC). MVCC maintains multiple copies of the same record to preserve read consistency. The history list length is the total number of undo logs that contain modifications in the history list. When there's a long running transaction that writes or reads data, the history list length increases until the transaction completes or rolls back. Also, history list length increases while other transactions modify the tables that are used by the long running transaction.
If your workload demands multiple open or long running transactions, then expect a high history list length on the database. If you don't monitor your history list length size, then performance can decrease over time. High history list length can also cause high resource consumption, slow and inconsistent SELECT statement performance, and a storage increase.
Note: Long running transactions aren't the only cause of history list length spikes. If purge threads can't keep up with changes on the DB, then history list length remains high. In extreme cases, you can also experience a database outage.
To check your history list length, run the following command:
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
If Performance Insights is turned on for your Amazon RDS for MySQL instances, then complete the following steps to check the RollbackSegmentHistoryListLength:
- Navigate to the writer Performance Insight.
- Select Manage metrics, and then select Database metrics.
- Select the trx_rseg_history_len metric, and then select Update graph.
To resolve issues that cause history list length to increase, take the following actions:
- If DML writes cause history list length increases, then cancel or stop the write statements. This involves a rollback of the interrupted transaction and takes a significant amount of time to roll back the updates.
- If a READ causes history list length increases, then use mysql.rds_kill_query to stop the query.
- If your query runs for a long time, then work with your DBA to check if you can stop the query.
Note: It's a best practice to avoid open or long running transactions on the database and to commit your data in small batches.
Improve your query performance
To improve your query performance, use the following best practices:
-
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 performed on the database server.
-
Use the SHOW ENGINE INNODB STATUS command to obtain information about transaction processing, wait events, and deadlocks.
-
Find and resolve blocking queries. 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 CloudWatch Logs. Logs rotate every hour to maintain the 2% of the allocated storage space threshold. Logs that are more than two weeks old or have a combined size that exceeds the 2% threshold are removed.
-
To monitor your resource usage and be alerted when thresholds are exceeded, set a CloudWatch alarm.
-
Locate the query operation plan and check if your query uses the appropriate indexes. Use EXPLAIN to optimize your query and view details about how MySQL runs the query. For more information, see Optimizing Queries with EXPLAIN on the MySQL website.
-
Keep your query statistics updated with ANALYZE TABLE. For more information, see ANALYZE TABLE Statement on the MySQL website.
-
MySQL 8.0 uses EXPLAIN ANALYZE. The EXPLAIN ANALYZE statement shows where MySQL allocates times on your query and why that time is allocated. When the query completes, EXPLAIN ANALYZE prints the plan and its measurements. For more information, see Obtaining Information with EXPLAIN ANALYZE on the MySQL website.
-
In MySQL 8.0, lock waits are listed in the performance schema of the data_lock_waits table. For more information, see Using InnoDB Transaction and Locking Information on the MySQL website.
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;
Relevant content
- asked 4 years agolg...
- asked 9 months agolg...
- asked 7 months agolg...
- asked 2 years agolg...
- asked 10 months agolg...
- AWS OFFICIALUpdated a year ago
- AWS OFFICIALUpdated 3 months ago
- AWS OFFICIALUpdated a year ago