7.5.5版本加索引很慢（或者说一直卡着一个状态不动了）

xxxxxxxx · 2025 年1 月 9 日 10:14

tidb版本 7.5.5

现象：对一个1000w行的表，表大小不足2GB新建一个二级索引，执行时间超过40min了，还没结束。但是在4.0.13版本的tidb创建索引32min就结束了，ddl相关参数都是保持一致的。

[2025/01/09 17:26:43.024 +08:00] [INFO] [session.go:3902] ["CRUCIAL OPERATION"] [conn=1465909272] [schemaVersion=520] [cur_db=tidb_monitor] [sql="CREATE INDEX k_6 ON sbtest6(k)"] [user=dba@10.%]
[2025/01/09 17:26:43.052 +08:00] [INFO] [ddl.go:1077] ["start DDL job"] [category=ddl] [job="ID:414, Type:add index, State:queueing, SchemaState:none, SchemaID:57, TableID:399, RowCount:0, ArgLen:6, start time: 2025-01-09 17:26:43.017 +0800 CST, Err:<nil>, ErrCount:0, SnapshotVersion:0, UniqueWarnings:0"] [query="CREATE INDEX k_6 ON sbtest6(k)"]
[2025/01/09 17:26:43.833 +08:00] [INFO] [session.go:3902] ["CRUCIAL OPERATION"] [conn=1465909274] [schemaVersion=523] [cur_db=tidb_monitor] [sql="CREATE INDEX k_8 ON sbtest8(k)"] [user=dba@10.%]
[2025/01/09 17:26:43.853 +08:00] [INFO] [ddl.go:1077] ["start DDL job"] [category=ddl] [job="ID:415, Type:add index, State:queueing, SchemaState:none, SchemaID:57, TableID:401, RowCount:0, ArgLen:6, start time: 2025-01-09 17:26:43.816 +0800 CST, Err:<nil>, ErrCount:0, SnapshotVersion:0, UniqueWarnings:0"] [query="CREATE INDEX k_8 ON sbtest8(k)"]
[2025/01/09 17:26:44.536 +08:00] [INFO] [session.go:3902] ["CRUCIAL OPERATION"] [conn=1465909276] [schemaVersion=523] [cur_db=tidb_monitor] [sql="CREATE INDEX k_3 ON sbtest3(k)"] [user=dba@10.%]
[2025/01/09 17:26:44.556 +08:00] [INFO] [ddl.go:1077] ["start DDL job"] [category=ddl] [job="ID:416, Type:add index, State:queueing, SchemaState:none, SchemaID:57, TableID:404, RowCount:0, ArgLen:6, start time: 2025-01-09 17:26:44.517 +0800 CST, Err:<nil>, ErrCount:0, SnapshotVersion:0, UniqueWarnings:0"] [query="CREATE INDEX k_3 ON sbtest3(k)"]
[2025/01/09 17:26:44.994 +08:00] [INFO] [session.go:3902] ["CRUCIAL OPERATION"] [conn=1465909266] [schemaVersion=523] [cur_db=tidb_monitor] [sql="CREATE INDEX k_1 ON sbtest1(k)"] [user=dba@10.%]
[2025/01/09 17:26:45.003 +08:00] [INFO] [ddl.go:1077] ["start DDL job"] [category=ddl] [job="ID:419, Type:add index, State:queueing, SchemaState:none, SchemaID:57, TableID:408, RowCount:0, ArgLen:6, start time: 2025-01-09 17:26:44.967 +0800 CST, Err:<nil>, ErrCount:0, SnapshotVersion:0, UniqueWarnings:0"] [query="CREATE INDEX k_1 ON sbtest1(k)"]
[2025/01/09 17:26:45.999 +08:00] [INFO] [session.go:3902] ["CRUCIAL OPERATION"] [conn=1465909268] [schemaVersion=523] [cur_db=tidb_monitor] [sql="CREATE INDEX k_2 ON sbtest2(k)"] [user=dba@10.%]
[2025/01/09 17:26:46.009 +08:00] [INFO] [ddl.go:1077] ["start DDL job"] [category=ddl] [job="ID:420, Type:add index, State:queueing, SchemaState:none, SchemaID:57, TableID:411, RowCount:0, ArgLen:6, start time: 2025-01-09 17:26:45.966 +0800 CST, Err:<nil>, ErrCount:0, SnapshotVersion:0, UniqueWarnings:0"] [query="CREATE INDEX k_2 ON sbtest2(k)"]
[2025/01/09 17:26:46.230 +08:00] [INFO] [session.go:3902] ["CRUCIAL OPERATION"] [conn=1465909270] [schemaVersion=523] [cur_db=tidb_monitor] [sql="CREATE INDEX k_7 ON sbtest7(k)"] [user=dba@10.%]
[2025/01/09 17:26:46.241 +08:00] [INFO] [ddl.go:1077] ["start DDL job"] [category=ddl] [job="ID:421, Type:add index, State:queueing, SchemaState:none, SchemaID:57, TableID:410, RowCount:0, ArgLen:6, start time: 2025-01-09 17:26:46.217 +0800 CST, Err:<nil>, ErrCount:0, SnapshotVersion:0, UniqueWarnings:0"] [query="CREATE INDEX k_7 ON sbtest7(k)"]

