Problem description
When you use apsaradb RDS for MySQL, system exceptions may occur frequently due to high CPU usage, such as slow response, connection failures, and errors.
Cause
In scenarios where the CPU usage is too high, more than 95% of the problems are caused by abnormal SQL statements. When the SQL statement submitted is not optimized enough, it will affect the performance of the database as follows. In addition, a large number of row lock conflicts, row lock waiting, or background tasks may also cause the instance CPU usage to be too high, but the probability of occurrence is very low, which is not discussed in this article.
- The database generates a large number of logical reads, resulting in high CPU usage.
- Causes a large number of physical reads to the database, resulting in high IOPS and I/O latency.
Description: For more information about logical reads and physical reads, see more information.
Solution
Alibaba Cloud reminds you that:
- Before you perform operations that may cause risks, such as modifying instance configurations or data, we recommend that you check the disaster recovery and fault tolerance capabilities of the instances to ensure data security.
- You can modify the configurations and data of instances including but not limited to Elastic Compute Service (ECS) and Relational Database Service (RDS) instances. Before the modification, we recommend that you create snapshots or enable RDS log backup.
- If you have authorized or submitted sensitive information such as the logon account and password in the Alibaba Cloud Management Console, we recommend that you modify such information in a timely manner.
This topic describes how to use CloudDBA to locate slow and abnormal SQL statements. You can then use suggestions from CloudDBA to optimize these SQL statements, reducing the CPU usage of the instance and improving system efficiency.
Use SQL diagnostics to troubleshoot abnormal SQL statements
- Login RDS console. Select the region where the destination instance is located.
- Click the ID of the instance to go to the basic information page.
- In the left-side navigation pane, choose slow SQL queries. The slow SQL page appears.
- Select the time to query, and then click confirm.
Description: Currently, only slow SQL data of the last month is supported.
- If you have slow SQL in the instance, the icon displays the time points and the number of slow SQL generated. Click a time point in the figure.
- How to analyze slow SQL statements. Focus on the values of response line and scanned rows in the slow log details. Each SQL statement scans many rows, but the number of returned rows is 0. This indicates that a large number of logical and physical reads are generated. Physical reads are generated because the memory size is limited and it is not possible to cache all data. If there are a large number of data requests, a large number of physical I/O requests are bound to be generated. A large amount of logic reading consumes a large amount of CPU resources, which leads to a high CPU usage.
- Click an SQL statement to view details of the SQL statement.
- Click SQL optimization to view CloudDBA's optimization suggestions for the SQL statement. The following analysis shows that an index is missing in the SQL statement, leading to 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, thus making the problem even more serious. All sessions that execute this statement will be queued and waiting, and the single execution cost is extremely high, so it is easy to cause the CPU usage to be high or even reach 100%.
- Optimize abnormal SQL statements based on the optimization suggestions. The problem of high CPU usage will be resolved accordingly.
Use SQL statistics to troubleshoot SQL statements
For more information about how to troubleshoot SQL exceptions by using the SQL statistics feature, see usage.
Attention SQL audit must be charged separately. To save costs, you can enable SQL audit before viewing SQL statements in databases, and turn off this feature after troubleshooting is completed.
More information
The data in a database is measured in blocks. One MySQL block is 8kB, and one logical read or physical read corresponds to one data block. When the database executes business query statements (including data modification operations), the CPU first requests data blocks from the memory. If the corresponding data exists in the memory, the CPU returns the result to you after running the computing task. If no data is found in the memory, the system triggers the read operation. The two data acquisition processes are logical read and physical Read respectively.
Application scope
- ApsaraDB for RDS