Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.
Original topic: analyze worker panic
【TiDB Usage Environment】Production
【TiDB Version】5.7.25-TiDB-v5.4.0
【Encountered Problem】Some tables fail to analyze, whether it’s auto or manual execution
【Reproduction Path】Manual execution of analyze table ${tableName}
【Problem Phenomenon and Impact】
After analyze fails, the statistics are inaccurate, leading to slow queries
【Attachments】
logs.zip (595.9 KB)
Please provide the version information of each component, such as cdc/tikv, which can be obtained by executing cdc version/tikv-server --version.
[t_account_erp_report|attachment](upload://tns1goTJgdDwp0dVu9jhdDS6DJt.) (845 bytes)
Failed to clear the table, re-collect. Did it run out of memory (OOM)? What version is tidb_analyze_version?
Check if tidb_analyze_version is equal to 2.
When tidb_analyze_version = 2, if an OOM occurs after executing the ANALYZE statement, please set the global variable tidb_analyze_version = 1, and then perform one of the following actions:
- If the ANALYZE statement is executed manually, manually analyze each required table:
select distinct(concat('ANALYZE ',table_schema, '.', table_name,';')) from information_schema.tables, mysql.stats_histograms where stats_ver = 2 and table_id = tidb_table_id ;
- If the ANALYZE statement is automatically executed by TiDB after enabling auto analyze, use the following SQL statement to generate the DROP STATS statements and execute them:
select distinct(concat('DROP STATS ',table_schema, '.', table_name,';')) from information_schema.tables, mysql.stats_histograms where stats_ver = 2 and table_id = tidb_table_id ;
The tidb_analyze_version version is 2, and the default value is 2. After changing it to 1, it can be executed successfully, and no OOM logs have been found.
Yes, after v5.3, the default value is 2. After trying to change it to 1, executing ANALYZE TABLE was successful.
Has the value of the variable tidb_enable_fast_analyze been modified? The default is 0.
I haven’t modified it. It is currently off.
Have you tried this?
When tidb_analyze_version = 2, if an OOM occurs after executing the ANALYZE statement, set the global variable tidb_analyze_version = 1, and then perform one of the following actions:
- If the ANALYZE statement is executed manually, manually analyze each required table:
select distinct(concat('ANALYZE ',table_schema, '.', table_name,';')) from information_schema.tables, mysql.stats_histograms where stats_ver = 2 and table_id = tidb_table_id ;
- If the ANALYZE statement is automatically executed by TiDB after enabling automatic analyze, use the following SQL statement to generate and execute the DROP STATS statement:
select distinct(concat('DROP STATS ',table_schema, '.', table_name,';')) from information_
If the table is large and the statistics are inaccurate, increase the sampling rate with WITH FLOAT_NUM SAMPLERATE. You can also refer to this link Full Collection for several collection options.
I have tried setting the version to 1, and the analyze table statement can be executed successfully, but I did not find any OOM exception information.
Statistics collected successfully, but the information is inaccurate? Is the data volume large? Is the data distribution balanced? Try adjusting the default ANALYZE options.
Check this parameter: feedback-probability
It’s not a matter of whether the statistics collection is accurate or not. When using version 2, the collection fails, but it succeeds when switched to version 1. The table is very small with a small amount of data.
This parameter has a default value of 0 and has not been enabled.
A couple of days ago, one of my nodes experienced an OOM because of this parameter.
This topic was automatically closed 60 days after the last reply. No new replies are allowed.
|
username: 朱振文啊
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: