Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.
Original topic: 手动收集统计信息
【TiDB Usage Environment】Production / Testing / PoC
【TiDB Version】
【Reproduction Path】What operations were performed when the issue occurred
【Encountered Issue: Problem Phenomenon and Impact】
Analyze a table and check the records:
analyze table all columns with 256 buckets, 500 topn, 0.0008299058160259808 samplerate
After manually specifying parameters: --modified buckets and samplerate
analyze table all columns with 1024 buckets, 500 topn, 0.005 samplerate.
The default configuration will persist, and the next time analyze table is executed, it will use the previously specified parameters.
How can it be restored to the default values? Let the sampling rate be automatically generated.
【Resource Configuration】
【Attachments: Screenshots / Logs / Monitoring】
The official documentation provides relevant instructions: 常规统计信息 | PingCAP 文档中心
If you want to persist the column configuration in the ANALYZE
statement, set the system variable tidb_persist_analyze_options
to ON
to enable the ANALYZE configuration persistence feature. After enabling the ANALYZE configuration persistence feature:
- When TiDB automatically collects statistics or you manually execute the
ANALYZE
statement to collect statistics without specifying the column configuration, TiDB will continue to use the previously persisted configuration.
- When you manually execute the
ANALYZE
statement multiple times and specify the column configuration, TiDB will use the configuration specified in the latest ANALYZE
statement to overwrite the previously recorded persisted configuration.
Therefore, if you do not want to persist the configuration, you can set the system variable tidb_persist_analyze_options
to OFF
to disable ANALYZE configuration persistence.
Is there a table-level setting?
After disabling persistence, the persistence of other tables will also be disabled.
If you don’t want to disable persistence for the entire database, you can directly collect statistics for the table for which you want to restore the default values. Next time, it will use the default values for statistics collection.
It looks like samplerate
does not have a default value.
You can first use the following statement to clear the previously specified persistent parameters: ALTER TABLE <table_name> MODIFY COLUMN <column_name> STATISTICS NULL;
I tried deleting first and then counting, it works.
DROP STATS xxx;
Thank you, everyone.
This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.