Slow SQL queries can degrade database stability. When high database load or performance jitters occur, database administrators (DBAs) or developers first check for slow-running queries. The Database Autonomy Service (DAS) Slow Log Analysis feature collects and analyzes SQL statements that exceed a specified execution time.
Prerequisites
The database engine is PolarDB for MySQL.
PolarDB for MySQL Enterprise Edition single node instances are not supported.
Background
The database kernel generates slow query logs. Configuration parameters and thresholds for slow query logs vary across different database engines. For specific details, see the official documentation for your database engine.
Procedure
Log on to the DAS console.
In the navigation pane on the left, click .
Find the target instance and click the instance ID to open the instance details page.
-
In the left-side navigation pane, click Request Analysis > Slow Logs.
-
On the Slow Log Analysis tab, select a time range to view the Slow Log Trend, Event Distribution, Slow Log Statistics, and Slow Log Details.
-
In the Slow Log Trend chart, you can click a specific point in time to view the Slow Log Statistics and Slow Log Details for that time.
NoteIf an SQL statement is too long to be fully displayed, hover over the statement. The complete statement appears in a tooltip.
-
In the Event Distribution section, you can find slow query log events within the specified time range. Click an event to view its details.
-
From the Node ID drop-down list, you can view the number of slow requests for each node.
-
On the Slow Query Log Statistics and Slow Query Log Details tabs, click
to save the slow query log information to your local computer. -
Click
to go to the OpenAPI Explorer with the current parameters pre-filled for API debugging. -
In the Slow Log Statistics section:
-
Above the list, you can select filter conditions to refine the data. The available filters vary by database engine.
-
Click an ID in the Query ID column of an SQL template to view related information such as user distribution, client distribution, and metric trends, along with a list of details.
-
In the Actions column for the target SQL template, click Optimize. In the SQL Diagnostic Optimization dialog box, view the diagnosis results.
If you accept the suggestion, click Copy in the upper-right corner and paste the optimized SQL into your database client or DMS to execute it. Otherwise, click Cancel.
NoteDAS diagnoses an SQL statement based on its complexity, the data volume of the corresponding tables, and the current database load. This process can take over 20 seconds. The diagnosis engine then provides results, optimization suggestions, and the expected performance improvement. Use this information to decide whether to apply the suggestions.
-
In the Actions column for the target SQL template, click Throttling. On the SQL Throttling page, configure throttling parameters for the target SQL. For detailed instructions, see SQL Throttling.
-
For PolarDB for MySQL database instances, in the Actions column for a target SQL template, click IMCI to view the In-Memory Columnar Index (IMCI) documentation.
Note-
The IMCI button appears if the PolarDB for MySQL instance has no IMCI node, and a slow query has a Max Execution Time over 20 seconds and Max Scanned Rows over 200,000.
-
For complex queries on large datasets, consider using an In-Memory Columnar Index (IMCI) to improve query performance.
-
-
-
In the Slow Log Details section, you can also click Optimize or Throttling in the Actions column for a target SQL statement to perform SQL Diagnostic Optimization or 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.
Related documentation
You can enable the autonomy features of DAS to automatically optimize slow SQL queries on your database instance.