Performance tuning

Overview of FAQ

• Why does CPU usage remain high after the write peak drops?

• In which scenarios is query performance slow in AnalyticDB for MySQL?

• How do I resolve query memory overflow?

• What causes the disk space limit error during queries, and how do I resolve it?

• How do I identify the cause of suddenly slower queries?

• How do I identify queries that consume large amounts of memory or high CPU?

• Why is the ANALYZE statement diagnosed as a slow query?

• Why is CPU load too high when using the statistics feature?

• How do I handle high CPU load and affected query response time caused by statistics collection?

• Why are statistics not updated for multiple days when I query statistics by using SELECT * FROM INFORMATION_SCHEMA.COLUMN_STATISTICS?

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

Why does CPU usage remain high after the write peak drops?

AnalyticDB for MySQL builds indexes in real time for written data to accelerate queries. Building indexes consumes system resources, especially when write peaks cause a surge in write volume, which makes the index building process more resource-intensive.

In which scenarios is AnalyticDB for MySQL query performance slow?

AnalyticDB for MySQL is a distributed system that leverages multi-node parallelism to improve the processing speed of massive data, making it suitable for large-scale data analysis. In some scenarios where the query computation is not particularly heavy, AnalyticDB for MySQL incurs distributed overhead, which may result in slower queries. In other scenarios, AnalyticDB for MySQL single-node clusters can better utilize storage indexes to improve query performance.

How do I resolve query memory overflow?

Query memory limits in AnalyticDB for MySQL are designed to ensure cluster stability and prevent cluster crashes caused by slow SQL queries. For troubleshooting and analysis of slow SQL queries, see Typical slow queries. Pay attention to peak memory and scan volume.

The following table summarizes the error codes, causes, and solutions for query memory overflow.

What causes the disk space limit error during queries, and how do I resolve it?

When an AnalyticDB for MySQL cluster is in elastic mode, queries may use the batch mode. In this mode, intermediate results are written to disk. If the intermediate result set is large, the disk space limit may be exceeded.

How do I identify the cause of suddenly slower queries?

Queries with the same SQL pattern may become slower due to the following reasons:

• Under the same conditions, the data volume of some tables increases, resulting in more data to process and slower queries.

• Query conditions have changed, such as an increase in the number of secondary partitions scanned or a wider query range.

• System pressure increases. This may be caused by an increase in write volume that consumes more system resources and affects query performance, or by bad SQL queries that consume excessive system resources.

You can click View Details in the Actions column of the target SQL pattern to view the resource consumption of different SQL patterns under normal conditions, such as execution count, query duration, execution duration, scan volume, and peak memory. For more information, see SQL Pattern.

How do I identify queries that consume large amounts of memory or high CPU?

For more information, see Typical slow queries.

Why is the ANALYZE statement diagnosed as a slow query?

The ANALYZE statement automatically initiated by the system during the maintenance window runs at a low priority (with I/O throttling and low CPU priority). Therefore, it executes slowly and takes a long time, and is diagnosed as a slow query. This generally does not affect your business. If the CPU load is not high, or if the high CPU load is not clearly related to the maintenance window, you can ignore this issue. If the CPU load remains high, see the following section on how to handle high CPU load and affected query response time.

Why is CPU load too high when using the statistics feature?

The following two factors can cause high CPU load:

• During the default maintenance window of 04:00 to 05:00, the system performs a full scan of tables to collect statistics for each column, which causes high CPU load during this period.

• Most statistics are collected incrementally, and resource consumption is generally not too high. The statistics feature is enabled by default only for AnalyticDB for MySQL Data Warehouse Edition clusters with kernel version 3.1.6 or later. Therefore, when you upgrade the cluster kernel version from a version earlier than 3.1.6 to 3.1.6 or later, a full statistics collection is triggered. This results in a heavy statistics collection workload and high CPU load for a period after the upgrade. The load subsides after the collection is complete.

When the CPU load is too high, you need to determine whether the query response time is affected. If the average query response time does not change significantly, the query response time is not affected. This is because the ANALYZE statement runs at a low CPU priority with I/O throttling, and your own queries may not be affected. Even if monitoring shows high CPU load, resources are prioritized for query tasks when queries are running.

How do I handle high CPU load and affected query response time caused by statistics collection?

When query response time is affected, try the following solutions in order:

• Adjust the maintenance window to an off-peak period.

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

• If you cannot determine an appropriate off-peak period, you can reduce the system query I/O limit. The default value is 50 MB. We recommend that you do not set it lower than 16 MB.

  set adb_config CSTORE_IO_LIMIT_SYSTEM_QUERY_BPS = 52428800;

• Assign statistics collection to a specific resource group, such as a low-priority resource group, to isolate the load. For more information, see Automatic statistics collection.

  set adb_config O_CBO_AUTONOMOUS_STATS_ACCOUNT = [user_name];

• Increase the expiration ratio of columns to reduce the collection workload. The default column expiration ratio is 0.1 (10%). The valid range is (0, 1). We recommend that you do not set this value to more than 0.5.

  set adb_config O_CBO_STATS_EXPIRED_RATIO = 0.1;

If none of the preceding solutions resolve the issue, you can try disabling the autonomous statistics feature (set adb_config O_CBO_AUTONOMOUS_STATS_ENABLED=false;). However, after you disable automatic statistics collection, query performance may degrade. If you need statistics later, you must maintain them manually. For more information, see Manual statistics collection.

Why are statistics not updated for multiple days when I query statistics by using SELECT * FROM INFORMATION_SCHEMA.COLUMN_STATISTICS?

Statistics are not updated due to the following reasons:

• The statistics of the table have not expired.

  The default statistics expiration ratio is 0.1 (10%), which means statistics are updated only when the data change volume (Update, Delete, Insert, or Replace) exceeds 10%. If the data change volume is small, you can continue to observe for another week and use the statistics as normal.

• There are too many tables and columns, and the data volume is large.

  By default, excluding incremental updates, only one hour per day is available for collection. If there are many tables and columns, they cannot all be updated within one day, and it may take up to a week for a full update. If there are many tables and columns (for example, more than 1,000 columns) and the statistics were updated within the past week, it is normal for statistics to not be updated for multiple days. You can continue to observe and use them as normal.

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

When you use the INSERT OVERWRITE batch import method, basic statistics are automatically collected immediately after data import is complete. When you use real-time import methods such as INSERT INTO and REPLACE INTO, you need to wait for the maintenance window or the incremental collection cycle after a build is complete. We recommend that you manually collect basic statistics once after importing data. For more information, see Manual statistics collection.