TIDB 8.1.0添加索引一直runnning问题咨询

老鹰506 · 2025 年2 月 27 日 08:52

【 TiDB 使用环境】测试
【 TiDB 版本】8.1.0
【复现路径】
【遇到的问题：问题现象及影响】

昨天下午在测试环境执行的添加索引操作，一直没有执行完毕。通过TIDB server的tidb.log日志发现，目前一直在刷日志

[2025/02/27 16:41:37.571 +08:00] [INFO] [ddl_worker.go:1417] ["schema version doesn't change"] [category=ddl] [jobID=3443]
[2025/02/27 16:41:37.582 +08:00] [INFO] [ddl_worker.go:1206] ["run DDL job"] [worker="worker 2, tp add index"] [category=ddl] [jobID=3443] [conn=1746077462] [category=ddl] [job="ID:3443, Type:add index, State:running, SchemaState:write reorganization, SchemaID:606, TableID:3434, RowCount:0, ArgLen:0, start time: 2025-02-24 17:25:16.696 +0800 CST, Err:<nil>, ErrCount:0, SnapshotVersion:456232564710703119, LocalMode: false, UniqueWarnings:0"]
[2025/02/27 16:41:37.586 +08:00] [INFO] [index.go:857] ["index backfill state running"] [category=ddl] ["job ID"=3443] [table=orderInfo] ["ingest mode"=true] [index=seller_updateTime_orderType_isDelete]

如下是昨天下午开始新增索引时的日志

