写点什么

MySQL 5.7 DDL 与 GH-OST 对比分析

  • 2024-08-08
    广东
  • 本文字数:3835 字

    阅读完需:约 13 分钟

作者:来自 vivo 互联网存储研发团队- Xia Qianyong


本文首先介绍 MySQL 5.7 DDL 以及 GH-OST 的原理,然后从效率、空间占用、锁阻塞、binlog 日志产生量、主备延时等方面,对比 GH-OST 和 MySQL5.7 DDL 的差异。

一、背景介绍

在 MySQL 数据库中,DDL(数据定义语言)操作包括对表结构、索引、触发器等进行修改、创建和删除等操作。由于 MySQL 自带的 DDL 操作可能会阻塞 DML(数据操作语言)写语句的执行,大表变更容易产生主备延时,DDL 变更的速度也不能控制,因此在进行表结构变更时需要非常谨慎。


为了解决这个问题,可以使用 GitHub 开源的工具 GH-OST。GH-OST 是一个可靠的在线表结构变更工具,可以实现零宕机、低延迟、自动化、可撤销的表结构变更。相比于 MySQL 自带的 DDL 操作,GH-OST 可以在不影响正常业务运行的情况下进行表结构变更,避免了 DDL 操作可能带来的风险和影响。


通过使用 GH-OST 工具,可以对 MySQL 数据库中的表进行在线结构变更,而不会对业务造成太大的影响。同时,GH-OST 工具还提供了多种高级特性,如安全性检测、自动化流程等,可以帮助用户更加高效地进行表结构变更。

二、MySQL5.7 几种 DDL 介绍

2.1 copy

  • server 层触发创建临时表

  • server 层对源表加 MDL 锁,阻塞 DML 写、不阻塞 DML 读

  • server 层从源表中逐行读取数据,写入到临时表

  • 数据拷贝完成后,升级字典锁,禁止读写

  • 删除源表,把临时表重命名为源表


MySQL copy 方式的 DDL 变更,数据表的重建(主键、二级索引重建),server 层作为中转把从 innodb 读取数据表,在把数据写到 innodb 层临时表。简单示意图如下:

2.2 inplace

(1)rebuild table

需要根据 DDL 语句创建新的表结构,根据源表的数据和变更期间增量日志,重建新表的主键索引和所有的二级索引。


Prepare 阶段

  • 创建新的临时 frm 文件

  • 持有 EXCLUSIVE-MDL 锁,禁止读写

  • 根据 alter 类型,确定执行方式(copy,online-rebuild,online-norebuild)假如是 Add Index,则选择 online-norebuild

  • 更新数据字典的内存对象

  • 分配 row_log 对象记录增量

  • 生成新的临时 ibd 文件


ddl 执行阶段 :

  • 降级 EXCLUSIVE-MDL 锁,允许读写

  • 扫描 old_table 的聚集索引每一条记录 rec

  • 遍历新表的聚集索引和二级索引,逐一处理各个索引

  • 根据 rec 构造对应的索引项

  • 将构造索引项插入 sort_buffer 块排序

  • 将 sort_buffer 块更新到新表的索引上

  • 记录 ddl 执行过程中产生的增量(记录主键和索引字段)

  • 重放 row_log 中的操作到新表索引商

  • 重放 row_log 间产生 dml 操作 append 到 row_log 最后一个 Block


commit 阶段 :

  • 当前 Block 为 row_log 最后一个时,禁止读写,升级到 EXCLUSIVE-MDL 锁

  • 重做 row_log 中最后一部分增量

  • 更新 innodb 的数据字典表

  • rename 临时 idb 文件,frm 文件

  • 增量完成


MySQL rebuild table 方式的 DDL,数据不需要通过 sever 层中转,innodb 层自己完成数据表的重建。简单示意图如下:


(2)build-index

需要根据 DDL 语句创建新的表结构,根据源表的数据和变更期间增量日志,创建新的索引。


Prepare 阶段 :

  • 持有 EXCLUSIVE-MDL 锁,禁止读写

  • 根据 alter 类型,确定执行方式(copy,online-rebuild,online-norebuild)

  • 假如是 Add Index,则选择 online-norebuild

  • 更新数据字典的内存对象

  • 分配 row_log 对象记录增量


