AnalyticDB for MySQL provides the SQL pattern feature that aggregates similar SQL statements to improve the efficiency of intelligent diagnostics.
Background information
The SQL pattern feature is designed for real-time SQL statements. It performs grouped diagnostics and analysis on SQL statements and aggregates similar SQL statements into an SQL pattern to improve the efficiency of intelligent diagnostics. The aggregation results of SQL patterns can serve as an effective basis for database optimization. SQL statements that cause excessive workloads on AnalyticDB for MySQL clusters can be intercepted. For more information, see Persist plan and query blocker.
Benefits
The SQL pattern feature provides the following benefits:
Aggregation: groups similar SQL statements based on the SQL text.
Macro statistics: compares average values with maximum values and displays the total values and percentages of key pattern metrics.
Identification of problematic SQL statements: identifies abnormal SQL statements and drills down to diagnose problematic SQL statements.
Usage notes
You can view SQL patterns only within the last 14 days. The maximum time range for each query is 24 hours.
Procedure
Log on to the AnalyticDB for MySQL console. In the upper-left corner of the console, select a region. In the left-side navigation pane, click Clusters. On the Data Warehouse Edition tab, find the cluster that you want to manage and click the cluster ID.
In the left-side navigation pane, click Diagnostics and Optimization.
Click the SQL Pattern tab.
By default, SQL patterns within the last 30 minutes are displayed. You can search for SQL patterns by entering a keyword or specifying a time range.
The following table describes the parameters in the SQL pattern list.
Parameter
Description
Actions
You can click View Details to view the details of the SQL pattern. For more information, see the "Pattern analysis" section of this topic.
Username
The name of the database account used to submit SQL statements that belong to the SQL pattern.
Client IP Address
The IP address of the client used to submit SQL statements that belong to the SQL pattern.
SQL Pattern
The SQL statement that represents the SQL pattern. You can click the
icon to copy the entire SQL statement. Total CPU Cost
The total amount of CPU time consumed by SQL statements that belong to the SQL pattern within the specified time range. The percentage in parentheses is calculated by using the following formula: Total amount of CPU time consumed by the SQL pattern/Total amount of CPU time consumed by all SQL patterns. If the percentage exceeds 30%, the parameter value is automatically highlighted.
You can monitor the CPU cost percentage of the SQL pattern to determine whether the SQL statements that belong to the SQL pattern consume a large amount of CPU time within the specified time range. You can also monitor the CPU metrics to perform further analysis.
Total Peak Memory
The total peak memory consumed by SQL statements that belong to the SQL pattern within the specified time range. The percentage in parentheses is calculated by using the following formula: Total peak memory consumed by the SQL pattern/Total peak memory consumed by all SQL patterns. If the percentage exceeds 30%, the parameter value is automatically highlighted.
You can monitor the peak memory percentage of the SQL pattern to determine whether the SQL statements that belong to the SQL pattern consume a large amount of memory within the specified time range. You can also monitor the compute memory usage metrics to perform further analysis.
Total Duration
The total execution duration of SQL statements that belong to the SQL pattern within the specified time range. The percentage in parentheses is calculated by using the following formula: Execution duration of the SQL pattern/Execution duration of all SQL patterns. If the percentage exceeds 30%, the parameter value is automatically highlighted.
You can monitor the execution duration percentage of the SQL pattern to determine whether the SQL statements that belong to the SQL pattern result in an increase in query response time metrics within the specified time range. You can also monitor the query response time metrics to perform further analysis.
Total Size of Read Data
The total size of data read by SQL statements that belong to the SQL pattern within the specified time range. The percentage in parentheses is calculated by using the following formula: Total size of data read by the SQL pattern/Total size of data read by all SQL patterns. If the percentage exceeds 30%, the parameter value is automatically highlighted.
You can monitor the data reading percentage of the SQL pattern to determine whether the SQL statements that belong to the SQL pattern read a large amount of data within the specified time range. You can also monitor the table data reading metrics to perform further analysis.
Total Cost for Reading Data
The total amount of CPU time consumed by SQL statements that belong to the SQL pattern to read data within the specified time range. The percentage in parentheses is calculated by using the following formula: Total amount of CPU time consumed by the SQL pattern to read data/Total amount of CPU time consumed by all SQL patterns to read data. If the percentage exceeds 30%, the parameter value is automatically highlighted.
You can monitor the data reading percentage of the SQL pattern to determine whether the SQL statements that belong to the SQL pattern consume a large amount of CPU time to read data within the specified time range. If a large amount of CPU time is consumed to read data, the CPU utilization of reserved nodes (storage nodes) is increased. You can also monitor the CPU metrics of reserved nodes or original storage nodes to perform further analysis.
Average CPU Cost
The average amount of CPU time consumed by SQL statements that belong to the SQL pattern within the specified time range.
Maximum CPU Cost
The maximum amount of CPU time consumed by SQL statements that belong to the SQL pattern within the specified time range.
Average CPU Cost for Reading Tables
The average amount of CPU time consumed by SQL statements that belong to the SQL pattern to read data within the specified time range.
Maximum CPU Cost for Reading Tables
The maximum amount of CPU time consumed by SQL statements that belong to the SQL pattern to read data within the specified time range.
Executions
The number of executions of the SQL statements that belong to the SQL pattern within the specified time range. In the event of resource usage bursts within a cluster, you can sort SQL statements based on the number executions and identify the SQL statements that are frequently executed when the cluster does not run as expected. Then, you can perform analysis and identify the cause of the increase in the executions of the SQL statements.
Failures
The number of failed executions of the SQL statements that belong to the SQL pattern within the specified time range.
Average Total Time Consumed
The average total amount of time consumed by SQL statements that belong to the SQL pattern within the specified time range. Unit: milliseconds.
Maximum Total Time Consumed
The maximum total amount of time consumed by SQL statements that belong to the SQL pattern within the specified time range. Unit: milliseconds. You can compare the values of the Average Total Time Consumed and Maximum Total Time Consumed parameters to determine whether an increase in the amount of time consumed by the SQL pattern is caused by other SQL patterns. If the value of the Maximum Execution Duration parameter is almost the same as the value of the Average Total Time Consumed parameter when the cluster runs as expected, an increase in the amount of time consumed by the SQL pattern when the cluster does not run as expected is caused by other SQL patterns.
Average Execution Duration
The average execution duration of the SQL statements that belong to the SQL pattern within the specified time range. Unit: milliseconds.
Maximum Execution Duration
The maximum execution duration of the SQL statements that belong to the SQL pattern within the specified time range. Unit: milliseconds. For more information about the execution duration, see Use query monitoring charts and SQL queries.
Average Peak Memory
The average peak memory of the SQL statements that belong to the SQL pattern within the specified time range. Unit: bytes.
Maximum Peak Memory
The maximum peak memory of the SQL statements that belong to the SQL pattern within the specified time range. Unit: bytes. You can compare the values of the Maximum Peak Memory and Average Peak Memory parameters to determine whether the memory usage of the SQL pattern is stable. If the value of the Maximum Peak Memory parameter is far greater than the value of the Average Peak Memory parameter, the amount of data scanned by the SQL pattern may be increased or the execution plan may be changed. You can click View Details in the Actions column to view the SQL statement list on the Pattern Analysis page and identify the cause.
Average Data Scanned
The average amount of data scanned by SQL statements that belong to the SQL pattern within the specified time range. Unit: MB.
Maximum Data Scanned
The maximum amount of data scanned by SQL statements that belong to the SQL pattern within the specified time range. Unit: MB. You can compare the values of the Maximum Data Scanned and Average Data Scanned parameters to determine whether the amount of data scanned by the SQL pattern is stable. If the value of the Maximum Data Scanned parameter is far greater than the value of the Average Data Scanned parameter, the amount of data scanned is unstable. You must identify the cause.
Table Name
The names of the tables scanned by SQL statements that belong to the SQL pattern.
Pattern analysis
The Pattern Analysis page displays the key metrics of an SQL pattern in charts within a specific time range. The metrics include the executions, amount of time consumed, execution duration, amount of data scanned, and peak memory. For the amount of time consumed, execution duration, amount of data scanned, and peak memory metrics, the maximum and average values are provided to facilitate comparison and analysis. The SQL statement list describes all SQL statements that belong to the SQL pattern within the specified time range. You can click Diagnose in the Actions column to view the diagnostic results and execution plans of the SQL statements. For more information, see Use execution plans to analyze queries.
On the SQL Pattern tab, click View Details in the Actions column of an SQL pattern to go to the Pattern Analysis page. You can view the changing metrics and SQL statement list of the SQL pattern.