转转高效改表平台的演进之路

## 前言

我们假设这么一个场景，你管理着一个渡口，现在有一个人要过河，在没有桥的情况下，不管是早上晚上还是凌晨，你都要用船驮着他过河。随着时间的推移，越来越多的人过河。你会忙不过来，甚至崩溃。

如果这时候修了一座桥，有人再过河他直接过就好了，你不用管他在什么时间过河，你每天就可以很轻松了。

场景中的管理者好比`DBA`，渡河者好比研发，那座桥就好比改表平台。

今天就来分享一下转转公司关于这座桥的故事。

> 本文所有内容都是在讨论`MySQL`的改表。

## 1 1.0时代

这个时期的改表平台就好比是独木桥，通行能力比较弱不说，体质还不行，经常要修修补补。对业务来说，不仅限制多，还容易出现错误，对`DBA`来说，需要频繁介入，错误提示也不明显，还容易引发一些额外问题。

### 1.1 业务的槽点

- 功能少，如不支持添加唯一索引，不支持变更字段名(`change`)；
- 不支持交互，比如像终止任务、查看任务进度等都需要找`DBA`协助；
- 不支持分库分表的项目，分库分表项目需要提交16/32个工单；
- 报错信息不可见，需要找`DBA`介入处理；

### 1.2 DBA的槽点

- 错误的工单提示不明确，需要花费较多时间处理错误工单；
- 没有交互能力，需要查询正在进行的任务较繁琐；
- 手动处理需求较多，唯一索引，分库分表，以及其他不支持的需求都需要人工处理；
- 可控性低，任务一旦开始，除了可以终止（当然操作也很麻烦），其余不再可控；
- `MySQL`从5.6就可以`Online DDL`了，该学习了；

### 1.3 其他问题

- `binlog`暴增导致机器告警；
- `mdl`问题；
- 死锁问题；
- 延迟问题；

## 2 2.0时代

经过不断的升级优化来到了2.0，现阶段已经比较稳健完善，我们先来看看架构图。


我们2.0的改表平台系统拥有如下能力：

- 用户交互，用户可以终止，暂停，继续等操作；
- 支持添加唯一索引；
- 支持分库分表项目；
- 丰富的监控，比如mdl监控、磁盘监控、负载监控、延迟监控；
- 拥有处理常见告警能力，比如磁盘告警、`mdl`告警；
- 定时改表能力，这个可以很好的回避在高峰时间操作。根据用户给定的执行时间区间，在此区间之外的会暂停；
- 支持快速改表。这里的快速改表是使用`alter table`进行改表。比如`varchar`的扩展（5.7+）、删除列、删除索引、修改列名、修改列的默认值及注释、`MySQL 8.0`快速加列等场景；

2.0版本在2023-2-17上线，到2023年年底，我们对1.0系统的工单和2.0系统的工单做了个对比。

- 1.0版本。统计2022-2-17至2022-12-15改表工单数据，总工单数2851，失败数275，占比9.65%。
- 2.0版本。统计2023-2-17至2023-12-15改表工单数据，总工单数2424，失败数103，占比4.25%，错误率下降66%，其中因bug引起的错误有36个，主要集中在前面几个月出现，后面已经修复。

> 从1.0到2.0还有一个收益，使用快速改表的工单达到267个，占比11%，这267个工单是提交后使用`alter table`进行改表，如果是大表收益还是很可观的，大大提升了效率。

### 2.1 功能介绍

#### 2.1.1 智能改表

所谓智能，就是说改表平台会根据改表工单的需求自适应选定最佳改表方案。

比如，业务有个需求只是删除索引或者删除字段这种只改变元数据的操作，就可以使用快速改表方案，这样对于大表来说可以大大提升效率及使用体验。

再比如对于不能使用快速改表方案的场景，会优先选择`gh-ost`进行改表，在`gh-ost`不满足的情况下才会选择`pt-osc`进行改表。

之所以是这样的逻辑，主要是考虑到时间成本及改表对业务集群负载问题，快速改表基本没有时间成本，对业务集群也几乎不会加重负载，所以是最优选。

至于`gh-ost`和`pt-osc`的选择是考虑到`pt-osc`使用了触发器，容易触发死锁，添加唯一索引还存在丢数据的风险，所以会优先考虑`gh-ost`。

> 快速改表能力是我们改表平台的一大亮点。

