Statistics Inaccuracy in TiDB v6.1.7

Translated
1

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

Original topic: TIDB v6.1.7版本统计信息不准

| username: residentevil

[TiDB Usage Environment] Production Environment
[TiDB Version] v6.1.7
[Reproduction Path] Import data through DTS in full
[Encountered Problem: Problem Phenomenon and Impact] Executing show stats_healthy reveals that 70% of the tables (total number of tables is over 20,000) have a statistics value of 0, while only 30% of all tables have a value of 100. Has TiDB done any special handling for table statistics collection? Because this kind of problem has not been encountered in native MySQL.

2
| username: Fly-bird | Original post link

Directly query the table to see how many there are.

3
| username: xfworld | Original post link

There are two ways to handle statistics:

  1. Automatic processing, which requires enabling and configuring rules.
  2. Manual processing, which can be done by running SQL statements.

If the data volume changes and statistics are not collected in time, it can lead to inconsistencies in query optimization…

Reference documents:

4
| username: residentevil | Original post link

There is no problem with querying the table data volume, but I am just worried that inaccurate statistics will lead to inaccurate execution plans.

5
| username: residentevil | Original post link

I understand that the collection of statistics needs to be handled automatically by the product itself. From the official documentation, there isn’t much content about automatic handling, mainly discussing the tidb_analyze_version configuration, which is currently set to 2 by default. After comparing some configurations related to statistics collection with the official documentation, I didn’t find any clues. Is it possible that TiDB’s statistics collection tasks run in a single thread? Wouldn’t the collection speed be too slow in scenarios with tens of thousands of tables? :sweat_smile:

6
| username: xfworld | Original post link

Yes, the automated tasks are still single-threaded. :+1::+1::+1::+1::+1:

If you find it slow, you can try doing it manually. :yum:

7
| username: residentevil | Original post link

It seems there could be a serious pitfall here. If one day the statistics for online tables (in scenarios with a relatively large number of tables) become inaccurate, the execution plan could be completely messed up.

8
| username: xfworld | Original post link

No worries, the TiDB version is still iterating, and the subsequent resource isolation capabilities are aimed at maximizing resource utilization scenarios, which will gradually improve.

Currently, if you encounter similar issues, you may need to handle them manually (or use hints, so even if the collected statistics are outdated, it won’t affect the SQL optimization and matching process).

9
| username: gcworkerishungry | Original post link

Version 6.5 introduced a parameter that allows for greater concurrency if there are sufficient CPU cores and memory:

tidb_auto_build_stats_concurrency introduced in v6.5.0

  • Scope: GLOBAL
  • Persisted to cluster: Yes
  • Type: Integer
  • Default value: 1
  • Range: [1, 256]
  • This variable is used to set the concurrency level for automatic statistics updates.
10
| username: residentevil | Original post link

Looks like we need to upgrade the version again, haha.

11
| username: 路在何chu | Original post link

The newly imported data needs to have all statistics collected. You can take a look at the Lightning import; it ultimately needs to analyze all tables.

12
| username: 路在何chu | Original post link

If you don’t collect it immediately, you’ll have to wait for it to be collected automatically.

13
| username: residentevil | Original post link

Native MySQL definitely doesn’t need it [InnoDB and RocksDB].

14
| username: residentevil | Original post link

Moreover, I also found an issue: the manually triggered analyze table operation is actually performed synchronously (with a sampling ratio of around 10%, so it takes a relatively long time), unlike native MySQL, which performs it asynchronously.

15
| username: residentevil | Original post link

I see that the parameter tidb_build_stats_concurrency is available in version 6.1.7. I’ll try increasing the concurrency to see the effect.