High CPU utilization on an ApsaraDB RDS for MySQL instance typically causes slow query responses, connection failures, and application errors. In more than 95% of cases, the root cause is unoptimized SQL statements that generate excessive logical reads or physical reads. CloudDBA analyzes slow SQL and provides actionable optimization suggestions to bring CPU utilization back to normal levels.
Row lock conflicts, row lock waiting, and background tasks can also cause high CPU utilization, but these cases are rare and are not covered here.
How unoptimized SQL drives CPU utilization
When a query runs, the database engine reads data in blocks. One MySQL data block is 8 kB. Each block read from memory is a logical read; each block read from disk because it is not cached in memory is a physical read. Unoptimized SQL—particularly statements that perform a full table scan or lack an appropriate index—can cause thousands or millions of block reads per execution, consuming large amounts of CPU and I/O resources.
Use SQL diagnostics to identify slow SQL statements
CloudDBA's SQL diagnostics feature surfaces the slow SQL statements in your instance and shows optimization suggestions for each one.
Log in to the RDS console and select the region where your instance is located.
Click the instance ID to go to the basic information page.
In the left navigation pane, choose slow SQL queries. The slow SQL page appears.
Select a time range and click confirm.
Only slow SQL data from the last month is available.
If slow SQL exists, the chart shows the time points and count of slow SQL statements. Click a time point to view the statements generated at that time.
Review the slow log details. Focus on the scanned rows and response line values. A statement that scans a large number of rows but returns zero rows indicates excessive logical reads and physical reads, which directly drives up CPU utilization and IOPS.
Click an SQL statement to open its details.
Click SQL optimization to view CloudDBA's optimization suggestions. A common finding is a missing index that forces a full table scan. In addition, according to the data update mechanism of MySQL, the entire table is locked each time the statement is executed, causing all concurrent sessions running the same statement to queue—leading to CPU utilization spikes that can reach 100%.
Apply the optimization suggestions. After the SQL statements are optimized, CPU utilization should return to normal.
Use SQL statistics to identify high-impact SQL statements
For a broader view of SQL activity—including high-frequency statements that individually appear fast but collectively consume significant CPU—use the SQL statistics feature in Database Autonomy Service (DAS).
For instructions, see the SQL statistics usage guide.
SQL Audit is billed separately. To save costs, enable SQL Audit before you start troubleshooting and disable it when you are done.
Before performing high-risk operations
Before modifying instance configurations or data:
Verify the disaster recovery and fault tolerance capabilities of your instances to confirm data security.
Create a snapshot or enable RDS log backup before applying changes to Elastic Compute Service (ECS) or ApsaraDB RDS instances.
If you have entered sensitive credentials such as a logon account or password in the Alibaba Cloud Management Console, update those credentials promptly.
Application scope
ApsaraDB for RDS