analyze becomes slower after br restoring cluster data

[jebter]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

The analyze of the source cluster takes 67 min, and after br backup and restoration, the analyze takes nearly 4 hours.（320T data cluster）

mysql> select @@tidb_analyze_distsql_scan_concurrency,@@tidb_build_stats_concurrency,@@tidb_build_sampling_stats_concurrency,@@tidb_analyze_partition_concurrency;
+-----------------------------------------+--------------------------------+-----------------------------------------+--------------------------------------+
| @@tidb_analyze_distsql_scan_concurrency | @@tidb_build_stats_concurrency | @@tidb_build_sampling_stats_concurrency | @@tidb_analyze_partition_concurrency |
+-----------------------------------------+--------------------------------+-----------------------------------------+--------------------------------------+
|                                     400 | 16                             | 16                                      | 16                                   |
+-----------------------------------------+--------------------------------+-----------------------------------------+--------------------------------------+
1 row in set (0.00 sec)

mysql> analyze table test.item;
Query OK, 0 rows affected, 1 warning (1 hour 7 min 55.31 sec)

mysql> 
mysql> show analyze status;
+--------------+------------+----------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------+---------------------+---------------------+----------+--------------------------------------------+----------------------------------------------------------------------------+------------+-------------------+----------+----------------------+
| Table_schema | Table_name | Partition_name | Job_info                                                                                                                                                                                                                                                                     | Processed_rows | Start_time          | End_time            | State    | Fail_reason                                | Instance                                                                   | Process_ID | Remaining_seconds | Progress | Estimated_total_rows |
+--------------+------------+----------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------+---------------------+---------------------+----------+--------------------------------------------+----------------------------------------------------------------------------+------------+-------------------+----------+----------------------+
| test         | item       |                | analyze index                                                                                                                                                                                                                                                |    33900000000 | 2024-01-19 01:52:37 | 2024-01-19 03:00:31 | finished | NULL                                       | tc-tidb-3 |       NULL | NULL              |     NULL |                 NULL |
| test         | item       |                | analyze index                                                                                                                                                                                                                                                       |    33899999996 | 2024-01-19 01:52:37 | 2024-01-19 02:46:40 | finished | NULL                                       | tc-tidb-3 |       NULL | NULL              |     NULL |                 NULL |
| test         | item       |                | analyze index                                                                                                                                                                                                                                 |    33888704472 | 2024-01-19 01:52:37 | 2024-01-19 02:39:34 | finished | NULL                                       | tc-tidb-3 |       NULL | NULL              |     NULL |                 NULL |
| test         | item       |                | analyze index                                                                                                                                                                                                                                              |    33888703596 | 2024-01-19 01:52:37 | 2024-01-19 02:38:12 | finished | NULL                                       | tc-tidb-3|       NULL | NULL              |     NULL |                 NULL |
| test         | item       |                | analyze index                                                                                                                                                                                                                             |    33888704472 | 2024-01-19 01:52:37 | 2024-01-19 02:35:04 | finished | NULL                                       | tc-tidb-3.tc-tidb-pee |       NULL | NULL              |     NULL |                 NULL |
| test         | item       |                | analyze table    |     6780000000 | 2024-01-19 01:52:37 | 2024-01-19 02:34:52 | finished | NULL                                       | tc-tidb-3 |       NULL | NULL              |     NULL |                 NULL |
| test         | item       |                | analyze index                                                                                                                                                                                                                                                |    12881891376 | 2024-01-19 01:52:37 | 2024-01-19 02:08:23 | finished | NULL                                       | tc-tidb-3 |       NULL | NULL              |     NULL |                 NULL |
| test         | item       |                | analyze index                                                                                                                                                                                                                                                  |    12882003737 | 2024-01-19 01:52:37 | 2024-01-19 02:08:04 | finished | NULL                                       | tc-tidb-3 |       NULL | NULL              |     NULL |                 NULL |

after br restoring cluster data,analyze from 18:34 to 22:12

select @@tidb_analyze_distsql_scan_concurrency,@@tidb_build_stats_concurrency,@@tidb_build_sampling_stats_concurrency,@@tidb_analyze_partition_concurrency;
+-----------------------------------------+--------------------------------+-----------------------------------------+--------------------------------------+
| @@tidb_analyze_distsql_scan_concurrency | @@tidb_build_stats_concurrency | @@tidb_build_sampling_stats_concurrency | @@tidb_analyze_partition_concurrency |
+-----------------------------------------+--------------------------------+-----------------------------------------+--------------------------------------+
| 400                                     | 16                             | 16                                      | 16                                   |
+-----------------------------------------+--------------------------------+-----------------------------------------+--------------------------------------+

 show analyze status where Process_ID=2915041356
 +--------------+------------+----------------+--------------------------------------------------+----------------+---------------------+----------+---------+-------------+----------------------------------------------------------------------------+------------+-------------------+--------------------+----------------------+
| Table_schema | Table_name | Partition_name | Job_info                                         | Processed_rows | Start_time          | End_time | State   | Fail_reason | Instance                                                                   | Process_ID | Remaining_seconds | Progress           | Estimated_total_rows |
+--------------+------------+----------------+--------------------------------------------------+----------------+---------------------+----------+---------+-------------+----------------------------------------------------------------------------+------------+-------------------+--------------------+----------------------+
| test         | items      |                | analyze index     | 13062532079    | 2024-01-21 10:34:05 | <null>   | running | <null>      | tc-tidb-0 | 2915041356 | -1h44m40s         | 1.9266271502949852 | 6780000000           |
| test         | items      |                | analyze index  | 14587287258    | 2024-01-21 10:34:05 | <null>   | running | <null>      | tc-tidb-0 | 2915041356 | -1h56m29s         | 2.1515172946902656 | 6780000000           |
| test         | items      |                | analyze index                     | 13376224728    | 2024-01-21 10:34:05 | <null>   | running | <null>      | tc-tidb-0 | 2915041356 | -1h47m19s         | 1.9728945026548672 | 6780000000           |
| test         | items      |                | analyze index                            | 11912954649    | 2024-01-21 10:34:05 | <null>   | running | <null>      | tc-tidb-0 | 2915041356 | -1h33m46s         | 1.757072957079646  | 6780000000           |
| test         | items      |                | analyze index                   | 8630563726     | 2024-01-21 10:34:05 | <null>   | running | <null>      | tc-tidb-0 | 2915041356 | -46m40s           | 1.272944502359882  | 6780000000           |
+--------------+------------+----------------+--------------------------------------------------+----------------+---------------------+----------+---------+-------------+----------------------------------------------------------------------------+------------+-------------------+--------------------+----------------------+

SHOW STATS_HEALTHY;
+--------------------+--------------+----------------+---------+
| Db_name            | Table_name   | Partition_name | Healthy |
+--------------------+--------------+----------------+---------+
| test               | items        |                | 99      |
| lightning_metadata | table_meta   |                | 100     |
| lightning_metadata | task_meta_v2 |                | 100     |
+--------------------+--------------+----------------+---------+

 select count(*) from test.items;
+------------+
| count(*)   |
+------------+
| 6780069630 |
+------------+

2. What did you expect to see? (Required)

3. What did you see instead (Required)

4. What is your TiDB version? (Required)

v7.6.0-pre

[3pointer]

If increase the concurrency of analyze in tidb side helps. then I think maybe it's a known issue of restore data distribution changed.

[hawkingrei]

It has been solved by adjusting the parameters. #50641