What is a slow SQL query and how to use the SQL analysis feature-PolarDB(PolarDB)-阿里云帮助中心

PolarDB for MySQL defines SQL statements that take more than 1 second to execute as slow SQL queries and provides an SQL analysis feature in the console. You can view the trend and statistics of slow query logs. In addition, the system provides optimization suggestions and diagnostic analysis for related SQL queries to facilitate in-depth analysis and optimization.

Prerequisites (optional)

To view slow query logs, make sure that your account has been granted the AliyunHDMFullAccess permission. For finer-grained permission management, you can use a custom policy to grant permissions to a RAM user for flexible control over PolarDB.

Note

The maximum length of a slow log is 16 KB. The portion that exceeds this length is truncated.

View slow SQL queries

Log on to the PolarDB console. In the left-side navigation pane, click Clusters, select the region where the cluster is deployed, and click the target cluster ID to go to the cluster details page.
In the left-side navigation pane, choose Diagnostics and Optimization > Slow SQL.
The Slow SQL page provides the following three features:
Slow log analysis
On the Slow Log Analysis tab, select a time range, and then view Slow Log Trend, Event Distribution, Slow Log Statistics, and Slow Log Details.
- In the Slow Log Trend chart, you can select a specific point in time to view the corresponding Slow Log Statistics and Slow Log Details.
  Note
  If a slow SQL statement is too long to be fully displayed, hover the pointer over the statement to view the complete text in a pop-up window.
- In the Event Distribution section, you can find slow 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 a local file.
- Click to go to OpenAPI Explorer and debug the API. The currently selected and entered parameters are passed automatically.
- In the Slow Log Statistics section:
  Above the list, you can select filter conditions to refine the data. The available filter conditions vary by database engine.
  Click an ID in the Query ID column of a SQL template to view its correlation analysis and a detailed list, 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, view the SQL diagnosis results.
  If you accept the suggestion, click Copy in the upper-right corner and paste the optimized SQL statement into a database client or DMS to run it. If you do not accept the suggestion, click Cancel to close the dialog box.
  Note
  DAS diagnoses SQL statements based on their complexity, the data volume of related tables, and the database load. The diagnosis may take more than 20 seconds to complete. Once complete, the SQL diagnostics engine provides a result, an optimization suggestion, and the expected performance improvement. You can then decide whether to accept the suggestion.
  In the Actions column for the target SQL template, click Throttling. On the SQL Throttling page, configure throttling parameters for the target SQL statement. For more information, see SQL throttling.
  For PolarDB for MySQL database instances, click IMCI in the Actions column to view the documentation for In-Memory Column Index (IMCI).
  Note
  The IMCI button is displayed for a PolarDB for MySQL instance if it does not have an IMCI node, its slow query log's Max Execution Time exceeds 20 seconds, and the Max Scanned Rows exceeds 200,000.
  For complex queries on large volumes of data, we recommend that you use In-Memory Column 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.
Automatic SQL optimization suggestions
On the Automatic SQL optimization suggestions page, select a time range.
Enable automatic SQL optimization: This feature automatically identifies problematic SQL queries, generates index optimization suggestions, and creates indexes without causing table locks.
Note
For more information, see Automatic SQL optimization.
Query governance
On the Query Governance page, view the query governance results.
- Query governance results overview: View the categorized result data tagged by the system.
  Note
  Only Failed SQL Executions for instances with DAS Enterprise Edition enabled are counted.
- Query governance trends: View the trend of query governance results within the selected time range.
- Top Rankings: View the Best-performing Instances and Worst-performing Instances.
  Instance blacklist: View the number of slow SQL query executions for each database instance.
  Instance red list: View the change in the number of slow SQL query executions for each database instance. A negative value indicates a decrease (good optimization result), while a positive value indicates an increase.
  DAS recommends that you focus on the Optimizable SQL rankings.
- SQL to Be Optimized: You can set filter criteria to filter SQL queries that require governance.
  Note
  You can filter by DB name, SQL keyword, rule tag, and database username. These four filters are combined with a logical AND.
  Multiple DB names are separated by commas (,) and combined with a logical OR.
  Multiple SQL keywords are separated by spaces and combined with a logical AND.
  Multiple database usernames are separated by commas (,) and combined with a logical OR.
  You can select multiple rule tags. The selected tags are combined with a logical OR.
  In the Actions column for a target SQL sample, click Suggestions to view detailed governance recommendations.
  In the Actions column for a target SQL sample, click Add Tag to manually tag the SQL query. For more information about the tags, see SQL tags that support manual tagging.
  You can also select multiple SQL queries to tag them in a batch.
  In the Actions column for a target SQL sample, click Sample to view the details of the slow query log sample for this query.
  In the Actions column for a target SQL sample, click Trend to view the slow log analysis details for this query. For more information about slow log analysis, see Slow query log.
  You can export and share the data of SQL queries to be optimized. For more information, see Slow query log.