mysql> admin show ddl jobs;
+--------+--------------+------------+------------------------+----------------------+-----------+----------+-----------+---------------------+---------------------+---------------------+----------+
| JOB_ID | DB_NAME      | TABLE_NAME | JOB_TYPE               | SCHEMA_STATE         | SCHEMA_ID | TABLE_ID | ROW_COUNT | CREATE_TIME         | START_TIME          | END_TIME            | STATE    |
+--------+--------------+------------+------------------------+----------------------+-----------+----------+-----------+---------------------+---------------------+---------------------+----------+
|    414 | tidb_monitor | sbtest6    | add index /* ingest */ | write reorganization |        57 |      399 |   9997457 | 2025-01-09 17:26:43 | 2025-01-09 17:26:43 | NULL                | running  |
|    415 | tidb_monitor | sbtest8    | add index              | none                 |        57 |      401 |         0 | 2025-01-09 17:26:43 | NULL                | NULL                | queueing |
|    416 | tidb_monitor | sbtest3    | add index              | none                 |        57 |      404 |         0 | 2025-01-09 17:26:44 | NULL                | NULL                | queueing |
|    417 | tidb_monitor | sbtest5    | add index              | none                 |        57 |      402 |         0 | 2025-01-09 17:26:44 | NULL                | NULL                | queueing |
|    418 | tidb_monitor | sbtest4    | add index              | none                 |        57 |      398 |         0 | 2025-01-09 17:26:44 | NULL                | NULL                | queueing |
|    419 | tidb_monitor | sbtest1    | add index              | none                 |        57 |      408 |         0 | 2025-01-09 17:26:44 | NULL                | NULL                | queueing |
|    420 | tidb_monitor | sbtest2    | add index              | none                 |        57 |      411 |         0 | 2025-01-09 17:26:45 | NULL                | NULL                | queueing |
|    421 | tidb_monitor | sbtest7    | add index              | none                 |        57 |      410 |         0 | 2025-01-09 17:26:46 | NULL                | NULL                | queueing |
|    413 | tidb_monitor | sbtest2    | create table           | public               |        57 |      411 |         0 | 2025-01-09 17:20:49 | 2025-01-09 17:20:49 | 2025-01-09 17:20:49 | synced   |
|    412 | tidb_monitor | sbtest7    | create table           | public               |        57 |      410 |         0 | 2025-01-09 17:20:49 | 2025-01-09 17:20:49 | 2025-01-09 17:20:49 | synced   |
|    409 | tidb_monitor | sbtest1    | create table           | public               |        57 |      408 |         0 | 2025-01-09 17:20:49 | 2025-01-09 17:20:49 | 2025-01-09 17:20:49 | synced   |
|    407 | tidb_monitor | sbtest3    | create table           | public               |        57 |      404 |         0 | 2025-01-09 17:20:49 | 2025-01-09 17:20:49 | 2025-01-09 17:20:49 | synced   |
|    406 | tidb_monitor | sbtest8    | create table           | public               |        57 |      401 |         0 | 2025-01-09 17:20:49 | 2025-01-09 17:20:49 | 2025-01-09 17:20:49 | synced   |
|    405 | tidb_monitor | sbtest6    | create table           | public               |        57 |      399 |         0 | 2025-01-09 17:20:49 | 2025-01-09 17:20:49 | 2025-01-09 17:20:49 | synced   |
|    403 | tidb_monitor | sbtest5    | create table           | public               |        57 |      402 |         0 | 2025-01-09 17:20:49 | 2025-01-09 17:20:49 | 2025-01-09 17:20:49 | synced   |
|    400 | tidb_monitor | sbtest4    | create table           | public               |        57 |      398 |         0 | 2025-01-09 17:20:49 | 2025-01-09 17:20:49 | 2025-01-09 17:20:49 | synced   |
|    397 | tidb_monitor | sbtest8    | drop table             | none                 |        57 |      375 |         0 | 2025-01-09 17:20:09 | 2025-01-09 17:20:09 | 2025-01-09 17:20:10 | synced   |
|    396 | tidb_monitor | sbtest7    | drop table             | none                 |        57 |      379 |         0 | 2025-01-09 17:20:09 | 2025-01-09 17:20:09 | 2025-01-09 17:20:09 | synced   |
+--------+--------------+------------+------------------------+----------------------+-----------+----------+-----------+---------------------+---------------------+---------------------+----------+

