Slow queries are one of the most common causes of database performance degradation. Database Autonomy Service (DAS) analyzes SQL statements that exceed a configurable execution duration threshold, identifies the root cause, and suggests optimizations—so you can resolve performance issues without manually sifting through raw log files.
Prerequisites
Before you begin, ensure that you have:
An ApsaraDB RDS for MySQL instance or a self-managed MySQL database
(Not supported) ApsaraDB RDS for MySQL Basic Edition instances
How slow query logs work
Slow query logs are generated by the database kernel. The parameters and thresholds that define a slow query vary by database engine.
For ApsaraDB RDS for MySQL, the long_query_time parameter controls the threshold. Any SQL statement whose execution duration exceeds this value is recorded as a slow query log. To modify this parameter, see Modify instance parameters.
Analyze slow query logs
The following steps use an ApsaraDB RDS for MySQL instance as an example.
Log on to the DAS console.
In the left-side navigation pane, choose Intelligent O &M Center > Instance Monitoring.
Find the instance you want to manage and click its instance ID.
In the left-side navigation pane, choose Request Analysis > Slow Logs.
On the Slow Log Analysis tab, set a time range to load the slow query data for that period.
The time range cannot exceed 24 hours, and the end time must be later than the start time. You can query slow query logs generated within the previous month.
The tab displays four sections. Use each section to answer a different type of question:
Section Purpose What it shows Slow Query Log Trends Identify when slow queries occurred A time-series chart of slow query volume. Click any point to see statistics and details for that moment. Event Distribution Understand the breakdown of slow query types A breakdown of slow query events by category. Slow Query Log Statistics Find which SQL templates are causing problems Aggregated statistics by SQL template, with actions to optimize or throttle each template. Slow Query Log Details Inspect individual slow query statements Individual slow query records with per-statement actions. If a SQL statement is too long to display in full, hover over the statement to see the complete text.
To download the slow query logs to your computer, click
.To debug a slow query via the OpenAPI console, click
. This populates the selected and entered parameters into the OpenAPI console.
Work with Slow Query Log Statistics
On the Slow Query Log Statistics tab:
Filter results: Use the filter conditions at the top of the list. Available filters vary by database engine.
Inspect a SQL template: Click the SQL ID to view user distribution, client distribution, and metric trend correlation and detailed list for that template.
Optimize a SQL template: Click Optimize in the Actions column. In the SQL Diagnostic Optimization dialog box, review the diagnostic results.
To apply the suggestions, click Copy in the upper-right corner and paste the optimized SQL into your database client or Data Management (DMS).
To dismiss the suggestions, click Cancel.
SQL diagnostics considers statement complexity, table data volume, and database load. Results may take more than 20 seconds to appear. The diagnostic engine provides results, optimization suggestions, and expected performance gains.
Throttle a SQL template: Click Throttling in the Actions column and configure the parameters in the SQL Throttling dialog box. For details, see SQL throttling.
Enable In-Memory Column Index (IMCI) (PolarDB for MySQL only): If the IMCI button appears in the Actions column, click it to view the IMCI feature documentation. The button appears when all three conditions are met:
No IMCI nodes are purchased for the cluster
The maximum execution duration of the SQL template exceeds 20 seconds
The maximum number of scanned rows exceeds 200,000
IMCI improves performance for complex queries on large datasets.
Work with Slow Query Log Details
On the Slow Query Log Details tab, find the SQL statement you want to manage and click Optimize or Throttling in the Actions column to run SQL diagnostic optimization or apply SQL throttling.
FAQ
Why does the execution completion time in the slow query log differ from the actual completion time?
This is a time zone issue. The time zone used to record a slow query's completion time is resolved in the following order: session level > database level > system level.
If a SQL statement changes the session-level time zone, the completion time recorded in the slow query log reflects that modified time zone—which may not match the time zone of your system or monitoring tools.
What's next
Enable DAS autonomy features to automatically handle slow queries without manual intervention: