TiDB7.5.5 版本加索引巨慢问题梳理
作者：TiDB 社区干货传送门
- 2025-01-17 北京
本文字数：13910 字
阅读完需：约 46 分钟
作者： xxxxxxxx 原文来源：https://tidb.net/blog/b2ddded3
[toc]
一、背景介绍
近期对7.5.5版本进行调研，在调研过程中采用sysbench对该版本进行压力测试，测试过程发现一个 1000w 行的表，表大小不足 2GB，建一个二级索引，执行时间超过3h还没结束。但是在4.0.13版本创建索引32min就结束了，ddl相关参数都是保持一致的。
本文就来聊聊一下这个问题的原因及解决方案，顺便吐槽一下。
本文测试依赖
sysbench工具环境，请自行准备。
二、问题复现
1、部署 7.5.5 集群
自定义参数配置如下，其余参数使用该版本默认值。
server_configs: tidb: binlog.enable: false binlog.ignore-error: false log.file.max-days: 30 mem-quota-query: 10737418240 new_collations_enabled_on_first_bootstrap: false tikv: log.file.max-days: 30 raftdb.defaultcf.write-buffer-size: 128MB readpool.coprocessor.use-unified-pool: true readpool.storage.use-unified-pool: true readpool.unified.max-thread-count: 24 rocksdb.defaultcf.block-cache-size: 6GB rocksdb.defaultcf.block-size: 64KB rocksdb.defaultcf.write-buffer-size: 128MB rocksdb.writecf.block-cache-size: 6GB rocksdb.writecf.write-buffer-size: 128MB storage.block-cache.capacity: 6GB
复制代码
2、测试数据
sysbench /usr/local/sysbench/src/lua/oltp_read_write.lua \--mysql-host=192.168.1.100 --mysql-port=15122 --mysql-db=tidb_monitor \--mysql-user=test --mysql-password=123456 --table_size=10000000 \--tables=32 --threads=8 --report-interval=10 --time=60 prepare
复制代码
新建
tidb_monitor测试库。
在sysbench的prepare阶段会做如下三个事情。
create table
insert into
create index
在prepare阶段通过输出日志可以知道在create insert阶段一直不动了，对此很疑惑，下面来抓取证据。
（1）tidb 的日志
可以通过该日志确定在2025/01/09 17:26:46左右开始创建索引。
[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)"]
复制代码
（2）ddl 执行状态
通过admin show ddl jobs查看ddl的执行状态。目标表大概是1000w行，现在执行到 999w 行的时候就一直不动了，看起来是在最后阶段卡住了。
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 20: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>
复制代码
通过上述信息可以发现，加索引执行时间差不多两个半小时，目标表大小1000w，表大小1.8GB，ddl 相关参数tidb_ddl_reorg_worker_cnt确实设置的有点低，这也是我们线上使用的值。
碰到这个问题第一反应是会不会遇到锁了，但是转念一想，这是测试环境的库，没有访问流量，属实很郁闷。因为前不久刚对4.0.13版本做了相同的测试，测试还很顺利，所以就返回去找了4.0.13版本执行ddl的情况。
以下是 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>
复制代码
可以看到，在4.0.13版本创建二级索引只需要 32 分钟就完成了。
所以后来就又重新梳理了测试流程，清理环境并再次测试，结果还是一样，在创建索引的时候卡住，最后实在想不通就到论坛寻求帮助，好消息是最后找到了问题根源，坏消息是对我们当前环境 / 架构很不友好，改造成本很高。
这是论坛的问题帖子，https://asktug.com/t/topic/1038228
三、问题原因
究其根源其实就是7.5.5版本引入了分布式执行框架，对加索引这种场景可以更加快速。
这个功能的使用时有条件的，即tidb节点所在的机器必须有高速盘（SSD），否则会遇到不可预知的问题，很巧我们这次遇到的问题就是文档里面说的不可预知的问题。
分布式执行框架 https://docs.pingcap.com/zh/tidb/v7.5/tidb-distributed-execution-framework
看到文档的描述，结合了我们公司的情况发现一个大问题。
我们都知道，tidb在之前的版本中扮演的角色就是路由功能，只会消耗cpu 内存 网络等资源，所以我们当时规划的时候是为这类组件分配到了机械盘的机器，也就是说，我们公司的tidb集群环境中tidb组件是部署在机械盘的机器上，不满足分布式执行框架的使用条件，但是这个功能默认是开启的，所以这就很闹心了。
这里必须要吐槽一下，既然新功能存在使用条件，且未达到使用条件的场景可能触发不可预知的问题，那么这种情况下就不应该默认打开，要不然很容易引发生产环境的故障。
四、解决方案
既然知道了问题的原因，那么解决起来就十分简单了。这里列举两个解决方案：
1、关闭分布式执行功能
这个方案亲测有效。
关闭该功能十分简单，只需要连接到tidb执行如下命令即可。
SET GLOBAL tidb_ddl_enable_fast_reorg = OFF;
复制代码
这个参数的开关不需要重启tidb组件，关闭该功能后重新测试就很顺畅了。
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 |+--------+--------------+------------+---------------------+----------------------+-----------+----------+-----------+---------------------+---------------------+---------------------+---------+| 321 | tidb_monitor | sbtest8 | add index /* txn */ | write reorganization | 47 | 302 | 9450303 | 2025-01-10 16:55:57 | 2025-01-10 16:55:57 | NULL | running || 322 | tidb_monitor | sbtest2 | add index /* txn */ | write reorganization | 47 | 310 | 9457175 | 2025-01-10 16:56:07 | 2025-01-10 16:56:07 | NULL | running || 334 | tidb_monitor | sbtest9 | create table | public | 47 | 333 | 0 | 2025-01-10 17:05:45 | 2025-01-10 17:05:45 | 2025-01-10 17:05:45 | synced || 332 | tidb_monitor | sbtest11 | create table | public | 47 | 331 | 0 | 2025-01-10 17:05:25 | 2025-01-10 17:05:25 | 2025-01-10 17:05:25 | synced || 330 | tidb_monitor | sbtest12 | create table | public | 47 | 329 | 0 | 2025-01-10 17:04:56 | 2025-01-10 17:04:56 | 2025-01-10 17:04:56 | synced || 328 | tidb_monitor | sbtest14 | create table | public | 47 | 327 | 0 | 2025-01-10 17:04:27 | 2025-01-10 17:04:27 | 2025-01-10 17:04:27 | synced || 326 | tidb_monitor | sbtest15 | create table | public | 47 | 325 | 0 | 2025-01-10 17:03:38 | 2025-01-10 17:03:38 | 2025-01-10 17:03:38 | synced || 324 | tidb_monitor | sbtest13 | create table | public | 47 | 323 | 0 | 2025-01-10 17:02:54 | 2025-01-10 17:02:54 | 2025-01-10 17:02:54 | synced || 320 | tidb_monitor | sbtest1 | add index /* txn */ | public | 47 | 300 | 10000000 | 2025-01-10 16:55:48 | 2025-01-10 16:55:48 | 2025-01-10 17:05:44 | synced || 319 | tidb_monitor | sbtest7 | add index /* txn */ | public | 47 | 304 | 10000000 | 2025-01-10 16:55:44 | 2025-01-10 16:55:44 | 2025-01-10 17:03:37 | synced || 318 | tidb_monitor | sbtest3 | add index /* txn */ | public | 47 | 306 | 10000000 | 2025-01-10 16:55:37 | 2025-01-10 16:55:37 | 2025-01-10 17:05:25 | synced || 317 | tidb_monitor | sbtest6 | add index /* txn */ | public | 47 | 308 | 10000000 | 2025-01-10 16:55:36 | 2025-01-10 16:55:36 | 2025-01-10 17:04:27 | synced |+--------+--------------+------------+---------------------+----------------------+-----------+----------+-----------+---------------------+---------------------+---------------------+---------+12 rows in set (0.01 sec)
mysql>
复制代码
可以看到大概10min就创建完成了，比4.0.13版本快了20min。
2、为tidb分配一块高性能盘
这个方案我实测没有达到预期结果，所以请大家慎用，这里只是梳理一下流程。
因资源紧张，但是又要方便测试，所以我将 tidb 节点缩减至 1 个，找一台 ssd 机器来部署这个 tidb 节点。
这个实现起来比较简单，只需要更新一下tidb组件的配置文件即可，如下。
tiup cluster edit-config cluster-name
复制代码
server_configs: tidb: temp-dir: /work/tidb/deploy/tidb-15148/temp #该目录需要是高性能盘
复制代码
该参数默认值是/tmp/tidb目录。
mysql> show config where type = 'tidb' and name = 'temp-dir';+------+-------------------+----------+-----------+| Type | Instance | Name | Value |+------+-------------------+----------+-----------+| tidb | 11.23.69.26:15148 | temp-dir | /tmp/tidb |+------+-------------------+----------+-----------+4 rows in set (0.10 sec)
mysql>
复制代码
更新完配置后，直接reload即可.
tiup cluster reload cluster-name -R tidb
复制代码
reload以后会在这个目录下新建一个tmp_ddl-port的目录。
# ll /work/tidb/deploy/tidb-15148/temptotal 4drwx------ 2 tidb tidb 4096 Jan 10 17:10 tmp_ddl-15148#
复制代码
mysql> show config where type = 'tidb' and name = 'temp-dir';+------+-------------------+----------+-----------------------------------+| Type | Instance | Name | Value |+------+-------------------+----------+-----------------------------------+| tidb | 11.23.69.26:15148 | temp-dir | /work/tidb/deploy/tidb-15148/temp |+------+-------------------+----------+-----------------------------------+4 rows in set (0.02 sec)
mysql>
复制代码
做完这几个操作后确认tidb的配置tidb_ddl_enable_fast_reorg是否是打开状态。
mysql> show variables like 'tidb_ddl_enable_fast_reorg';+----------------------------+-------+| Variable_name | Value |+----------------------------+-------+| tidb_ddl_enable_fast_reorg | ON |+----------------------------+-------+1 row in set (0.00 sec)
mysql>
复制代码
做完以上事情我以为就能万事大吉了，但是事与愿违，测试过程中并不能达到预期，即创建索引的时候还是会卡住，但是查看该tidb节点发现temp-dir目录是有数据的，所以不太可能是因为权限问题导致。
# ll /work/tidb/deploy/tidb-15148/temp/tmp_ddl-15148/872/194a1ec7-d1d6-5e7b-b1ae-8de2d857e51etotal 97340-rw-r--r-- 1 tidb tidb 0 Jan 11 17:10 000002.log-rw-r--r-- 1 tidb tidb 99659294 Jan 11 17:10 000004.sst-rw-r--r-- 1 tidb tidb 875 Jan 11 17:10 000005.sst-rw-r--r-- 1 tidb tidb 16 Jan 11 17:10 CURRENT-rw-r--r-- 1 tidb tidb 0 Jan 11 17:10 LOCK-rw-r--r-- 1 tidb tidb 224 Jan 11 17:10 MANIFEST-000001-rw-r--r-- 1 tidb tidb 1113 Jan 11 17:10 OPTIONS-000003#
复制代码
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 |+--------+--------------+------------+------------------------+----------------------+-----------+----------+-----------+---------------------+---------------------+---------------------+----------+| 872 | tidb_monitor | sbtest2 | add index /* ingest */ | write reorganization | 57 | 858 | 0 | 2025-01-11 17:10:22 | 2025-01-11 17:10:22 | NULL | running || 873 | tidb_monitor | sbtest4 | add index | none | 57 | 860 | 0 | 2025-01-11 17:10:23 | NULL | NULL | queueing || 874 | tidb_monitor | sbtest7 | add index | none | 57 | 859 | 0 | 2025-01-11 17:10:23 | NULL | NULL | queueing || 875 | tidb_monitor | sbtest8 | add index | none | 57 | 857 | 0 | 2025-01-11 17:10:23 | NULL | NULL | queueing || 876 | tidb_monitor | sbtest1 | add index | none | 57 | 862 | 0 | 2025-01-11 17:10:23 | NULL | NULL | queueing || 877 | tidb_monitor | sbtest3 | add index | none | 57 | 856 | 0 | 2025-01-11 17:10:23 | NULL | NULL | queueing || 878 | tidb_monitor | sbtest6 | add index | none | 57 | 867 | 0 | 2025-01-11 17:10:25 | NULL | NULL | queueing || 879 | tidb_monitor | sbtest5 | add index | none | 57 | 870 | 0 | 2025-01-11 17:10:26 | NULL | NULL | queueing || 871 | tidb_monitor | sbtest5 | create table | public | 57 | 870 | 0 | 2025-01-11 17:04:11 | 2025-01-11 17:04:11 | 2025-01-11 17:04:12 | synced || 869 | tidb_monitor | sbtest6 | create table | public | 57 | 867 | 0 | 2025-01-11 17:04:11 | 2025-01-11 17:04:11 | 2025-01-11 17:04:11 | synced || 868 | tidb_monitor | sbtest1 | create table | public | 57 | 862 | 0 | 2025-01-11 17:04:11 | 2025-01-11 17:04:11 | 2025-01-11 17:04:11 | synced || 866 | tidb_monitor | sbtest4 | create table | public | 57 | 860 | 0 | 2025-01-11 17:04:11 | 2025-01-11 17:04:11 | 2025-01-11 17:04:11 | synced || 865 | tidb_monitor | sbtest7 | create table | public | 57 | 859 | 0 | 2025-01-11 17:04:11 | 2025-01-11 17:04:11 | 2025-01-11 17:04:11 | synced || 864 | tidb_monitor | sbtest2 | create table | public | 57 | 858 | 0 | 2025-01-11 17:04:11 | 2025-01-11 17:04:11 | 2025-01-11 17:04:11 | synced || 863 | tidb_monitor | sbtest8 | create table | public | 57 | 857 | 0 | 2025-01-11 17:04:11 | 2025-01-11 17:04:11 | 2025-01-11 17:04:11 | synced || 861 | tidb_monitor | sbtest3 | create table | public | 57 | 856 | 0 | 2025-01-11 17:04:11 | 2025-01-11 17:04:11 | 2025-01-11 17:04:11 | synced || 855 | tidb_monitor | sbtest8 | drop table | none | 57 | 825 | 0 | 2025-01-11 17:04:11 | 2025-01-11 17:04:11 | 2025-01-11 17:04:11 | synced || 854 | tidb_monitor | sbtest7 | drop table | none | 57 | 831 | 0 | 2025-01-11 17:04:11 | 2025-01-11 17:04:11 | 2025-01-11 17:04:11 | synced |+--------+--------------+------------+------------------------+----------------------+-----------+----------+-----------+---------------------+---------------------+---------------------+----------+18 rows in set (0.01 sec)
mysql> select now();+---------------------+| now() |+---------------------+| 2025-01-11 17:26:57 |+---------------------+1 row in set (0.00 sec)
mysql> select version();+--------------------+| version() |+--------------------+| 8.0.11-TiDB-v7.5.5 |+--------------------+1 row in set (0.00 sec)
mysql>
复制代码
之前没有改
temp-dir配置之前，ROW_COUNT的值还是接近1000w的，现在直接是0了。
后来试着将tidb_enable_dist_task参数也改成打开状态，也不行，这个有点懵逼了，从官方文档的描述来看操作应该是没问题的，但是结果有出入，所以慎用吧。
mysql> show variables like 'tidb_enable_dist_task';+-----------------------+-------+| Variable_name | Value |+-----------------------+-------+| tidb_enable_dist_task | ON |+-----------------------+-------+1 row in set (0.00 sec)
mysql>
复制代码
五、总结
感觉最新版本的尝试还是要慎重，虽然新版本能解决很多旧版本的问题，或许性能也有一定的提升，但是升级新版本也会带来未知的问题，尤其是不易通过测试测出来的。
有想尝试7.5.5版本的小伙伴还是充分测试一下吧，慎重。
再次吐槽一下官方，既然新功能可能导致不可预知的问题，那还是建议默认关闭吧，线上环境的复杂度远超想象，保不齐测试没测出来，上线就故障了。
以上仅供参考。
划线
评论
复制
发布于: 刚刚阅读数: 3
版权声明: 本文为 InfoQ 作者【TiDB 社区干货传送门】的原创文章。
原文链接:【http://xie.infoq.cn/article/2f32175c858d83162a0a8650e】。文章转载请联系作者。
TiDB 社区干货传送门
关注
TiDB 社区官网:https://tidb.net/ 2021-12-15 加入
TiDB 社区干货传送门是由 TiDB 社区中布道师组委会自发组织的 TiDB 社区优质内容对外宣布的栏目，旨在加深 TiDBer 之间的交流和学习。一起构建有爱、互助、共创共建的 TiDB 社区 https://tidb.net/
评论