```
[ 2024-02-06 14:15:12 ] [ 提示 ] [ 192.168.168.1 ] [ check/f_check_mode_opt.sh ] [ f_check_mode_opt:167 ] [ 开始执行 : 'main.sh ' ] [ 本次操作日志, 详情请看 : /opt/soft/zzonlineddl/logs/script/alter_table.dbzz_monitor.3306.20240220162542.log ]
[ 2024-02-06 14:15:12 ] [ 提示 ] [ 192.168.168.1 ] [ check/f_check_mode_opt.sh ] [ f_check_mode_opt:168 ] [ 从 '192.168.168.4' 拉取sql文件保存在 '/opt/soft/zzonlineddl/logs/sql/alter_table.dbzz_monitor.3306.20240220162542' ]
[ 2024-02-06 14:15:15 ] [ 成功 ] [ 192.168.168.1 ] [ check/f_check_net_delay.sh ] [ f_check_net_delay:20 ] [ '192.168.168.1' 到 '192.168.1.2' 的网络延迟为 1ms ]
[ 2024-02-06 14:15:15 ] [ 成功 ] [ 192.168.168.1 ] [ mysql/f_check_mysql_info.sh ] [ f_prepare_mysqltest_env:601 ] [ 在 '192.168.168.1.3:3306' 环境创建测试库完成 'testdb : alterdb_104064144_3306_26463_ghost' ]
[ 2024-02-06 14:15:15 ] [ 成功 ] [ 192.168.168.1 ] [ mysql/f_exe_sql_in_mysql.sh ] [ f_exe_sql_to_`DBA`db:105 ] [ f_get_diy_info:访问zzonlineddl ] [ update `DBA`db_alter_table_repl_time set big_table = 0,disk_space = 0,unique_key = 0 where order_id = 86536 and port = 3306; ]
[ 2024-02-06 14:15:15 ] [ 成功 ] [ 192.168.168.1 ] [ analysis/f_get_alter_sql.sh ] [ f_get_alter_sql:359 ] [ 提交的sql文件通过检测 ]
[ 2024-02-06 14:15:16 ] [ 成功 ] [ 192.168.168.1 ] [ mysql/f_create_test_env.sh ] [ f_create_test_env:34 ] [ '192.168.168.1.3:3306' 创建 'tb_test' 成功 ]
[ 2024-02-06 14:15:16 ] [ 警告 ] [ 192.168.168.1 ] [ analysis/f_get_alter_sql.sh ] [ f_get_alter_sql:389 ] [ sup_add_unique_key=1 '-K 1' 本次操作允许添加唯一索引 ]
[ 2024-02-06 14:15:16 ] [ 提示 ] [ 192.168.168.1 ] [ check/f_check_osc_or_alter.sh ] [ f_check_osc_or_alter:5 ] [ 本次操作将执行如下sql:ALTER TABLE tb_test modify column rshost varchar(25) DEFAULT NULL comment 'xxxx';  ]
[ 2024-02-06 14:15:16 ] [ 提示 ] [ 192.168.168.1 ] [ mysql/f_check_mysql_info.sh ] [ f_check_table_size:439 ] [ '192.168.1.2:3306' 实例的表 'dbzz_monitor.tb_test' 大小 '331MB' ]
[ 2024-02-06 14:15:16 ] [ 提示 ] [ 192.168.168.1 ] [ monitor/f_check_disk_space.sh ] [ f_check_disk_space:37 ] [ 获取到 '192.168.1.2' 机器剩余空间大小 '2440418MB' ]
[ 2024-02-06 14:15:16 ] [ 成功 ] [ 192.168.168.1 ] [ mysql/f_exe_sql_in_mysql.sh ] [ f_exe_sql_to_`DBA`db:105 ] [ f_check_osc_or_alter:访问zzonlineddl ] [ replace into `DBA`db_alter_table_order_info(order_id,user_mail,port,db_name,t_name,run_state,repl_time,osc_tool,progress,remarks,a_time,u_time) values(86536,'test@test',3306,'dbzz_monitor','tb_test',2,60,1,'0%','',now(),now()) ]
[ 2024-02-06 14:15:21 ] [ 成功 ] [ 192.168.168.1 ] [ mysql/f_support_onlineddl.sh ] [ f_check_modify_sql:250 ] [ modify 操作修改列属性仅修改元数据(包含varchar扩展长度), 满足使用online ddl特性. ]
[ 2024-02-06 14:15:21 ] [ 提示 ] [ 192.168.168.1 ] [ monitor/f_check_connect.sh ] [ f_check_connect:19 ] [ master '192.168.1.2:3306' 开始检查连接数 ]
[ 2024-02-06 14:15:22 ] [ 提示 ] [ 192.168.168.1 ] [ monitor/f_check_connect.sh ] [ f_check_connect:114 ] [ master '192.168.1.2:3306' 当前连接数 166 ]
[ 2024-02-06 14:15:23 ] [ 提示 ] [ 192.168.168.1 ] [ monitor/f_check_connect.sh ] [ f_check_connect:119 ] [ master '192.168.1.2:3306' 检查连接数结束 ]
[ 2024-02-06 14:15:23 ] [ 成功 ] [ 192.168.168.1 ] [ check/f_check_work_is_run.sh ] [ f_check_work_is_run:51 ] [ 该表 'dbzz_monitor.tb_test' 改表任务开始 '/opt/soft/zzonlineddl/logs/mark/mark.192.168.1.2.3306.dbzz_monitor.tb_test'. ]
[ 2024-02-06 14:15:23 ] [ 提示 ] [ 192.168.168.1 ] [ mysql/f_exe_alter_sql.sh ] [ f_exe_alter_sql:20 ] [ 本次改表在测试环境执行的操作 ] [ ALTER TABLE tb_test modify column rshost varchar(25) DEFAULT NULL comment 'xxxx';  ]
```

从上面日志可以看到（倒数第六行日志），对于扩展`varchar`的场景直接选择了快速改表，而不是使用改表工具对目标表进行数据拷贝，这样能大大提升改表效率。

#### 2.1.2 分库分表


业务可以根据实际情况进行选择，如果是分库分表项目，会自动生成其余库表的工单。

> 我们线上分库分表是对一个大表水平拆分成多个物理表，所以每个表的表结构都是一致，`DBA`可以通过映射关系生成其余表的工单，过去没有这个功能的时候一个表有16个分表的时候需要提16次工单。

#### 2.1.3 定时功能


> 完整的工单申请图请参见2.1.2部分的图。

这里的定时任务功能是完整的定时任务。很多系统的定时设计只关注开始，而不关注结束，比如用户希望是在凌晨2点到4点执行，那么如果4点没执行完就应该终止或者暂停，我们这个平台就做到了这点。

如果业务规定的时间内没执行完毕，就会暂停任务。

> 这里是判断当前时间是否处于业务要求的时间之内，如果否就给改表任务下发一个暂停信号。

#### 2.1.4 唯一索引


图中有个`极端场景`选择按钮，这个属于隐藏功能，只有当用户提交的语句是添加唯一索引的场景才会显示，而且默认是选择`是`，这种情况下是不允许提交工单，只有选择`否`才允许提交，且提交的时候会弹出提示框，要求用户查看丢数据的极端场景说明文档，不查看也不允许提交。

