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 causes 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.
  • Identification of problematic SQL statements: identifies abnormal SQL statements and drills down to diagnose problematic SQL statements.

Procedure

  1. Log on to the AnalyticDB for MySQL console.
  2. In the upper-left corner of the page, select the region where the cluster is deployed.
  3. In the left-side navigation pane, click Clusters.
  4. On the V3.0 Clusters tab, find the cluster and click its ID in the Cluster ID column.
  5. In the left-side navigation pane, click Diagnostics and Optimization.
  6. Click the SQL Pattern tab.
    By default, SQL patterns for the last 30 minutes are displayed. You can search for SQL patterns by entering a keyword or specifying a time range. 1
    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 "Query details" section of this topic.
    SQL Pattern The statement of the SQL pattern. You can click the 3 icon to copy the SQL statement.
    Username The database username that is used to commit the SQL statement.
    Client IP The IP address of the client that commits the SQL statement.
    Table Name The tables scanned based on the SQL pattern.
    Creation Time The earliest commit time of the SQL statement within the query time range.
    Executions The number of executions of the SQL statement within the query time range. In the event of resource usage bursts in a cluster, you can sort SQL statements by Executions and identify the SQL statements that are frequently executed while the cluster runs abnormally. You can then further perform analysis and identify the cause to the increase in the executions of these SQL statements.
    Failures The number of failed executions of the SQL statement within the query time range.
    Average Total Time Consumed The average total amount of time consumed by the SQL statement within the query time range. Unit: milliseconds.
    Maximum Total Time Consumed The maximum total amount of time consumed by the SQL statement within the query time range. Unit: milliseconds. You can compare the values of Average Total Time Consumed and Maximum Total Time Consumed to understand whether an increase in the amount of time consumed by the SQL pattern is caused by other SQL patterns. If the value of Maximum Total Time Consumed is close to that of Average Total Time Consumed during normal running of the cluster, an increase in the amount of time consumed by the SQL pattern during abnormal running of the cluster is caused by other SQL patterns.
    Average Execution Duration The average execution duration of the SQL statement within the query time range. Unit: milliseconds.
    Maximum Execution Duration The maximum execution duration of the SQL statement within the query time range. Unit: milliseconds. For more information about the execution duration, see Use query monitoring charts and lists.
    Average Peak Memory The average peak memory size of the SQL statement within the query time range. Unit: byte.
    Maximum Peak Memory The maximum peak memory size of the SQL statement within the query time range. Unit: byte. You can compare the values of Maximum Peak Memory and Average Peak Memory to determine the stability of memory resource usage of the SQL statement. If the value of Maximum Peak Memory is far greater than that of Average Peak Memory, the amount of data being scanned may be increasing or the execution plan may be changed. You can click View Details in the Actions column to view the SQL statement list on the Query Details page and identify the cause.
    Average Data Scanned The average amount of data scanned based on the SQL statement within the query time range. Unit: MB.
    Maximum Data Scanned The maximum amount of data scanned based on the SQL statement within the query time range. Unit: MB. You can compare the values of Maximum Data Scanned and Average Data Scanned to determine the stability of the amount of data scanned based on SQL statements in SQL patterns of the same category. If the value of Maximum Data Scanned is far greater than that of Average Data Scanned, the amount of data scanned is unstable. You can then identify the cause.

Query details

The Query Details page shows the key metrics of SQL statements in charts for a specified time range. These metrics include executions, amount of time consumed, execution duration, amount of data scanned, and peak memory size. For the amount of time consumed, execution duration, amount of data scanned, and peak memory size metrics, maximum and average values are provided to facilitate comparison and analysis. The SQL statement list shows all SQL statements of the SQL pattern within the specified time range. You can click Diagnose to view the diagnostic results and execution plans of the SQL statement. For more information, see Use execution plans to analyze queries.

On the SQL Pattern tab, click View Details in the Actions column corresponding to an SQL pattern to go to the Query Details page. You can view the metric changes and SQL statement list of the SQL pattern. 2

Related operations