AWS RDS MySQL执行ADD COLUMN操作耗时过长问题咨询

问题1：额外写入来源及可关闭项 #

额外写入主要来源 #

该场景下15倍左右的写入放大属于正常现象，额外写入主要来自以下几部分：

• InnoDB事务日志写入：包括Redo Log和Undo Log，拷贝全表生成临时表的过程属于大事务，所有数据页变更都会记录Redo Log，Undo Log用于事务回滚，同时Undo Log本身也会产生Redo Log，这部分至少带来1~2倍的写入放大。
• Binlog日志写入：RDS默认开启Binlog用于高可用同步和备份，行模式下全量记录所有行变更内容，写入量等同于新表的全量数据，带来1倍左右的写入放大。
• 双写缓冲写入：MySQL默认开启InnoDB双写缓冲防止数据页断裂，所有数据页写入前会先写入双写缓冲区域再写入实际数据页，直接带来2倍的写入放大。
• 外键约束校验开销：DDL中同时添加外键，创建外键过程中需要逐行校验新列和关联表主键的匹配性，校验过程产生的临时数据、元数据变更也会带来额外写入。
• RDS底层额外开销：包括RDS监控指标采集、存储多副本同步等底层固有开销，也会占用部分写入带宽。

可关闭的额外写入项 #

以下配置可以临时关闭，操作完成后需要恢复原有配置避免影响业务稳定性：

• 会话级关闭Binlog：执行DDL前在当前会话执行SET SESSION sql_log_bin = 0;，当前会话的DDL操作不会写入Binlog，注意操作完成后需手动触发一次全量备份，保证数据可恢复。
• 临时关闭双写缓冲：如果使用MySQL 8.0及以上版本，可动态执行SET GLOBAL innodb_doublewrite = 0;关闭双写缓冲，DDL完成后再改回原值，MySQL 5.7版本需要重启实例才能修改，不建议调整。
• 移除DDL中的外键创建逻辑：外键校验开销极高，可以先完成加列操作，再单独创建外键，也可以直接在业务层实现外键约束，取消数据库级外键。

问题2：加列操作加速方案 #

可以根据MySQL版本和业务容忍度选择以下方案：

1. 优先使用INSTANT在线DDL：如果MySQL版本为8.0.12及以上，加列操作满足「加在表末尾、列非生成列、默认值为常量」三个条件时，可以指定ALGORITHM=INSTANT执行，不需要拷贝全表，毫秒级即可完成，语句示例：

ALTER TABLE MY_TABLE
ADD COLUMN NEW_COLUMN_1 DECIMAL(39, 30) NULL,
ADD COLUMN NEW_COLUMN_2  DECIMAL(39, 30) NULL,
ADD COLUMN NEW_COLUMN_3 INT(10) UNSIGNED NULL,
ADD COLUMN NEW_COLUMN_4 DATE NULL,
ALGORITHM=INSTANT, LOCK=NONE;

外键创建不支持INSTANT，可在加列完成后单独执行。
2. 提升存储IO能力：当前使用的1000 IOPS预配置SSD存储的吞吐量上限约为46MB/s，实际已接近存储性能上限，可临时将存储预配置IOPS上调到5000~10000，DDL完成后再调回原值，RDS调整IOPS为在线操作无需重启，性能提升效果最明显。
3. 调整InnoDB参数提升IO效率：DDL执行前临时调整以下参数，完成后恢复原有配置：

• 将innodb_buffer_pool_size调整为实例内存的70%（db.m5.xlarge为16G内存，可调整为11G），缓存更多数据减少磁盘刷写。
• 将innodb_log_file_size调整为4G，减少Redo Log的刷盘频率。
• 将innodb_flush_log_at_trx_commit调整为2、sync_binlog调整为0，降低事务落盘频次，空闲场景下几乎无数据丢失风险。

4. 使用第三方DDL工具：如果版本不支持INSTANT DDL，可以使用pt-online-schema-change或者gh-ost工具执行加列操作，这类工具分批拷贝数据，支持自定义限速，空闲场景下可以拉满IO，比原生COPY模式DDL快2~3倍，且全程不锁表。

内容的提问来源于stack exchange，提问作者mabead