Causes
When an application submits query or modification requests, the system performs a large number of logical read operations, which indicates high logical I/O on a large number of rows in the target data table. Therefore, the system consumes a large number of CPU resources to maintain the consistency of data that is read from storage to memory.
A large number of row lock conflicts, row lock waits, or backend tasks may also cause high CPU utilization of instances. However, the probability of these events is low, and these events are not discussed in this article.
Solutions
The following descriptions use a simplified model to illustrate the relationship among system resources, query execution costs, and queries per second (QPS).
- Condition: The application configuration does not change.
- avg_lgc_io: the average logical I/O that is required to execute each query.
- total_lgc_io: the total logical I/O that can be processed by using the CPU resources of an instance per unit of time.
- Relationship formula: total_lgc_io = avg_lgc_io × QPS, that is, CPU resources consumed per unit of time = Average execution cost for each query × Number of queries per unit of time
You can use Data Management (DMS) to fix the issue of high CPU utilization of RDS for MySQL instances.The following sections use DMS as an example.
DMS provides the following functions to troubleshoot instance performance issues: - Instance diagnostics reports
- SQL windows, where you can query optimization suggestions and view execution plans
- Instance session management
The instance diagnostics reports function is optimal for troubleshooting the performance issues of RDS for MySQL instances. If a performance issue occurs, we recommend that you first view the instance diagnostics report, particularly the SQL optimization suggestions, sessions, and slow query logs in the report.
General guidelines to avoid 100% CPU utilization
- Set CPU utilization alerts to help you monitor CPU utilization and plan workloads accordingly.
- During application design and development, optimize MySQL queries based on general rules and techniques to reduce the logical I/O of queries and improve application scalability.
- Before you launch a new function or module, use the production data for stress testing. In this situation, you can use YiSu Cloud Performance Testing (PTS).
- Before you launch a new function or module, we recommend that you use the production data for regression testing.
- We recommend that you use DMS diagnostics reports.