CPU Usage Increase

Scenario 1: CPU Usage Increase Caused by Slow Queries

Cause: A large number of slow SQL queries cause an increase in CPU usage. The slow SQL queries need to be optimized.

Troubleshooting:

View the CPU usage and slow query logs.

• If a large number of slow query logs are generated and the number change is consistent with the CPU usage curve, the CPU usage increase is caused by slow SQL queries.
• If there are a few slow query logs but the number change is basically consistent with the CPU usage curve, check whether the row read rate change is consistent with the CPU curve.

  

  If yes, the CPU usage increase is caused by access to a large amount of row data. Although there are a small number of slow SQL queries, the queries need to access a large amount of row data, causing high average I/O. Therefore, even if the QPS is not high (for example, the website access traffic is not heavy), the CPU usage of the instance is also high.

Solution:

1. View slow query logs generated within the corresponding time period.
2. Pay attention to slow queries with more than one million rows scanned or more than one million rows returned, and slow queries with long lock waiting time.
3. Analyze slow queries or use SQL Diagnosis.
4. Create read replicas and enable Database Proxy to split read and write requests. Read replicas can offload the read pressure from the primary instance, thus improving the database throughput. For details, see Introducing Read/Write Splitting.
5. Analyze live sessions on the database to locate slow SQL statements.
   1. Connect to the database.
   2. Run the show full processlist; command.
   3. Analyze sessions that take a long time to execute and are in the Sending data, Copying to tmp table, Copying to tmp table on disk, Sorting result, or Using filesort state.

Scenario 2: CPU Usage Increase Caused by Increased Connections and QPS

Cause: Increased requests cause an increase in CPU usage.

Troubleshooting:

Check whether the changes of the QPS, active connections, total connections, and CPU usage are consistent.

QPS refers to the number of queries per second. If the QPS and active connections increase at the same time, and the QPS curve matches the CPU usage curve, the CPU usage increase is caused by increased requests, as shown in the following figure.

In this scenario, SQL statements are usually simple and the execution efficiency is high. There is little room for optimization on SQL statements. You need to optimize the database.

Solution:

1. Upgrade the vCPU specifications of your instance because this problem usually occurs in instances with smaller vCPU specifications.
2. Optimize slow queries by referring to Scenario 1: CPU Usage Increase Caused by Slow Queries. If this method is not so helpful, upgrade the vCPU specifications of your instance.
3. Use database and table sharding for tables with a large amount of data to reduce the amount of data accessed in a single query.
4. Create read replicas and enable Database Proxy to split read and write requests. Read replicas can offload the read pressure from the primary instance, thus improving the database throughput. For details, see Introducing Read/Write Splitting.