PolarDB:Slow SQL queries

Pre-check (optional)

To view slow SQL logs, make sure that your account is granted the AliyunHDMFullAccess permission. For more granular and flexible permission management of your PolarDB clusters, you can create custom policies to grant specific permissions to RAM users.

Procedure

1. Log on to the PolarDB console. In the left-side navigation pane, click Clusters. In the upper-left corner, select the region of the desired cluster. Find the cluster and click its ID to go to the Basic Information page.

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

3. On the page that appears, use the features as needed.

   Slow log analysis

   On the Slow Log Analysis tab, select a time range to view the trends, statistics, and details of the slow query logs within the selected time range.

   Note

   When you specify the time range, the end time must be later than the start time, and the interval between the start time and the end time cannot exceed 24 hours. You can query slow query logs within the previous month.

   • In the Slow Query Log Trends section, you can click a point in time in the trend chart to view the statistics and details of the slow query logs at the point in time.

   • Select the desired node from the Node ID drop-down list to view the number of slow queries on the node.

   • Click the icon to download the slow query logs to your computer.

   • On the Slow Query Log Statistics tab, you can perform the following operations:

     • Find the SQL template that you want to manage and click Details in the Actions column. In the Slow Log Sample dialog box, view the slow query log sample of the SQL template.

     • Find the SQL template that you want to manage and click Optimize in the Actions column. In the SQL Diagnostic Optimization dialog box, view the SQL diagnostics results.

       If you accept the SQL optimization suggestions, click Copy in the upper-right corner and paste the optimized SQL statement to the database client or Data Management (DMS) for execution.

       Note

       DAS performs SQL diagnostics based on the complexity of SQL statements, the amount of data in the table, and the database workload. Suggestions may be returned in more than 20 seconds after the SQL diagnostics is performed. After the diagnostics is complete, the SQL diagnostics engine provides diagnostics results, optimization suggestions, and expected optimization benefits. You can determine whether to accept the suggestions based on the diagnostics results.

     • Find the SQL template that you want to manage and click Throttling in the Actions column to throttle the corresponding SQL queries. In the SQL Throttling dialog box, configure the parameters. For more information, see SQL throttling.

   • On the Slow Query Log Details tab, find the SQL statement that you want to manage and click Optimize or Throttling in the Actions column to perform SQL diagnostics 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 abnormal SQL queries, provides index optimization suggestions, and automatically creates indexes without causing table locks.

   Note

   For more information, see Automatic SQL optimization.

   Query governance

   On the Query Governance page, you can view the query governance results.

   • Overview: displays the query governance results grouped by predefined categories.

     Note

     Failed SQL Executions counts only the failed SQL executions for instances with DAS Enterprise Edition enabled.

   • Trends: displays the query governance result trend within a time range.

   • Top Rankings:

     • Worst-performing Instances: displays the database instances with the highest number of slow SQL executions.

     • Best-performing Instances: displays the database instances with the largest reduction in slow SQL executions over the past 24 hours. A negative value indicates a decrease in slow SQL executions (reflecting effective optimization). A positive value indicates an increase in slow SQL executions.

     DAS recommends that you pay attention to the top rankings of Optimizable SQL.

   • SQL to Be Optimized: You can set filter conditions to select the SQL statements that require governance.

   Note

   You can filter SQL statements by database name, SQL keyword, rule tags, and database username. The logical relationship between these filters is "AND".

   • Separate multiple database names with commas (,). Their logical relationship is "OR".

   • Separate multiple SQL keywords by spaces. Their logical relationship is "AND".

   • Separate multiple usernames with commas (,). Their logical relationship is "OR".

   • You can select multiple rule tags. Their logical relationship is "OR".

   • To view detailed governance recommendations for a SQL sample, click Suggestions in its Actions column.

   • To add a tag to a SQL sample, click Add Tag in its Actions column. For more information about tags, see SQL tags that can be manually added.

     You can select SQL samples and bulk add tags.

   • To view the slow query logs of a SQL sample, click Sample in its Actions column.

   • To view the detailed slow query log analysis of a SQL sample, click Trend in its Actions column. For more information about slow query logs, see Slow query logs.

   You can export SQL statements that require optimization. For more information, see Slow query logs.

   • Failed SQL: You can specify filter conditions to select the desired SQL statements.

     Note

     • Failed SQL counts only the failed SQL executions of instances for which DAS Enterprise Edition is enabled.

     • You can filter SQL statements by database name and SQL keyword. The logical relationship between these filters is "AND".

       • Separate multiple DB names with commas (,). Their logical relationship is "OR".

       • Separate multiple SQL keywords by spaces. Their logical relationship is "AND".

     To view the details of a SQL sample, click Sample in its Actions column.

FAQ

• Why is the execution completion time of SQL statements recorded in slow query logs different from the actual execution completion time of the SQL statements?

  This issue usually occurs when the time zones recorded in the slow query logs are modified by the executed SQL statements. The execution completion time of an SQL statement varies with the time zone recorded in slow query logs at the following levels: session level, database level, and system level. If a time zone is specified for the database, the execution completion time of an SQL statement is recorded based on the time zone of the database. Otherwise, the execution completion time of an SQL statement is recorded based on the time zone of the system. If an SQL statement modifies the time zone at the session level, the time zone recorded in slow query logs may not be properly converted.

• How do I find slow SQL queries?

  You can find slow SQL queries by using one of the following methods:

  • View slow SQL queries in the PolarDB console. For more information, see Slow SQL queries.

  • Connect to the cluster and then execute the show processlist statement to find SQL queries that take a long time to execute. For information about how to connect to a cluster, see Database connection.

Best practices

High CPU utilization of PolarDB for MySQL clusters

Related API operations