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

  • DAS provides the slow query log analysis feature only for the following types of database instances:
    • 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
    • Redis
    • MongoDB
  • A 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 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 within a 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.
    • You can click Export Slow Log to download slow query logs.
    • On the Slow Log Statistics tab, perform the following steps:
      • Click Sample in the Actions column corresponding to the SQL template to view the slow query log sample of the SQL template.
      • 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 accept the SQL diagnosis 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 diagnosis 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 are performed. After the diagnostics are 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 assistance, health diagnostics, performance optimization, security assurance, and data migration.

      • Click Throttling in the Actions column corresponding to the SQL template. In the SQL Throttling dialog box, configure throttling parameters to throttle the SQL statement. For more information, see SQL throttling.
    • On the Slow Log Details tab, click Optimize and Throttling in the Actions column corresponding to the SQL template to perform SQL Diagnostic Optimization and SQL Throttling on the SQL template.
    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, and the interval between the start time and the end time cannot exceed 7 days. You can query slow query logs within 14 days.