> 添加唯一索引丢数据的场景总结可以查看这篇文章 https://mp.weixin.qq.com/s/A7on81_QVz3-JEQB1426cA


关于唯一索引这个问题需要注意一下，如果改表平台是使用`pt-osc`进行改表，强烈建议关闭唯一索引的功能，否则时刻准备跑路吧。

> `pt-osc --check-unique-key-change`：禁止添加唯一索引。

我们在`pt-osc`添加唯一索引上踩过一次雷（添加唯一索引的目标字段存在重复数据），后来故障复盘后就关闭这个功能了，直到在2.0的时候使用`gh-ost`进行改表才重新打开此功能。

> `gh-ost`默认也是有丢数据的风险，但是它可以通过`hook`功能进行辅助，以此保证在丢数据的场景下可以直接终止操作。

#### 2.1.5 用户交互

（1）进度提示


改表平台的工单管理页面会主动展示改表进度及预计剩余时间，另外还会定时将改表进度通过企业微信及邮件的方式告知到用户。

> 这里的发送频率会根据剩余时间去判断。改表开始后十分钟发送第一次（如果十分钟没改完），如果预计剩余时间超过一小时，每半小时发送一次，否则每十分钟发送一次，后续计划将这个功能改成自定义，让用户去设置发送频率及是否需要发送进度。

（2）管理状态


用户可以通过管理页面进行交互行为，支持的操作有：

- 暂停工单，点击暂停后，至多延迟一分钟，改表任务就会被暂停；
- 终止工单，点击终止后，至多延迟一分钟，改表任务就会被终止；
- 恢复工单，`暂停状态`下，才会展示`恢复工单`按钮（如下图）；


上述三个工单状态的切换是通过改表工具自身的机制去实现，由于快速改表有10秒超时的机制，所以这类工单的状态可以不用考虑。

> - `pt-osc --pause-file`：可以通过控制这个标志文件实现任务的状态变更。
> - `gh-ost --panic-flag-file`：可以通过控制这个标志文件实现任务的状态变更。

- 查看工单详情；
- 更新`最大延迟`时间数，指左边的延迟时间输入框，输入需要更改后点击`更新`按钮即可；

这里`最大延迟`表示业务可以根据自身业务允许最大延迟时间去自定义，如果延迟不敏感可填大点，这样改表会快一些，如果延迟敏感就填小点，当然我们要给一个设置范围，不能让用户滥用。

> 所有交互操作仅工单提交者或者`DBA`可以操作。

#### 2.1.6 监控告警

（1）mdl监控

每个改表任务开始后，都会由主线程force一个子线程去检查目标集群的`mdl`情况，直到改表结束。

```
[ 2024-02-06 14:18:36 ] [ 成功 ] [ 192.168.1.1 ] [ `DBA`db/f_update_status_for_task.sh ] [ f_update_status_for_task:59 ] [ '192.168.1.2:3306' 改表修改chunk_size值, 从 '1500' 改为 '500' 完成 ]
[ 2024-02-06 14:18:37 ] [ 提示 ] [ 192.168.1.1 ] [ monitor/f_check_mdl_lock.sh ] [ f_check_mdl_lock:82 ] [ master '192.168.1.2:3306' 开始检查mdl状态 ]
[ 2024-02-06 14:19:21 ] [ 成功 ] [ 192.168.1.1 ] [ mysql/f_exe_sql_in_mysql.sh ] [ f_exe_sql_to_`DBA`db:105 ] [ f_print_progress_info:访问zzonlineddl ] [ update `DBA`db_alter_table_repl_time set progress = 'ongoing:4.3%, remaining:19m26s' where order_id = 87039;update `DBA`db_alter_table_order_info set run_state=0,repl_time=60,osc_tool='',progress='ongoing:4.3%, remaining:19m26s',u_time=now() where order_id=87039 and db_name='dbzz_monitor' and t_name='tb_monitor_host_info' and run_state != 3 ]
[ 2024-02-06 14:19:37 ] [ 成功 ] [ 192.168.1.1 ] [ mysql/f_exe_sql_in_mysql.sh ] [ f_exe_sql_to_`DBA`db:105 ] [ f_check_diy_info:访问zzonlineddl ] [ update `DBA`db_alter_table_repl_time set chunk_size = 700 where port = 3306 and order_id = 87039; ]
```

> 上面是检查`mdl`的子线程开启的日志提示，下图是改表遇到`mdl`后的邮件提示。


可以看到这个改表工单在切表的时候遇到了`mdl`导致切表受阻，这时候我们的改表系统会做出相应的处理，首先是将受`mdl`影响的语句及可能是mdl源的语句打印出来并告知用户或`DBA`，然后将产生的`mdl`源的SQL语句进行终止（如果符合`kill`条件的话），目的是让切表继续进行。

关于触发`mdl`问题，一般都是在最后切表的时候发生，这种情况下改表任务会做一些释放连接的处理：

- 第一步：`kill`掉空连接。所有空连接都会被`kill`，如果`mdl`恢复就继续切表，否则跳到第二步；
- 第二步：`kill`掉`sleep`状态的连接。这个有个时间约束，只有`sleep`状态的时间超过预定义的时间（设置了180s）就会被`kill`掉；
- 第三步：暂停。如果经过上述两步操作还存在`mdl`，就会做暂停处理，并发送通知给业务，让业务联系`DBA`介入进行处理。如果`30min`内未处理就将任务置为失败，结束工单；

> 添加这个暂停逻辑是考虑到，改表本身是一个周期较长的过程，如果是因为最后切表遇到`mdl`导致改表失败，成本有点高。另外这里的等待时间会根据表的大小进行微调，如果是小表就仅等待`15min`，如果是大表就等待`60min`。

（2）磁盘监控