- Failed SQL: You can set filter criteria to filter the SQL queries that you want to view.
  Note
  Only Failed SQL for instances with DAS Enterprise Edition enabled are counted.
  You can filter by DB name and SQL keyword. These two filters are combined with a logical AND.
  Multiple DB names are separated by commas (,) and combined with a logical OR.
  Multiple SQL keywords are separated by spaces and combined with a logical AND.
  In the Actions column for a target SQL sample, click Sample to view the details of this SQL sample.

Adjust the slow SQL query threshold

You can go to the PolarDB console and navigate to Settings and Management > Parameters. On that page, set the long_query_time parameter to adjust the threshold for slow SQL queries.

Parameter

Description

long_query_time

Records all queries whose execution time exceeds the parameter value in the slow query log.

Valid values: 0.03 to 31536000. Default value: 1. Unit: seconds.

FAQ

Why is the SQL execution completion time recorded in the slow query log different from the actual execution time?

This issue usually occurs when the time zone is changed by an SQL statement. The slow query log records the SQL execution time based on a time zone hierarchy: session level, database level, and system level. When a database-level time zone is set, it takes precedence. Otherwise, the system time zone is used. If you modify the session-level time zone by using an SQL statement, the time zone in the slow query log may not be converted correctly.

How do I find slow SQL queries?

You can find slow SQL queries in the following ways:

Query slow SQL queries directly in the console. For more information, see Slow SQL query.
Connect to the database cluster and run the show processlist command to find long-running SQL queries. For more information about how to connect to a database cluster, see How to connect to a Database.

What is rows_examined? What do I do if the number of scanned rows is much larger than the number of returned rows?

The number of scanned rows (rows_examined) indicates the number of rows actually read during the execution of an SQL statement. The database engine collects this metric automatically. No additional configuration is required.

If the number of scanned rows of a slow SQL query is much larger than the number of returned rows, the query reads a large amount of unnecessary data. This issue is usually caused by one of the following reasons:

The query conditions lack a suitable index, which results in a full table scan or an index with low selectivity.
The table statistics are outdated, which causes the optimizer to choose a suboptimal execution plan.

You can perform the following steps to optimize the query:

Run EXPLAIN to view the execution plan of the SQL statement and check whether the expected index is used.
Run ANALYZE TABLE to recollect the table statistics so that the optimizer can choose a better execution plan.
If the execution plan is still suboptimal, add or adjust indexes based on the query conditions.

The Automatic SQL optimization suggestions feature described in this topic can automatically identify problematic SQL queries and generate index optimization suggestions to help assess whether the current indexes are adequate.

What do I do if the InvalidVersion error is returned when I call a slow log API operation by using an SDK?

If the InvalidVersion error is returned when you call a slow log API operation such as DescribeSlowLogRecords by using an SDK (for example, a Python script), check whether the Version parameter in the request is correct.

We recommend that you first use the debugging feature in Alibaba Cloud OpenAPI Explorer to verify that the parameters are correct and the request path works as expected, and then update the configuration in your code.

Why does the execution time of an identical SQL statement or an update by primary key occasionally fluctuate significantly (for example, from milliseconds to seconds)?

Troubleshoot the issue in the following order:

Check statistics: Run ANALYZE TABLE to recollect the statistics of the related tables. Outdated statistics may cause the optimizer to choose an incorrect index.
Compare execution plans: Obtain the execution plans of both normal and abnormal executions and check whether the fluctuation is caused by a different index choice.
Check the client and runtime environment: If no anomaly is found on the database side and no corresponding record exists in the slow query log (that is, the execution time does not exceed the long_query_time threshold described in the Adjust the slow SQL query threshold section of this topic), the time is likely not spent on database execution. Check whether the client server has network latency, resource contention, or connection pool exhaustion (such as a HikariCP connection acquisition timeout).
Temporary mitigation: If the connection pool is exhausted, you can temporarily increase the maximumPoolSize parameter of the connection pool to reduce waiting. However, to resolve the issue, prioritize optimizing the slow SQL queries that account for the largest share of execution time so that connections are held for shorter periods.

Best practices

High CPU utilization for PolarDB for MySQL

PolarDB proxy configuration and traffic anomalies

PolarDB deadlock

Related APIs

API	Description
DescribeSlowLogs	Queries the statistics of slow query logs for a PolarDB for MySQL cluster.
DescribeSlowLogRecords	Queries the details of slow query logs for a PolarDB for MySQL cluster.
DescribeDBClusterAuditLogCollector	Queries whether SQL collection features (such as audit log and SQL Explorer) are enabled for a PolarDB for MySQL cluster.
ModifyDBClusterAuditLogCollector	Enables or disables SQL collection features (such as audit log and SQL Explorer) for a PolarDB for MySQL cluster.

Prerequisites (optional)

Note

View slow SQL queries

Slow log analysis

Automatic SQL optimization suggestions

Query governance

Adjust the slow SQL query threshold

FAQ

Best practices

Related APIs