ddl 执行阶段 :

  • 降级 EXCLUSIVE-MDL 锁,允许读写

  • 扫描 old_table 的聚集索引每一条记录 rec

  • 遍历新表的聚集索引,根据 rec 构造新的索引数据

  • 将构造索引项插入 sort_buffer 块排序

  • 将 sort_buffer 块更新到新表的索引上

  • 记录 ddl 执行过程中产生的增量(仅记录主键和新索引字段)

  • 重放 row_log 中的操作到新表索引上

  • 重放 row_log 间产生 dml 操作 append 到 row_log 最后一个 Block


commit 阶段 :

  • 当前 Block 为 row_log 最后一个时,禁止读写,升级到 EXCLUSIVE-MDL 锁

  • 重做 row_log 中最后一部分增量

  • 更新 innodb 的数据字典表

  • 增量完成


MySQL rebuild index 方式的 DDL,数据不需要通过 sever 层中转,innodb 层只需要完成变更二级索引的创建。简单示意图如下:


(3)only modify metadata

只修改元数据(.frm 文件和数据字典),不需要拷贝表的数据。

三、GH-OST

在 GH-OST 端,根据 DDL 语句创建新的表结构,根据源表的数据和增量期间增量日志,重建新表的主键索引和所有的二级索引,最终完成 DDL 增量。


主要流程如下:

  • 根据 DDL 语句和源表创建新的表结构

  • 根据唯一索引(主键索引或者其它唯一索引)

- 优先应用新增量的 binlog 到新的表中,需要经过 GH-OST 把 binlog 日志转换为 sql,然后回放到影子表

- 其次拷贝源表中的数据到新的表中,表数据拷贝通过 sql 语句 insert ignore into (select .. from)直接在 MySQL 实例上执行,无需经过 GH-OST 中转

  • 数据拷贝完成并应用完 binlog 后,通过 lock table write 锁住源表

  • 应用数据完成-获取到锁期间产生的增量 binlog

  • delete 源表,rename 影子表为源表,完成数据增量


GH-OST 进行 DDL 变更,GH-OST 服务通知 server 层,server 层作为中转把从 innodb 读取数据表,在把数据写到 innodb 层影子表。并且 GH-OST 作为中转读取 DDL 变更期间增量 binlog 解析成 SQL 写语句回放到影子表。简单示意图如下:

四、对比分析

DDL 变更执行时长、对磁盘的额外占用(临时数据表+binlog)、锁阻塞时长、主备延时都是执行 DDL 变更人员比较关心的问题,本章将从从执行效率、占用表空间、锁阻塞、产生 binlog 日志量、主备延时等方面对 MySQL 原生的 DDL 和 GH-OST 进行对比分析。

4.1 执行效率

(1)only modify metadata(正常小于 1S)

(2)build-index: 数据条目越多、新索引字段越大耗时越多

  • 增量日志超过 innodb_online_alter_log_max_size 造成 DDL 失败

(3)rebuild table: 数据条目越多、所有索引字段之和越大耗时越多

  • 增量日志超过 innodb_online_alter_log_max_size 造成 DDL 失败

(4)copy:数据条目越多,所有索引字段之和越大耗时越多,相对于 rebuild table,数据需要从 server 层中转,所以比 rebuild table 耗时多

(5)GH-OST :数据条目越多,所有索引字段之和越大耗时越多,

  • 相对于 copy,增量日志数据需要从 GH-OST 中转,所以比 copy 耗时多

  • 有各种限流,(主备延时,threads 超限延时…),增加耗时

  • 增量期间应用 binlog 速度如果跟不上业务产生 binlog 日志的速度,将无法完成增量

  • critical 参数还会导致主动退出,例如 thread_running

耗时:only modify metadata < build-index < build < copy < GH-OST
复制代码

4.2 占用表空间

  • 【only modify metadata】:忽略

  • 【build-index】:额外需要,新增索引字段占用的空间

  • 【rebuild-table】:额外需要约两倍的表空间

  • 【copy】:额外需要约两倍的表空间

  • 【GH-OST】 :临时表占用约两倍的表空间,另外生成影子表会产生大量的 binlog 日志会占用表空间

占用表空间: only modify metadata < build-index < build = copy < GH-OST
复制代码

