How do I troubleshoot and resolve high CPU utilization on my Amazon RDS for MySQL or Aurora MySQL-Compatible DB instance?

Short description

Several factors can cause increases in CPU use, such as user-initiated heavy workloads, multiple concurrent queries, or long-running transactions.

To identify the source of the CPU use in your DB instance, check the following resources:

• Enhanced Monitoring
• Performance Insights
• Queries that detect the cause of CPU use in the workload
• Logs with activated monitoring

After you identify the source, analyze and optimize your workload to reduce CPU use.

Resolution

Use Enhanced Monitoring

Enhanced Monitoring provides an operating system (OS) level view to identify the cause of a high CPU load. For example, you can review the load average, OS process list, and System (%) or Nice (%) CPU distribution.

Use Enhanced Monitoring to check the loadAverageMinute data in intervals of 1, 5, and 15 minutes. A load average that's greater than the number of vCPUs shows that the instance is under a heavy load. If the load average is less than the number of vCPUs for the DB instance class, then CPU throttling might not cause the application latency. To avoid false positives when you diagnose the cause of CPU usage, check the load average.

For example, you have a DB instance that uses a db.m5.2xlarge instance class and it reaches the CPU limit. The instance class has 8 vCPUs associated with it. A load average that exceeds 170 shows that the machine is under heavy load during the measured time frame:

Load average minute:

• Fifteen: 170.25
• Five: 391.31
• One: 596.74

CPU utilization:

• User (%): 0.71
• System (%): 4.9
• Nice (%): 93.92
• Total (%): 99.97

Note: Amazon RDS gives your workload a higher priority over other tasks that run on the DB instance. To prioritize management-related tasks, workload tasks have a different Nice value. As a result, in Enhanced Monitoring, Nice (%) represents the amount of CPU your workload uses against the database.

After you turn on Enhanced Monitoring, check the OS process list that's associated with the DB instance. Enhanced Monitoring shows a maximum of 100 processes. This list can help you identify the processes that affect CPU and memory performance the most.

In the OS process list section of Enhanced Monitoring, review the OS processes and RDS processes. These metrics can help you confirm whether the OS or RDS processes increase CPU usage. Or, use these metrics to monitor the percentage of CPU the mysqld or aurora processes use. If the Aurora Storage Daemon shows high CPU usage on an Aurora instance, then the instance has a heavy read/write workload. This high CPU usage can also suggest that the instance size might be too small for the current storage volume and workload. Or, there are complex operations occurring in the background.

To see the division of CPU usage, review the metrics for cpuUtilization. For more information, see Monitoring OS metrics with Enhanced Monitoring.

Note: If you activate Performance Schema, then you can map the OS thread ID to the process ID only for your RDS MySQL DB instance. You can't map the OS thread ID to the process ID for your Aurora MySQL DB instance. For more information, see Why does my Amazon RDS DB instance use swap memory when I have sufficient memory?

Use Database Insights

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. Also, see Turning on the Advanced mode of Database Insights for Amazon Aurora.

You can use Database Insights to identify the queries that run on the DB instance, and cause high CPU usage.

First, activate Database Insights on your MySQL instance. Then, use Database Insights to optimize your workload. You can also work with your database administrator to identify the root cause of the issue.

For information about engine, AWS Region, and instance class support, see Aurora DB engine, Region, and instance class support for Database Insights. Also, see Amazon RDS DB engine, Region, and instance class support for Database Insights.

Use queries to detect the cause of CPU utilization in the workload

Before you can optimize your workload, you must identify the problematic query. To identify the root cause of the CPU utilization, run the following queries when the high CPU issue occurs.

To view the threads that run on your MySQL instance, run the SHOW FULL PROCESSLIST command:

SHOW FULL PROCESSLIST;

Note: Run the SHOW PROCESSLIST query as the primary system user. You must have MySQL PROCESS server administration permissions to see all the threads that run on a MySQL instance. Without admin permissions, SHOW PROCESSLIST shows only the threads that are associated with the MySQL account that you use.

Sometimes, the same set of statements might continue to run without completion. When this happens, the subsequent statements must wait for the first set of statements to finish. This is because InnoDB row-level locking might update the same rows. For more information, see SHOW PROCESSLIST statement on the MySQL website.

The INNODB_TRX table provides information about all the InnoDB transactions that run and aren't read-only transactions. To view the INNODB_TRX table, run the following query:

SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;

The INNODB_LOCKS table provides information about locks that an InnoDB transaction requests but doesn't receive. To view the INNODB_LOCKS table, run the following query:

MySQL 5.7 or earlier:

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;

MySQL 8.0:

SELECT * FROM performance_schema.data_locks;

