Database Autonomy Service:Slow query logs

Video introduction

%E6%96%B0%E7%89%88%E6%85%A2%E6%97%A5%E5%BF%97%E4%BB%8B%E7%BB%8D.mp4

Prerequisites

Before you begin, make sure that:

• The database engine is one of the following:

  • ApsaraDB RDS for MySQL, PolarDB for MySQL, MyBase for MySQL, or self-managed MySQL

  • ApsaraDB RDS for SQL Server or MyBase for SQL Server

  • ApsaraDB RDS for PostgreSQL or PolarDB for PostgreSQL

  • Tair (Redis OSS-compatible), or self-managed Redis

  • PolarDB for PostgreSQL (Compatible with Oracle)

  • PolarDB-X 2.0

  • ApsaraDB for MongoDB or self-managed MongoDB

• The target database instance is connected to DAS. For more information, see Connect a database instance to DAS.

How slow query logs work

Slow query logs are generated by the database kernel when a query exceeds the configured execution time threshold. The threshold parameter name and default value vary by engine. For the exact parameter names and configuration steps, see the official documentation for your database engine.

View slow query logs for a single instance

This section uses an ApsaraDB RDS for MySQL database as an example.

1. Log on to the DAS console.

2. In the left navigation pane, click Intelligent O&M Center > Instance Monitoring.

3. Click the ID of the target instance to go to the instance details page.

4. In the left navigation pane, choose Request Analysis > Slow Logs.

5. On the Slow Log Analysis tab, select a time range. The tab displays the following sections: Slow Query Log Trends A chart showing the slow query count over time. Click a point in the chart to filter Slow Query Log Statistics and Slow Query Log Details to that point in time.

   • Click to save the slow log data locally.

   • Click to open OpenAPI Explorer and debug the API with the parameters you selected.

   Event distribution Slow log events within the selected time range. Click an event to view its details. Slow Query Log Statistics Aggregated slow query data grouped by SQL template. Each row shows metrics including execution count, total execution duration, average execution duration, scanned rows, and returned rows. Use the filters above the list to narrow results — available filter options vary by database engine. Slow Query Log Details Individual slow query records. In the Actions column, click Optimize or Throttling to run SQL diagnosis or configure throttling for a specific statement.

   • Click a SQL template ID in the SQL ID column to view correlations and distribution details, including user distribution, client distribution, and metric trends.

   • In the Actions column, click Optimize to run SQL diagnosis. DAS analyzes the statement based on its complexity, the data volume of the related table, and the database workload. Diagnosis may take more than 20 seconds. After it completes, the diagnostics engine returns a diagnosis result, optimization suggestions, and estimated performance improvements. > Note: If a slow SQL statement is too long to display fully, hover over it to view the complete statement in a dialog box.

   • In the Actions column, click Throttling to open the Create Throttling Rule page and configure throttling parameters for the SQL statement. For more information, see SQL throttling.

   • For PolarDB for MySQL instances, the IMCI button appears in the Actions column when all of the following conditions are met: the instance has no In-Memory Column Index (IMCI) node, the Maximum Execution Duration of its slow query log exceeds 20 seconds, and the Maximum Scanned Rows exceeds 200,000. Click IMCI to view the IMCI documentation and improve query performance for complex queries on large datasets.

   The end time must be later than the start time. The maximum time range is 7 days. You can query logs generated within the last month.

View global slow query logs

If you manage multiple database instances, the Global Slow Log Trend page gives you an overview of slow query activity across all applicable instances, plus statistics for the top 10 instances.

1. Log on to the DAS console.

2. In the left navigation pane, go to Intelligent O&M Center > Slow Logs. The page displays the Global Slow Log Trend chart and Top Instance Statistics.

   The end time must be later than the start time. You can query logs generated within the last 14 days.

FAQ

For ApsaraDB RDS for MySQL and PolarDB for MySQL instances, why does the completion time in the slow query log differ from the actual SQL execution time?

This happens when an SQL statement modifies the session time zone. Slow query log timestamps follow this logic: if a database-level time zone is configured, that time zone is used; otherwise, the system time zone applies. Session-level time zone changes made by SQL statements may not be applied correctly to the slow query log record.

Why do I get a NoPermission or Forbidden.RAM error on the new Slow Query Log page when the old page worked?

The new page uses a different set of RAM actions than the old page. Grant the RAM user the following permissions. For instructions, see RAM user authorization.

Required permissions:

Recommended permissions:

What's next

Enable automatic management in DAS to handle slow SQL statements without manual intervention:

• Automatic SQL optimization

• Automatic SQL throttling