改表可能会复制表的数据，对于大表这本身需要至少一倍的目标表的大小，全量数据的拷贝又会产生大量的`binlog`，为了稳妥起见，需要预留足够多的磁盘空间。

这里分改表前和改表后的监控，以此确保线上稳定的前提下改表任务的正常进行。

- 改表前

```
[ 2024-02-06 14:18:09 ] [ 成功 ] [ 192.168.1.1 ] [ check/f_check_net_delay.sh ] [ f_check_net_delay:20 ] [ '192.168.1.1' 到 '192.168.1.2' 的网络延迟为 1ms ]
[ 2024-02-06 14:18:10 ] [ 提示 ] [ 192.168.1.1 ] [ check/f_check_osc_or_alter.sh ] [ f_check_osc_or_alter:5 ] [ 本次操作将执行如下sql:alter table tb_monitor_host_info engine = innodb; ]
[ 2024-02-06 14:18:10 ] [ 提示 ] [ 192.168.1.1 ] [ mysql/f_check_mysql_info.sh ] [ f_check_table_size:439 ] [ '192.168.1.2:3306' 实例的表 'dbzz_monitor.tb_monitor_host_info' 大小 '8784MB' ]
[ 2024-02-06 14:18:11 ] [ 提示 ] [ 192.168.1.1 ] [ monitor/f_check_disk_space.sh ] [ f_check_disk_space:37 ] [ 获取到 '192.168.1.2' 机器剩余空间大小 '2471723MB' ]
[ 2024-02-06 14:18:15 ] [ 提示 ] [ 192.168.1.1 ] [ monitor/f_check_connect.sh ] [ f_check_connect:19 ] [ master '192.168.1.2:3306' 开始检查连接数 ]
[ 2024-02-06 14:18:16 ] [ 提示 ] [ 192.168.1.1 ] [ monitor/f_check_connect.sh ] [ f_check_connect:114 ] [ master '192.168.1.2:3306' 当前连接数 148 ]
[ 2024-02-06 14:18:16 ] [ 提示 ] [ 192.168.1.1 ] [ monitor/f_check_connect.sh ] [ f_check_connect:119 ] [ master '192.168.1.2:3306' 检查连接数结束 ]
[ 2024-02-06 14:18:16 ] [ 提示 ] [ 192.168.1.1 ] [ check/f_check_osc_or_alter.sh ] [ f_check_osc_or_alter:157 ] [ master:'192.168.1.2' 与本机 '192.168.1.1', 网络延迟小于预设值, 且binlog_format=ROW, 本次ddl使用ghost来操作 ]
[ 2024-02-06 14:18:16 ] [ 成功 ] [ 192.168.1.1 ] [ check/f_check_work_is_run.sh ] [ f_check_work_is_run:51 ] [ 该表 'dbzz_monitor.tb_monitor_host_info' 改表任务开始 '/opt/soft/zzonlineddl/logs/mark/mark.192.168.1.2.3306.dbzz_monitor.tb_monitor_host_info'. ]
[ 2024-02-06 14:18:17 ] [ 命令 ] [ 192.168.1.1 ] [ mysql/f_exe_osc_comm.sh ] [ f_exe_osc_comm:33 ] [ 本次改表在测试环境执行的操作 ] [ gh-ost --host=192.168.168.1.3 --port=3306 --user=alter_user --password=xxxxxx --database=alterdb_104064144_3306_24807_ghost --table=tb_monitor_host_info --alter=" engine = innodb;" --replica-server-id=801455774 --serve-socket-file=/opt/soft/zzonlineddl/logs/mark/a82175a3d060ef3476bbf17c8379b77a_socket_24807 --panic-flag-file=/opt/soft/zzonlineddl/logs/mark/64780353dbde511d754438697afe1c5e_pause_24807 --allow-on-master --max-load 'Threads_running=64,Threads_connected=4000' --chunk-size=3000 --max-lag-millis=30000 --cut-over-lock-timeout-seconds=30 --execute --ok-to-drop-table ]
```

从上面的日志可以看到（第四行日志），改表前会对磁盘空间进行监控，磁盘空间可用容量小于两倍目标表的大小，就会改表失败。
> 改表至少要求需要两倍目标表的大小，否则可能会因为磁盘空间不够导致改表失败。

- 改表后

```
[ 2024-02-06 14:19:37 ] [ 成功 ] [ 192.168.1.1 ] [ `DBA`db/f_update_status_for_task.sh ] [ f_update_status_for_task:59 ] [ '192.168.1.2:3306' 改表修改chunk_size值, 从 '500' 改为 '700' 完成 ]
[ 2024-02-06 14:20:22 ] [ 成功 ] [ 192.168.1.1 ] [ mysql/f_exe_sql_in_mysql.sh ] [ f_exe_sql_to_`DBA`db:105 ] [ f_print_progress_info:访问zzonlineddl ] [ update `DBA`db_alter_table_repl_time set progress = 'ongoing:8.7%, remaining:19m16s' where order_id = 87039;update `DBA`db_alter_table_order_info set run_state=0,repl_time=60,osc_tool='',progress='ongoing:8.7%, remaining:19m16s',u_time=now() where order_id=87039 and db_name='dbzz_monitor' and t_name='tb_monitor_host_info' and run_state != 3 ]
[ 2024-02-06 14:20:37 ] [ 提示 ] [ 192.168.1.1 ] [ monitor/f_check_disk_space.sh ] [ f_check_disk_for_node:49 ] [ 开始检查192.168.1.2 192.168.1.5磁盘空间使用率 ]
[ 2024-02-06 14:20:38 ] [ 成功 ] [ 192.168.1.1 ] [ mysql/f_exe_sql_in_mysql.sh ] [ f_exe_sql_to_`DBA`db:105 ] [ f_check_diy_info:访问zzonlineddl ] [ update `DBA`db_alter_table_repl_time set chunk_size = 600 where port = 3306 and order_id = 87039; ]
```

从上面日志可以看到（第三行日志），改表任务开启后，会起一个子线程对磁盘空间进行监控，如果触发告警线就会去清理`binlog`，只要触发告警线且`binlog`文件数量大于50个就会清理。

> 触发告警线后，`binlog`文件小于等于50个文件，就会退出改表，并清理临时表（文件）。

（3）延迟监控

改表可能会放大业务的`dml`流量，对于大表来说，很容易产生从库延迟。

目标集群本身已经存在从库延迟，还进行改表操作，无疑是会加重延迟，为了避免此类问题的出现，我们对从库延迟也做了监控，这里分改表前和改表后的监控。

- 改表前

```
[ 2024-02-06 14:18:20 ] [ 提示 ] [ 192.168.1.1 ] [ check/f_check_osc_or_alter.sh ] [ f_check_osc_or_alter:5 ] [ 本次操作将执行如下sql:alter table tb_monitor_host_info engine = innodb; ]
[ 2024-02-06 14:18:21 ] [ 成功 ] [ 192.168.1.1 ] [ mysql/f_exe_sql_in_mysql.sh ] [ f_exe_sql_to_`DBA`db:105 ] [ f_check_osc_or_alter:访问zzonlineddl ] [ update `DBA`db_alter_table_order_info set run_state=0,a_time=now(),progress='0%' where order_id=87039 and db_name='dbzz_monitor' and t_name='tb_monitor_host_info' ]
[ 2024-02-06 14:18:25 ] [ 提示 ] [ 192.168.1.1 ] [ mysql/f_check_slave_status.sh ] [ f_check_slave_status:48 ] [ 从节点 '192.168.1.5:3306:1s' 复制存在延迟, 小于10s, 本次改表继续 ]
[ 2024-02-06 14:18:25 ] [ 提示 ] [ 192.168.1.1 ] [ monitor/f_check_connect.sh ] [ f_check_connect:19 ] [ master '192.168.1.2:3306' 开始检查连接数 ]
[ 2024-02-06 14:18:26 ] [ 提示 ] [ 192.168.1.1 ] [ monitor/f_check_connect.sh ] [ f_check_connect:114 ] [ master '192.168.1.2:3306' 当前连接数 148 ]
[ 2024-02-06 14:18:26 ] [ 提示 ] [ 192.168.1.1 ] [ monitor/f_check_connect.sh ] [ f_check_connect:119 ] [ master '192.168.1.2:3306' 检查连接数结束 ]
[ 2024-02-06 14:18:26 ] [ 成功 ] [ 192.168.1.1 ] [ `DBA`db/f_check_diy_info.sh ] [ f_check_diy_info:90 ] [ insert into `DBA`db_alter_table_repl_time(id,order_id,user_mail,port,chunk_size,run_state,repl_time,max_connect,remarks) select 0,87039, 'test@test.com',3306,1000,0,60,2500,'临时记录工单完成会删除'; ]
[ 2024-02-06 14:18:26 ] [ 成功 ] [ 192.168.1.1 ] [ mysql/f_exe_sql_in_mysql.sh ] [ f_exe_sql_to_`DBA`db:105 ] [ f_check_diy_info:访问zzonlineddl ] [ insert into `DBA`db_alter_table_repl_time(id,order_id,user_mail,port,chunk_size,run_state,repl_time,max_connect,remarks) select 0,87039, 'test@test.com',3306,1000,0,60,2500,'临时记录工单完成会删除'; ]
[ 2024-02-06 14:18:26 ] [ 提示 ] [ 192.168.1.1 ] [ mysql/f_exe_osc_comm.sh ] [ f_exe_osc_comm:110 ] [ 现在开始在 '192.168.1.2:3306' 环境执行提交的改表语句 ]
```

从上面日志可以看到（第三行日志），改表前会对目标集群的从库进行延迟检查，如果存在延迟，就会进入一个600秒等待延迟的逻辑。

在600秒内任意时间追上延迟就会继续改表，如果等待600秒后仍然存在延迟就会终止改表。

> 这里的延迟阈值根据业务在提交工单的时候设置的值，如果不设置就是默认值10秒，即从库延迟超过10秒就会进入600秒等待阶段。

- 改表后

改表任务开启后，也会有延迟监控，这块逻辑是改表工具自身机制，出现延迟后就会做相应处理。其中`gh-ost`会减少`chunk size`的值，`pt-osc`会直接暂停。

> - `gh-ost --max-lag-millis`：当主从复制延迟时间超过该值后，将采取节流(throttle)措施，慢慢减少。
> - `pt-osc --max-lag`：当主从复制延迟时间超过该值后, 会暂停n秒，然后再检查，直到主从延时小于`--max-lag`值。这里的n是`--check-interval`的值。

（4）负载监控

改表可能会放大业务的`dml`流量，可能会加大集群的整体负载。

目标集群本身就存在较高的负载仍要改表，无疑是加重负载，可能会把集群搞崩，为了保险起见，我们对负载也做了监控，这里分改表前和改表后的监控。

- 改表前

```
[ 2024-02-06 14:18:26 ] [ 提示 ] [ 192.168.1.1 ] [ mysql/f_exe_osc_comm.sh ] [ f_exe_osc_comm:134 ] [ 开始在线上环境改表操作 ]
[ 2024-02-06 14:18:36 ] [ 提示 ] [ 192.168.1.1 ] [ monitor/f_check_connect.sh ] [ f_check_connect:19 ] [ master '192.168.1.2:3306' 开始检查活跃连接数 ]
[ 2024-02-06 14:18:36 ] [ 提示 ] [ 192.168.1.1 ] [ monitor/f_check_connect.sh ] [ f_check_connect:19 ] [ master '192.168.1.2:3306' 开始检查连接数 ]
[ 2024-02-06 14:18:36 ] [ 成功 ] [ 192.168.1.1 ] [ mysql/f_exe_sql_in_mysql.sh ] [ f_exe_sql_to_`DBA`db:105 ] [ f_check_diy_info:访问zzonlineddl ] [ update `DBA`db_alter_table_repl_time set chunk_size = 500 where port = 3306 and order_id = 87039; ]
```

从上面日志可以看到（第二、三行日志），改表前会对目标集群的负载进行检查，如果连接数较高（包括活跃连接数），就会进入一个30秒等待的逻辑。如果持续30秒都连接数过高，就会终止改表。

- 改表后

改表任务开启后，也会有负载监控，这块逻辑是改表工具自身机制，出现负载后就会做相应处理。其中`gh-ost`会减少`chunk size`的值，`pt-osc`会直接暂停或者终止。

> - `gh-ost --max-load`：当负载超过该值后，将采取节流(throttle)措施，这个节流的动作由我们根据活跃连接数的大小进行设置。
> - `pt-osc --max-load`：当负载超过该值后，会暂停。另外还有个参数`--critical-load`，这个参数功能跟`--max-load`类似，但是会多一个终止的能力，所以我们对这两参数的控制如下：

```
--max-load='Threads_running=20'
--critical-load='Threads_running=40'
```

#### 2.1.7 弱原子性

我们都知道一个改表工单可能存在多个表的需求，这就涉及到原子性问题。

如果一个工单存在多个需求，里面的语句要么都成功要么都失败，虽然在技术上我们是没法解决原子性问题，只能说是尽可能的保证原子性。

我们现在的做法是先在测试环境将目标表都准备好，然后再测试环境预跑一遍所有的改表语句，可以一定程度上保证原子性。

#### 2.1.8 工单重试

如果一个工单存在多个需求，执行到某个语句失败了，但是可以经过人为解决或者重试就可以继续改表，这时候如果需要重新提交工单，重新走一遍审批流，对业务来说不太友好。

对此我们做了兼容，支持重试功能，对于已经改表成功的语句我们会跳过。言外之意就是会从上次改表失败的语句继续开始，这个重试仅限同一个工单。


> 只有错误的工单才支持重试，如果是定时改表工单这个功能会失效，另外对于重试操作会强制使用改表工具进行改表，即快速改表方案被忽略。

#### 2.1.9 收尾工作

这部分主要是针对`gh-ost`及`pt-osc`。

- gh-ost

