Top SQL

Usage Notes

• Top SQL is not available to free instances. To use this function, set free instances as paid instances or subscribe to Intelligent O&M.
• To use Top SQL, enable Collect All Query Logs first, which is disabled by default. Collecting all SQL statements generates a performance loss of no more than 5%.
• After Collect All Query Logs is disabled, new SQL statements will not be collected and the collected SQL statements will be deleted.
• SQL Explorer cannot record all SQL data. As for RDS for MySQL, it has the following constraints:
  • All SQL statements are constrained by the memory buffer. When service volume is high, buffer overflow may result in dropped records.
  • Any SQL statement that exceeds 4096 bytes is discarded by default.

    This constraint can be removed by setting parameter rds_sql_tracer_reserve_big_records for RDS for MySQL 5.7.33.3 or later. You can set the parameter to ON by following prompts on the page for modifying parameters of an RDS for MySQL instance, which means that a single record exceeding 4096 bytes will not be discarded.

• All query logs of free instances are retained for only one hour. If you have subscribed to advanced Intelligent O&M or paid instances, all query logs are retained for seven days by default and can be retained for up to 30 days. After the retention period ends, all query logs are deleted.
• This function is supported only in DAS 5.6.51.1 and later, 5.7.29.2 and later, and 8.0.20.3 and later versions.

Procedure

1. Log in to the console.
2. Click in the upper left corner and select a region and project.
3. Click in the upper left corner, and under Databases, click Data Admin Service.
4. In the navigation pane, choose Intelligent O&M > Instance List.

   Alternatively, on the Overview page, click Go to Intelligent O&M.

5. In the upper right corner of the Instance List page, search for instances by engine type, instance name, or instance IP.
6. Locate the target instance and click Details.
7. Choose SQL > Top SQL.

   If Collect All Query Logs is disabled, DAS cannot collect all SQL statements for analysis. You can click Log Settings and enable Collect All Query Logs.

   Figure 1 Configuring log settings
   
   

   Collect All Query Logs takes effect once it is enabled.

   To disable Collect All Query Logs, click Log Settings and toggle off the switch.

8. On the Top SQL tab page, view execution duration of top SQL statements.
   • You can select Last 1 hour, Last 3 hours, Last 6 hours, or Last 12 hours. You can also select other time ranges, but the interval must be within one day.

     The execution duration distribution displays the execution duration distribution of all SQL statements within the selected time range. The duration ranges are as follows:

     • < 100 ms: SQL statement executions when the duration is less than 100 ms
     • 100 ms–500 ms: SQL statement executions when the duration is greater than or equal to 100 ms and less than 500 ms
     • 500 ms–1s: SQL statement executions when the duration is less than 1s and greater than or equal to 500 ms
     • > 1s: SQL statement executions when the duration is greater than 1s

     SQL statistics in the four elapsed time lengths are collected over the time range you specified.

     • If you select Last hour, SQL statement executions are calculated every 10 seconds.
     • If you select Last 6 hours, SQL statement executions are calculated every minute.
     • If you select a time range longer than 6 hours, SQL statement executions are calculated every 5 minutes.
   • Hover your mouse at any point in time on the graph to view top SQL templates at that time.
     Figure 2 Viewing top SQL statements at a specific point in time
     
   • Specify a time range using your mouse on the graph and you will see top SQL statements during that time range.
     Figure 3 Viewing top SQL statements in a specified time range
     
   • Select Comparison by Date in the upper left corner to compare and analyze distribution of top SQL statements of the current instance in the same time range on different days.
     Figure 4 Top SQL comparison
     

9. View and export SQL templates, for example, database names, SQL operation types, total executions, average execution duration, average lock wait duration, and average number of scanned rows.
   • View SQL templates after selecting a SQL template, database, and SQL type.
     Figure 5 SQL template
     
   • In the SQL template list, locate a SQL template and click Details to view information such as the total execution, average rows scanned, and average execution duration.
   • Locate a SQL template and click SQL Throttling in the Operation column to create a SQL throttling rule. For details, see SQL Throttling.
   • In the SQL template list, locate a SQL template and click Diagnose to view details such as the execution plan and suggestions for tuning indexes and SQL statements.
   • Locate a SQL template and click Export in the Operation column to export the SQL template.