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

前言

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

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

场景中的管理者好比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 唯一索引

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

添加唯一索引丢数据的场景总结可以查看这篇文章 mp.weixin.qq.com/s/A7on81_QV…

关于唯一索引这个问题需要注意一下，如果改表平台是使用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运维及转转数据库平台开发。