```
[ 2024-02-06 14:20:41 ] [ 成功 ] [ 192.168.1.1 ] [ `DBA`db/f_update_status_for_task.sh ] [ f_update_status_for_task_from_api:148 ] [ 遇到mdl等待,现在暂停改表 请于0min内联系`DBA`进行处理 ]
[ 2024-02-06 14:20:41 ] [ 成功 ] [ 192.168.1.1 ] [ mysql/f_exe_sql_in_mysql.sh ] [ f_exe_sql_to_`DBA`db:105 ] [ f_update_status_for_task_from_api:访问zzonlineddl ] [ update `DBA`db_alter_table_repl_time set run_state = 0 , user_mail = 'test@test.com' where order_id = 86538; ]
[ 2024-02-06 14:20:41 ] [ 成功 ] [ 192.168.1.1 ] [ `DBA`db/f_update_status_for_task.sh ] [ f_update_status_for_task_from_api:148 ] [ 改表恢复,请稍等 等待计算 ]
[ 2024-02-06 14:21:13 ] [ 错误 ] [ 192.168.1.1 ] [ monitor/f_check_mdl_lock.sh ] [ f_check_mdl_lock:236 ] [ master节点 '192.168.1.2:3306' kill掉空连接后还存在mdl锁等待, 现在将终止改表任务 ]
[ 2024-02-06 14:21:13 ] [ 提示 ] [ 192.168.1.1 ] [ monitor/f_check_mdl_lock.sh ] [ f_check_mdl_lock:241 ] [ 现在将在 '192.168.1.2:3306' kill掉如下连接 ][ ID : 1178905237 ] [ USER : inception ] [ IP : 192.168.1.1:39200 ] [ DB : dbzz_monitor ] [ COMMAND : Query ] [ TIME : 69 ] [ STATE : Waiting for table metadata lock ] [ SQL : drop /* gh-ost */ table if exists `dbzz_monitor`.`_tb_monitor_host_info_del` ]
[ 2024-02-06 14:21:13 ] [ 提示 ] [ 192.168.1.1 ] [ monitor/f_check_mdl_lock.sh ] [ f_check_mdl_lock:245 ] [ 现在将在 '192.168.1.2:3306' kill掉如上连接 ]
[ 2024-02-06 14:21:14 ] [ 提示 ] [ 192.168.1.1 ] [ mysql/f_exe_osc_comm.sh ] [ f_exe_osc_comm:137 ] [ 注 : 改表在线上环境执行结束, 但是不代表任务执行成功 ] [ alter table tb_monitor_host_info engine = innodb; ]
[ 2024-02-06 14:21:14 ] [ 警告 ] [ 192.168.1.1 ] [ mysql/f_exe_osc_comm.sh ] [ f_exe_osc_comm:141 ] [ 正在等待mdl检查任务结束(最长120秒), 如果需要手动终止检查mdl任务可以删除'/opt/soft/zzonlineddl/logs/mark/f_check_mdl_lock_for_192.168.1.2_3306_dbzz_monitor_tb_monitor_host_info_23766'文件 ]
[ 2024-02-06 14:21:14 ] [ 提示 ] [ 192.168.1.1 ] [ monitor/f_check_connect.sh ] [ f_check_connect:119 ] [ master '192.168.1.2:3306' 检查活跃连接数结束 ]
[ 2024-02-06 14:21:14 ] [ 提示 ] [ 192.168.1.1 ] [ monitor/f_check_connect.sh ] [ f_check_connect:119 ] [ master '192.168.1.2:3306' 检查连接数结束 ]
[ 2024-02-06 14:21:14 ] [ 提示 ] [ 192.168.1.1 ] [ mysql/f_delete_tmp_table.sh ] [ f_delete_tmp_table:31 ] [ 准备清理临时表 'drop table if exists _tb_monitor_host_info_del; drop table if exists _tb_monitor_host_info_ghc; drop table if exists _tb_monitor_host_info_gho;' ] [ 原因是遇到mdl导致改表任务终止 ]
[ 2024-02-06 14:21:14 ] [ 提示 ] [ 192.168.1.1 ] [ mysql/f_delete_tmp_table.sh ] [ f_delete_tmp_table:35 ] [ 正在清理改表工具 'gh-ost' 生成的临时表 'drop table if exists _tb_monitor_host_info_del; drop table if exists _tb_monitor_host_info_ghc; drop table if exists _tb_monitor_host_info_gho;' ]
[ 2024-02-06 14:21:15 ] [ 成功 ] [ 192.168.1.1 ] [ mysql/f_exe_osc_comm.sh ] [ f_exe_osc_comm:153 ] [ mdl检查任务完成 ]
[ 2024-02-06 14:21:15 ] [ 成功 ] [ 192.168.1.1 ] [ mysql/f_exe_sql_in_mysql.sh ] [ f_exe_sql_to_`DBA`db:105 ] [ f_record_info_to_`DBA`db:访问zzonlineddl ] [ insert into `DBA`db_alter_table_record_info(order_id,user_mail,order_type,port,dbname,t_name,t_time,remarks) select 86538,'test@test.com','gh-ost',3306,'dbzz_monitor','tb_monitor_host_info','2024-02-06 14:21:15','遇到mdl等待. 详情请看192.168.1.1:/opt/soft/zzonlineddl/logs/script/alter_table.dbzz_monitor.3306.20240125190109.log'; insert into `DBA`db_alter_table_progress_info(order_id,user_mail,port,table_name,send_state,start_time,remarks) select 86538, 'test@test.com',3306,'dbzz_monitor.tb_monitor_host_info',0,'2024-01-25 19:01:26','遇到mdl等待,改表终止 如有疑问请联系`DBA`'; ]
[ 2024-02-06 14:21:18 ] [ 错误 ] [ 192.168.1.1 ] [ mysql/f_exe_osc_comm.sh ] [ f_exe_osc_comm:172 ] [ 在线上环境 '192.168.1.2:3306' 遇到mdl等待, 终止任务, 详情请看 '/opt/soft/zzonlineddl/logs/script/alter_table.dbzz_monitor.3306.20240125190109.log' ] [ alter table tb_monitor_host_info engine = innodb; ]
[ 2024-02-06 14:21:18 ] [ 成功 ] [ 192.168.1.1 ] [ mysql/f_exe_sql_in_mysql.sh ] [ f_exe_sql_to_`DBA`db:105 ] [ f_exit:访问zzonlineddl ] [ update `DBA`db_alter_table_crontab_task set istate=0 where order_id=86538 and istate = 1; ]
[ 2024-02-06 14:21:18 ] [ 成功 ] [ 192.168.1.1 ] [ mysql/f_exe_sql_in_mysql.sh ] [ f_exe_sql_to_`DBA`db:105 ] [ f_exit:访问zzonlineddl ] [ update `DBA`db_alter_table_queue_info set order_id = 0, inip = '', run_state = 0 where mysql_port = 3306 and db_name = 'dbzz_monitor' and run_state = 1 and order_id = 86538; ]
[ 2024-02-06 14:21:18 ] [ 成功 ] [ 192.168.1.1 ] [ mysql/f_exe_sql_in_mysql.sh ] [ f_exe_sql_to_`DBA`db:105 ] [ f_exit:访问zzonlineddl ] [ delete from `DBA`db_alter_table_repl_time where order_id = 86538 and port = 3306; ]
[ 2024-02-06 14:21:18 ] [ 成功 ] [ 192.168.1.1 ] [ mysql/f_exe_sql_in_mysql.sh ] [ f_exe_sql_to_`DBA`db:105 ] [ f_exit:访问zzonlineddl ] [ update dbzz_dbcloud.tb_prod_workorder set status = '驳回',repulse_time=now(),complete_time=now(), reject_msg="[ master节点 'xxxx:3306' kill掉空连接后还存在mdl锁等待, 现在将终止改表任务 ]" where id = 86538; update `DBA`db_alter_table_order_info set run_state=-1,u_time=now() , remarks = concat(remarks,'',"[ master节点 'xxxx:3306' kill掉空连接后还存在mdl锁等待, 现在将终止改表任务 ]") where order_id=86538 and db_name='dbzz_monitor' and (t_name='tb_monitor_host_info' or run_state = 2) ]
[ 2024-02-06 14:21:19 ] [ 成功 ] [ 192.168.1.1 ] [ mysql/f_delete_tmp_table.sh ] [ f_delete_tmp_table:60 ] [ 改表工具 'gh-ost' 生成的临时表 'drop table if exists _tb_monitor_host_info_del; drop table if exists _tb_monitor_host_info_ghc; drop table if exists _tb_monitor_host_info_gho;' 清理成功 ]
```

从上面的日志可以看到，遇到`mdl`后导致工单失败，然后进行临时表的清理记录。

- pt-osc

> 我们线上暂时没有遇到使用`pt-osc`改表失败的场景，所以没有相关错误日志，但是大致的日志内容跟`gh-ost`差不多，只是多了一个清理触发器的记录。

