Analyze Table: A Small Table, Not Sure Why It's Running Slowly

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

Original topic: analyze table 一个小表,不明白为什么执行慢。

| username: TiDBer_明明

[TiDB Usage Environment] Testing
[TiDB Version] TiDB-v5.3.1
[Reproduction Path] The table has a total of 338 rows, analyze table sbtest_338;
[Encountered Problem: Phenomenon and Impact]
MySQL [sbtest]> show global variables like “%tidb_enable_fast_analyze%”;
±-------------------------±------+
| Variable_name | Value |
±-------------------------±------+
| tidb_enable_fast_analyze | ON |
±-------------------------±------+
1 row in set (0.00 sec)

MySQL [sbtest]> select count() from sbtest_338;
±---------+
| count(
) |
±---------+
| 338 |
±---------+
1 row in set (0.01 sec)

MySQL [sbtest]> analyze table sbtest_338;
Query OK, 0 rows affected (1.00 sec)
[Resource Configuration] Unrelated to resources
[Attachment: Screenshot/Log/Monitoring]

| username: TiDBer_明明 | Original post link

I have seen the official documentation: 统计信息简介 | PingCAP 归档文档站 but cannot understand where it is slow.

Executing the ANALYZE TABLE statement in TiDB takes longer than in MySQL or InnoDB. InnoDB samples only a small number of pages, but TiDB completely reconstructs a series of statistics. Scripts suitable for MySQL mistakenly assume that executing ANALYZE TABLE takes a shorter time.

It is also known that the basic components of TiDB statistics are:

  1. Table-level statistics
    ① Total number of rows
    ② Table health
    Used to describe whether the current statistics are fresh
  2. Column-level statistics
    ① Histogram
    Distribution of values in the column
    ② Count-Min Sketch
    Frequency of values in the column
    ③ Distribution and number of distinct values
    Can determine the selectivity of the column
    ④ Number of null values

Slow statement:

The problem is not clear at which stage it is slow. Why is it slow?

| username: Billmay表妹 | Original post link

Possible reasons include the following:

  1. TiDB’s analyze table command needs to scan the entire table. If the table contains a large amount of data, the execution time will be relatively long. You can increase the number of TiKV and TiDB instances in the TiDB cluster to improve the analysis speed.
  2. The tidb_enable_fast_analyze parameter in TiDB is enabled by default, which can speed up the analysis. You can confirm whether this parameter is enabled by executing the show global variables like '%tidb_enable_fast_analyze%'; command. If this parameter is not enabled, you can enable it by executing the set global tidb_enable_fast_analyze=ON; command.
  3. If there are a large number of update and delete operations in your table, it may cause the table’s statistics to be inaccurate, thereby affecting the execution speed of the analyze table command. You can skip the sampling process and directly use historical data for analysis by executing the analyze table command with the nosample option, thereby improving the analysis speed. For example: analyze table sbtest_338 nosample;.
| username: Billmay表妹 | Original post link

Please take a screenshot of your TiDB Dashboard → Cluster Info → Hosts page for us to take a look.

| username: xingzhenxiang | Original post link

Is 1 second considered slow? Doesn’t cluster scheduling preparation take time?

| username: TiDBer_明明 | Original post link

This is actually just my test environment. This is just a very simple small table with 338 rows, almost no updates/deletions, and I have already enabled fast analyze:

| username: TiDBer_明明 | Original post link

1 second may not be considered slow, but it’s just a few hundred lines of data.
The time must be spent on some specific step, but I’m not sure where it is.
Cluster coordination preparation? This direction is too broad, and I didn’t see any cluster coordination related to analyze table on PCTP.

| username: 我是咖啡哥 | Original post link

1 second is still considered slow? I thought it was the issue with automatic collection being slow.
No matter how little the data, it still has to go through so many processes. :joy:

| username: TiDBer_明明 | Original post link

I encountered a small table online (also 338 rows), and it takes more than 20 minutes to analyze the table. I want to learn about the processes involved in “going through so many procedures.” Can you provide relevant materials? The PCTP course didn’t cover this.

| username: 我是咖啡哥 | Original post link

This probably requires looking at the source code.
I encountered a situation before where automatically collecting statistics was very slow and often exceeded the GC time, but doing it manually was quite fast.

| username: TiDBer_明明 | Original post link

I don’t understand this issue, are you really going to look at the source code?

| username: h5n1 | Original post link

  1. The automatic analyze task is single-threaded, so it is relatively slow. If it exceeds the GC time, you can only collect manually or increase the GC time.
  2. Manual collection can speed up by adjusting the three parameters tidb_build_stats_concurrency, tidb_distsql_scan_concurrency, and tidb_index_serial_scan_concurrency.
  3. Currently, analyze still reads all data, and fast analyze does not work. In addition to data reading IO, statistics collection also updates tables related to statistical information and involves some calculations, which also take time.