Slow SQL queries can significantly affect database stability. When a database experiences issues such as high workloads or performance jitters, the first step for database administrators (DBAs) or developers is to check for slow queries. Database Autonomy Service (DAS) provides a slow query log analysis feature that collects and analyzes SQL statements whose execution time exceeds a specified threshold. This feature helps you quickly identify and resolve database performance issues to improve system stability and reliability.
Background information
The database kernel generates slow query logs. Configuration parameters and thresholds vary by database engine. For 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 .
-
Select a time range to view the Slow Log Trend, Event Distribution, Slow Log Statistics, and Slow Log Details.
-
In the Nodes section, view the number of slow requests for each node.
-
In the Event Distribution section, you can query slow query log events within the specified time range. Click an event to view its details.
-
In the Slow Log Trend chart, select a point in time to view the Slow Log Statistics and Slow Log Details for that time.
NoteIf a slow SQL statement is too long to be fully displayed, you can hover over the statement to view its full content in a tooltip.
-
On the Slow Query Log Statistics and Slow Query Log Details tabs, click
to save the slow query logs locally. -
Click
to open the OpenAPI Console with the current parameters for API debugging. -
In the Slow Log Statistics section:
-
Above the list, you can select filters to narrow the results. The available filters vary by database engine.
-
Click a data ID in the Query ID column for a target SQL template to view its correlation analysis and details, including user distribution, client distribution, and metric trends.
-
In the Actions column for the target SQL template, click Optimize. In the SQL Diagnostic Optimization dialog box that appears, view the SQL diagnosis results.
If you accept the suggestion, click Copy in the upper-right corner and paste the optimized SQL into a database client or DMS to execute it. Otherwise, click Cancel.
NoteDAS performs SQL diagnosis based on factors such as SQL complexity, the data volume of the corresponding tables, and the database workload. The diagnosis may take over 20 seconds. After the diagnosis is complete, the SQL diagnosis engine provides the results, optimization suggestions, and expected performance gains. You can then decide whether to accept the suggestions.
-
In the Actions column for the target SQL template, click Throttling. On the SQL Throttling page, configure parameters to apply throttling to the target SQL. For details, see SQL throttling.
-
For PolarDB for MySQL database instances, click IMCI in the Actions column for the target SQL template to view documentation about In-Memory Columnar Index (IMCI).
Note-
The IMCI button is displayed when a PolarDB for MySQL instance does not have an In-Memory Columnar Index (IMCI) node, the Max Execution Time of a slow query log exceeds 20 seconds, and the Max Scanned Rows exceeds 200,000.
-
For complex queries on large datasets, we recommend using 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 to run SQL Diagnostic Optimization or SQL Throttling.
-