For more information, see MySQL 5.7 section The INFORMATION_SCHEMA.INNODB_LOCKS table and MySQL 8.0 section The data_locks table on the MySQL website.

The INNODB_LOCK_WAITS table provides one or more rows for each blocked InnoDB transaction. To view the INNODB_LOCKS_WAITS table, run the following query.

MySQL 5.7 or earlier:

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;

MySQL 8.0:

SELECT * FROM performance_schema.data_lock_waits;

To see the transactions that are waiting and the transactions that are blocking the waiting transactions, run a query similar to the following example:

MySQL 5.7 or earlier:

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       information_schema.innodb_lock_waits w  
INNER JOIN information_schema.innodb_trx b  
  ON b.trx_id = w.blocking_trx_id  
INNER JOIN information_schema.innodb_trx r  
  ON r.trx_id = w.requesting_trx_id;

MySQL 8.0:

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;

To interpret the output of this query, see MySQL 8.0 section Using InnoDB transaction and locking information on the MySQL website.

To get information from the standard InnoDB monitor about the state of the InnoDB storage engine, run the following query:

SHOW ENGINE INNODB STATUS;

For more information, see MySQL 8.0 section SHOW ENGINE Statement on the MySQL website.

To view the server status, run the following command.

SHOW GLOBAL STATUS;

For more information, see MySQL 8.0 section SHOW STATUS statement on the MySQL website.

To check history list length (HLL), run the following command:

select NAME AS RollbackSegmentHistoryListLength, COUNT from INFORMATION_SCHEMA.INNODB_METRICS where NAME = 'trx_rseg_history_len';

If your workload requires multiple open or long-running transactions, then your database might have a high HLL. Also, if purge threads can't keep up with DB changes, then you might have a high HLL. A high HLL causes higher resource use and slow and inconsistent SELECT statement performance.

On an Aurora MySQL write instance, use the RollbackSegmentHistoryListLength CloudWatch metric to monitor your HLL.

If the instance has high HLL, then review your SQL statement. This issue occurs when you apply START TRANSACTION and there's no COMMIT. Because the thread entered a SLEEP state, you can't see the previous SQL statement.

To resolve this issue, run the following command.

SELECT event_id, current_schema, sql_text, lock_time 
 FROM performance_schema.events_statements_history 
 WHERE thread_id=<thread_id>  
 ORDER BY event_id DESC; 

Analyze logs and turn on monitoring

Analyze the MySQL general query log to view what the mysqld is doing at a specific time. You can also view the queries that are run on your instance at a specific time, such as information about when clients connect or disconnect. For more information, see The general query log on the MySQL website.

Important: When you activate the general query log for long periods, the logs consume storage and can add to performance overhead.

Analyze the MySQL slow query logs to find queries that take longer to run than the seconds that you set for long_query_time. You can also review your workload and analyze your queries to improve performance and memory consumption. For more information, see 7.4.5 The slow query log on the MySQL website.

Note: When you use slow query Log or general query log, it's a best practice to set the parameter log_output to FILE.

Use the MariaDB Audit Plugin to audit database activity on Amazon RDS for MySQL or Amazon RDS for MariaDB. For example, track users that log in to the database or track queries that run against the database.

If you use Aurora MySQL-Compatible, then you can use Advanced Auditing. Advanced Auditing provides more control over the types of queries that you want to log and reduces the overhead for logging.

Use the innodb_print_all_deadlocks parameter to check for deadlocks and resource locking. You can use this parameter to record information about deadlocks in InnoDB user transactions in the MySQL error log. For more information, see innodb_print_all_deadlocks on the MySQL website.

Analyze and optimize the high CPU workload

After you identify the query that increases CPU usage, optimize your workload to reduce the CPU consumption.

If you see a query that's not required for your workload, run the following command to end the connection:

CALL mysql.rds_kill(processID);

Important: When you end Data Manipulation Language (DML) writes on an instance, it rolls back the interrupted transaction. It can take a long time to roll back the updates. If your query runs for a long time, then work with your database administrator to check if you can stop the query.

To find the processID of a query, run the SHOW FULL PROCESSLIST command.

If you don't want to end the query, then use EXPLAIN to optimize the query. EXPLAIN shows the individual steps involved when you run a query. For more information, see Optimizing queries with EXPLAIN on the MySQL website.

To view profile details, activate profiling. The SHOW PROFILE command shows resource usage for statements that run during the current session. For more information, see SHOW PROFILE statement on the MySQL website.

To view and optimize table statistics, use the ANALYZE TABLE query. For more information, see ANALYZE TABLE statement on the MySQL website.

Related information

Tuning Aurora MySQL with wait events

How do I activate and monitor logs for an Amazon RDS for MySQL DB instance?

Amazon CloudWatch Database Insights applied in real scenarios