大数据量的表如何快速增加索引

[tzh (笃志弘毅)]

请问下 加索引的DLL频繁出现 写冲突，这种情况是什么问题？好像我们其他表在insert数据时也是会出现 写冲突，重试很多次都是如此。

[zhenjiaogao]

1、请看下 tidb.log 和 tikv.log 是否有冲突

2、admin show ddl jobs 看下相关信息

[mount]

TiDB v3.0.2
我添加索引显示的信息是这样：

MySQL [(none)]> admin show ddl ;
+------------+--------------------------------------+-----------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------------------------+--------------------------------------------------------------+
| SCHEMA_VER | OWNER_ID                             | OWNER_ADDRESS   | RUNNING_JOBS                                                                                                                                                                                                                                                                                                                                                                                                      | SELF_ID                              | QUERY                                                        |
+------------+--------------------------------------+-----------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------------------------+--------------------------------------------------------------+
|       1139 | 6ce49aa0-0318-4934-bd84-e46c175ebd9b | 11.0.66.31:4000 | ID:1641, Type:add index, State:running, SchemaState:write reorganization, SchemaID:1603, TableID:1607, RowCount:12671226, ArgLen:0, start time: 2019-10-28 10:20:07.057 +0800 CST, Err:[tikv:9006]GC life time is shorter than transaction duration, transaction starts at 2019-10-28 10:20:07.457 +0800 CST, GC safe point is 2019-10-28 10:53:02.557 +0800 CST, ErrCount:26, SnapshotVersion:412150453359607810 | 6ce49aa0-0318-4934-bd84-e46c175ebd9b | alter table AccountFlow add index idx_ext1(ext1,CreateTime) |
+------------+--------------------------------------+-----------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------------------------+--------------------------------------------------------------+

DDL也要调gc life time参数吗？

[Gin]

运行 ddl 不需要调整 gc 配置

[mount]

@mount

Err:[tikv:9006]GC life time is shorter than transaction duration,

那报这个信息正常吗？

[Gin]

这个报错是 ddl 返回的吗？

[mount]

@mount

admin show ddl ;

这个命令发现。tidb日志里面也报这个：

[2019/10/28 11:01:06.838 +08:00] [INFO] [index.go:1072] ["[ddl] add index worker handle batch tasks successful"] [totalAddedCount=11309854] [startHandle=188155882] [nextHandle=188997271] [batchAddedCount=78364] [takeTime=44.818765448s]
[2019/10/28 11:01:06.838 +08:00] [INFO] [index.go:941] ["[ddl] add index worker exit"] [workerID=0]
[2019/10/28 11:01:06.884 +08:00] [INFO] [reorg.go:140] ["[ddl] run reorg job done"] ["handled rows"=11309854]
[2019/10/28 11:01:06.884 +08:00] [ERROR] [ddl_worker.go:570] ["[ddl] run DDL job error"] [worker="worker 2, tp add index"] [error="[tikv:9006]GC life time is shorter than transaction duration, transaction starts at 2019-10-28 10:20:07.457 +0800 CST, GC safe point is 2019-10-28 10:43:02.657 +0800 CST"] [errorVerbose="[tikv:9006]GC life time is shorter than transaction duration, transaction starts at 2019-10-28 10:20:07.457 +0800 CST, GC safe point is 2019-10-28 10:43:02.657 +0800 CST
github.com/pingcap/errors.AddStack
	/home/jenkins/workspace/release_tidb_3.0/go/pkg/mod/github.com/pingcap/errors@v0.11.4/errors.go:174
github.com/pingcap/parser/terror.(*Error).GenWithStackByArgs
	/home/jenkins/workspace/release_tidb_3.0/go/pkg/mod/github.com/pingcap/parser@v0.0.0-20190806084718-1a31cabbaef2/terror/terror.go:238
github.com/pingcap/tidb/store/tikv.(*tikvStore).CheckVisibility
	/home/jenkins/workspace/release_tidb_3.0/go/src/github.com/pingcap/tidb/store/tikv/kv.go:171
github.com/pingcap/tidb/store/tikv.(*Scanner).getData
	/home/jenki。。。

DDL命令一直处于执行状态

[Gin]

麻烦提供下该命令的返回： admin show ddl jobs;

[AilinKid-PingCAP (Arenatlx)]

可能是建索引的时间过长（startTs 很早），中途有 GC 行为导致 safePoint 后移 > startTs，故 addIndex 失败，失败的 job 会重试，但是 startTS 好像没变，所以会一直报错。可以 cancel 这个 job，重新 addIndex 看看。

[mount]

自动终止了，重新执行试试

[mount]

重新试了下成功了，设置了gc lifetime 为10h. 可能与之有关！

[近墨者zyl]

学习案例，谢谢