AnalyticDB for MySQL provides the SQL diagnostics feature. You can search for SQL queries such as slow queries based on a variety of conditions and view the search results as charts. You can also download and save the search results to your computer. This topic describes how to use the query monitoring charts and SQL queries in SQL diagnostics.
Query monitoring charts
In the Query Monitoring Information section of the SQL Diagnostics tab, you can view the duration of queries that are executed within a specific time range to quickly identify the queries that consume the most time.
Each colored bar in the chart represents a single query.
Different, arbitrary colors are used to distinguish different queries from each other. The longer the colored bar, the longer the execution time consumed by the corresponding query.
Move the pointer over the colored bar corresponding to a query to view details such as the start time, end time, and amount of scanned data. Click Details to go to the details page of a query and view the Query Properties, SQL Statement, and Execution Plan parameters. For more information, see Use execution plans to analyze queries.
Up to 10,000 queries can be displayed in the Query Monitoring Information section. The specific information of each query is displayed in the SQL Queries section below the monitoring chart. For more information, see the "SQL queries" section of this topic.
Only general search results of queries are displayed in the Query Monitoring Information section. For example, you can search for queries that meet the Last 5 Minutes and Top 100 Most Time-consuming Queries conditions. Advanced search in the upper-right corner of the SQL Queries tab does not affect the results that are displayed in the Query Monitoring Information section. For more information, see the "Search methods" section of the Overview topic.
SQL queries
SQL queries can display more information than query monitoring charts can, such as specific SQL statements and source addresses. You can further filter search results by using the advanced search feature in the upper-right corner of the SQL Queries tab. This helps you find faulty queries more accurately. Advanced search results do not affect the results that are displayed in the query monitoring charts.
Parameter | Description | Instruction |
Actions | The Diagnose operation that allows you to view execution details such as the SQL statement, statistics, execution plan, and diagnostic results. | Click Diagnose. On the page that appears, click the Execution Plan tab to analyze queries. For more information, see Use execution plans to analyze queries. |
SQL | The executed SQL statement. | Click the |
Started At | The time when the query is submitted. | None |
Query ID | The ID of the query. | None |
Source Address | The IP address that is used to connect to the client. | On the Diagnostics and Optimization page, click the Connection Information tab to view the source address. |
Database | The name of the database that is used to connect to the client. | Use the advanced search feature to search for SQL queries based on a database. |
Status | The execution status of the SQL statement. Valid values:
| None |
Total Duration | The total amount of time consumed from the time when the SQL statement is submitted to the time when its execution is complete. Total duration = Queuing time + Execution plan duration + Execution duration. | None |
Queuing Time | The amount of time that the SQL statement spends in queue. It is measured from the time when the SQL statement is submitted to an access node of AnalyticDB for MySQL to the time when its execution starts. | AnalyticDB for MySQL implements throttling on access nodes when large numbers of queries are concurrently executed. If the number of submitted queries is greater than or equal to the maximum number of queries that can be executed, new queries are added to the queue. They may remain in the queue for an extended period of time. In this case, if only a small amount of computing resources are being consumed, you can increase the maximum number of queries to allow more queries to be executed. For more information, see Priority queues and concurrency control of interactive resource groups. If a large amount of computing resources are being consumed, you must identify bad queries and their causes. For more information about the access node, see Overall architecture. |
Execution Plan Duration | The amount of time consumed to generate an execution plan. | An execution plan is generated after an SQL statement is submitted to an access node. Execution plans may take a long time to be generated due to the following reasons:
If a complex SQL statement is used, you can optimize the statement. For example, you can remove the overused joins from the statement. If an access node has high resource usage, you can check whether large amounts of data are returned to the client or whether large numbers of queries are concurrently executed. For more information, see Query-level diagnosis results. |
Execution Duration | The amount of time consumed to execute the SQL statement. | Sort the displayed queries by execution duration. Find resource-consuming queries based on the sorting results, queuing time, and execution plan duration. |
Returned Data | The amount of data that is returned to the client by the SELECT statement. | A query that returns a large amount of data consumes a large amount of computing resources. This may affect the submission and execution of other queries. Sort the displayed queries by returned data amount. Find the queries that return large amounts of data based on the sorting results. |
Username | The name of the user that is used to connect to the client. | On the Diagnostics and Optimization page, click the Connection Information tab to view the username and the number of user connections. |
Resource Group | The name of the resource group that executes the SQL statement. | None |
Peak Memory | The peak memory usage of the query. | AnalyticDB for MySQL executes SQL queries in stages. Dependent stages are executed in series, while independent stages are executed in parallel. As such, a peak usage exists for the amount of memory occupied. This limit is reflected in this parameter. |
Scanned Data | The amount of data returned from the storage layer to the computing layer. | The amount of scanned data reflects the resource usage at the storage layer. The more data that is read, the more disk I/O resources that are consumed. In this case, the computing layer also requires more resources and slows down queries. |
Total Stages | The total number of stages generated from the query. | The number of stages reflects the complexity of an SQL statement. Large numbers of stages require substantial network interactions to execute the SQL statement and consume enormous system resources. If an SQL statement generates a large number of stages, you must optimize the SQL statement. For more information about the concept of stages, see Query process and execution plan. |
ETL Written Rows | The number of rows written to the desired table by the extract-transform-load (ETL) related query. | None |
icon to copy the SQL statement. The following table describes the operations that you can perform on the SQL Queries tab.
Operation | Description |
Download | Click Download to save the query results to an Excel file. The file is automatically stored in the Last 5 Downloads drop-down list. Note Up to 100,000 records can be downloaded at a time. |
Last 5 Downloads | Select the query result that you want to download from the Last 5 Downloads drop-down list to complete the download. |
Show Advanced Search | Click Show Advanced Search and set filter conditions. For example, you can set filter conditions based on Resource Group or Source Address to further filter the query results in the Query Monitoring Information section. |
Diagnose | Click Diagnose in the Actions column to go to the details page of a query and view Query Properties, SQL Statement, and Execution Plan parameters. For more information, see Use execution plans to analyze queries. |
The advanced search feature in the upper-right corner of the SQL Queries tab can be used to further filter general search results. Valid values of the filter conditions in the advanced search section are existing values in the general search results instead of all values in the current AnalyticDB for MySQL cluster. For more information, see the "Search methods" section of the Overview topic.
If you select Running Queries for general search, only details of SQL queries that took longer than 10 seconds to complete are displayed in the search result and the Resource Consumption Ranking column is displayed on the SQL Queries tab. A smaller value in the Resource Consumption Ranking column indicates that more CPU and memory resources are consumed by the corresponding query.
Up to 5,120 characters can be displayed in the SQL column on the SQL Queries tab. Additional characters are truncated if SQL statements exceed 5,120 characters in length. You can view complete SQL statements in the downloaded Excel file or on the SQL Statement tab of the details page of corresponding queries.