我们都知道`pt-osc`是通过触发器去同步增量数据，当改表任务被异常终止（`kill -9`），主线程是不会去清理触发器的，这势必会影响下次改表。

如果手动清理可能会因操作不当引发故障。为了避免误操作，我们直接在代码进行收尾操作，确保了先删除触发器再清理临时表。

> 未清理触发器就先删除临时表，这时候触发器会报错进而导致原表的`dml`失败。

### 2.2 问题梳理

从调研，到开发再到上线，其实遇到了很多问题，在这里我们挑选三个大问题来分享一下。

#### 2.2.1 SQL解析

如何去判断一个改表需求是符合快速改表？这个具有一定的难度及复杂度，如果细讲都够写一篇长文了，所以这里仅简单讲讲思路。

我们都知道`alter table`语法十分复杂，一个语句可能包含很多操作。所以我们需要将需求拆解，这就需要去研究`alter table`语句的截断。实现方式应该很多，我们简单介绍一下我们的做法。

（1）忽略注释的内容

这部分包含`MySQL`层面的注释以及改表语句的comment，如下列举的四种情况。

> - /* */，这种代码注释包裹起来的内容直接被忽略；
> - \#打头的行直接被忽略；
> - \--打头的行直接被忽略；
> - comment ''，这种引号之间的内容直接被忽略；

（2）忽略括号包裹的内容

这部分包`index(col1,col2)`，`decimal(5,2)`，`add(col1 int,col2 int)`类似这些的语法。

上述语法在括号内可能包含逗号，我们最终需要以逗号作为截断标志将改表语句进行拆解。

> 这部分可能有很多场景, 需要去看下官方文档总结一下。

（3）规范使用

比如，唯一索引不允许与其他操作一起使用，因为如果新加字段并在新加的字段上添加唯一索引会导致丢数据。

修改表名使用`change`操作，且仅允许修改表名，其他属性不允许变更。

（4）添加兜底手段

SQL解析可能会解析错误，解析错误就会导致选择了错误的方案进行改表。

案例一、某需求被解析成了快速改表，但是实际上需要重建，这种场景就会锁表，如果是大表，势必会引发长时间无法写入，业务肯定是无法接受的。

案例二、某需求被解析成了快速改表，但是实际上需要原地拷贝数据。如果是大表，势必会引发主从延迟。对于延迟敏感的业务也会难以接受的。

> 关于这个案例我们就在线上遇到过，在2.0新上线之初，有个业务对`varchar`进行扩展，然后其他属性都没变更，改表平台就解析成可以使用快速改表（查阅官方文档，这个场景也确实只修改了元数据），然后就导致该集群出现了从库延迟。

为了规避上述两种问题，我们做了一个兜底策略，即：快速改表的工单会加一个10秒超时，10秒未执行完成就放弃改表，即便解析错误，我们的执行结果也是可控的。

#### 2.2.2 varchar扩展

`varchar`实际存储的内容不固定，要用1到2个字节来存实际长度，所以要求修改前后，这个存放实际长度的字节数要求一致。

这条要求是官方文档介绍的，基于这个要求我们设计好代码后遇到两个比较郁闷的问题，其中还一个还有点玄学的味道。

（1）目标字段有索引

我们线上环境遇到一个场景。如果业务扩展在`varchar`扩展的时候目标字段是索引字段，这时候我们的改表平台还是会将工单解析成可以快速改表，实际上这个场景会重建索引需要拷贝数据。

> 这个就是`问题一`部分的`案例二`。

（2）其他问题

我们线上环境还遇到一个场景。业务扩展在`varchar`扩展的时候目标字段没有索引字段，也仅仅只是扩展长度，其他信息都没变，这时候我们的改表平台会将工单解析成可以快速改表，实际上这个场景也是需要拷贝数据，具体原因不明，最后通过重建表就解决了，就是说重建表以后再碰上这类需求就能快速改表了。

关于这个问题，我们没找到原因，甚至没法在测试环境复现，也排查了表空洞问题，猜测可能是跟统计信息不准确有关系（或者约束）。

>如果是使用物理备份文件进行恢复环境是可以复现的，当初我们是想着表不是很大，而且`mysqldump`导出导入方便点，所以搞了很多次都没有复现，原因是`mysqldump`导出导入属于重建表了。

#### 2.2.3 唯一索引

MySQL读数据是按数据页，如果数据本身保证了唯一，那唯一索引和普通索引在读性能上并没有太大的区别，但是在写操作普通索引可以用到`change buffer`，这样看来唯一索引的写性能可能还不如普通索引，所以我们可以引导业务使用普通索引。

非要用唯一索引的话，一定不能使用pt-osc工具去添加唯一索引。小表推荐使用`alter table`直接添加，大表推荐使用`gh-ost hook`的方式添加。

## 3 总结

本文简单的介绍了转转改表平台的发展历程，并展示了相关功能以及梳理总结了一些问题，希望能帮大家提供一些思路以及规避一些问题。

其实改表需求本身挺简单的，就一条命令的事，但是深入分析总结后会发现，整个需求会涉及到很多问题，比如延迟、负载、磁盘告警、锁等等问题，归根结底可能还是由于线上环境的复杂性，导致改表需求变得复杂，或者说变得困难。

我们一步步走过来，从命令行到脚本，从脚本到工单，从1.0到2.0。触发过死锁和`mdl`，踩过唯一索引的坑，也遇到过`varchar`扩展的问题。一直都在不断的完善功能，优化逻辑，到今天我们的平台算是比较稳健完善了。有了交互能力，有了止血的能力，也有了自愈的能力，像是一个智能的机器人协助`DBA`处理日常的改表需求，虽然还是有一些瑕疵，但最终的收益是比较可观的。

以上，仅供参考。

---
关于作者
> 莫善，转转`DBA`。负责TiDB、MongoDB、MySQL运维及转转数据库平台开发。