ApsaraDB RDS:Use the slow query analysis feature for an ApsaraDB RDS for MySQL instance

Usage notes

If the size of an SQL statement exceeds 8,192 bytes, the SQL statement cannot be recorded.

Procedure

1. Access RDS Instances, select a region at the top, and then click the ID of the target RDS instance.
2. In the left-side navigation pane, choose Autonomy Service > Slow Query Logs.
3. On the Slow Log Analysis tab, specify a time range and view the trends, statistics, and details about the slow SQL queries within the specified time range.
   Note When you select a time range, the end time must be later than the start time, and the interval between the start time and the end time cannot exceed 24 hours. You can query slow query logs that are generated within the last month.

   • You can click a point in time in the trend chart and view the statistics and details about the slow SQL queries at the point in time.
   • You can click Export Slow Log to download slow query logs to your computer.
     Important You can export a maximum of 1,000 slow query logs. If you want to obtain all slow query logs, you can call the Query the details of the slow query logs of an instance operation.
   • On the Slow Log Statistics tab, you can perform the following operations:
     • Find the SQL template that you want to manage and click Sample in the Actions column. In the dialog box that appears, view the details about the slow query logs.
     • Find the SQL template that you want to manage and click Optimize in the Actions column. In the SQL Diagnostic Optimization dialog box, view the SQL diagnostic results.

       If you accept the SQL diagnostic suggestions, click Copy in the upper-right corner and paste the optimized SQL statements to the database client or Data Management (DMS) for execution. If you do not accept the SQL diagnostic suggestions, click OK.

       Note DAS performs SQL diagnostics based on the complexity of SQL statements, the amount of data in the table, and the database load. Suggestions may be returned 20 seconds after the SQL diagnostics is performed. After the diagnostics is complete, the SQL diagnostic engine provides diagnostic results, optimization suggestions, and expected optimization benefits. You can determine whether to accept the suggestions based on the diagnostic results.

       You can also click Database Expert Service to purchase the expert service. Database Expert Service provides value-added professional database services, such as emergency assistance, health diagnostics, performance tuning, security assurance, and data migration.

     • Find the SQL template that you want to manage and click Throttle in the Actions column. In the SQL Throttling dialog box, configure the parameters. For more information, see SQL throttling.
   • On the Slow Log Details tab, find the SQL statement that you want to manage and click Optimize or Throttle in the Actions column. This way, you can also perform SQL diagnostic optimization and SQL throttling.

   

FAQ

Why is the execution completion time of SQL statements recorded in slow query logs different from the actual execution time of the SQL statements?

This issue usually occurs when the time zones recorded in the slow query logs are modified by the executed SQL statements. The execution completion time of an SQL statement varies with the time zone recorded in slow query logs at the following levels: session level, database, level, and system level. If a time zone is specified for the database, the execution completion time of an SQL statement is recorded based on the time zone of the database. Otherwise, the execution completion time of an SQL statement is recorded based on the time zone of the system. If an SQL statement modifies the time zone at the session level, the time zone recorded in slow query logs may not be properly converted.