18 rows in set (0.03 sec)

mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2025-01-09 18:07:52 |
+---------------------+
1 row in set (0.00 sec)

mysql> select TABLE_ROWS,(DATA_LENGTH+INDEX_LENGTH)/1024/1024/1024 size from information_schema.tables where table_name = 'sbtest6';
+------------+----------------+
| TABLE_ROWS | size           |
+------------+----------------+
|   10000000 | 1.834705471992 |
+------------+----------------+
1 row in set (0.01 sec)

mysql> select version();
+--------------------+
| version()          |
+--------------------+
| 8.0.11-TiDB-v7.5.5 |
+--------------------+
1 row in set (0.00 sec)

mysql> show variables like '%ddl%';
+--------------------------------+--------------------------+
| Variable_name                  | Value                    |
+--------------------------------+--------------------------+
| ddl_slow_threshold             | 300                      |
| tidb_ddl_disk_quota            | 107374182400             |
| tidb_ddl_enable_fast_reorg     | ON                       |
| tidb_ddl_error_count_limit     | 512                      |
| tidb_ddl_flashback_concurrency | 64                       |
| tidb_ddl_reorg_batch_size      | 256                      |
| tidb_ddl_reorg_max_write_speed | 0                        |
| tidb_ddl_reorg_priority        | PRIORITY_LOW             |
| tidb_ddl_reorg_worker_cnt      | 2                        |
| tidb_enable_ddl                | ON                       |
| tidb_last_ddl_info             | {"query":"","seq_num":0} |
+--------------------------------+--------------------------+
11 rows in set (0.00 sec)

mysql> select * from mysql.tidb_mdl_view;
Empty set (0.06 sec)

mysql> show variables like '%tidb_service_scope%';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| tidb_service_scope |       |
+--------------------+-------+
1 row in set (0.00 sec)

mysql>

以下是4.0.13版本的情况

mysql> admin show ddl jobs;
+--------+--------------+------------+--------------+--------------+-----------+----------+-----------+---------------------+---------------------+--------+
| JOB_ID | DB_NAME      | TABLE_NAME | JOB_TYPE     | SCHEMA_STATE | SCHEMA_ID | TABLE_ID | ROW_COUNT | START_TIME          | END_TIME            | STATE  |
+--------+--------------+------------+--------------+--------------+-----------+----------+-----------+---------------------+---------------------+--------+
|   1068 | tidb_monitor | sbtest27   | add index    | public       |       769 |     1065 |  10000000 | 2025-01-09 13:48:51 | 2025-01-09 14:19:24 | synced |
|   1067 | tidb_monitor | sbtest29   | add index    | public       |       769 |     1062 |  10000000 | 2025-01-09 13:44:06 | 2025-01-09 14:15:05 | synced |
|   1066 | tidb_monitor | sbtest27   | create table | public       |       769 |     1065 |         0 | 2025-01-09 13:43:22 | 2025-01-09 13:43:22 | synced |
|   1064 | tidb_monitor | sbtest26   | add index    | public       |       769 |     1059 |  10000000 | 2025-01-09 13:39:18 | 2025-01-09 14:10:34 | synced |
|   1063 | tidb_monitor | sbtest29   | create table | public       |       769 |     1062 |         0 | 2025-01-09 13:38:34 | 2025-01-09 13:38:35 | synced |
|   1061 | tidb_monitor | sbtest31   | add index    | public       |       769 |     1056 |  10000000 | 2025-01-09 13:34:26 | 2025-01-09 14:06:06 | synced |
|   1060 | tidb_monitor | sbtest26   | create table | public       |       769 |     1059 |         0 | 2025-01-09 13:33:49 | 2025-01-09 13:33:50 | synced |
|   1058 | tidb_monitor | sbtest30   | add index    | public       |       769 |     1053 |  10000000 | 2025-01-09 13:29:37 | 2025-01-09 14:01:28 | synced |
|   1057 | tidb_monitor | sbtest31   | create table | public       |       769 |     1056 |         0 | 2025-01-09 13:28:59 | 2025-01-09 13:29:00 | synced |
|   1055 | tidb_monitor | sbtest25   | add index    | public       |       769 |     1050 |  10000000 | 2025-01-09 13:24:49 | 2025-01-09 13:57:07 | synced |
+--------+--------------+------------+--------------+--------------+-----------+----------+-----------+---------------------+---------------------+--------+
10 rows in set (0.03 sec)

