SQL Pattern
The AnalyticDB for MySQL SQL pattern feature improves diagnostic efficiency by grouping similar SQL queries and displaying their aggregated performance characteristics.
Background information
The SQL pattern feature analyzes a real-time stream of all SQL queries. It categorizes, diagnoses, and groups similar queries into a single SQL pattern, significantly improving diagnostic efficiency. The aggregated results also provide a solid basis for database optimization. To quickly restore instance performance, use the interception feature to block resource-intensive queries that are causing excessive workloads on an instance. For more information, see Persist plan and Query-Blocker.
Features
The SQL pattern feature provides the following capabilities:
-
Pattern aggregation: Groups similar SQL statements based on their text structure.
-
High-level statistics: Compares average and maximum values, and shows totals and percentages for key metrics of each SQL pattern.
-
Abnormal SQL identification: Pinpoints patterns that deviate from their historical baseline and provides a drill-down feature to locate the problematic SQL queries and obtain diagnostic results.
Notes
You can view SQL pattern records for the last 14 days. The maximum time range for a single search 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, the page displays SQL patterns from the last 30 minutes. You can search for SQL patterns by using SQL keywords or by specifying a time range.
The following table describes the parameters in the SQL pattern list.
Parameter
Description
Actions
Click View Details to view the details of the SQL pattern. For more information, see Pattern Analysis.
Database Account
The database account that submitted the queries in the SQL pattern.
Client IP Address
The client IP address used to submit the queries in the SQL pattern.
SQL Pattern
The representative SQL statement for the pattern. Click the
icon to copy the full statement.Total CPU Cost
The total CPU time consumed by queries in the SQL pattern within the selected time range. The percentage in parentheses indicates this pattern's share of the total CPU time consumed by all patterns. The value is highlighted if the percentage exceeds 30%.
By observing the CPU cost percentage, you can quickly determine if a specific type of query is causing high CPU consumption. For further analysis, you can correlate this data with the CPU monitoring metrics.
Total Peak Memory
The sum of peak memory used by queries in the SQL pattern within the selected time range. The percentage in parentheses indicates this pattern's share of the total peak memory used by all patterns. The value is highlighted if the percentage exceeds 30%.
By observing the peak memory percentage, you can quickly determine if a specific type of query is causing high memory consumption. For further analysis, you can correlate this data with the compute memory usage monitoring metrics.
Total Duration
The total execution duration of queries in the SQL pattern within the selected time range. The percentage in parentheses indicates this pattern's share of the total duration of all patterns. The value is highlighted if the percentage exceeds 30%.
By observing the duration percentage, you can quickly determine if a specific type of query is contributing to an increase in query response time (RT). For further analysis, you can correlate this data with the query RT monitoring metrics.
Total Size of Read Data
The total amount of data read by queries in the SQL pattern within the selected time range. The percentage in parentheses indicates this pattern's share of the total data read by all patterns. The value is highlighted if the percentage exceeds 30%.
By observing the data read percentage, you can quickly determine if a specific type of query is reading a large amount of data. For further analysis, you can correlate this data with the table read result data size monitoring metrics.
Total Cost for Reading Data
The total CPU time consumed for reading data by queries in the SQL pattern within the selected time range. The percentage in parentheses indicates this pattern's share of the total CPU time consumed for data reading by all patterns. The value is highlighted if the percentage exceeds 30%.
By observing this percentage, you can quickly determine if a specific type of query consumes significant CPU time for data reading. An increase in table read CPU cost affects the CPU utilization of reserved nodes (storage nodes). For further analysis, you can correlate this data with the CPU monitoring metrics for reserved nodes or original storage nodes.
Average CPU Cost
The average CPU time consumed by queries in the SQL pattern within the selected time range.
Maximum CPU Cost
The maximum CPU time consumed by any single query in the SQL pattern within the selected time range.
Average CPU Cost for Reading Tables
The average CPU time consumed for reading data by queries in the SQL pattern within the selected time range.
Maximum CPU Cost for Reading Tables
The maximum CPU time consumed for reading data by any single query in the SQL pattern within the selected time range.
Executions
The number of times queries in the SQL pattern were executed within the selected time range. If you notice a sudden spike in resource usage, you can sort by this column to identify the most frequently executed queries and determine if the increase is expected.
Failures
The number of failed executions for queries in the SQL pattern within the selected time range.
Average Total Time Consumed
The average query duration for queries in the SQL pattern within the selected time range. Unit: ms.
Maximum Total Time Consumed
The maximum query duration for any single query in the SQL pattern within the selected time range. Unit: ms. Compare this value with the Average Total Duration to understand latency variations. If the maximum and average values are similar during normal operation, a significant increase in this pattern's duration during an abnormal period might be influenced by other SQL statements.
Average Execution Duration
The average execution duration for queries in the SQL pattern within the selected time range. Unit: ms.
Maximum Execution Duration
The maximum execution duration for any single query in the SQL pattern within the selected time range. Unit: ms. For more information about the definition of execution duration, see Introduction to query monitoring charts and the SQL list.
Average Peak Memory
The average peak memory used by queries in the SQL pattern within the selected time range. Unit: bytes.
Maximum Peak Memory
The maximum peak memory used by any single query in the SQL pattern within the selected time range. Unit: bytes. By comparing the maximum and average peak memory, you can assess the stability of memory usage for this pattern. If the maximum peak memory is much higher than the average, it may indicate an increase in data scanned or a change in the execution plan. Click View Details and check the SQL list on the Query Details page to investigate the cause.
Average Data Scanned
The average amount of data scanned by queries in the SQL pattern within the selected time range. Unit: MB.
Maximum Data Scanned
The maximum amount of data scanned by any single query in the SQL pattern within the selected time range. Unit: MB. By comparing the maximum and average data scanned, you can determine the stability of data access for this pattern. If the maximum amount is much larger than the average, the amount of data scanned is unstable, and you must determine whether this is expected.
Table Name
The database tables scanned by the SQL pattern.
Pattern Analysis
The Pattern Analysis page graphs key metrics for an SQL pattern over time, including executions, query duration, execution duration, data scanned, and peak memory. For query duration, execution duration, data scanned, and peak memory, both maximum and average values are displayed to facilitate comparison and analysis. The SQL list shows all individual queries that match the current pattern within the selected time window. You can click Diagnose to view diagnostic results and the execution plan for a specific query. For more information, see Analyze queries using execution plans.
On the SQL Pattern tab, click View Details in the Actions column of an SQL pattern to open the Pattern Analysis page. On this page, you can view time-series charts for various metrics and the list of associated SQL queries.