All Products
Search
Document Center

ApsaraDB RDS:Use the slow query log analysis feature

Last Updated:Dec 05, 2023

Slow SQL queries greatly affect the stability of your ApsaraDB RDS for MySQL instance. If issues such as high load and performance jitters occur on your RDS instance, the administrator or developer personnel first checks whether slow SQL queries are being run. Database Autonomy Service (DAS) provides the slow query log analysis feature. The feature collects statistics on and analyzes the slow SQL queries that are run in your RDS instance, and locates performance issues and provides solutions to the issues. This helps improve system stability and reliability.

Prerequisites

The RDS instance runs MySQL.

Note

The RDS instance does not run RDS Basic Edition.

Usage notes

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

Procedure

  1. Go to the Instances page. In the top navigation bar, select the region in which the RDS instance resides. Then, find the RDS instance and click the ID of the instance.
  2. In the left-side navigation pane, choose Autonomy Services > Slow Query Logs.

  3. On the Slow Log Analysis tab, specify a time range and view the Slow Log Trend, Slow Log Statistics, and Slow Log Details of the slow query logs within the specified time range.

    Note

    When you specify the 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 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.

    • Click Export Slow Log to download slow query logs.

      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 DescribeSlowLogRecords operation.

    • On the Slow Log Statistics tab, perform the following steps:

      • Click Sample in the Actions column of the SQL template that you want to manage to view the details of the slow query log.

      • 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 optimization 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 optimization suggestions, click Cancel.

        Note

        DAS performs SQL diagnostics based on the level of complexity of SQL statements, the amount of data in the table, and the database load. Suggestions may be returned in more than 20 seconds after the SQL diagnostics is performed. After the diagnostics is complete, the SQL diagnostics engine provides diagnostics results, optimization suggestions, and expected optimization benefits. You can determine whether to accept the suggestions based on the diagnostics 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 Throttling 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 Throttling in the Actions column to perform SQL diagnostic optimization or SQL throttling.

FAQ

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

A: 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.

References

If slow SQL queries are detected on your RDS instance, you can enable the autonomy features of DAS to implement automatic optimization.