ANALYZE TABLE consumes a lot of memory

Translated
1

Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.

Original topic: analyze table消耗很大内存

| username: 特雷西-迈克-格雷迪

【TiDB Usage Environment】Production Environment
【TiDB Version】v6.5.9
【Reproduction Path】Issues with TiDB automatically analyzing tables
【Encountered Problem: Phenomenon and Impact】

analyze table lxx_pro.t_call_log;
The memory consumption of analyze reached 4G, while the table size is only 1.5G.

[2024/07/10 18:05:27.542 +08:00] [WARN] [expensivequery.go:118] [expensive_query] [cost_time=300.291215976s] [conn_id=695449359] [user=root] [database=l_pro] [txn_start_ts=451046441542221825]
[mem_max=“4332905993 Bytes (4.04 GB)”] [sql=“analyze table lxx_pro.t_call_log”]

【Resource Configuration】Go to TiDB Dashboard - Cluster Info - Hosts and take a screenshot of this page
【Attachments: Screenshots/Logs/Monitoring】

2
| username: 呢莫不爱吃鱼 | Original post link
  1. Check table data distribution: Use the EXPLAIN command to view the execution plan behind ANALYZE TABLE and check if the data distribution is even.
  2. Adjust parallelism: If the parallelism is set too high, try lowering it to reduce memory consumption.
  3. Check TiDB configuration: Review the settings of parameters such as tidb_analyze_auto_threshold and tidb_mem_quota_query, and adjust them according to the actual situation.
  4. Monitor resource usage: While executing ANALYZE TABLE, monitor the system’s resource usage, including CPU, memory, and disk I/O, to help identify bottlenecks.
3
| username: MrSylar | Original post link

There has always been a “phenomenon” that is not so “intuitive.” I hope TiDB can solve it soon or come up with a clear estimation formula.

4
| username: TiDBer_7S8XqKfl | Original post link

Looking forward to an official solution.

5
| username: TIDB-Learner | Original post link

Are there any other high-consumption tasks during the execution of the analyze command?

6
| username: yiduoyunQ | Original post link

How did you come to this conclusion?

7
| username: zhanggame1 | Original post link

Partitioned table? Also, did you check the number of keys in the table?

8
| username: TiDBer_7S8XqKfl | Original post link

Use TiDB’s monitoring tools to track resource usage during the ANALYZE TABLE operation to determine if there are excessive concurrent operations or configuration issues.

9
| username: vincentLi | Original post link

Is this the same reasoning for a table of 1.5G but the index might be 3 or 4G?

10
| username: 逍遥_猫 | Original post link
  1. Check TiDB configuration: Review the settings of parameters like tidb_analyze_auto_threshold and tidb_mem_quota_query, and adjust them according to the actual situation.
    There is also a parameter tidb_mem_quota_analyze=-1 by default. If tidb_mem_quota_query=3G at this time, is the memory controlled by tidb_mem_quota_query when executing ANALYZE TABLE?
    Is it determined by the smaller value of these two parameters whether ANALYZE will be terminated?
11
| username: tony5413 | Original post link

The consumption is quite high. How about trying the tidb_mem_quota_query limit?

12
| username: TiDBer_小阿飞 | Original post link

Currently, TiDB supports two types of statistics: tidb_analyze_version = 1 and tidb_analyze_version = 2. In versions v5.3.0 and later, the default value of this variable is 2. If you upgrade a cluster from a version prior to v5.3.0 to v5.3.0 or later, the default value of tidb_analyze_version does not change.

Version 2 statistics avoid the significant errors that may occur in large datasets due to hash collisions in Version 1, while maintaining estimation accuracy in most scenarios.

13
| username: TiDBer_7S8XqKfl-1158 | Original post link

The current version should not have resolved it.

14
| username: TiDBer_3Cusx9uk-0775 | Original post link

Adjust memory limit: In TiDB’s configuration file, adjust the performance.max-memory option to limit the maximum amount of memory that a single query can use.

15
| username: 濱崎悟空 | Original post link

It’s quite large.

16
| username: zhaokede | Original post link

For larger tables, it’s always like this. Not sure about the situation with the new version.

17
| username: xfworld | Original post link

The good strategy is probably not out yet.
One is very fast, definitely requires a lot of resources.
One can be slower, requiring relatively fewer resources.
One is balanced, with resource requirements balanced according to the actual situation.

It probably needs more time.

18
| username: dba-kit | Original post link

You can limit the time for automatic analyze to off-peak hours to prevent auto analyze from affecting business operations. At the same time, you can also reduce the amount of memory that can be used. The parameters related to analyze performance are:

+----------------------------------------+-------------------------------+
| Variable_name                          | Value                         |
+----------------------------------------+-------------------------------+
| tidb_analyze_partition_concurrency     | 10                            |
| tidb_analyze_skip_column_types         | json,blob,mediumblob,longblob |
| tidb_auto_analyze_end_time             | 08:00 +0800                   |
| tidb_auto_analyze_partition_batch_size | 1                             |
| tidb_auto_analyze_start_time           | 00:00 +0800                   |
| tidb_max_auto_analyze_time             | 0                             |
| tidb_mem_quota_analyze                 | -1                            |
+----------------------------------------+-------------------------------+
19
| username: TiDBer_iLonNMYE | Original post link

Can’t you perform sampling analysis? sample_size

20
| username: hawkingrei | Original post link

In the 6.5 series, if the configuration is not reasonable, there may be a risk of high memory usage during analyze. Additionally, the characteristics of the table itself can affect the memory usage of analyze, especially for large wide tables and partitioned tables. In this regard, optimizations have been made in version 7.5 and above, which you can try using. Additionally, tidb_enable_historical_stats might cause OOM; this has been disabled by default in the latest version, so you might consider turning it off directly.

  1. Provide the table structure.
  2. Check if the default configuration values for analyze have been changed.