Three difficult questions
MySQL database users will inevitably encounter the following three problems:
1. Historical problems are difficult to locate
The database generates a 100% CPU alarm at 3:00 am, but there is no slow SQL during this period. How can I continue to find the cause?
2, SQL pressure template is difficult to obtain
Next week will be a big pressure test, DBA can only find business side to collect SQL templates one by one, execution frequency, this way is inefficient, and can not guarantee the correctness;
Not all slow SQL needs first priority optimization. Users urgently need to know which SQL on the database consumes the most resources and optimize these SQL to improve the stability of the instance.
Full SQL diagnostics can quickly solve the above problems.
Introduction to Important Functions
Execution Time Distribution
If the execution time distribution of an instance is closer to blue, the SQL health of the instance is better; if the execution time distribution is closer to orange and red, the SQL health of the instance is worse.
Execution Time Distribution displays the distribution of all SQL execution time within the selected time range. The execution time is divided into seven ranges, calculated every minute:
[0,1]ms ≤ 0ms ≤ execution time ≤ 1ms percentage of SQL execution times
(1,2) ms 1ms < execution time ≤ 2ms percentage of SQL execution times
(2,3) ms 2ms < execution time ≤ 3ms percentage of SQL execution times
(3,10) ms 3ms < execution time ≤ 10ms percentage of SQL execution times
(10,100) ms 10ms < execution time ≤ 100ms percentage of SQL execution times
(0.1,1]s 0.1s < Execution time ≤ 1s Percentage of SQL execution times
>1s 1s < Execution time Percentage of SQL execution times
For example: 2017-10-12 19:15:00 the number of SQL executions that take [0,1]ms accounts for 80%. The proportion of SQL execution times in (3,10]ms is 13.33%; The number of SQL executions that take (0.1,1]s to execute accounts for 6.67%.
Execution Time (SQL RT)
You can view the SQL execution time per minute in the specified time range.
The SQL list
The SQL list shows the SQL text, elapsed time ratio, average execution time, and number of returned rows for each type of SQL in the selected time range.
Time ratio = (SQL execution time of this type
Therefore, the SQL with a higher time-consuming ratio can be simply interpreted as the SQL that occupies more MySQL resources. Optimization of this SQL can obtain a higher revenue ratio, as shown in the following scenario:
For SQL circled in red box, the average time is 67.78ms and the execution times are 43 times, so the time ratio is 67.18%.
Although the average time of SQL circled in yellow box reached 209.99ms, the time ratio was only 4.84% because the execution times were only once.
Therefore, in the case of no change in the business model, optimizing the first SQL can better reduce the resource use of instances and improve the stability of instances.
The SQL subsidiary
Click SQL ID to view the execution plan, performance trend, and SQL sample of this TYPE of SQL.
The premise condition
You have obtained the rights and logged in to the DMS console.
The DMS full SQL diagnosis function does not charge users. However, before using this function, users must enable SQL audit. This function is a charging function and charging standard.
Background information
Currently, only RDS MySQL instances of the cloud database are supported (MySQL 5.7 is not supported).
steps
Log in DMS console – > choose MySQL database – > select “properties” menu – > click “SQL trend” — > click “full amount SQL diagnosis”
Detailed operation steps are as follows:
- After logging in to the DMS console, the interface is as follows:
-
Select the MySQL database and click the Log In Database button to log in.
-
As shown below, in the top navigation bar menu, choose Performance >SQL Trends:
