TiDB 在线 DDL 操作对业务到底有没有影响
作者：TiDB 社区干货传送门
- 2024-05-10 北京
本文字数：18798 字
阅读完需：约 62 分钟
作者： 数据源的 TiDB 学习之路原文来源：https://tidb.net/blog/42d7ba4b
在 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 CSTSTART RUN oltp_read_write-10 TESTSTART TIME:2024-05-06 13:34:20BENCH_TYPE:oltp_read_writeBENCH_THREADS:10Config Content:mysql-host=10.17.3.151,10.17.3.152,10.17.3.153mysql-port=4000mysql-user=rootmysql-password=root123mysql-db=sbtesttime=7200report-interval=10db-driver=mysqlCommand: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 runsysbench 1.1.0 (using bundled LuaJIT 2.1.0-beta3)
Running the test with following options:Number of threads: 10Report 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=32Starting 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=32store:"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=32Starting 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=32store:"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!
复制代码
划线
评论
复制
发布于: 刚刚阅读数: 6
版权声明: 本文为 InfoQ 作者【TiDB 社区干货传送门】的原创文章。
原文链接:【http://xie.infoq.cn/article/1cef7ca84d5df4de6ae3d9c81】。文章转载请联系作者。
TiDB 社区干货传送门
关注
TiDB 社区官网:https://tidb.net/ 2021-12-15 加入
TiDB 社区干货传送门是由 TiDB 社区中布道师组委会自发组织的 TiDB 社区优质内容对外宣布的栏目，旨在加深 TiDBer 之间的交流和学习。一起构建有爱、互助、共创共建的 TiDB 社区 https://tidb.net/
评论