How to use the query monitoring chart and sql query list in sql diagnostics - AnalyticDB

The AnalyticDB for MySQL cluster provides the sql diagnostics feature, which allows you to search for SQL queries that meet specific criteria, such as slow queries, across multiple dimensions. You can visualize the search results and download them to your computer for local analysis. This topic describes how to use the query monitoring chart and the sql query list within the sql diagnostics feature.

Query monitoring chart

In the Query Monitoring Information section of sql diagnostics, you can view the distribution of query execution durations within a specified time range. This helps you quickly identify long-running queries.

Note

Each colored block in the chart represents a single query.
The colors of the blocks have no special meaning and are used only to distinguish between different queries. Longer blocks represent queries with longer execution durations.
Hover over a block to view information about the query, such as its start or end time and the amount of scanned data. Click Details to go to the query's details page, where you can view information such as Query Properties, the SQL Statement, and the Execution Plan. For more information, see Use an execution plan to analyze a query.
The Query Monitoring Information chart can display up to 10,000 queries. The specific details for each query are shown in the SQL Queries table below the chart. For more information, see sql query list.
The Query Monitoring Information chart displays only the results of a general search, such as a search for the Top 100 Most Time-consuming Queries queries within the Last 5 Minutes. Using the advanced search feature in the SQL Queries table does not affect the results shown in the Query Monitoring Information chart. For more information, see Search methods.

SQL query list

The sql query list provides more information than the query monitoring chart, such as the specific sql statement and source address. You can also use the advanced search feature in the upper-right corner of the list to further filter the results. This filtering does not affect the query monitoring chart, but it helps you pinpoint problematic queries with greater precision.

Parameter	Description	Usage
Actions	Click Diagnose to view execution details, including the sql statement, statistics, execution plan, and diagnostic results.	On the Diagnose page, click Execution Plan to analyze the query. For more information, see Use an execution plan to analyze a query.
SQL	The sql statement.	Click to copy the full sql statement.
Start time	The time when the query was submitted.	N/A
Query ID	The ID of the query.	N/A
Source address	The client's IP address.	On the Diagnostics and Optimization page, click Connection Information to view the source address.
Database	The name of the database to which the client is connected.	You can use the advanced search feature to search for sql statements by database.
Status	The execution status of the sql statement. Valid values: Completed Running Failed	N/A
Total duration	The total time elapsed from when an SQL statement is submitted until it finishes execution. This metric includes the total time spent on queuing, execution plan generation, and actual execution. It does not include the network transfer time for returning the result data to the client.	Total Duration = Queuing Time + Execution Plan Duration + Execution Duration.
Queuing time	The time an SQL statement waits in the traffic control queue on the access node before it is scheduled for execution. This period starts when the SQL statement enters the queue and ends when it is picked up and the execution plan generation begins.	AnalyticDB for MySQL performs traffic control at access nodes. When concurrency is high, queries that exceed the traffic control limit are queued, resulting in long queuing times. If compute resource utilization is low at this time, try to increase the queue size. For more information, see Priority queues and concurrency of interactive resource groups. If compute resource utilization is high, you must identify the root cause of inefficient sql statements. For more information about access nodes, see Architecture.
Execution plan duration	The time the access node takes to parse, optimize, and generate an execution plan for the SQL statement. This period starts when the SQL statement finishes queuing and begins being processed, and ends when the execution plan is generated and ready to be dispatched to compute nodes.	After an sql statement is submitted to an access node, an execution plan is generated. A long execution plan duration may be caused by the following: The sql statement is complex. The access node is under a heavy load. If the sql statement is complex, try to simplify it, for example, by reducing the number of joins. If the access node is under a heavy load, check whether a large amount of data is being returned to the client or if query concurrency is high. For more information, see Query-level diagnostic results.
Execution duration	The time compute nodes take to actually execute the SQL statement. This period starts when the execution plan is dispatched to compute nodes and they begin execution, and ends when all compute nodes complete their computation tasks. Execution duration does not include queuing time, execution plan generation time, or the network transfer time for returning result data from the server to the client.	Execution duration reflects only the actual computation overhead on compute nodes. If the total duration is long but the execution duration is short, the bottleneck is likely in the queuing or execution plan generation phase rather than the SQL execution efficiency itself. You can sort by the execution duration to accurately identify SQL statements that consume excessive compute resources, while excluding the impact of queuing time and execution plan duration.
Returned data	The amount of data that a `SELECT` statement returns to the client.	Avoid returning large amounts of data to the client. Large data volumes can cause a query to consume resources in the access node queue, affecting the submission and execution of other queries. You can sort by the amount of returned data to find queries that return large data volumes.
Username	The username for the client connection.	On the Diagnostics and Optimization page, click Connection Information to view the username and the number of user connections.
Resource group	The name of the resource group that executed the sql statement.	N/A
Peak memory	The peak memory consumed by the query.	AnalyticDB for MySQL executes sql statements in stages. Dependent stages run sequentially, while independent stages run in parallel. As a result, the query's memory consumption reaches a peak. Peak memory indicates the query's maximum memory consumption.
Scanned data	The amount of data returned from the storage layer to the compute layer.	The amount of scanned data indicates the pressure a query places on the storage layer. Reading more data consumes more disk I/O resources. The compute layer also requires more resources to process the data, which affects query speed.
Total stages	The total number of stages generated for the query.	The number of stages can indicate the complexity of an sql statement. A higher number of stages means more network interactions are required during execution by AnalyticDB for MySQL, which increases the overall system load. If a query has a high number of stages, you should optimize it. For more information about stages, see Query process and execution plan.
ETL written rows	The number of rows written to a destination table by an ETL-related sql query.	N/A

You can perform the following operations on the SQL Queries tab:

Actions	Description
Download	Click Download to save the current query results as an Excel file. This download is automatically added to the Last 5 Downloads drop-down list. Note You can download up to 100,000 records at a time.
Last 5 Downloads	Select a recent download from the Last 5 Downloads drop-down list to download it again.
Advanced Search	Click Show Advanced Search and select filter dimensions based on your business needs, such as filtering the search results from Query Monitoring Information by Resource Group or Source Address.
Diagnose	In the Actions column, click Diagnose to go to the query details page, where you can view information such as Query Properties, the SQL Statement, and the Execution Plan. For more information, see Use an execution plan to analyze a query.

Note

The advanced search feature in the upper-right corner of the SQL Queries tab allows you to further filter the results of a general search. The available filter values are populated from the current result set, not from all possible values in the AnalyticDB for MySQL cluster. For more information, see Search methods.
When you select Running Queries as the general search condition, the search results show details only for sql queries with an execution duration longer than 10 seconds. In this case, the SQL Queries table displays a Resource Consumption Ranking column. A lower value in the Resource Consumption Ranking column indicates that the query consumes more resources, such as CPU and memory.
The SQL column in the SQL Queries table displays a maximum of 5,120 characters. sql statements that exceed this limit are truncated. You can view the full sql statement in the downloaded Excel file or on the SQL Statement tab of the query details page.