TiDB 增加索引卡住

【 TiDB 使用环境】生产环境
【 TiDB 版本】5.4.0
【复现路径】8亿的表,增加索引,卡住不动
【遇到的问题:问题现象及影响】
admin show ddl jobs row count 始终是 102

看 owner 节点,关于 ddl 的日志不断刷:

看 owner 节点关于 ddl 的调用栈如下:

1 @ 0x1329705 0x133a977 0x28142f3 0x284d05c 0x282cdcb 0x341458d 0x340fe85 0x34240be 0x338625c 0x33850e5 0x338454e 0x35aa69f 0x35a92cb 0x363eb0b 0x3633271 0x36315d8 0x362c2af 0x3628d7c 0x3661ab3 0x1361b01
#	0x28142f2	github.com/pingcap/tidb/ddl.(*ddl).doDDLJob+0x732				/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/ddl/ddl.go:593
#	0x284d05b	github.com/pingcap/tidb/ddl.(*ddl).CreateIndex+0xd5b				/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/ddl/ddl_api.go:5507
#	0x282cdca	github.com/pingcap/tidb/ddl.(*ddl).AlterTable+0x120a				/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/ddl/ddl_api.go:2739
#	0x341458c	github.com/pingcap/tidb/executor.(*DDLExec).executeAlterTable+0x1ec		/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/executor/ddl.go:585
#	0x340fe84	github.com/pingcap/tidb/executor.(*DDLExec).Next+0x1604				/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/executor/ddl.go:143
#	0x34240bd	github.com/pingcap/tidb/executor.Next+0x2dd					/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/executor/executor.go:286
#	0x338625b	github.com/pingcap/tidb/executor.(*ExecStmt).handleNoDelayExecutor+0x33b	/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/executor/adapter.go:635
#	0x33850e4	github.com/pingcap/tidb/executor.(*ExecStmt).handleNoDelay+0x1e4		/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/executor/adapter.go:490
#	0x338454d	github.com/pingcap/tidb/executor.(*ExecStmt).Exec+0x72d				/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/executor/adapter.go:439
#	0x35aa69e	github.com/pingcap/tidb/session.runStmt+0x37e					/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/session/session.go:1841
#	0x35a92ca	github.com/pingcap/tidb/session.(*session).ExecuteStmt+0xb0a			/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/session/session.go:1718
#	0x363eb0a	github.com/pingcap/tidb/server.(*TiDBContext).ExecuteStmt+0x6a			/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/server/driver_tidb.go:220
#	0x3633270	github.com/pingcap/tidb/server.(*clientConn).handleStmt+0x1d0			/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/server/conn.go:1972
#	0x36315d7	github.com/pingcap/tidb/server.(*clientConn).handleQuery+0x497			/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/server/conn.go:1841
#	0x362c2ae	github.com/pingcap/tidb/server.(*clientConn).dispatch+0xaee			/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/server/conn.go:1336
#	0x3628d7b	github.com/pingcap/tidb/server.(*clientConn).Run+0x2bb				/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/server/conn.go:1091
#	0x3661ab2	github.com/pingcap/tidb/server.(*Server).onConn+0xa92				/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/server/server.go:548

1 @ 0x1329705 0x133a977 0x2859a55 0x1361b01
#	0x2859a54	github.com/pingcap/tidb/ddl.(*worker).start+0x314	/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/ddl/ddl_worker.go:179

1 @ 0x1329705 0x133a977 0x285b1b9 0x1361b01
#	0x285b1b8	github.com/pingcap/tidb/ddl.(*ddl).limitDDLJobs+0x2d8	/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/ddl/ddl_worker.go:264

1 @ 0x1329705 0x133a977 0x28986b6 0x28715dc 0x285fe88 0x28b9915 0x21208e3 0x285db4a 0x2859a99 0x1361b01
#	0x28986b5	github.com/pingcap/tidb/ddl.(*worker).runReorgJob+0x155			/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/ddl/reorg.go:237
#	0x28715db	github.com/pingcap/tidb/ddl.(*worker).onCreateIndex+0xb5b		/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/ddl/index.go:568
#	0x285fe87	github.com/pingcap/tidb/ddl.(*worker).runDDLJob+0x8a7			/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/ddl/ddl_worker.go:787
#	0x28b9914	github.com/pingcap/tidb/ddl.(*worker).handleDDLJobQueue.func1+0x2f4	/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/ddl/ddl_worker.go:552
#	0x21208e2	github.com/pingcap/tidb/kv.RunInNewTxn+0x122				/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/kv/txn.go:49
#	0x285db49	github.com/pingcap/tidb/ddl.(*worker).handleDDLJobQueue+0x169		/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/ddl/ddl_worker.go:508
#	0x2859a98	github.com/pingcap/tidb/ddl.(*worker).start+0x358			/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/ddl/ddl_worker.go:199

gc 看起来也正常

image

owner 节点也切换过

1 个赞

数据量大时创建索引是耗时较长,多等会观察一下

不是时间的问题,是rowcount始终不增加,始终卡在102.

诚恳的建议升级一下版本,在高版本中做了很大的优化,

以我们实际项目为例:
升级前版本是v5.1.0,数据库表现:添加索引特别慢,稍微大点的表就要几十分钟甚至超过1个小时。
升级后版本是v6.5.4,数据库表现:1.6亿数据表加索引,用时192秒,3分钟左右。

1 个赞

升级这个事儿只能慢慢规划,就当下这个问题确实是没解决。
最终选择了新建一个带索引的表,然后数据倒腾一遍。但是这个问题还在这里,没解决。

2 个赞

可以开 debug 日志看看,当前确实没啥信息

1 个赞

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