RDS for MySQL 大表操作

1. 名词解释

2. 实现原理

3. 推荐方法

RDS for MySQL 的大表操作（比如 空间回收、添加字段、调整索引）一直是比较困扰 RDS for MySQL 用户的问题。

近期相关咨询和 反馈的问题（比如 主实例上执行 optimize table 导致只读实例高延迟）比较多，下面汇总说明下操作的几种方法及其各自的特点。

1. 名词解释

2. 实现原理

2.1 Online DDL

Online DDL 主要思路是将 DDL 执行过程中的 DML 增量数据保存在独立的日志文件中，在完成全量数据后进行数据回放 来支持并发 DML （仍旧存在某些操作不支持并发 DML）。

由此带来了几个问题：

• 大表 DDL 操作执行时间长，如果 DML 增量数据过多，会导致存放增量数据的日志空间不够，引发 DDL 操作失败。

• DML 增量数据存储在独立日志中，如果表上有唯一键约束，DDL 执行过程中会 忽略 唯一性约束，会有一定概率在重放增量数据时由于 duplicated key 错误而导致 DDL 操作失败。

• 不是全部操作都支持并发 DML。

• DDL 一旦开始执行无法暂停，并且无法控制完成时间点。
• 大表 DDL 操作执行时间长，完成后复制到只读实例上执行，会导致只读实例复制延迟。
• 在 DDL 开始和结束部分都需要获取表 Metadata Lock 的 排他锁，容易引发表 MDL 锁等待进而导致表不可访问。

2.2 Percona Toolkit - Online Schema Change

PT-OSC 的主要思路是 创建目标结构的新表，将原表中数据拷贝到新表中，同时通过原表上的 Insert、Update 和 Delete 触发器（Trigger）同步拷贝过程中的增量数据；数据同步后 rename 新表为原表。

由于是基于 Trigger 的实现，Trigger 绑定在并发 DML 操作的事务中执行，由此带来一些问题。

• 速度慢，Trigger 是基于 行（per-row）变化来解释执行（每行变化都要解释执行一次，无法预编译，无法批量执行）。

• 不支持原表上存在 Trigger。

• 原表必须定义主键 或 唯一键。

• Trigger 对被绑定的事务引入 新表 上的锁竞争。
• Rename 表 和 删除表上创建的 Trigger 都需要获取表 Metadata Lock 的 排他锁，容易引发表 MDL 锁等待进而导致表不可访问。

2.3 GH - Online Schema Transfer 

GH-OST 结合 RDS for MySQL 使用的主要思路是创建目标结构的新表，然后通过将自身模拟为一个 Slave 实例，获取表上的增量数据并将之再应用到新表上；规避了使用 Trigger 带来的问题。

Step 1、RDS for MySQL 主实例上持续存在 对 原表 old_table 的 DML 操作。

Step2、GH-OST 客户端连接 RDS for MySQL 主实例。

Step 3、根据 DDL 创建影子表 ghost_table。

Step 4、GH-OST 客户端将自身注册为一个 Slave 实例，获取 RDS for MySQL 主实例的增量 binlog 信息（要求登录账户有 replication slave，replication client 权限）。

Step 5、并发拷贝 表 old_table 中的全量 和 增量数据（来自 binlog）到影子表 ghost_table。

Step 6、当用户触发切换，阻止原表 old_table 上的 DML 操作。

Step 7、等待影子表 ghost_table 和 原表 old_table 数据一致。

Step 8、切换（rename）影子表 ghost_table 为 原表 old_table。

2.4 方法对比 

下面小节下各个工具的优缺点：

RDS for MySQL Online DDL 使用
RDS for MySQL 表上 Metadata Lock 的产生和处理
MySQL 5.7 Online DDL Operations
MySQL 5.6 Online DDL Operations
MySQL 5.7 Online DDL Limitations
MySQL 5.6 Online DDL Limitations

RDS for MySQL 如何使用 Percona Toolkit
Percona Toolkit Documentation

gh-ost: GitHub's online schema migration tool for MySQL
GH-OST Requirements and limitations

