When a cluster runs thousands of similar queries per minute—varying only in filter values or IDs—analyzing them individually is impractical. SQL pattern groups structurally identical statements into a single view so you can answer questions like:
Which query patterns consume the most CPU or memory?
Has the performance of a query pattern changed over time?
Which patterns run most frequently when the cluster is under stress?
How often does a query pattern fail?
Patterns with a resource percentage above 30% are automatically highlighted. SQL statements that cause excessive workloads can be intercepted. For more information, see Persist plan and query blocker.
How it works
AnalyticDB for MySQL analyzes real-time SQL statements and groups them by SQL text structure. Statements that differ only in literal values—such as filter conditions or ID values—are merged into one pattern. For example, the following two queries belong to the same pattern because the only difference is the literal value in the WHERE clause:
SELECT * FROM orders WHERE user_id = 1001
SELECT * FROM orders WHERE user_id = 2048The pattern view aggregates execution count, CPU cost, memory, duration, and data scanned across all statements in the group.
Usage notes
SQL patterns are available for the last 14 days only.
Each query covers a maximum time range of 24 hours.
View SQL patterns
Log on to the AnalyticDB for MySQL console. In the upper-left corner, select a region. In the left-side navigation pane, click Clusters. On the Data Warehouse Edition tab, find the cluster and click its ID.
In the left-side navigation pane, click Diagnostics and Optimization.
Click the SQL Pattern tab.
By default, patterns from the last 30 minutes are displayed. Filter by keyword or time range as needed.
SQL pattern list parameters
Parameter | Description |
Actions | Click View Details to open the Pattern Analysis page for this pattern. |
Username | The database account used to submit SQL statements in this pattern. |
Client IP Address | The IP address of the client that submitted the SQL statements. |
SQL Pattern | A representative SQL statement for the pattern. Click the |
Total CPU Cost | Total CPU time consumed by all statements in this pattern within the time range. The percentage shows this pattern's share of all patterns' CPU time. Values above 30% are highlighted. If this pattern's CPU percentage is high, check the CPU metrics for further analysis. |
Total Peak Memory | Total peak memory consumed by all statements in this pattern within the time range. The percentage shows this pattern's share of all patterns' peak memory. Values above 30% are highlighted. If this pattern's memory percentage is high, check the compute memory usage metrics for further analysis. |
Total Duration | Total execution duration of all statements in this pattern within the time range. The percentage shows this pattern's share of all patterns' total duration. Values above 30% are highlighted. If this pattern's duration percentage is rising, check the query response time metrics for correlation. |
Total Size of Read Data | Total size of data read by all statements in this pattern within the time range. The percentage shows this pattern's share of all patterns' read data. Values above 30% are highlighted. If this pattern reads a disproportionate amount of data, check the table data reading metrics for further analysis. |
Total Cost for Reading Data | Total CPU time spent reading data by all statements in this pattern within the time range. The percentage shows this pattern's share of all patterns' data reading CPU time. Values above 30% are highlighted. High values increase CPU utilization on reserved nodes (storage nodes). Check the CPU metrics of reserved nodes or original storage nodes for further analysis. |
Average CPU Cost | Average CPU time per execution within the time range. |
Maximum CPU Cost | Maximum CPU time for a single execution within the time range. |
Average CPU Cost for Reading Tables | Average CPU time spent reading data per execution within the time range. |
Maximum CPU Cost for Reading Tables | Maximum CPU time spent reading data for a single execution within the time range. |
Executions | Number of times statements in this pattern ran within the time range. Sort by this column to identify frequently executed patterns during cluster resource bursts. |
Failures | Number of failed executions within the time range. |
Average Total Time Consumed | Average total time per execution. Unit: milliseconds. |
Maximum Total Time Consumed | Maximum total time for a single execution. Unit: milliseconds. If Maximum Total Time Consumed is close to Average Total Time Consumed during normal operation, a spike in total time is likely caused by resource contention from other patterns rather than this pattern itself. |
Average Execution Duration | Average execution duration per statement within the time range. Unit: milliseconds. |
Maximum Execution Duration | Maximum execution duration for a single statement within the time range. Unit: milliseconds. For details, see Use query monitoring charts and SQL queries. |
Average Peak Memory | Average peak memory per execution within the time range. Unit: bytes. |
Maximum Peak Memory | Maximum peak memory for a single execution within the time range. Unit: bytes. If Maximum Peak Memory is much higher than Average Peak Memory, the data scanned by the pattern may have increased or the execution plan may have changed. Click View Details to identify the cause. |
Average Data Scanned | Average amount of data scanned per execution within the time range. Unit: MB. |
Maximum Data Scanned | Maximum data scanned for a single execution within the time range. Unit: MB. If Maximum Data Scanned is much higher than Average Data Scanned, the data scanned is unstable. Identify the cause before the pattern affects cluster performance. |
Table Name | Names of the tables scanned by statements in this pattern. |
icon to copy the full statement.Analyze a pattern
On the SQL Pattern tab, click View Details in the Actions column to open the Pattern Analysis page. The page shows key metrics for the pattern in time-series charts within the selected time range, including executions, total time consumed, execution duration, data scanned, and peak memory. For each metric, both maximum and average values are shown to help you distinguish between typical and outlier executions.
The page also lists all individual SQL statements that belong to this pattern within the time range. Click Diagnose in the Actions column to view diagnostic results and execution plans for a specific statement. For more information, see Use execution plans to analyze queries.