PolarDB for MySQL:Slow SQL query

Prerequisites

The cluster must be of PolarDB for MySQL Cluster Edition, Multi-master Cluster (Database/Table) Edition, or X-Engine Edition. This feature is not supported on the cluster of Single Node Edition. For more information, see Overview.

Procedure

1. Log on to the PolarDB console.
2. In the upper-left corner of the console, select the region in which the cluster that you want to manage is deployed.
3. Find the cluster and click the cluster ID.
4. In the left-side navigation pane, choose Diagnostics and Optimization > Slow SQL Query.
5. On the Slow Log Analysis tab, specify a time range and view the trend, statistics, and details of the slow queries within the time range.
   Note When you specify the time range, the end time must be later than the start time and the time range cannot exceed 24 hours. You can query the slow query logs within the latest month at most.

   • Click a time point in the trend chart and view the statistics and details of the slow queries around that time point.
   • In the Nodes section, view the number of slow queries of each node.
   • Click Export Slow Log to download slow query logs.
   • On the Slow Log Statistics tab, perform the following operations:
     • Click Sample in the Actions column corresponding to the SQL template that you want to manage. In the Slow Log Sample dialog box, view the details of the slow query log.
     • Click Optimize in the Actions column corresponding to the SQL template that you want to manage. In the SQL Diagnostic Optimization dialog box, view the diagnostic results.

       If you want to adopt the SQL diagnostic suggestions, click Copy in the upper-right corner and paste the optimized SQL statements to the database client or Data Management (DMS) for execution. If you do not accept the SQL diagnostic suggestions, click OK.

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

       You can also click Database Expert Service to purchase the expert service. Database Expert Service provides value-added professional database services, such as emergency solutions, health diagnostics, performance optimization, security assurance, and data migration.

     • Click Throttle in the Actions column corresponding to the SQL template that you want to manage. In the SQL Throttling dialog box, configure the displayed parameters. For more information, see SQL throttling.
   • On the Slow Log Details tab, click Optimize or Throttle in the Actions column corresponding to the SQL statement that you want to manage. This way, you can also perform SQL diagnostic optimization and SQL throttling.

   

FAQ

Why is the execution completion time of SQL statements recorded in slow query logs different from the actual execution 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.

Related API operations