Slow queries lock resources, block other sessions, and degrade overall cluster throughput. PolarDB for MySQL flags any SQL statement that takes more than 1 second to run as a slow query by default. The Slow SQL page in the console gives you three complementary views — log analysis, automatic optimization suggestions, and query governance — so you can identify, prioritize, and fix performance bottlenecks without leaving the console.
Two metrics are most useful when triaging slow queries:
-
Execution time — identifies queries that hold resources too long and block other sessions.
-
Call count — identifies frequently executed queries that add up to high cumulative latency even if each individual call is fast.
Prerequisites
Before you begin, make sure your account has the AliyunHDMFullAccess permission. For fine-grained access control, create a custom RAM policy to grant specific permissions.
View slow SQL queries
-
Log on to the PolarDB console. In the left navigation pane, click Clusters. Select the region where the cluster is located, then click the cluster ID.
-
In the left navigation pane, choose Diagnostics and Optimization > Slow SQL.
The Slow SQL page has three tabs: Slow Log Analysis, Automatic SQL Optimization Suggestions, and Query Governance.
Slow log analysis
The Slow Log Analysis tab shows slow query trends and statistics for a selected time range.
Time range constraints:
-
The end time must be later than the start time.
-
The interval cannot exceed 24 hours.
-
Logs are available for up to the previous month.
The tab includes four sections:
Slow Query Log Trends — Displays a trend chart of slow query volume over time. Click any point on the chart to see the statistics and details for that moment.
If a SQL statement is truncated in the UI, hover over it to view the full text.
Event Distribution — Shows the distribution of slow query events within the selected time range. Click an event to view its details. Use the Node ID drop-down to filter results by cluster node.
You can also:
-
Click
to download the slow query logs to your computer. -
Click
to send the current parameters to the OpenAPI console for API debugging.
Slow Query Log Statistics — Groups slow queries by SQL template. A SQL template is a parameterized version of a query where literal values are replaced with placeholders. For example, SELECT * FROM orders WHERE user_id = ? is a template that represents all queries against that table regardless of the actual user ID. This grouping lets you spot patterns across thousands of individual executions.
On this section:
-
Set filter conditions at the top of the list. Available filters vary by database engine.
-
Click a SQL ID to see the user distribution, client distribution, and metric trends for that template.
-
Click Optimize in the Actions column to open the SQL Diagnostic Optimization dialog. DAS performs SQL diagnostics based on the complexity of the SQL statement, the amount of data in the table, and the database load. Suggestions may be returned in more than 20 seconds after the SQL diagnostics is performed. If you accept the suggestions, click Copy to paste the optimized SQL into your database client or Data Management (DMS) for execution.
-
Click Throttling in the Actions column to configure SQL throttling. For configuration details, see SQL throttling.
-
For PolarDB for MySQL clusters, click IMCI in the Actions column to view documentation for the In-Memory Column Index (IMCI) feature.
The IMCI button appears only 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, and the maximum number of scanned rows exceeds 200,000. Use IMCI to improve performance for complex queries that scan large amounts of data.
Slow Query Log Details — Shows individual slow query executions. Click Optimize or Throttling in the Actions column to run SQL diagnostics or configure throttling for a specific statement.
Automatic SQL optimization suggestions
On the Automatic SQL Optimization Suggestions tab, select a time period to review optimization suggestions for that period.
Enable Automatic SQL Optimization automatically identifies problematic SQL statements, generates index optimization suggestions, and creates indexes — all without causing table locks.
For more information, see Automatic SQL optimization.
Query governance
The Query Governance tab provides a cluster-wide view of query health.
Query Governance Results Overview — Shows query categorization results after system tagging.
Failed SQL executions counts only failures on instances with DAS Enterprise Edition enabled.
Query Governance Trends — Shows how query governance results have changed over the selected time range.
Top Rankings — Highlights Best-performing Instances and Worst-performing Instances:
-
Worst-performing Instances: shows the number of executions of SQL statements that cause slow queries.
-
Best-performing Instances: shows the change in slow query execution count. A negative value means the number of slow queries decreased (good optimization effect); a positive value means it increased.
Focus on the best and worst-performing instances when reviewing Optimizable SQL.
SQL to be optimized — Use filters to narrow down SQL statements that need attention. All four filters combine with AND logic.
| Filter | Separator | Operator |
|---|---|---|
| Database name | Comma (,) | OR |
| SQL keyword | Space | AND |
| Database username | Comma (,) | OR |
| Rule tag | Multiple selections | OR |
Actions available for each SQL sample:
-
Suggestions — View detailed governance recommendations.
-
Add Tag — Manually tag the SQL statement. For tag definitions, see SQL tags that can be manually added. Select multiple SQL samples to add tags in batch.
-
Sample — View slow log sample details for this SQL.
-
Trend — View slow log analysis details. For more information, see Slow query logs.
Export and share SQL statements that need optimization as needed. For more information, see Slow query logs.
Failed SQL — Filter failed SQL statements by database name (comma-separated, OR logic) or SQL keyword (space-separated, AND logic).
Failed SQL counts only failures on instances with DAS Enterprise Edition enabled.
Click Sample in the Actions column to view execution details for a failed statement.
Adjust the slow query threshold
The default slow query threshold is 1 second. To change it, go to Settings and Management > Parameters in the PolarDB console and set the long_query_time parameter.
| Parameter | Description | Default | Range | Unit |
|---|---|---|---|---|
long_query_time |
Records all queries that exceed this value to the slow query log | 1 | 0.03–31,536,000 | Seconds |
For instructions, see Specify cluster and node parameters.
FAQ
Why does the execution completion time in slow query logs differ from the actual execution time?
This happens when a SQL statement modifies the session-level time zone. PolarDB records execution completion time based on the time zone at three levels: session, database, and system. If a time zone is specified for the database, the execution completion time is recorded based on the time zone of the database. Otherwise, it is recorded based on the time zone of the system. If a SQL statement changes the session time zone mid-execution, the recorded timestamp may not be converted correctly.
How do I find slow SQL queries?
Two options:
-
View slow query logs directly on the Slow SQL page in the console, as described in this article.
-
Connect to the cluster and run
show processlistto see currently executing statements. For connection instructions, see Database connection.
API reference
| API | Description |
|---|---|
| DescribeSlowLogs | Queries statistics about slow query logs of a PolarDB for MySQL cluster |
| DescribeSlowLogRecords | Queries details of slow query logs of a PolarDB for MySQL cluster |
| DescribeDBClusterAuditLogCollector | Queries whether SQL data collector is enabled for a PolarDB for MySQL cluster. The features of SQL data collector include audit logs and SQL Explorer. |
| ModifyDBClusterAuditLogCollector | Enables or disables SQL data collector for a PolarDB for MySQL cluster. The features of the SQL data collector include audit logs and SQL Explorer. |