mysql> show variables like '%ddl%';
+----------------------------+--------------+
| Variable_name              | Value        |
+----------------------------+--------------+
| ddl_slow_threshold         | 300          |
| tidb_ddl_error_count_limit | 512          |
| tidb_ddl_reorg_batch_size  | 256          |
| tidb_ddl_reorg_priority    | PRIORITY_LOW |
| tidb_ddl_reorg_worker_cnt  | 2            |
+----------------------------+--------------+
5 rows in set (0.27 sec)

mysql> select version();
+---------------------+
| version()           |
+---------------------+
| 5.7.25-TiDB-v4.0.13 |
+---------------------+
1 row in set (0.00 sec)

mysql> select TABLE_ROWS,(DATA_LENGTH+INDEX_LENGTH)/1024/1024/1024 size from information_schema.tables where table_name = 'sbtest27';
+------------+----------------+
| TABLE_ROWS | size           |
+------------+----------------+
|   10282876 | 2.085646055639 |
+------------+----------------+
1 row in set (0.03 sec)

mysql>

看tikv的iops的话，隔一会又有3k左右的iops，tikv所在的机器没有业务在用，只有这一套集群，且这套集群也是空集群，供测试7.5.5版本使用的。

Jasper · 2025 年1 月 9 日 10:20

可能是mdl 锁卡住了， select * from mysql.tidb_mdl_view 看下

xxxxxxxx · 2025 年1 月 9 日 10:26

这个查询是空的

TiDBer_小杰 · 2025 年1 月 9 日 11:17

TiDB 配置： tidb_ddl_reorg_worker_cnt、tidb_ddl_reorg_batch_size
TiKV 配置：raftstore.raft-entry-max-size、raftstore.apply-pool-size
这些参数也一致吗

WalterWj · 2025 年1 月 9 日 11:25

新版本是 fast ddl，他需要 tidb-server 有一个好盘落文件。配置下 tmp-dir:https://docs.pingcap.com/zh/tidb/v7.5/tidb-distributed-execution-framework

没有好盘试试降级，把 fast ddl 直接关了吧。

xxxxxxxx · 2025 年1 月 9 日 11:30

所以说，采用默认参数装好的集群都没法对大表加索引呗？

另外没看到temp-dir的配置项，只有tmpdir

| tmpdir                           | /var/tmp/ |

residentevil · 2025 年1 月 9 日 11:42

学习了

WalterWj · 2025 年1 月 9 日 11:52

https://docs.pingcap.com/zh/tidb/v7.5/tidb-configuration-file#temp-dir-从-v630-版本开始引入

这个。

xxxxxxxx · 2025 年1 月 9 日 11:56

没找到这个参数呢

mysql> select version();
+--------------------+
| version()          |
+--------------------+
| 8.0.11-TiDB-v7.5.5 |
+--------------------+
1 row in set (0.00 sec)

mysql> show global variables like '%temp%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| avoid_temporal_upgrade | OFF   |
| show_old_temporals     | OFF   |
+------------------------+-------+
2 rows in set (0.01 sec)

mysql>

WalterWj · 2025 年1 月 9 日 11:58

这是 config，不是 variable。

xxxxxxxx · 2025 年1 月 9 日 12:00

这样去配置？

server_configs:
  tidb:
    temp-dir: /path/xxx

xxxxxxxx · 2025 年1 月 9 日 12:04

这段话有点疑惑。

第一段说如果temp-dir不可用就会回退到非加速方式。

第二段说大于6.5版本的话会出现不可预知的问题。

所以哪个优先级更高，到底是会回退呢还是会出现异常？

terry0219 · 2025 年1 月 10 日 02:13

磁盘性能不够吗？

tidb菜鸟一只 · 2025 年1 月 10 日 02:13

SET GLOBAL tidb_ddl_enable_fast_reorg = OFF;
查看用
SHOW GLOBAL VARIABLES LIKE ‘%tidb_ddl_enable_fast_reorg%’
不过该参数是 TiDB 的配置参数，设置后需要重启 TiDB 才能生效。

TiDBer_xTvoCh2f · 2025 年1 月 10 日 03:21

tiup cluster edit-config 修改吧
把索引加速也关掉

WalterWj · 2025 年1 月 10 日 03:34

第一个意思应该是有读写权限，但是空间很小就会回退吧
就是如果没有权限就会有问题。