4.3 锁阻塞

(1)only modify metadata

  • DDL prepare 阶段短暂的 MDL 排他锁,阻塞读写

(2)build-index table

  • DDL prepare 阶段短暂的 MDL 排他锁,阻塞读写

  • 执行阶段(主要耗时阶段),MDL SHARED_UPGRADABLE 锁,不阻塞读写

  • 执行阶段的最后会回放增量日志 row_log,两个 block 间隙和最后 block,持有源表索引的数据结构锁,会阻塞写

  • 提交阶段,MDL 锁升级为排他锁

  • 回放剩余的 row_log(执行完成致 MDL 锁升级期间新增的 row_log,持有源表索引的数据结构锁,阻塞读写)

(3)rebuild-table: 和 build-index table 一致

(4)copy

  • DDL prepare 阶段短暂的 MDL 排他锁,阻塞读写

  • 执行阶段(主要耗时阶段),阻塞写,不阻塞读

(5)GH-OST

  • 等待锁的时间也会阻塞业务

  • 进入 rename 到拿表写锁的间隙有少量的新增 binlog,后续需要持锁回放这部分日志

  • rename 表本身的耗时通常 1s 以内左右


锁阻塞时间:

only modify metadata=GH-OST < build-index table = rebuild-table  < copy(整个 DDL 期间都会阻塞业务的写)


锁阻塞分析:

MySQL DDL 在获取 MDL 排它锁和 GH-OST 获取表的的写锁,在获取锁的等待期间都会阻塞业务的读写

  • MySQL 等待锁的超时时间为 MySQL 参数 innodb_lock_wait_timeout。等待超时则失败

  • GH-OST 等待锁的时间,等待超时时间可配(默认 6 秒),等待超时次数可配

4.4 产生 binlog 日志量

【MySQL5.7 DDL】: 在 DDL 执行结束时仅向 binlog 中写入一条 DDL 语句,日志量较小。

【GH-OST】: 影子表在全量数据拷贝和增量数据应用过程中产生大量的 binlog 日志(row 模式),对于大表日志量非常大。

产生binlog日志量:MySQL5.7 DDL < GH-OST
复制代码

4.5 主备延时分析

(1)MySQL5.7 DDL:MySQL 集群主备环境

  • Master 上 DDL 执行完成,binlog 提交后,slave 才开始进行 DDL。

  • slave 串行复制、group 复制模式,需要等前面的 DDL 回放完成后才会进行后续 binlog 回放,主备延时至少是 DDL 回放的时间。


(2)GH-OST:主备复制延时基本可以忽略

  • GH-OST 在 master 上创建一个影子表,在执行数据拷贝和 binlog 应用阶段,GHO 表的 binlog 会实时同步到备。

  • 影子表(_GHO 表)应用完成后,通过 rename 实现新表切换,这个 rename 动作也会通过 binlog 传到 salve 执行完成 DDL。


延时时间:GH-OST < MySQL DDL

备库执行 DDL 期间主库异常,主备切换。备库升级为主过程中,要回放完 relaylog 中的 DDL 和 dml,才能对外服务,否则会出现数据丢失,这将造成业务较长时间的阻塞。

4.6 总结


GH-OST 工具和 MySQL 原生 DDL 工具的适用场景不同,具体使用哪种工具需要根据实际需求进行选择。

  • 变更人员无法判断本次 DDL 是否会造成 DML 阻塞、锁阻塞等,建议使用 GH-OST 工具。

  • 如果需要进行在线表结构变更,并且需要减少锁阻塞时间、减少主备延时等问题,建议使用 GH-OST 工具。

  • 变更只涉及到元数据的修改,建议使用 mysql 原生 DDL。

  • 如果表结构变更较小,对锁阻塞时间和主备延时要求不高,建议使用 MySQL 原生 DDL 工具。


参考资料:

发布于: 刚刚阅读数: 5
用户头像

官方公众号:vivo互联网技术,ID:vivoVMIC 2020-07-10 加入

分享 vivo 互联网技术干货与沙龙活动,推荐最新行业动态与热门会议。

评论

发布
暂无评论
MySQL 5.7 DDL 与 GH-OST 对比分析_MySQL_vivo互联网技术_InfoQ写作社区