TiDB Analyze Table Took 3 Hours

Translated
1

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

Original topic: tidb analyze table 用了3小时

| username: lxzkenney

tidb 5.0.3
analyze table xxx; 270 million records, took 3 hours, why did it take so long?

image
image1107×291 16.2 KB

2
| username: ddhe9527 | Original post link

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 will mistakenly assume that executing ANALYZE TABLE takes a shorter time.

3
| username: 啦啦啦啦啦 | Original post link

If resources are sufficient, you can adjust the concurrency of analyze.

4
| username: h5n1 | Original post link

Confirm whether this analyze is executed manually or by auto analyze. The concurrency parameters related to statistics collection for auto analyze are set to 1, so it will be slower if the data volume is large. Also, check the disk IO performance.

5
| username: Gin | Original post link

During off-peak hours, use the following system variables to improve analyze speed:

set @@session.tidb_distsql_scan_concurrency=60;
set @@session.tidb_index_serial_scan_concurrency=10;
set @@session.tidb_build_stats_concurrency=60;
6
| username: lxzkenney | Original post link

It’s auto. Can this kind of auto be increased?
Disk IO is not high, usage is 20%.

7
| username: h5n1 | Original post link

Auto cannot be adjusted temporarily.

8
| username: system | Original post link

This topic will be automatically closed 60 days after the last reply. No new replies are allowed.