MySQL · 社区动态 · Online DDL 工具 gh-ost 支持阿里云 RDS

3. 推荐方法

对于不会导致锁表的操作，并且能够支持只读实例延迟或不使用只读实例一定时间的业务，可以考虑直接使用 Online DDL。

对于要批量执行的低风险操作，可以考虑直接使用 Online DDL。

对于有只读实例并且对延迟敏感的一个或几个大表操作，建议考虑 GH-OST。

3.1 Online DDL

需要注意的地方：

• 调整 Innodb_online_alter_log_max_size 到最大值避免增量日志空间问题。

• alter 语句带 algorithm=inplace, lock=none 选项执行，避免自动降级锁表。

• 正式执行前创建带部分数据的同结构测试表，估算空闲空间使用情况和是否能支持并发 DML （DDL 结束后返回 N rows affected；如果 N = 0 则证明采用 inplace 方式执行）。

3.2 GH-OST

样例一  - 直接连接主实例收缩表：

gh-ost \
--aliyun-rds="true" \
--critical-load=Threads_running=100 \
--max-load=Threads_running=70 \
--chunk-size=1000 \
--assume-master-host="jacky01.mysql.rds.aliyuncs.com:3306" \
--assume-rbr \
--initially-drop-old-table \
--initially-drop-ghost-table \
--initially-drop-socket-file \
--ok-to-drop-table \
--host="jacky01.mysql.rds.aliyuncs.com" \
--port=3306 \
--user="jacky" \
--password="xxxx" \
--database="jacky" \
--table="ghost" \
--verbose \
--alter="engine=innodb" \
--allow-on-master \
--cut-over=default \
--default-retries=120 \
--panic-flag-file=/tmp/ghost.panic.flag \
--postpone-cut-over-flag-file=/tmp/ghost.postpone.flag \
--execute

# --chunk-size 指定每批次操作的表行数，默认 1000 行，取值范围： 100-100，000
# --initially-drop-old-table 如果存在已经存在的 OLD table（也许来自上次直接退出的 GH-OST 执行），则删除该表；不带该参数运行如果发现存在该表，则直接退出。
# --initially-drop-ghost-table 如果存在已经存在的 ghost table（也许来自上次直接退出的 GH-OST 执行），则删除该表；不带该参数运行如果发现存在该表，则直接退出。
# --initially-drop-socket-file 如果存在已经存在的 socket file（也许来自上次直接退出的 GH-OST 执行），则删除该 socket 文件；不带该参数运行如果发现存在该文件，则直接退出。
# --host 指定要操作的 RDS for MySQL 实例
# --port 指定该实例端口
# --user 指定操作使用的账户，该账户必须有 replication slave、replication client 权限
# --password 指定账户密码
# --database 指定操作的 库 名
# --table 指定要操作的表，该参数不能为空
# --alter 指定 DDL 操作
# --panic-flag-file 当这个文件被创建后，GH-OST 直接终止退出，不做任何清理操作（比如删除 ghost table）。

样例二 - 同时连接只读实例监控只读实例复制延迟

gh-ost \
--aliyun-rds="true" \
--critical-load=Threads_running=64 \
--max_load=Threads_running=32 \
--chunk-size=1000 \
--throttle-control-replicas="rr-2ze559088x3836.mysql.rds.aliyuncs.com:3306" \
--max-lag-millis=1500 \
--assume-master-host="jacky01.mysql.rds.aliyuncs.com:3306" \
--assume-rbr \
--initially-drop-old-table \
--initially-drop-ghost-table \
--initially-drop-socket-file \
--ok-to-drop-table \
--host="jacky01.mysql.rds.aliyuncs.com" \
--port=3306 \
--user="jacky" \
--password="xxxx" \
--database="jacky" \
--table="ghost" \
--verbose \
--alter="engine=innodb" \
--allow-on-master \
--cut-over=default \
--default-retries=120 \
--panic-flag-file=/tmp/ghost.panic.flag \
--postpone-cut-over-flag-file=/tmp/ghost.postpone.flag \
--execute