在TiDB的官方文档中，我们了解到TiDB中的DDL变更是一种在线异步模式变更方式，相比传统MySQL或有些数据库的优势在于这种在线DDL变更对业务几乎没有影响。之前和一些同学的交流中了解到，有些业务系统是不可避免的要在生产线上去实时的变更表结构，比如在气象传感物联网场景中经常会使用大宽表来存储不同指标的数据(如温度、风力、湿度等)。随着时间的推移，业务需求的变化导致需要在原有的表结构中增加一些新的指标项(如太阳照射强度)，这就要求数据库具备在线增加字段的能力。
还有一些场景是偏向OLTP交易型的，比如银行里面的核心业务系统，它要求每一笔交易都在百毫秒级别响应。随着数据量的不断增加，在线上运行中我们可能需要为了提升交易的性能而动态地添加索引，这也要求数据库具备在线动态添加索引的能力。
TiDB的在线DDL是否如文档描述中那样是一种在线异步模式变更，以及是否会对业务产生影响呢？本文我们通过在sysbench压测过程中执行各种不同的DDL来获得结论。
先上结论
TiDB中的DDL分为逻辑DDL语句和物理DDL语句。
- 逻辑DDL语句。只修改数据库对象的元数据，不对变更对象存储的数据进行处理，例如修改列名、增加字段、扩展字段长度等。逻辑DDL在TiDB中执行瞬间完成，对业务完全没有影响。
- 物理DDL语句。不但会修改变更对象的元数据，同时也修改变更对象所存储的用户数据。例如为表创建索引、减小字段长度、修改字段类型等。物理DDL在TiDB中执行耗时与表数据量有关，数据的重新组织(REORG)对业务会性能产生一定影响，但不会造成业务中断或报错。
测试总结
测试步骤
测试准备
准备一个3节点的TiDB集群，版本为7.5.1，同时准备另外1个节点运行sysbench压测程序。首先使用sysbench程序模拟造数 1亿 数据量的表，接着对 1亿 数据量的表进行50并发oltp_read_write测试执行。
mysql> select count(*) from sbtest1;
+-----------+
| count(*) |
+-----------+
| 100000000 |
+-----------+
1 row in set (5.99 sec)
[2024-05-06 11:50:04]Start prepare bench data......
sysbench 1.1.0 (using bundled LuaJIT 2.1.0-beta3)
Initializing worker threads...
Creating table 'sbtest1'...
Inserting 100000000 records into 'sbtest1'
Creating a secondary index on 'sbtest1'...
[2024-05-06 13:34:20]Finish prepare bench data......
2024年 05月 06日 星期一 13:34:20 CST
START RUN oltp_read_write-10 TEST
START TIME:2024-05-06 13:34:20
BENCH_TYPE:oltp_read_write
BENCH_THREADS:10
Config Content:
mysql-host=10.17.3.151,10.17.3.152,10.17.3.153
mysql-port=4000
mysql-user=root
mysql-password=root123
mysql-db=sbtest
time=7200
report-interval=10
db-driver=mysql
Command:
sysbench --config-file=/tidb/sysbench_test/log_test/sysbench.cfg oltp_read_write --threads=10 --tables=1 --table-size=100000000 --rand-type=uniform --mysql-ignore-errors=all run
sysbench 1.1.0 (using bundled LuaJIT 2.1.0-beta3)
Running the test with following options:
Number of threads: 10
Report intermediate results every 10 second(s)
Initializing random number generator from current time
Initializing worker threads...
Threads started!
[ 10s ] thds: 10 tps: 358.15 qps: 7176.46 (r/w/o: 5024.94/1434.21/717.31) lat (ms,95%): 32.53 err/s: 0.00 reconn/s: 0.00
[ 20s ] thds: 10 tps: 369.30 qps: 7382.73 (r/w/o: 5167.82/1476.41/738.50) lat (ms,95%): 30.81 err/s: 0.00 reconn/s: 0.00
[ 30s ] thds: 10 tps: 371.40 qps: 7431.97 (r/w/o: 5202.88/1486.19/742.90) lat (ms,95%): 30.26 err/s: 0.00 reconn/s: 0.00
[ 40s ] thds: 10 tps: 376.50 qps: 7530.40 (r/w/o: 5271.20/1506.20/753.00) lat (ms,95%): 30.81 err/s: 0.00 reconn/s: 0.00
[ 50s ] thds: 10 tps: 363.70 qps: 7268.62 (r/w/o: 5087.71/1453.60/727.30) lat (ms,95%): 33.12 err/s: 0.00 reconn/s: 0.00
[ 60s ] thds: 10 tps: 361.50 qps: 7234.27 (r/w/o: 5063.48/1447.69/723.10) lat (ms,95%): 32.53 err/s: 0.00 reconn/s: 0.00
[ 70s ] thds: 10 tps: 369.40 qps: 7386.30 (r/w/o: 5171.50/1476.00/738.80) lat (ms,95%): 31.37 err/s: 0.00 reconn/s: 0.00
[ 80s ] thds: 10 tps: 366.70 qps: 7336.59 (r/w/o: 5134.89/1468.30/733.40) lat (ms,95%): 31.94 err/s: 0.00 reconn/s: 0.00
[ 90s ] thds: 10 tps: 367.80 qps: 7353.22 (r/w/o: 5147.22/1470.40/735.60) lat (ms,95%): 31.37 err/s: 0.00 reconn/s: 0.00
…
查看TPS图表，sysbench程序正在稳定运行当中，
模拟在线增加字段
mysql> show create table sbtest1;
+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| sbtest1 | CREATE TABLE `sbtest1` (
`id` int(11) NOT NULL,
`k` int(11) NOT NULL DEFAULT '0',
`c` char(120) NOT NULL DEFAULT '',
`pad` char(60) NOT NULL DEFAULT '',
PRIMARY KEY (`id`) /*T![clustered_index] CLUSTERED */,
KEY `k_1` (`k`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin |
+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> select current_timestamp;
+---------------------+
| current_timestamp |
+---------------------+
| 2024-05-06 13:53:13 |
+---------------------+
1 row in set (0.01 sec)
mysql> alter table sbtest1 add column added_col char(10) not null default '';
Query OK, 0 rows affected (0.53 sec)
mysql> select current_timestamp;
+---------------------+
| current_timestamp |
+---------------------+
| 2024-05-06 13:53:13 |
+---------------------+
1 row in set (0.00 sec)
mysql> show create table sbtest1;
+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| sbtest1 | CREATE TABLE `sbtest1` (
`id` int(11) NOT NULL,
`k` int(11) NOT NULL DEFAULT '0',
`c` char(120) NOT NULL DEFAULT '',
`pad` char(60) NOT NULL DEFAULT '',
`added_col` char(10) NOT NULL DEFAULT '',
PRIMARY KEY (`id`) /*T![clustered_index] CLUSTERED */,
KEY `k_1` (`k`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin |
+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
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 |
+--------+---------+------------+------------------------+--------------+-----------+----------+-----------+---------------------+---------------------+---------------------+--------+
| 194 | sbtest | sbtest1 | add column | public | 184 | 191 | 0 | 2024-05-06 13:53:13 | 2024-05-06 13:53:13 | 2024-05-06 13:53:13 | synced |
+--------+---------+------------+------------------------+--------------+-----------+----------+-----------+---------------------+---------------------+---------------------+--------+
10 rows in set (0.01 sec)
上述输出显示在线增加字段的一个过程，从中我们看到在线增加字段耗时仅为0.53秒。再观察TPS图表，可以看到TPS无明显变化，证明在线增加字段对业务几乎无影响。
模拟在线删除字段
mysql> show create table sbtest1;
+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| sbtest1 | CREATE TABLE `sbtest1` (
`id` int(11) NOT NULL,
`k` int(11) NOT NULL DEFAULT '0',
`c` char(120) NOT NULL DEFAULT '',
`pad` char(60) NOT NULL DEFAULT '',
`added_col` char(10) NOT NULL DEFAULT '',
PRIMARY KEY (`id`) /*T![clustered_index] CLUSTERED */,
KEY `k_1` (`k`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin |
+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> select current_timestamp;
+---------------------+
| current_timestamp |
+---------------------+
| 2024-05-06 13:56:31 |
+---------------------+
1 row in set (0.00 sec)
mysql> alter table sbtest1 drop column added_col;
Query OK, 0 rows affected (0.54 sec)
mysql> select current_timestamp;
+---------------------+
| current_timestamp |
+---------------------+
| 2024-05-06 13:56:32 |
+---------------------+
1 row in set (0.00 sec)
mysql> show create table sbtest1;
+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| sbtest1 | CREATE TABLE `sbtest1` (
`id` int(11) NOT NULL,
`k` int(11) NOT NULL DEFAULT '0',
`c` char(120) NOT NULL DEFAULT '',
`pad` char(60) NOT NULL DEFAULT '',
PRIMARY KEY (`id`) /*T![clustered_index] CLUSTERED */,
KEY `k_1` (`k`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin |
+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
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 |
+--------+---------+------------+------------------------+--------------+-----------+----------+-----------+---------------------+---------------------+---------------------+--------+
| 195 | sbtest | sbtest1 | drop column | none | 184 | 191 | 0 | 2024-05-06 13:56:31 | 2024-05-06 13:56:32 | 2024-05-06 13:56:32 | synced |
| 194 | sbtest | sbtest1 | add column | public | 184 | 191 | 0 | 2024-05-06 13:53:13 | 2024-05-06 13:53:13 | 2024-05-06 13:53:13 | synced |
+--------+---------+------------+------------------------+--------------+-----------+----------+-----------+---------------------+---------------------+---------------------+--------+
10 rows in set (0.01 sec)
输出显示在线删除字段只花费0.54秒的时间，进一步查看TPS图表，发现业务性能也几乎没有任何变化。证明在线删除字段对业务完全无影响。
模拟扩展字段长度
mysql> show create table sbtest1;
+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| sbtest1 | CREATE TABLE `sbtest1` (
`id` int(11) NOT NULL,
`k` int(11) NOT NULL DEFAULT '0',
`c` char(120) NOT NULL DEFAULT '',
`pad` char(60) NOT NULL DEFAULT '',
PRIMARY KEY (`id`) /*T![clustered_index] CLUSTERED */,
KEY `k_1` (`k`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin |
+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> select current_timestamp;
+---------------------+
| current_timestamp |
+---------------------+
| 2024-05-06 14:09:58 |
+---------------------+
1 row in set (0.00 sec)
mysql> alter table sbtest1 change column pad pad char(120);
Query OK, 0 rows affected (0.54 sec)
mysql> select current_timestamp;
+---------------------+
| current_timestamp |
+---------------------+
| 2024-05-06 14:09:59 |
+---------------------+
1 row in set (0.00 sec)
mysql> show create table sbtest1;
+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| sbtest1 | CREATE TABLE `sbtest1` (
`id` int(11) NOT NULL,
`k` int(11) NOT NULL DEFAULT '0',
`c` char(120) NOT NULL DEFAULT '',
`pad` char(120) DEFAULT NULL,
PRIMARY KEY (`id`) /*T![clustered_index] CLUSTERED */,
KEY `k_1` (`k`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin |
+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
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 |
+--------+---------+------------+------------------------+--------------+-----------+----------+-----------+---------------------+---------------------+---------------------+--------+
| 196 | sbtest | sbtest1 | modify column | public | 184 | 191 | 0 | 2024-05-06 14:09:58 | 2024-05-06 14:09:58 | 2024-05-06 14:09:58 | synced |
| 195 | sbtest | sbtest1 | drop column | none | 184 | 191 | 0 | 2024-05-06 13:56:31 | 2024-05-06 13:56:32 | 2024-05-06 13:56:32 | synced |
+--------+---------+------------+------------------------+--------------+-----------+----------+-----------+---------------------+---------------------+---------------------+--------+
10 rows in set (0.01 sec)
根据输出结果显示，在线扩展字段长度耗时0.54秒，进一步查看TPS图表，发现此操作对业务也几乎没有影响。
模拟缩小字段长度
mysql> show create table sbtest1;
+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| sbtest1 | CREATE TABLE `sbtest1` (
`id` int(11) NOT NULL,
`k` int(11) NOT NULL DEFAULT '0',
`c` char(120) NOT NULL DEFAULT '',
`pad` char(120) DEFAULT NULL,
PRIMARY KEY (`id`) /*T![clustered_index] CLUSTERED */,
KEY `k_1` (`k`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin |
+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> select current_timestamp;
+---------------------+
| current_timestamp |
+---------------------+
| 2024-05-06 14:13:35 |
+---------------------+
1 row in set (0.00 sec)
mysql> alter table sbtest1 change column pad pad char(60);
Query OK, 0 rows affected (47 min 42.05 sec)
mysql> select current_timestamp;
+---------------------+
| current_timestamp |
+---------------------+
| 2024-05-06 15:01:17 |
+---------------------+
1 row in set (0.01 sec)
mysql> show create table sbtest1;
+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| sbtest1 | CREATE TABLE `sbtest1` (
`id` int(11) NOT NULL,
`k` int(11) NOT NULL DEFAULT '0',
`c` char(120) NOT NULL DEFAULT '',
`pad` char(60) DEFAULT NULL,
PRIMARY KEY (`id`) /*T![clustered_index] CLUSTERED */,
KEY `k_1` (`k`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin |
+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
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 |
+--------+---------+------------+------------------------+--------------+-----------+----------+-----------+---------------------+---------------------+---------------------+--------+
| 197 | sbtest | sbtest1 | modify column | public | 184 | 191 | 98903622 | 2024-05-06 14:13:35 | 2024-05-06 14:13:35 | 2024-05-06 15:01:16 | synced |
| 196 | sbtest | sbtest1 | modify column | public | 184 | 191 | 0 | 2024-05-06 14:09:58 | 2024-05-06 14:09:58 | 2024-05-06 14:09:58 | synced |
| 195 | sbtest | sbtest1 | drop column | none | 184 | 191 | 0 | 2024-05-06 13:56:31 | 2024-05-06 13:56:32 | 2024-05-06 13:56:32 | synced |
| 194 | sbtest | sbtest1 | add column | public | 184 | 191 | 0 | 2024-05-06 13:53:13 | 2024-05-06 13:53:13 | 2024-05-06 13:53:13 | synced |
+--------+---------+------------+------------------------+--------------+-----------+----------+-----------+---------------------+---------------------+---------------------+--------+
10 rows in set (0.01 sec)
输出结果显示，在线缩小字段长度耗时约47分钟。之所以耗时比较长，是因为此DDL后台产生了write reorganization动作。也就是说，在线缩小字段的动作背后需要重新组织数据，这可以在DDL执行的过程中执行admin show ddl jobs查看到中间状态。
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 |
+--------+---------+------------+------------------------+----------------------+-----------+----------+-----------+---------------------+---------------------+---------------------+---------+
| 197 | sbtest | sbtest1 | modify column | write reorganization | 184 | 191 | 97597722 | 2024-05-06 14:13:35 | 2024-05-06 14:13:35 | NULL | running |
| 196 | sbtest | sbtest1 | modify column | public | 184 | 191 | 0 | 2024-05-06 14:09:58 | 2024-05-06 14:09:58 | 2024-05-06 14:09:58 | synced |
+--------+---------+------------+------------------------+----------------------+-----------+----------+-----------+---------------------+---------------------+---------------------+---------+
11 rows in set (0.01 sec)
再检查一下对性能的影响，如下图所示，在线运行的TPS大约从400降到300，但是业务并没有任何报错，这可以从sysbench运行输出中查看到。由于后台需要重新组织数据，从CPU使用率、节点负载、网络通信、磁盘吞吐上面都能看到有明显的增长。
模拟修改字段类型(int修改为varchar)
mysql> show create table sbtest1;
+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| sbtest1 | CREATE TABLE `sbtest1` (
`id` int(11) NOT NULL,
`k` bigint(20) DEFAULT NULL,
`c` char(120) NOT NULL DEFAULT '',
`pad` char(60) DEFAULT NULL,
`added_col` int(11) DEFAULT NULL,
PRIMARY KEY (`id`) /*T![clustered_index] CLUSTERED */,
KEY `k_1` (`k`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin |
+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> select current_timestamp;
+---------------------+
| current_timestamp |
+---------------------+
| 2024-05-06 17:16:13 |
+---------------------+
1 row in set (0.00 sec)
mysql> alter table sbtest1 change column added_col added_col varchar(20);
Query OK, 0 rows affected (57 min 21.03 sec)
mysql> select current_timestamp;
+---------------------+
| current_timestamp |
+---------------------+
| 2024-05-06 18:13:34 |
+---------------------+
1 row in set (0.00 sec)
mysql> show create table sbtest1;
+---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| sbtest1 | CREATE TABLE `sbtest1` (
`id` int(11) NOT NULL,
`k` bigint(20) DEFAULT NULL,
`c` char(120) NOT NULL DEFAULT '',
`pad` char(60) DEFAULT NULL,
`added_col` varchar(20) DEFAULT NULL,
PRIMARY KEY (`id`) /*T![clustered_index] CLUSTERED */,
KEY `k_1` (`k`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin |
+---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
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 |
+--------+---------+------------+------------------------+--------------+-----------+----------+-----------+---------------------+---------------------+---------------------+---------------+
| 201 | sbtest | sbtest1 | modify column | public | 184 | 191 | 99606909 | 2024-05-06 17:16:13 | 2024-05-06 17:16:13 | 2024-05-06 18:13:32 | synced |
+--------+---------+------------+------------------------+--------------+-----------+----------+-----------+---------------------+---------------------+---------------------+---------------+
10 rows in set (0.01 sec)
从上述输出可以看出，修改字段类型耗时57分钟，此过程后台主要在重新组织数据。从sysbench输出可以看到压测无任何报错，观察TPS性能变化，大概下降20%左右。
模拟在线添加索引
mysql> show create table sbtest1;
+---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| sbtest1 | CREATE TABLE `sbtest1` (
`id` int(11) NOT NULL,
`k` bigint(20) DEFAULT NULL,
`c` char(120) NOT NULL DEFAULT '',
`pad` char(60) DEFAULT NULL,
`added_col` varchar(20) DEFAULT NULL,
PRIMARY KEY (`id`) /*T![clustered_index] CLUSTERED */,
KEY `k_1` (`k`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin |
+---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> select current_timestamp;
+---------------------+
| current_timestamp |
+---------------------+
| 2024-05-06 22:28:10 |
+---------------------+
1 row in set (0.00 sec)
mysql> create index ix_c on sbtest1(c);
Query OK, 0 rows affected (5 min 48.04 sec)
mysql> select current_timestamp;
+---------------------+
| current_timestamp |
+---------------------+
| 2024-05-06 22:33:58 |
+---------------------+
1 row in set (0.00 sec)
mysql> show create table sbtest1;
+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| sbtest1 | CREATE TABLE `sbtest1` (
`id` int(11) NOT NULL,
`k` bigint(20) DEFAULT NULL,
`c` char(120) NOT NULL DEFAULT '',
`pad` char(60) DEFAULT NULL,
`added_col` varchar(20) DEFAULT NULL,
PRIMARY KEY (`id`) /*T![clustered_index] CLUSTERED */,
KEY `k_1` (`k`),
KEY `ix_c` (`c`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin |
+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
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 |
+--------+---------+------------+------------------------+--------------+-----------+----------+-----------+---------------------+---------------------+---------------------+---------------+
| 204 | sbtest | sbtest1 | add index /* ingest */ | public | 184 | 191 | 100281941 | 2024-05-06 22:28:10 | 2024-05-06 22:28:10 | 2024-05-06 22:33:57 | synced |
+--------+---------+------------+------------------------+--------------+-----------+----------+-----------+---------------------+---------------------+---------------------+---------------+
10 rows in set (0.01 sec)
从上述输出可以看到，在线创建索引耗时近6分钟。进一步查看TPS情况，发现主要有两个拐点，第一个拐点时性能下降不明显(10%以内)，第二个拐点下降稍微明显(30%以内)。从sysbench执行输出来看，业务运行无任何报错。
问题记录
DDL reorg之后性能无法回到初始状态
如下图所示，DDL reorg之前TPS平均约420，但DDL reorg之后约380。
优化方案：对集群进行手工compact操作，参考 TiKV Control 使用说明 | PingCAP 文档中心
[tidb@host-10-17-3-151 ~]$ tiup ctl:v7.5.1 tikv --pd 10.17.3.151:2379 compact-cluster -d kv --bottommost force -c write --threads=32
Starting component `ctl`: /home/tidb/.tiup/components/ctl/v7.5.1/ctl tikv --pd 10.17.3.151:2379 compact-cluster -d kv --bottommost force -c write --threads=32
store:"10.17.3.152:20160" compact db:Kv cf:write range:[[], []) success!
store:"10.17.3.151:20160" compact db:Kv cf:write range:[[], []) success!
store:"10.17.3.153:20160" compact db:Kv cf:write range:[[], []) success!
[tidb@host-10-17-3-151 ~]$ tiup ctl:v7.5.1 tikv --pd 10.17.3.151:2379 compact-cluster -d kv --bottommost force -c default --threads=32
Starting component `ctl`: /home/tidb/.tiup/components/ctl/v7.5.1/ctl tikv --pd 10.17.3.151:2379 compact-cluster -d kv --bottommost force -c default --threads=32
store:"10.17.3.151:20160" compact db:Kv cf:default range:[[], []) success!
store:"10.17.3.152:20160" compact db:Kv cf:default range:[[], []) success!
store:"10.17.3.153:20160" compact db:Kv cf:default range:[[], []) success!