AnalyticDB:Tune cluster performance with monitoring information

High average CPU utilization

The average CPU utilization metric shows the average CPU usage across multiple nodes at a specific point in time. High average CPU utilization can affect cluster stability and slow down queries and writes. If the average CPU utilization remains high, it poses a significant risk to your cluster and requires immediate optimization.

Common causes of high average CPU utilization include:

• Queries

  High CPU utilization from queries can be caused by bad SQL, such as queries with complex computational logic, large data processing volumes, or JOINs that lack conditions and create a Cartesian product. You can use the diagnostics feature to locate problematic queries:

  • In the bad SQL detection results, queries that are long-running, read large amounts of data, have many stages, or are CPU-intensive can cause high cluster CPU utilization. These queries require further analysis based on the diagnostic results or the execution plan.

  • The abnormal pattern detection feature identifies unusually submitted patterns from the perspective of SQL templates. Similar to bad SQL, patterns that cause high CPU utilization need to be analyzed based on factors such as abnormal data read volume, high CPU consumption, and unusual query durations. These abnormal patterns can increase CPU utilization.

  • If you observe high CPU utilization on compute or storage nodes, you can analyze the issue by using the compute layer detection and storage layer detection results from the diagnostics feature. The abnormal operator detection feature uses operator details and summaries to filter and identify abnormal operators based on their CPU consumption.

• Writes

  Write operations (including INSERT, UPDATE, DELETE, REPLACE, INSERT OVERWRITE, and INSERT INTO SELECT) also consume CPU resources and can lead to high CPU utilization on storage nodes. In this case, check if monitoring metrics such as delete TPS, write TPS, update TPS, and load TPS also show a sudden increase.

  Common causes of high CPU utilization due to writes include:

  • Long primary keys

    When a primary key is very long, the primary key index becomes large and consumes more CPU resources during query processing.

  • DELETE SQL

    If a single DELETE WHERE statement matches many rows, the compute engine must calculate the primary keys for all matched rows and then send them individually to storage nodes for deletion. A single DELETE SQL operation can be amplified significantly, leading to high CPU utilization.

  • UPDATE SQL

    If a single UPDATE WHERE statement matches many rows, the compute engine must find the primary keys of all matched rows, update their corresponding field values, and then send the changes to storage nodes to mark old rows as deleted and append new ones. A single UPDATE SQL operation can be amplified significantly, resulting in high CPU utilization.

  • INSERT OVERWRITE

    A batch load performs CPU-intensive operations such as data parsing, sorting by clustered index fields (if a clustered index exists), and building primary key and regular indexes, with each shard requiring one thread for this work. Although there is a limit on concurrent batch load operations (for example, a maximum of two concurrent batch load SQL queries), CPU utilization can still become high because each shard needs a dedicated thread for these tasks.

  • INSERT INTO SELECT

    When a large amount of data is written in a short time, background BUILD jobs can accumulate, causing an increase in real-time data. If a query involves this real-time data, the database must scan a large volume of it because real-time data is not indexed. This leads to high CPU utilization.

• Build

  A BUILD job performs tasks such as building indexes and creating or clearing partitions. These tasks can cause high CPU utilization on storage nodes. You can compare the CPU utilization and the number of BUILD jobs in the console to identify the correlation between these two metrics.

  Note

  • For more information about BUILD, see BUILD.

  • To learn how to locate and analyze high resource usage caused by BUILD jobs, see Increased number of BUILD jobs.

CPU utilization skew

The maximum CPU utilization metric represents the CPU utilization of the busiest node at a specific point in time. A large and persistent gap between maximum and average CPU utilization indicates uneven task distribution within the cluster, leading to CPU utilization skew. This means some nodes are heavily loaded while others are lightly loaded. Severe skew (for example, a difference of more than 2x) can significantly impact cluster stability and waste resources. This is because distributed query subtasks are bottlenecked by the node with the highest CPU utilization, preventing further performance improvements. Often, the only solution is to scale up the cluster, even though other nodes are not highly utilized.

Possible causes for CPU utilization skew:

• Source table skew

  This typically occurs when the distribution key chosen during table creation is not uniform, causing significant differences in the amount of data across shards.

  As shown in the following figure, a large table is unevenly distributed. Shard_0 and Shard_1 on Storage Node 0 contain a large amount of data, while Shard_2 and Shard_3 on Storage Node 1 have less data. When you query this table, Storage Node 0 will likely process more data than Storage Node 1. This causes the CPU utilization of Storage Node 0 to be persistently higher than that of Storage Node 1, resulting in CPU utilization skew.

  

  For information about how to diagnose source table skew, see Storage diagnostics. The diagnostics feature also detects skewed tables that occupy large amounts of disk space to help you analyze resource skew.

