Slow SQL query

Prerequisites (optional)

To view the slow query log, ensure your account has the AliyunHDMFullAccess permission. For more granular permission management, you can use a custom policy to grant permissions to a RAM user for flexible control over PolarDB.

Notes

The maximum length of a slow query log entry is 16 KB. Entries that exceed this limit are truncated.

Slow SQL queries

1. Log on to the PolarDB console. In the left-side navigation pane, click Clusters. Select the cluster's region and click the cluster ID.

2. In the left-side navigation pane, choose Diagnostics and Optimization > Slow SQL.

3. The Slow SQL page has the following three tabs:

   Slow log analysis

   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 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

   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 that has been 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 lists.

     • Worst-performing Instances: Displays the number of slow SQL query executions for each database instance.

     • Best-performing Instances: Displays the change in the number of slow SQL query executions for each database instance. A negative value indicates a decrease in executions, while a positive value indicates an increase.

     DAS recommends that you focus on the rankings for Optimizable SQL.

   • SQL to Be Optimized: You can set filter criteria to identify SQL queries that require governance.

     Note

     You can filter by DB name, SQL keyword, rule tag, and database username. These filters are combined with a logical AND.

     • Multiple DB names are separated by commas (,) and are combined with a logical OR.

     • Multiple SQL keywords are separated by spaces and are combined with a logical AND.

     • Multiple database usernames are separated by commas (,) and are 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 for SQL queries that need to be optimized. For more information, see Slow query log.

   • Failed SQL: You can set filter criteria to identify 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 filters are combined with a logical AND.

       • Multiple DB names are separated by commas (,) and are combined with a logical OR.

       • Multiple SQL keywords are separated by spaces and are combined with a logical AND.

     In the Actions column for a target SQL sample, click Sample to view the details of this SQL sample.

Slow SQL query threshold

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

FAQ

Best practices

High CPU utilization of PolarDB for MySQL clusters

PolarDB proxy configuration and traffic anomalies

PolarDB deadlocks

Related APIs