[2025/02/24 17:25:16.713 +08:00] [INFO] [session.go:3894] ["CRUCIAL OPERATION"] [conn=1746077462] [schemaVersion=5658] [cur_db=orders] [sql="ALTER TABLE
orderInfo ADD INDEX `seller_updateTime_orderType_isDelete` (`shopkeeperId`,`updateTime`,`orderType`,`isSellerDelete`)"] [user=dongnan@192.168.%]
[2025/02/24 17:25:16.737 +08:00] [INFO] [ddl_worker.go:260] ["add DDL jobs"] [category=ddl] ["batch count"=1] [jobs="ID:3443, Type:add index, State:queue
ing, SchemaState:none, SchemaID:606, TableID:3434, RowCount:0, ArgLen:6, start time: 2025-02-24 17:25:16.696 +0800 CST, Err:<nil>, ErrCount:0, SnapshotVersion:0, LocalMode: false, UniqueWarnings:0; "] [table=true]
[2025/02/24 17:25:16.737 +08:00] [INFO] [ddl.go:1181] ["start DDL job"] [category=ddl] [job="ID:3443, Type:add index, State:queueing, SchemaState:none, SchemaID:606, TableID:3434, RowCount:0, ArgLen:6, start time: 2025-02-24 17:25:16.696 +0800 CST, Err:<nil>, ErrCount:0, SnapshotVersion:0, LocalMode: fals
e, UniqueWarnings:0"] [query="ALTER TABLE orderInfo ADD INDEX `seller_updateTime_orderType_isDelete` (`shopkeeperId`,`updateTime`,`orderType`,`isSellerDelete`)"]
[2025/02/24 17:25:16.821 +08:00] [INFO] [ddl_worker.go:1206] ["run DDL job"] [worker="worker 2, tp add index"] [category=ddl] [jobID=3443] [conn=1746077462] [category=ddl] [job="ID:3443, Type:add index, State:queueing, SchemaState:none, SchemaID:606, TableID:3434, RowCount:0, ArgLen:0, start time: 2025-02-24 17:25:16.696 +0800 CST, Err:<nil>, ErrCount:0, SnapshotVersion:0, LocalMode: false, UniqueWarnings:0"]
[2025/02/24 17:25:16.827 +08:00] [INFO] [index.go:654] ["run add index job"] [category=ddl] [job="ID:3443, Type:add index, State:running, SchemaState:none, SchemaID:606, TableID:3434, RowCount:0, ArgLen:6, start time: 2025-02-24 17:25:16.696 +0800 CST, Err:<nil>, ErrCount:0, SnapshotVersion:0, LocalMode:false, UniqueWarnings:0"] [indexInfo="{\"id\":27,\"idx_name\":{\"O\":\"seller_updateTime_orderType_isDelete\",\"L\":\"seller_updatetime_ordertype_isdelete\"},\"tbl_name\":{\"O\":\"\",\"L\":\"\"},\"idx_cols\":[{\"name\":{\"O\":\"shopkeeperId\",\"L\":\"shopkeeperid\"},\"offset\":3,\"length\":-1},{\"name\":{\"O\":\"updateTime\",\"L\":\"updatetime\"},\"offset\":60,\"length\":-1},{\"name\":{\"O\":\"orderType\",\"L\":\"ordertype\"},\"offset\":34,\"length\":-1},{\"name\":{\"O\":\"isSellerDelete\",\"L\":\"issellerdelete\"},\"offset\":41,\"length\":-1}],\"state\":0,\"backfill_state\":0,\"comment\":\"\",\"index_type\":1,\"is_unique\":false,\"is_primary\":false,\"is_invisible\":false,\"is_global\":false,\"mv_index\":false}"]
[2025/02/24 17:25:16.860 +08:00] [INFO] [domain.go:280] ["diff load InfoSchema success"] [currentSchemaVersion=5658] [neededSchemaVersion=5659] ["start time"=5.400802ms] [gotSchemaVersion=5659] [phyTblIDs="[3434]"] [actionTypes="[7]"] [diffTypes="[\"add index\"]"]
[2025/02/24 17:25:16.867 +08:00] [INFO] [domain.go:886] ["mdl gets lock, update self version to owner"] [jobID=3443] [version=5659]
[2025/02/24 17:25:16.903 +08:00] [INFO] [ddl_worker.go:1454] ["wait latest schema version changed(get the metadata lock if tidb_enable_metadata_lock is true)"] [category=ddl] [ver=5659] ["take time"=54.021841ms] [job="ID:3443, Type:add index, State:running, SchemaState:delete only, SchemaID:606, TableID:3434, RowCount:0, ArgLen:6, start time: 2025-02-24 17:25:16.696 +0800 CST, Err:<nil>, ErrCount:0, SnapshotVersion:0, LocalMode: false, UniqueWarnings:0"]

执行 admin show ddl jobs 如下

root@192.168.3.114:4000 16:40:  [(none)] >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     |
+--------+---------+--------------------+------------------------+----------------------+-----------+----------+-----------+---------------------+---------------------+---------------------+-----------+
|   3443 | orders  | orderinfo          | add index /* ingest */ | write reorganization |       606 |     3434 |         0 | 2025-02-24 17:25:16 | 2025-02-24 17:25:16 | NULL                | running   |

也没有报错，就一直卡顿，请问是哪里的问题呢？

【资源配置】

【复制黏贴 ERROR 报错的日志】
【其他附件：截图/日志/监控】

补充信息

1、表的数据量不大

root@192.168.3.114:4000 16:52:  [orders] >select count(1) from orderInfo ;
+----------+
| count(1) |
+----------+
|    31083 |
+----------+
1 row in set (0.03 sec)

2、表上的索引目前有点多

PRIMARY KEY (`orderId`) /*T![clustered_index] CLUSTERED */,
  KEY `shopId` (`shopId`),
  KEY `shopName` (`shopName`),
  KEY `userName` (`nickname`),
  KEY `createdTime` (`createdTime`),
  KEY `sellerConfirmedTime` (`sellerConfirmedTime`),
  KEY `finishTime` (`finishTime`),
  KEY `lockTime` (`lockTime`),
  KEY `receiverName` (`receiverName`),
  KEY `mobile` (`mobile`),
  KEY `orderStatus_timeToReceive` (`orderStatus`,`timeToReceive`),
  KEY `shopkeeperId_orderType_isSellerDelete_createdTime` (`shopkeeperId`,`orderType`,`isSellerDelete`,`createdTime`),
  KEY `shopkeeperId_orderStatus_orderType_isSellerDelete_createdTime` (`shopkeeperId`,`orderStatus`,`orderType`,`isSellerDelete`,`createdTime`),
  KEY `seller_orderStatus_orderType_isDelete_Reviewed_finishTime` (`shopkeeperId`,`orderStatus`,`orderType`,`isSellerDelete`,`sellerReviewed`,`finishTime`),
  KEY `userId_orderType_isBuyerDelete_createdTime` (`userId`,`orderType`,`isBuyerDelete`,`createdTime`),
  KEY `userId_orderStatus_orderType_isBuyerDelete_createdTime` (`userId`,`orderStatus`,`orderType`,`isBuyerDelete`,`createdTime`),
  KEY `userId_orderStatus_orderType_isDelete_Reviewed_finishTime` (`userId`,`orderStatus`,`orderType`,`isBuyerDelete`,`buyerReviewed`,`finishTime`),
  KEY `orderStatus_sellerReviewed_buyerReviewed_finishTime` (`orderStatus`,`sellerReviewed`,`buyerReviewed`,`finishTime`),
  KEY `userId_createdTime_orderType_isBuyerDelete` (`userId`,`createdTime`,`orderType`,`isBuyerDelete`),
  KEY `orderStatus_lockTime` (`orderStatus`,`lockTime`),
  KEY `shopkeeperId_createdTime_orderType_isSellerDelete` (`shopkeeperId`,`createdTime`,`orderType`,`isSellerDelete`),
  KEY `shopkeeperId_orderStatus_createdTime_orderType_isSellerDelete` (`shopkeeperId`,`orderStatus`,`createdTime`,`orderType`,`isSellerDelete`),
  KEY `seller_buyer` (`shopkeeperId`,`userId`),
  KEY `orderStatus_orderType_payTime` (`orderStatus`,`orderType`,`payTime`),
  KEY `orderStatus_orderType_shippingTime_createdTime_orderId` (`orderStatus`,`orderType`,`shippingTime`,`createdTime`,`orderId`),
  KEY `orderStatus_userId_mobile` (`orderStatus`,`userId`,`mobile`),
  KEY `orderStatus_shippingTime_orderId` (`orderStatus`,`shippingTime`,`orderId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci |

田帅萌7 · 2025 年2 月 27 日 08:53

tidb_enable_metadata_lock=off
tidb_ddl_enable_fast_reorg=off
然后在开个链接在试

老鹰506 · 2025 年2 月 27 日 09:27

除了上面两个参数之后，还需要把tidb_enable_dist_task 修改成OFF，然后在执行添加索引就很快了。

不然会报错如下

ERROR 8200 (HY000): Unsupported tidb_enable_dist_task setting. To utilize distributed task execution, please enable tidb_ddl_enable_fast_reorg first.

BrianLiu · 2025 年2 月 27 日 09:33

这种情况下第一时间可以看下对应 mdl 视图，看看是不是有 session 卡住了这个add index。

老鹰506 · 2025 年2 月 27 日 09:40

嗯嗯，也去看这个视图了，但是我目前的 8.1.0版本这个视图查询报错呢，

The-Fallen-Angel · 2025 年2 月 27 日 10:17

截图中pd内存不到1G/

老鹰506 · 2025 年2 月 28 日 03:12

对，因为是测试环境，pd给的是1C1G的机器，添加索引期间这个主机的CPU和内存也没有变化，应该不是它的问题吧

有猫万事足 · 2025 年2 月 28 日 07:36

感觉还是tidb_mdl_view查询不了的带来的衍生问题。

tidb_enable_metadata_lock=off

可能单独关掉元数据锁可能也能好。

老鹰506 · 2025 年3 月 3 日 02:41

单独只关闭这个，实际验证不行的。
后来是三个参数都关闭才执行成功的

有猫万事足 · 2025 年3 月 3 日 11:15

这个坑还挺深的

乡在人间 · 2025 年3 月 4 日 06:42

tidb_enable_metadata_lock=off
tidb_ddl_enable_fast_reorg=off
tidb_enable_dist_task =off
这三个参数调整为上面的，再测试一下行不行

老鹰506 · 2025 年3 月 5 日 01:22

嗯嗯，就是最终同时调整了这三个参数生效的哈

system · 2025 年3 月 12 日 01:23

此话题已在最后回复的 7 天后被自动关闭。不再允许新回复。