• Intermediate data skew

  Intermediate data skew is different from source table skew. In this scenario, the source table data may be evenly distributed across shards, but the distribution of values for a specific field is not uniform.

  When you perform a group-by aggregation query or use an unevenly distributed field as a JOIN condition, AnalyticDB for MySQL redistributes the data across different nodes based on that field. After redistribution, data with the same field value is sent to the same node, which can cause data skew.

  As shown in the following figure, a table is distributed by field 'a'. Because field 'a' has uniform values, the data is evenly distributed across storage nodes. However, when you group by field 'b' (group by b), Storage Node 1 sends rows where 'b' is 'b1' to Compute Node 1. To ensure Compute Node 1 has all rows where 'b' is 'b1', Storage Node 2 also sends its 'b1' rows to Compute Node 1, while sending 'b2' rows to Compute Node 2. As a result, Compute Node 1 receives far more rows than Compute Node 2, creating data skew. For subsequent computations, Compute Node 1 will consume more cluster resources.

  

  To troubleshoot uneven CPU utilization caused by intermediate data skew, you can analyze the query's Stage-level diagnostic results to pinpoint the problem.

High maximum and moderate average CPU utilization

Large query result size

Cause: A single SQL query that returns a very large amount of data can increase the CPU utilization of access nodes.

Solution: Add more precise query conditions to narrow the search scope and reduce the amount of returned data. Alternatively, use paginated queries to avoid loading too much content at once. If you need to export a large amount of data, you can use external tables.

High CPU usage by the optimizer

Cause: When cluster queries per second (QPS) is high and SQL is complex, the optimizer can consume a large amount of CPU resources.

Solution: Start by enabling the plan cache. After enabling it, observe whether CPU utilization decreases significantly. If it does not, submit a ticket for technical support.

Writes involving long fields

Cause: When a table has writes involving long fields, access nodes consume more resources to process these fields, leading to high CPU utilization.

Solution: Run the following statement to check for tables with long-field writes. If found, optimize the business logic for that table by limiting its field length or splitting the long fields.

SELECT *
FROM 
    (SELECT schema_name,
        table_name,
        column_name,
         cast(json_extract(stats,
        '$.avgSize') AS bigint) AS avg_size
    FROM INFORMATION_SCHEMA.COLUMN_STATISTICS ) tmp
ORDER BY  avg_size DESC limit 20;

High disk I/O throughput

Disk I/O throughput indicates the throughput of the underlying storage media, measured in MB/s. For the maximum value, see ESSD. This upper limit is a theoretical value obtained from tests under ideal conditions and may not reflect the actual cluster load. Typically, the actual load can reach about 80% of this nominal value.

High disk IOPS

Disk IOPS indicates the number of I/O operations per second on the underlying storage media. For the maximum disk IOPS value, see ESSD. This upper limit is a theoretical value obtained from tests under ideal conditions and may not reflect the actual cluster load. Typically, the actual load can reach about 80% of this nominal value.

High compute memory utilization

Analytical databases consume a significant amount of memory resources when performing large-scale data computations. Memory-intensive SQL queries typically include aggregation, topN, window, and join operators:

• Aggregation operator

  An aggregation operator consumes high memory mainly because AnalyticDB for MySQL temporarily stores grouping information in memory. If the grouping field has many unique values, a large amount of memory is consumed in the final stage of distributed aggregation. The partial stage consumes less memory because it does not require global aggregation; each node can send data to downstream nodes after completing local aggregation on a subset of data.

• TopN operator

  When AnalyticDB for MySQL performs a TopN calculation (for example, in a SQL query with ORDER BY id LIMIT m,n), the TopN operator in AnalyticDB for MySQL caches a large amount of data in memory to complete the final global sorting if the value of m is large. This process consumes a large amount of memory resources.

• Window operator

  A window operator is used to compute window functions. Similar to an aggregation operator, it needs to temporarily store a large amount of data in memory to achieve the final semantic result.

