Performance optimization-AnalyticDB(AnalyticDB)-阿里云帮助中心

CPU and write performance

Why does CPU utilization stay high after peak write throughput drops?

AnalyticDB for MySQL builds indexes on written data in real time to speed up queries. Index creation is resource-intensive, and a surge in write volume during peak throughput causes a corresponding spike in CPU usage. CPU utilization may remain elevated until the indexing backlog clears.

Why does a CPU overload occur when I use the statistics feature?

Two scenarios trigger this:

Maintenance window full scan: During the default maintenance window (04:00–05:00), the system performs a full table scan to collect column statistics, which causes CPU spikes.
Minor version upgrade to V3.1.6 or later: The statistics feature is enabled by default on Data Warehouse Edition (V3.0) clusters running V3.1.6 or later. When a cluster is upgraded from an earlier minor version, full statistics are collected for the first time. This causes CPU overloads during the first few days after the upgrade. CPU load returns to normal once the full scan completes.

In both cases, check whether query response time is actually affected. ANALYZE statements run with I/O throttling and low CPU priority, so query execution takes precedence over statistics collection. If average query response time is not significantly impacted, no action is needed.

Why are ANALYZE statements flagged as slow queries?

ANALYZE statements initiated automatically during the maintenance window run with I/O throttling and low CPU priority. Because they execute over extended periods, they may appear in slow query reports—but this does not affect services.

If the CPU is continuously overloaded, see What to do if query response time is affected by CPU overload from statistics collection.

Memory and disk errors

What to do if the query memory usage exceeds the limit

To maintain cluster stability, AnalyticDB for MySQL limits query memory usage. The following table describes the error codes, causes, and solutions.

Error code	Cause	Solution
`CLUSTER_OUT_OF_MEMORY(32001)`	The entire cluster is running low on memory. The system closes query connections that consume the most memory.	Set a concurrency limit on queries. See Priority queues and concurrency control. Then check the Diagnostics and Optimization page for slow queries with high peak memory usage and large amounts of scanned data.
`EXCEEDED_MEMORY_LIMIT(32003)`	The current query exceeds its memory limit.	Identify the operators in the SQL statement that are consuming the most memory.
`OUT_OF_PHYSICAL_MEMORY_ERROR(33015)`	The current query exceeds the internal memory pool limit.	Check for queries with high peak memory usage and large scanned data volumes during the time period when the error occurred.

For guidance on analyzing slow queries, see Typical slow queries.

What to do if disk usage exceeds the limit during a query

AnalyticDB for MySQL clusters in elastic mode for Cluster Edition may run queries in batch mode. In this mode, intermediate query results are written to disk. If the intermediate results are large, disk usage can exceed the limit.

Error code	Cause	Solution
`OUT_OF_SPILL_SPACE(32007)`	Disk space is insufficient to store data.	When using the bulk synchronous parallel (BSP) model in batch mode, shuffled data and operator states are written to disk. Reduce the number of concurrent queries. If the cluster has more than 32 compute nodes, set `batch_hash_partition_count` to a value greater than or equal to the number of compute nodes (default: 32). For example, on a 64-node cluster, set this parameter to 64 to distribute data more evenly and prevent disk overuse. If the issue persists, contact technical support.
`EXCEEDED_SPILL_LIMIT(32006)`	Disk spill limit exceeded.	Same as above.

Query slowdowns

How to identify the causes of a query slowdown

Queries using the same SQL pattern can slow down for three reasons:

More data: Query conditions are unchanged, but data has been added to the table, increasing the amount of data to process.
Broader query scope: Query conditions changed—for example, more partitions are scanned or the query range expanded.
Resource contention: Increased write activity or other slow queries are consuming cluster resources.

To diagnose which factor is causing the slowdown, go to the Diagnostics and Optimization page and use SQL pattern to find the affected SQL pattern. Click View Details in the Actions column to review resource consumption statistics—including number of executions, query duration, execution duration, amount of scanned data, and peak memory usage—across different time periods and compare them.

How to identify queries consuming large amounts of memory and CPU

See Typical slow queries for diagnostic steps and examples.

Statistics collection

What to do if query response time is affected by CPU overload from statistics collection

Try the following solutions in order:

Move the maintenance window to off-peak hours:

set adb_config O_CBO_MAINTENANCE_WINDOW_DURATION = [04:00-05:00];

Raise the I/O limit for system queries to 16 MB or higher (default: 50 MB):
```
set adb_config CSTORE_IO_LIMIT_SYSTEM_QUERY_BPS = 52428800;
```
Assign statistics collection to a low-priority resource group to isolate its load from production queries. For setup details, see the Automatic statistics collection section of the Statistics topic.
```
set adb_config O_CBO_AUTONOMOUS_STATS_ACCOUNT = [user_name];
```
Increase the expiration ratio to reduce the amount of data collected. The default is 0.1; keep it at 0.5 or below.
```
set adb_config O_CBO_STATS_EXPIRED_RATIO = 0.1;
```

If none of these resolve the issue, disable automatic statistics collection as a last resort. Note that this may degrade query performance and requires manual statistics collection going forward.

set adb_config O_CBO_AUTONOMOUS_STATS_ENABLED=false;

For manual collection steps, see the Manually collect statistics section of the Statistics topic.

Why does SELECT * FROM INFORMATION_SCHEMA.COLUMN_STATISTICS show statistics that are several days old?

Two causes are common:

Statistics have not expired yet. Statistics expire when the amount of updated, inserted, or replaced data reaches the expiration ratio (default: 0.1, or 10%). If only a small portion of your data has changed, the existing statistics are still valid. Monitor the cluster for another week.
Too many columns and tables. By default, the system has one hour per day to collect statistics that fall outside incremental updates. If more than 1,000 columns are involved, one day may not be enough—updates can take up to a week. This is expected behavior. Continue using the cluster normally and allow time for the update to complete.

Are statistics automatically updated after data is imported to a new table?

It depends on the import method:

`INSERT OVERWRITE` (batch import): Basic statistics are collected automatically after the import completes.
`INSERT INTO` or `REPLACE INTO` (real-time import): Statistics are collected during the next maintenance window, or an incremental collection task is triggered after each BUILD task completes.

For the most reliable results, manually collect basic statistics after importing data. See the Manually collect statistics section of the Statistics topic.

Query performance expectations

In which scenarios does AnalyticDB for MySQL provide low query performance?

AnalyticDB for MySQL is optimized for concurrent processing of large data volumes using a distributed architecture. In low-concurrency scenarios with small query volumes, the overhead of the distributed architecture can reduce performance compared to single-node systems. For these workloads, Basic Edition clusters make more efficient use of storage indexes and generally deliver better performance.