The AnalyticDB for MySQL Diagnose feature analyzes your cluster's health over a specific time period. It correlates monitoring data, logs, and table status information across dimensions such as resource levels, load, SQL, operators, and storage to help you troubleshoot issues more efficiently.
Procedure
-
Log on to the AnalyticDB for MySQL console. In the upper-left corner, select the cluster's region. In the left-side navigation pane, click Clusters, and then click the target cluster ID.
-
In the left-side navigation pane, click Monitoring Information.
-
Hover over a metric curve or select a time range from the drop-down list, and then click Diagnose.
ImportantYou can diagnose data from the past 14 days. The maximum time range for a single diagnosis is one hour.
The diagnostic results page opens automatically.
Diagnostic report
The diagnostic results include bad SQL detection, abnormal pattern detection, business metric detection, compute layer detection, and storage layer detection.
Bad SQL
The bad SQL detection report lists the top 10 queries for each of the following metrics, sorted in descending order: Top Stages SQL, Most Memory-Consuming SQL, Most CPU-Consuming SQL, Top Data Read SQL, Top Data Output SQL, and Most Time-Consuming SQL. For the Most Time-Consuming SQL metric, the system automatically runs a self-diagnosis on each query in the background. Click the 
icon to the left of the Actions column to immediately view the SQL diagnosis results. To analyze a query further, click Diagnose in the Actions column to go to the query details page, view the execution plan, and perform self-service analysis.
Abnormal patterns
This detection identifies SQL patterns with abnormal metrics—such as a sudden increase in submission count or peak memory usage—within the specified time period.This detection includes Peak Memory, Query Duration, Operator Cost, Amount of Output Data, and Query Submission. Each detected pattern includes the following three fields:
-
SQL pattern: The SQL template. You can use this template to identify the specific SQL statements that match the abnormal pattern.
-
Detection Result Details: The cause of the abnormality, including details such as percentile values and comparisons of minimum and maximum values.
-
Other Related Metrics: Related metrics for the current abnormal pattern, such as peak memory, operator cost, and query duration, to help you perform a comprehensive assessment and analysis.
Business metrics
Business metric detection checks various business metrics of the cluster, including cluster connections detection and query response time detection.
Cluster connections
This detection compares data from the specified time range (for example, 14:00 to 15:00) with data from the same time range on the three previous days. If 80% of the data points show a growth rate of more than 20% compared to the same periods on the three previous days, the number of connections is flagged as increasing. In this case, you should check for new business traffic or abnormal connections.
Query response time
Query response time detection uses the same logic as cluster connections detection. If 80% of the data points show a growth rate of more than 20% compared to the same periods on the three previous days, the query response time is flagged as increasing. You should check for increased traffic or slow queries.
The growth rate is calculated by using the following formula: (Current data point - Past data point) / Past data point × 100.
Compute layer
Compute layer detection includes abnormal operator detection, node status detection, CPU utilization skew detection, average CPU utilization detection for resource groups, CPU utilization skew detection among resource groups, and correlation analysis of time series metrics.
Abnormal operators
-
Operator details
This detection identifies abnormal operators (for example, Join, Aggregation, Sort, and Window operators), sorted in descending order by the selected metric. You can analyze abnormal operators by using fields such as memory consumption, CPU consumption, and the amount of input and output data. You can also click Diagnose in the Actions column to view the operator's original execution plan and identify the source SQL statement.
-
Operator summary
Because multiple abnormal operators can collectively increase resource consumption, this detection also provides an aggregated summary of two key categories: most CPU-consuming operators and most memory-consuming operators. This summary helps you view the maximum values for different operator metrics and identify abnormal operators.
Node status
An executor node under high pressure or with internal exceptions may become unhealthy, leading to query failures or slow queries. This detection automatically checks the status of all executor nodes during the specified time period and reports an exception if any node is unhealthy, helping you pinpoint the cause of related issues.
CPU utilization skew
This detection checks whether CPU utilization is balanced among different executor nodes. If the maximum CPU utilization across all executor nodes is twice their average CPU utilization, and this state persists for more than 80% of the specified time period, the system reports CPU utilization skew among the executor nodes. If this occurs, consider monitoring the cluster and checking for data skew. For more information, see data modeling diagnostics.
Detection results have three levels: NORMAL, WARNING, and CRITICAL. If the level is CRITICAL, prioritize resolving the issue to prevent impacting your business workloads.
Average CPU utilization for resource groups
This detection checks the average CPU utilization of resource groups during the specified time period. If the CPU utilization of a resource group exceeds 80% and this state persists for more than 80% of the specified time period, the system flags the resource group for high CPU utilization. If this occurs, consider evaluating whether you need to adjust the resources of the resource group. For more information, see modify a resource group.
Detection results have three levels: NORMAL, WARNING, and CRITICAL. If the level is CRITICAL, prioritize resolving the issue to prevent impacting your business workloads.
CPU utilization skew among resource groups
This detection checks whether CPU utilization is balanced among different resource groups. If the CPU utilization of the resource group with the highest usage is more than 1.5 times the average CPU utilization across resource groups, and this state persists for more than 80% of the specified time period, the system reports CPU utilization skew among the resource groups. If this occurs, consider evaluating whether to adjust resources across different resource groups. For more information, see modify a resource group.
Detection results have three levels: NORMAL, WARNING, and CRITICAL. If the level is CRITICAL, prioritize resolving the issue to prevent impacting your business workloads.
Time-series metric correlation
When the Average CPU Utilization of Compute Node and Maximum CPU Utilization of Compute Node metrics are abnormal, this analysis identifies correlated metrics that may be the cause. Potential root-cause metrics include QPS, BUILD jobs, Update TPS, Deletion TPS, and Write TPS.
For example, if QPS correlates with an increase in the Average CPU Utilization of Compute Node, it suggests that a high query volume is driving up the CPU usage of compute nodes.
For an accurate correlation analysis, ensure the diagnosis period covers the time when the key metric abnormalities occurred.
Storage layer
Storage layer detection includes table creation and access detection, node status detection, maximum CPU utilization detection, average CPU utilization detection, CPU utilization skew detection, and correlation analysis of time series metrics.
Table creation and access
-
Table access detection
Table access detection automatically checks the tables accessed during the specified time period. It includes five metrics: maximum data read volume, average data read volume, maximum data read duration, average data read duration, and number of table accesses. The results are sorted in descending order based on the current metric.
-
Details of tables with data skew
In AnalyticDB for MySQL, partitioned tables require you to select an evenly distributed field as the distribution field. When data is written, it is distributed to different worker nodes based on this distribution field. A non-uniform distribution field causes data skew. This feature detects data skew by first identifying the largest tables and then analyzing their data distribution. Optimizing skewed tables can improve query performance, balance data volume across disk space, and reduce the risk of disk locks.
-
Details of tables with improper partitioning
This detection first locates the tables with the largest data volumes and then checks whether these tables have unreasonable partitioning.
Node status
A worker node under high pressure or with internal exceptions may become unhealthy, leading to query failures or slow queries. This detection automatically checks the status of all worker nodes during the specified time period and reports an exception if any node is unhealthy, helping you pinpoint the cause of related issues.
Maximum CPU utilization
This detection checks the maximum CPU utilization of all worker nodes during the specified time period. If the maximum CPU utilization across all worker nodes exceeds 90% and this state persists for more than 80% of the specified time period, the system flags CPU utilization as high. In this case, check for slow queries. For more information, see Typical slow queries.
Detection results have three levels: NORMAL, WARNING, and CRITICAL. If the level is CRITICAL, prioritize resolving the issue to prevent impacting your business workloads.
Average CPU utilization
This detection checks the average CPU utilization of all worker nodes during the specified time period. If the average CPU utilization across all worker nodes exceeds 80% and this state persists for more than 80% of the specified time period, the system flags CPU utilization as high. In this case, check for slow queries. For more information, see Typical slow queries.
Detection results have three levels: NORMAL, WARNING, and CRITICAL. If the level is CRITICAL, prioritize resolving the issue to prevent impacting your business workloads.
CPU utilization skew
This detection checks whether CPU utilization is balanced among different worker nodes. If the maximum CPU utilization across all worker nodes is twice their average CPU utilization, and this state persists for more than 80% of the specified time period, the system reports CPU utilization skew among the worker nodes. If this occurs, consider monitoring the cluster and checking for data skew. For more information, see data modeling diagnostics.
Detection results have three levels: NORMAL, WARNING, and CRITICAL. If the level is CRITICAL, prioritize resolving the issue to prevent impacting your business workloads.
Time-series metric correlation
When the Average CPU Utilization of Storage Node and Maximum CPU Utilization of Storage Node metrics are abnormal, this analysis identifies correlated metrics that may be the cause. Potential root-cause metrics include QPS, BUILD jobs, Update TPS, Deletion TPS, and Write TPS.
For example, if QPS correlates with an increase in the Average CPU Utilization of Storage Node, it suggests that a high query volume is driving up the CPU usage of storage nodes.
For an accurate correlation analysis, ensure the diagnosis period covers the time when the key metric abnormalities occurred.