• Join operator

  AnalyticDB for MySQL supports standard JOIN query operations. The system typically uses hash and index algorithms to implement the join process. For more information, see Operators. The hash algorithm caches the smaller table (the build table) in memory and builds a hash table for it to speed up the join process. The following factors can cause the hash table to occupy a large amount of memory:

  • The build table itself is large:

    AnalyticDB for MySQL uses statistics to estimate the size of the tables on both sides of a JOIN operation and chooses the smaller one as the build table. However, it is still possible for the build table to be large.

  • Outdated or inaccurate statistics:

    If the tables in a JOIN operation are not source tables but are the result of multiple aggregations, filters, or other joins, it is difficult to accurately estimate their sizes based on source table statistics. Additionally, if statistics are outdated, a larger table might be incorrectly chosen as the build table and used to build the hash table. For more information, see Statistics.

  • Left Join:

    Due to semantic requirements, the right table of a LEFT JOIN must be used to build the hash table to ensure the correct result. If the right table of a LEFT JOIN is large, the join operation will consume a large amount of memory.

For more information about these operators, see Operators.

When there is a high concurrency of SQL queries containing these operators, or when a single operator consumes a large amount of memory, the compute memory utilization metric increases. This can affect cluster stability and cause query errors. Common errors include:

• Query exceeded reserved memory limit: A query used more memory than its limit on a single node.

• Query exceeded system memory pool limit: A single field is too long, or too many columns are involved in the computation.

• Out of Memory Pool size pre cal. available: The physical memory pool is exhausted.

• The cluster is out of memory, and your query was killed: When the cluster runs out of memory, it terminates the largest currently running query.

To reduce compute memory utilization, tune the SQL queries that contain these operator types. For detailed instructions, see Operator-level diagnostic results.

Increased number of BUILD jobs

A BUILD job primarily builds indexes for written data, cleans up expired data, and executes asynchronous DDL tasks. This process transforms data from a write-optimized to a read-optimized state. In some cases, BUILD jobs can consume high CPU and disk I/O resources on storage nodes, which may affect other operations and lead to cluster stability issues. The following table describes the BUILD metrics.

If an increase in the number of BUILD jobs affects the CPU utilization of read/write nodes, you can investigate and analyze the issue from the following perspectives:

• Large single partitions in partitioned tables. When a single partition is large, it is more likely to be written to, updated, or deleted from, which more easily triggers a BUILD on that partition. You can use Storage diagnostics to locate these types of tables and optimize their structure.

• Very large non-partitioned tables are also a common cause. When a non-partitioned table is large, it is also more likely to be involved in writes, updates, or deletes, which can easily trigger a full-table BUILD.

• A large number of read and write requests cause sustained high CPU utilization on storage nodes, which in turn slows down the execution of BUILD jobs.

Increased number of offline nodes

When nodes within an AnalyticDB for MySQL cluster become unavailable, they go offline. Node failures degrade cluster stability, causing slower queries and writes, as well as query errors. When a node goes offline, analyze whether CPU utilization is persistently high or if I/O-related metrics are consistently at their limits.

P95 curves

AnalyticDB for MySQL provides P95 monitoring curves for metrics such as CPU utilization, access node CPU utilization, compute memory utilization, disk I/O throughput, disk IOPS, disk I/O utilization, and disk I/O wait time. The P95 metric represents the value at or below which 95% of the observations fall. For example, take the CPU utilization of compute nodes. If a cluster has 100 compute nodes, the CPU utilization of all nodes is sorted in ascending order at a given point in time. The CPU utilization of the 95th node is the P95 CPU utilization for compute nodes.

The differences between maximum, average, and P95 values are as follows:

• The maximum value simply indicates the upper limit of the data. In the presence of outliers or extreme values, the maximum value of a monitoring metric can be influenced by these individual points and may not accurately represent the general or typical condition of the dataset.

• The average value is intended to describe the central tendency of the data, but it may not accurately reflect the general state if the dataset contains outliers or has a skewed distribution.

• The P95 value focuses on the performance of the upper portion of the data while ignoring the most extreme data points. It is suitable for evaluating performance or levels in most situations.

Increased query response time

The query response time metric represents the time from when a query is submitted, through queuing, to when it completes execution. For more information about execution time in AnalyticDB for MySQL, see Monitoring FAQ.

A sudden increase in the cluster's query response time may be caused by the following factors:

• Bad SQL

  Bad SQL consumes a large amount of cluster resources, affecting the execution of other SQL queries.

• Abnormal patterns

  Abnormal patterns can manifest in two ways: either a low-resource query is executed with very high frequency, or a high-resource query creates a performance bottleneck for the entire cluster. This ultimately affects other queries and increases the overall query response time.

• Increased write volume

  An increased write volume consumes more CPU and disk I/O resources, triggers more BUILD jobs, and ultimately leads to an increase in the overall query response time.

On the Monitoring Information page in the console, you can select the time range when the query response time increased and run diagnostics to analyze the specific cause based on the various diagnostic results.

Increased query wait time

