All Products
Search

This Product

    ApsaraDB RDS:Use the slow query log analysis feature

    Document Center

    ApsaraDB RDS:Use the slow query log analysis feature

    Last Updated:Jun 14, 2024

    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.Slow Log Trend Slow Log Statistics Slow Log Details

      • 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.Slow Log Sample

        • 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 complexity of SQL statements, the amount of data in the table, and the instance workloads. Suggestions may be returned in more than 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 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.SQL Diagnostic Optimization SQL Throttling

    Additional information

    The following items are the key metrics for the slow query log analysis feature:

    • Maximum Scanned Rows: one of the metrics in slow query log statistics. The metric indicates the maximum number of rows that are scanned in slow queries within the query time range.

    • SQL Template: the SQL template that is generated for SQL statements of the same type.

    • Slow SQL Sample: the data that is sampled from the SQL template at regular intervals. Sample statistics cannot cover all slow query logs. For more information, see View details about the slow query logs of an ApsaraDB RDS for MySQL instance.

    • Slow Log Details: the details of the slow queries. The slow query log analysis feature is enabled by default. The SQL statements whose execution duration is longer than the value of the long_query_time parameter are displayed in the ApsaraDB RDS console.

      In ApsaraDB RDS for MySQL, you can enable the log_queries_not_using_indexes parameter to record SQL queries that do not use indexes. For more information, see https://dev.mysql.com/doc/refman/8.0/en/slow-query-log.html.

    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