Manual Collection of Statistics

Translated
1

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

Original topic: 手动收集统计信息

| username: chenhanneu

【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】

2
| username: Jellybean | Original post link

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.

3
| username: chenhanneu | Original post link

Is there a table-level setting?
After disabling persistence, the persistence of other tables will also be disabled.

4
| username: tidb菜鸟一只 | Original post link

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.

5
| username: chenhanneu | Original post link

It looks like samplerate does not have a default value.

6
| username: redgame | Original post link

You can first use the following statement to clear the previously specified persistent parameters: ALTER TABLE <table_name> MODIFY COLUMN <column_name> STATISTICS NULL;

7
| username: chenhanneu | Original post link

I tried deleting first and then counting, it works.
DROP STATS xxx;
Thank you, everyone.

8
| username: system | Original post link

This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.