Application scenario

Slow SQL Report: Historical slow SQL trend analysis, for optimization of the system's overall performance

Slow SQL Real-time Diagnosis: Real-time slow SQL diagnosis and analysis, for quickly locating the system's performance problems in progress.

Operating Steps

1. Log in to cloud database RDS management console
2. Find the "Product Service"-"Database"-"Cloud Database RDS" on the left-hand Navbar.
3. At the top left corner of the page, select current region of the instance.
4. Find the target instance, and single-click to instance ID
5. Single click "Log Management" on the Navbar below instance ID
6. On the "Log Management" page, you can see "Slow SQL Report Form" and "Slow SQL Real-time Diagnosis".

Introduction of Features

Both "Slow SQL Report Form" and "Slow SQL Real-time Diagnosis" are shown by instance dimension, and the master instance and read-only instance are shown separately.

Slow SQL Report

"Slow SQL Report" shows slow SQLs that are summed up by day granularity before 0:00 o'clock on the current day. ""In the last week" or "In the last month" are optional, and the time window is up to "In the last three months", but "In the last week" is the default value.

Trend chart: The horizontal axis denotes time by day granularity, and the vertical axis denotes the number of slow SQLs.

They are shown as follows:

When you click the daily "Slow SQL" dots, the system shows "Slow SQL Statistics" information of the current day by default. Or, you can click "Download Details" to get the Slow SQL details on the current day.

Of which, in "Slow SQL Statistics", slow SQLs are sorted in reverse order by execution times. In "Download Details", slow SQLs are sorted by execution time.

Slow SQL real-time diagnosis

"Slow SQL Real-time Diagnosis" shows the slow SQLs that are summed up by minute granularity after 0:00 o'clock on the current day. For the display effect, the time window is up to "In 3 Hours", and "In 3 Hours" is the default value.

Trend chart: The horizontal axis denotes the time by minute granularity. The vertical axis on the left-hand side denotes the number of slow SQLs. The vertical axis on the right-hand side denotes the CPU usage rate. Of which, the CPU usage rate is plotted in a curve, and numbers of slow SQLs constitute a column chart with a time interval of 5 minutes.

which are shown as follows:

When you click "Slow SQL" column chart, "Slow SQL Statistics" of the last 5 minutes is shown on the lower part by default. You may also switch to view "Slow SQL Details". You can click "Slow SQL Statistics" SQL template and "Slow SQL Details" SQL statement, respectively, to view the complete SQL.

Of which, in "Slow SQL Statistics", slow SQLs are sorted in reverse order by execution times. In "Slow SQL Details", slow SQLs are sorted by execution time.

Slow SQL Statistics

Slow SQL Details:

Best Practice

Example: How to carry out the location using the Slow SQL feature when the current system responds slowly?

1. Slow system response is in progress, and you need to make an analysis using the "Slow SQL Real-time Diagnosis" feature.

2. In "Slow SQL Statistics Information", particular focus should be given to Top SQLs. Usually, SQL does not slow down without reasons, and you need to make analysis according to execution times, average/maximum execution time, average/maximum number of rows returned, and average/maximum number of rows scanned, and the frequent cases are as follows:

   (1) SQL's number of rows scanned is far more than the number of rows returned: it is probable that SQL fails to make reasonable use of an index.
   (2) SQL execution times are many, but both number of rows scanned, and number of rows returned are small: it is because the oversized concurrency leads to SQL pileup and then the entire system's slow response.
   (3) both SQL's number of rows scanned and number of rows returned are large: it is because SQL itself has no large optimization space, and you need to perform optimization at the business layer. (4) Oversize service traffic results in slow system response: if the write traffic is relatively large, you may take advantage of Baidu AI Cloud Cloud Database DRDS , in which you can improve the entire cluster's read ability and storage capacity using DRDS's shard-based expansion feature. If the read traffic is relatively large, you may take advantage of Baidu AI Cloud Read-only Instance and Proxy Instance, in which you can alleviate the master database's read traffic pressure using the proxy instance's read/write splitting and load balancing features. Also, you can set the "Monitoring and Alarm" for slow queries and system resources on BCM. If the system's performance pressure is sensed, the system makes timely analysis via "Slow SQL Real-time Diagnosis".

3. You can view detailed SQL by clicking "Slow SQL Details".