When a query is submitted to an access node, the cluster determines whether to queue the query based on the access layer's queue size settings. This prevents too many SQL queries from running concurrently, which could increase cluster pressure and affect overall stability. For more information, see Concurrency control.

A sudden increase in query wait time is usually caused by a decrease in the cluster's internal execution efficiency. This can be due to bad SQL or abnormal patterns consuming a large amount of cluster resources. You can use diagnostics to check the multi-dimensional bad SQL detection results and abnormal pattern detection results. An increase in the amount of data being written, which consumes more CPU and I/O resources on storage nodes, can also lead to longer query wait times.

Increased query failure rate

The query failure rate metric only counts the proportion of failed queries and does not provide the reasons for failure. Query failures can have multiple causes. Common causes and solutions are as follows:

• Query failure due to SQL statement issues

  • Syntax error

    The SQL statement does not conform to the SQL syntax defined by AnalyticDB for MySQL. An error is usually reported during the SQL parsing stage. Examples include incomplete SQL statements, incorrect formatting, and missing keywords or punctuation.

  • Semantic error

    The SQL statement conforms to the SQL syntax defined by AnalyticDB for MySQL, but an error in a database object is found during the semantic check. An error is reported during the semantic analysis stage. Examples include an incorrect table name, a non-existent column, a missing GROUP BY field, or an incorrect function parameter type.

• Query failure due to internal cluster issues

  • Query timeout

    AnalyticDB for MySQL has a default query timeout. You can also configure a query timeout based on your business needs. If a query's execution time exceeds this limit, the query fails.

    Note

    • For information about the default query timeout, see Limits.

    • To learn how to modify the query timeout, see Config and Hint configuration parameters.

  • High cluster pressure

    When the cluster experiences high pressure, internal node communication timeouts or internal process failures can cause query failures.

• Read-only

  When the system detects an issue with the Raft log, it immediately sets the process state to read-only. In this state, write operations will fail.

• Timeout

  If the system cannot consume the Raft log queue in time (for example, due to slow writes caused by long primary keys), backpressure occurs, which eventually slows down the write speed and causes timeout errors.

Amount of data read from tables

In AnalyticDB for MySQL, data is stored on different storage nodes. The Amount of data read from tables metric shows the total amount of data returned from the storage layer to the compute layer by all SQL queries at a specific point in time.

As shown in the following figure, at a specific point in time (Time_1), 6 SQL queries (query1, query2, query3, query4, query5, and query6) read data from 6 tables (user, report, customer, test, region, and partition). At this point in time, the total amount of data read from tables is 20.1 GB (calculated as 1.6 + 2 + 3 + 0.7 + 4.8 + 8 = 20.1 GB). The average amount of data read from tables is 6.7 GB (calculated as total data read from all storage nodes / number of storage nodes, which is (1.6 + 2 + 3 + 0.7 + 4.8 + 8) / 3 = 6.7 GB). The maximum amount of data read from tables is 12.8 GB (calculated as 4.8 + 8 = 12.8 GB).

The total, maximum, and average amounts of data read from tables can reflect the pressure of SQL queries on the cluster to some extent.

• When the average amount of data read from tables suddenly increases, it indicates that a large amount of data is being sent from the storage layer to the compute layer for processing, which consumes more CPU and memory resources. At the same time, the increased amount of data read from the storage layer also consumes more disk I/O resources.

• When there is a large difference between the maximum and average amounts of data read from tables, it indicates that the amount of data being read from different storage nodes varies. This difference in data processing load can cause some nodes to reach their resource bottlenecks prematurely, which in turn affects the overall performance of the cluster. This situation is often caused by suboptimal table design, such as choosing an uneven distribution key for some tables, leading to uneven data distribution across multiple storage nodes.

Increased write, delete, and update response times

The write response time, delete response time, and update response time metrics indicate the time taken to process each row for INSERT INTO VALUES, DELETE, and UPDATE operations, respectively. Response times are generally affected by the following factors:

• High CPU utilization on storage nodes

  This can be caused by other factors, such as bad SQL or an increase in write, delete, or update TPS.

• High write-related I/O metrics on storage nodes

  Write-related I/O metrics on storage nodes include disk I/O throughput and disk IOPS. These metrics can increase for the following reasons:

  • An increase in the number of BUILD jobs.

  • The system performed backup or scaling operations.

  Because these operations all require disk writes, they can affect the relevant response times.

  If write response times continue to increase during data processing, we recommend that you schedule large-scale data processing jobs during off-peak hours and limit the number of concurrent write jobs to reduce disk I/O pressure.