济南市城市建设规划局官方网站,长沙建网,中考管理系统登录网站,企业网站模板科技感文章目录 1. MDL 锁介绍2. 变更工具3. gh-ost 原理解析4. 安装部署5. 操作演示5.1. 重点参数介绍5.2. 执行变更5.3. 动态控制 6. 风险提示 1. MDL 锁介绍
MySQL 的锁可以分为四类#xff1a;MDL 锁、表锁、行锁、GAP 锁#xff0c;其中除了 MDL 锁是在 Server 层加的之外MDL 锁、表锁、行锁、GAP 锁其中除了 MDL 锁是在 Server 层加的之外其它三种都是在 InnoDB 层加的。
下面主要介绍一下MDL 元数据锁主要作用就是维护 DDL 过程中数据的安全性 正确性。 当对一个表进行 DML 时需要加 MDL 读锁当需要对一张表结构进行变更时需要加 MDL 写锁。读锁之间不互斥即可以多个线程对一张表进行并发增删改。读写锁与写锁之间是互斥的用来保证变更表结构操作的安全性。因此如果有两个线程要同时给一个表加字段其中一个要等另一个执行完才能开始执行。 读操作与写操作都需要加 MDL 读锁而 DDL 需要加 MDL 写锁两者互斥的那么 Online DDL 如何保障并发 DML 呢这里就要介绍 Online DDL 加锁过程
首先在开始进行 DDL 时需要拿到对应表的 MDL 写锁然后进行一系列的准备工作然后MDL 写锁降级为MDL 读锁开始真正的 DDL最后再次将 MDL 读锁升级为 MDL 写锁完成 DDL 操作释放 MDL 锁
其中第二阶段占用了 DDL 整个过程的大量时间在这段时间 DDL 才是真正的 online。
那么问题来了如果有一个大查询持有 MDL 读锁那么我们此时进行一个 DDL 操作后因为查询持有读锁DDL 需要加写锁所以变更必须等待查询结束才能进行此时再进行一个查询就会被卡住请看案例
Session 1Session 2Session 3Session 4begin;select * from sbtest1 limit 5;select * from sbtest1 limit 5;alter table sbtest1 drop column fantasy ,ALGORITHMINPLACE, LOCKNONE; ❌select * from sbtest1 limit 5; ❌
Session 1 开启一个事物执行一条查询此时 Session 1 持有 MDL 读锁 Session 2 也执行一条查询执行一条查询正常执行 Session 3 执行一条 DDL 语句因为需要 MDL 写锁被 Session 1 读锁 Session 4 执行一条查询因为需要读锁但是因为 Session 3 也处于等待状态后面的增删改查都处于等待状态。也就是说这张表完全不可用了。
即使变更支持 online 也非常怕长事务所以生产环境大表变更前我们可以去查会话和 innodb_trx 表确认没有长事务避免变更被 MDL 读锁堵塞。
看了上面的案例我们理想状态下 DDL 变更是什么样的呢
DDL 请求锁时可以带上超时时间即使拿不到锁也不会影响后面的业务语句对数据库的性能影响最小大表变更非常令人头疼不会给从库带来太大延迟大表变更后从库往往延迟十几分钟读写分离的话会影响业务查询
2. 变更工具
MySQL 变更比较常用的工具有大名鼎鼎的 PT 生态 **percona pt-online-schema-change **和 Facebook OSC 但是它们都是基于触发器来实现的简单来讲就是通过数据库的触发器把作用在源表的操作在一个事务内同步到修改后的表中这在业务高峰期时会极大的加重主库的负载。
gh-ost 是由 Github 开发的一款开源 **online DDL **工具使用订阅和过滤 binlog 的方式代替原来的触发器来做的增量数据同步这样可以降低主库负载异步的执行。
使用 gh-ost 的优点
不会给主库带来太大负载不会导致从库有太大延迟大表变更经常会导致从库延迟很长时间支持阿里云 RDS为云上变更提供了参数 –aliyun-rds通过 binlog 实现增量数据的获取基本做到了原子性的切换可暂停动态控制限流切换时间可设定
限制
使用 DTS 做数据同步可能会导致同步失败仅影响同步对象不能对外键关系及触发器的表进行 online DDL若有同名但是字母大小写不同的表则无法进行修改表必须有主键或者唯一索引
3. gh-ost 原理解析
官方图解 (https://github.com/github/gh-ost) 主要执行过程
检查是否有外键触发器及主键信息检查是否主库或从库是否开启 log_slave_updates 以及 binlog 信息检查 gho 和 ghc 结尾的临时表是否存在创建 ghc 结尾的表存数据迁移的信息以及 binlog 信息等初始化 stream 的连接添加 binlog 的监听根据 alter 语句创建 gho 结尾的幽灵表开启迁移数据按照主键把源表数据写入到 gho 结尾的表上以及 binlog apply进入 cut-over 阶段锁住主库的源表等待 binlog 应用完毕然后替换 gh-ost 表为源表清理 ghc 表删除 socket 文件。
cut-over 即表 rename 阶段gh-ost 利用了 MySQL 的一个特性原子性的 rename 请求在所有被 blocked 的请求中rename 优先级永远是最高的。gh-ost 基于此设计了该方案一个连接对原表加锁另启一个连接尝试 rename 操作此时会被阻塞住当释放 lock 的时候rename 会首先被执行其他被阻塞的请求会继续应用到新表。
4. 安装部署
下载地址 gh-ost GA 版本存档 二进制包开箱即用。 5. 操作演示
5.1. 重点参数介绍
下面介绍 gh-ost 中重点参数
-execute如果不添加该参数仅进行环境检查测试不实际执行-assume-rbr如果用户没有 Super 权限的话需要加上这个参数这样 gh-ost 会认为 Binlog 本身就是 row 模式不会再去修改-max-load可以添加一些 MySQL 状态变量阈值例如--max-loadThreads_running20则表示 MySQL 线程数大于 20 则进入限流模式使用逗号分隔指定多个状态变量-critical-load可以添加一些 MySQL 状态变量阈值如果超过阈值则暂停工作等待一段时间后重试由 -critical-load-interval-milli来设置等待多少毫秒-chunk-size在每次迭代中处理的行数量(允许范围100-100000)默认值为1000-dml-batch-size在单个事务中应用 DML 事件的批量大小范围1-100默认值为10-cut-over-lock-timeout-secondsgh-ost 在 cut-over 阶段最大锁等待时间当锁超时时gh-ost 的 cut-over 将重试-discard-foreign-keys有风险加上该参数表如果与外键约束幽灵表迁移后不会保留如果是故意想清理外键时比较有用-exact-rowcount如果指定该参数则使用 select count() 来统计表行数不添加则是使用 explain 来预估行数会影响进度统计大表 count() 还是有风险的建议不使用-heartbeat-interval-millis多久写入一条日志信息默认 100 毫秒-ok-to-drop-table切换完成后是否删除源表drop 大表是一个非常消耗 IO 的操作默认切换后不删除源表如果需要切换完成立即删除源表需要指定该参数-timestamp-old-table在旧表名中使用时间戳。这会使旧表名称具有唯一且无冲突的交叉迁移-postpone-cut-over-flag-file创建 flag 文件表迁移完成后进入等待 cut-over 阶段直到该文件被删除通过该参数用户可以自定义切换时间-panic-flag-file当该文件被创建则立即停止 gh-ost 工作直接停止不会进行环境清理-throttle-additional-flag-file当该文件被创建则暂停 gh-ost 工作-aliyun-rds阿里云 RDS 使用需要添加该参数可以绕开非法字符校验正常使用。
上面就是常用的参数用户可以控制切换时间锁超时时间以及迁移阶段对源库的负载。除此之外 gh-ost 还有三种操作模式
连接到从库在主库做迁移直接在主库上面操作在从库上修改和测试 大部分变更我们使用 connect to master 模式其它两种模式感兴趣详细可以参数官方文档在此不详细介绍https://github.com/github/gh-ost/blob/master/doc/cheatsheet.md
5.2. 执行变更
/myinstall/gh-ost \
--max-loadThreads_running30 \
--critical-loadThreads_running50 \
--critical-load-interval-millis5000 \
--chunk-size1000 \
--dml-batch-size10 \
--usercooh \
--password112233 \
--host127.0.0.1 \
--port3306 \
--databasesbtest \
--tablesbtest1 \
--alterengineinnodb \
--verbose \
--assume-rbr \
--cut-overdefault \
--cut-over-lock-timeout-seconds1 \
--allow-on-master \
--concurrent-rowcount \
--default-retries30 \
--heartbeat-interval-millis2000 \
--panic-flag-file/myinstall/ddl_room/ghost.panic.flag \
--postpone-cut-over-flag-file/myinstall/ddl_room/ghost.postpone.flag \
--serve-socket-file/myinstall/ddl_room/ghost.sock \
--throttle-additional-flag-file/myinstall/ddl_room/ghost.pause.flag \
--timestamp-old-table \
--execute 21 | tee /myinstall/ddl_room/ddl_sbtest1.log # 参数解释和设置技巧
# 这两个参数可以先使用 show status like Threads_running;
# 了解平时数据库线程数再进行设置。
--max-loadThreads_running30
--critical-loadThreads_running50
# 下面两个参数影响着迁移阶段的速度如果开启后观察数据库压力较大可以动态调整
--chunk-size1000
--dml-batch-size10
# 各种操作在 panick 前重试次数默认 60 次
--default-retries
# 切换完成后给源表添加时间戳如果不要求切换完立即删除源表建议使用该参数
--timestamp-old-table
# 切换完后立即删除源表如果表比较大会影响 IO 可根据实际情况设定
--ok-to-drop-table
# 设置暂停、退出 flag 的文件位置建议为 gh-ost 创建一个单独文件夹存放 flag 文件
--panic-flag-file
--throttle-additional-flag-file
--serve-socket-file
# 如果想自定义切换时间可以使用该参数常被嵌入自动化 DDL 平台
--postpone-cut-over-flag-file
# 步骤失败后重试次数例如切换获取锁尝试多少次如果一直没有则终止任务
--default-retries执行 DDL 操作过程中会自动创建两个中间状态表 _gho 幽灵表也就是目标表_ghc 是记录 gh-ost 执行状态的表
coohmysql 16:36: [sbtest]show tables;
------------------
| Tables_in_sbtest |
------------------
| _sbtest1_ghc |
| _sbtest1_gho |
| sbtest1 |
------------------
3 rows in set (0.00 sec)可以通过查询 _ghc 表来查询变更进度和状态
coohmysql 16:40: [sbtest]select * from _sbtest1_ghc order by id desc limit 1\G
*************************** 1. row ***************************id: 344
last_update: 2022-04-06 16:40:12hint: copy iteration 100 at 1649234412value: Copy: 99999/99999 100.0%; Applied: 0; Backlog: 0/1000; Time: 4m0s(total), 2s(copy); streamer: mysql-bin.000005:344430440; Lag: 0.99s, HeartbeatLag: 1.00s, State: postponing cut-over; ETA: due
1 row in set (0.00 sec)Copy: 99999/99999 100.0%; 需要迁移 99999 行目前已迁移 99999 行进度 100.0%
Applied: 0指在二进制日志中处理的 event 数量。在上面的例子中迁移表没有流量因此没有被处理日志 event。
Backlog: 0/1000表示我们在读取二进制日志方面表现良好在二进制日志队列中没有任何积压Backlog事件。
Backlog: 7/1000当复制行时在二进制日志中积压了一些事件并且需要应用。
Backlog: 1000/1000表示我们的 1000 个事件的缓冲区已满程序写死的 1000 个事件缓冲区低版本是 100 个此时就注意 binlog 写入量非常大gh-ost 处理不过来 event 了可能需要暂停 binlog 读取需要优先应用缓冲区的事件。
State: 目前 gh-ost 的状态
streamer: mysql-bin.000005:344430440; 表示当前已经应用到 binlog 文件位置此时状态为postponing cut-over 即等待切换因为我们使用 --postpone-cut-over-flag-file/myinstall/ddl_room/ghost.postpone.flag 参数由用户来控制切换时间如果我们不删除 ghost.postpone.flag 就会一直处理等待状态我们删除后
Copy: 99999/99999 100.0%; Applied: 0; Backlog: 0/1000; Time: 8m38s(total), 2s(copy); streamer: mysql-bin.000005:344547927; Lag: 0.99s, HeartbeatLag: 0.04s, State: migrating; ETA: due
2022-04-06 16:44:50 INFO Setting RENAME timeout as 1 seconds
2022-04-06 16:44:50 INFO Session renaming tables is 200
2022-04-06 16:44:50 INFO Issuing and expecting this to block: rename /* gh-ost */ table sbtest.sbtest1 to sbtest._sbtest1_20220406163612_del, sbtest._sbtest1_gho to sbtest.sbtest1
2022-04-06 16:44:50 INFO Found atomic RENAME to be blocking, as expected. Double checking the lock is still in place (though I dont strictly have to)
2022-04-06 16:44:50 INFO Checking session lock: gh-ost.194.lock
2022-04-06 16:44:50 INFO Connection holding lock on original table still exists
2022-04-06 16:44:50 INFO Will now proceed to drop magic table and unlock tables
2022-04-06 16:44:50 INFO Dropping magic cut-over table
2022-04-06 16:44:50 INFO Releasing lock from sbtest.sbtest1, sbtest._sbtest1_20220406163612_del
2022-04-06 16:44:50 INFO Tables unlocked
2022-04-06 16:44:50 INFO Tables renamed
2022-04-06 16:44:50 INFO Lock rename duration: 1.006946435s. During this time, queries on sbtest1 were blocked
[2022/04/06 16:44:50] [info] binlogsyncer.go:164 syncer is closing...
2022-04-06 16:44:51 INFO Closed streamer connection. errnil
2022-04-06 16:44:51 INFO Dropping table sbtest._sbtest1_ghc
[2022/04/06 16:44:51] [error] binlogsyncer.go:631 connection was bad
[2022/04/06 16:44:51] [error] binlogstreamer.go:77 close sync with err: Sync was closed
[2022/04/06 16:44:51] [info] binlogsyncer.go:179 syncer is closed
2022-04-06 16:44:51 INFO Table dropped
2022-04-06 16:44:51 INFO Am not dropping old table because I want this operation to be as live as possible. If you insist I should do it, please add --ok-to-drop-table next time. But I prefer you do not. To drop the old table, issue:
2022-04-06 16:44:51 INFO -- drop table sbtest._sbtest1_20220406163612_del
2022-04-06 16:44:51 INFO Done migrating sbtest.sbtest1
2022-04-06 16:44:51 INFO Removing socket file: /myinstall/ddl_room/ghost.sock
2022-04-06 16:44:51 INFO Tearing down inspector
2022-04-06 16:44:51 INFO Tearing down applier
2022-04-06 16:44:51 INFO Tearing down streamer
2022-04-06 16:44:51 INFO Tearing down throttler
# Done表示 gh-ost 已执行完成ghc 表已清理源表名改为_sbtest1_20220406163612_del
coohmysql 16:46: [sbtest]show tables;
-----------------------------
| Tables_in_sbtest |
-----------------------------
| _sbtest1_20220406163612_del |
| sbtest1 |
-----------------------------
2 rows in set (0.00 sec)5.3. 动态控制
用户可以通过操作特定的文件对正在执行的 gh-ost 进行动态控制请看下面案例 停止 gh-ost 指定 --panic-flag-file/myinstall/ddl_room/ghost.panic.flag 参数我们通过创建 ghost.panic.flag 来终止 gh-ost。 touch /myinstall/ddl_room/ghost.panic.flag 会输出下面日志 2022-04-06 16:56:14 FATAL Found panic-file /myinstall/ddl_room/ghost.panic.flag. Aborting without cleanup 暂停/开始 指定 --throttle-additional-flag-file/myinstall/ddl_room/ghost.pause.flag 参数我们可以通过创建删除 ghost.pause.flag 文件来控制。 开启限流 echo throttle | socat - /myinstall/ddl_room/ghost.sock关闭限流 echo no-throttle | socat - /myinstall/ddl_room/ghost.sock动态修改参数 echo chunk-size1024 | socat - /myinstall/ddl_room/ghost.sock
echo max-lag-millis100 | socat - /myinstall/ddl_room/ghost.sock
echo max-loadThread_running23 | socat - /myinstall/ddl_room/ghost.sock6. 风险提示 gh-ost 执行 cut-over 阶段会短暂堵塞变更表的读写操作可以选择业务低峰期进行切换操作减少对业务影响。 大表变更前需要注意 存储空间是否充裕可以通过 SQL 查询元数据。 SELECT TABLE_NAME,round(SUM(data_length index_length) / 1024 / 1024, 2) AS TOTAL_MB,round(SUM(data_length) / 1024 / 1024, 2) AS DATA_MB,round(SUM(index_length) / 1024 / 1024, 2) AS INDEX_MB
FROM INFORMATION_SCHEMA.tables
WHERE TABLE_SCHEMA sbtest and TABLE_NAME sbtest1
GROUP BY TABLE_NAME;-- 输出结果
-----------------------------------------
| TABLE_NAME | TOTAL_MB | DATA_MB | INDEX_MB |
-----------------------------------------
| sbtest1 | 24.06 | 21.55 | 2.52 |
-----------------------------------------大表变更周期长如果一直获取不到锁可能会变更失败若变更失败不会影响现有的业务但表结构变更需要重新开始因此建议线上表仅保留线上热点活跃数据历史数据及时归档将表保持在一种健康状态。 添加唯一索引时会造成索引字段重复数据丢失如果使用 MySQL 原生 DDL 添加唯一索引如果有重复值会报错 [23000][1062] Duplicate entry 因为 gh-ost 在数据迁移阶段使用的是 insert ignore所以不会报错但是索引字段的重复条目会丢失这点需要确认风险。
