Database Autonomy Service (DAS) provides the log analysis feature for slow queries. You can use this feature to view slow query trends, information about slow query executions, and suggestions on how to optimize slow queries. This topic provides an example on how to use the slow query log analysis feature. In this example, an ApsaraDB RDS for MySQL instance is used.

Prerequisites

  • The database instance that you want to manage is of one of the following types:
    • ApsaraDB RDS for MySQL
    • ApsaraDB RDS for SQL Server
    • ApsaraDB RDS for PostgreSQL
    • PolarDB for MySQL
    • PolarDB for PostgreSQL
    • PolarDB for Oracle
    • PolarDB-X 2.0
    • ApsaraDB for Redis
    • MongoDB
  • The database instance is connected to DAS. For more information, see Access instances.

View the slow query log statistics of a single database instance

  1. Log on to the DAS console.
  2. In the left-side navigation pane, click Instance Monitoring.
  3. On the page that appears, click the ID of the database instance that you want to manage. The instance details page appears.
  4. In the left-side navigation pane, choose Request Analysis > Slow Logs.
  5. On the Slow Log Analysis tab, specify a time range and view the trends, statistics, and details of the slow 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 generated within the last month.
    • You can click a time point in the trend chart and view the statistics and details of the slow queries at the time point.
    • Click Export Slow Log to download slow query logs to your computer.
    • On the Slow Log Statistics tab, perform the following operations:
      • Click Sample in the Actions column corresponding to the SQL template that you want to manage. In the Slow Log Sample dialog box, view the details of the slow query logs.
      • Click Optimize in the Actions column corresponding to the SQL template. In the SQL Diagnostic Optimization dialog box, view the SQL diagnosis results.

        If you adopt 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 in more than 20 seconds after the SQL diagnostics is performed. After the diagnostics is complete, the SQL diagnostic engine provides diagnosis results, optimization suggestions, and expected optimization benefits. You can determine whether to accept the suggestions based on the diagnosis 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 solutions, health diagnostics, performance optimization, security assurance, and data migration.

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

View the global trends of slow query logs

If you have multiple database instances, you can view the global trends of slow query logs for the database instances and the statistics of slow query logs for the top 10 database instances on the Global Slow Log Trend page.

Note DAS provides the global trends of slow query logs only for self-managed MongoDB databases, ApsaraDB RDS for MySQL instances, self-managed MySQL databases, ApsaraDB for Redis instances, and self-managed Redis databases.
  1. Log on to the DAS console.
  2. In the left-side navigation pane, choose Request Analysis > Slow Logs. On the page that appears, you can view the statistics on the Global Slow Log Trend and Top Instance Statistics tabs.
    Note When you select a time range, the end time must be later than the start time. You can query slow query logs within up to 14 days.

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 on ApsaraDB RDS for MySQL instances or PolarDB for MySQL clusters?

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.