RDS for MySQL Online DDL 使用
- Online DDL 的限制
- Online DDL 建议的选项
- 异常处理
RDS for MySQL 5.6 支持 Online DDL 特性。
Online DDL (在线 DDL)功能允许在表上执行 DDL 的操作(比如创建索引)的同时不阻塞并发的 DML 操作 和 查询(select)操作。
注:
- 从低版本(比如 RDS for MySQL 5.5)升级到 RDS for MySQL 5.6,第一次执行 DDL 时有可能会因为表数据的文件格式仍旧是 5.5 版本而不支持 Online DDL 特性。这种情况可以通过执行下面的命令来转换下:
# InnoDB 引擎表
alter table tab_name engine=innodb;
1. Online DDL 的限制
# | 操作 | In-Place? | Copies Table? |
并发DML? |
并发查询? |
注释 |
1 | 创建普通索引 | 支持 | 不需要 | 允许 | 允许 | |
2 | 创建全文索引 | 支持 | 不需要 | 不允许 | 允许 | 第一个全文索引需要通过 table copy 的方式创建;其后的全文索引可以通过 in-place 方式创建。 |
3 | 删除索引 | 支持 | 不需要 | 允许 | 允许 | 仅修改表元数据 metadata。 |
4 | optimize table | 支持 | 需要 | 允许 | 允许 | 如果表上创建有全文索引,则不支持 algorithm=inplace 选项。 |
5 | 设置列默认值 | 支持 | 不需要 | 允许 | 允许 | 仅修改表云数据 metadata。 |
6 | 修改自增列值 | 支持 | 不需要 | 允许 | 允许 | 仅修改表元数据 metadata。 |
7 | 添加外键约束 | 支持 | 不需要 | 允许 | 允许 | set foreign_key_checks=0; 来关闭 foreign_key_checks,避免 table copy。 |
8 | 删除外键约束 | 支持 | 不需要 | 允许 | 允许 | foreign_key_checks 选项开启或者关闭都可以。 |
9 | 重命名列 | 支持 | 不需要 | 允许 | 允许 | 如果仅仅修改字段名称,而不要修改字段类型,是支持并发 DML 操作的。 |
10 | 添加列 | 支持 | 需要 | 允许 | 允许 |
在添加 auto_increment 自增列时,是不允许并发 DML 操作的。 尽管支持 Algorithm=INPLACE ,但因为数据实质上需要重新组织,因此操作的开销高昂。 |
11 | 删除列 | 支持 | 需要 | 允许 | 允许 | 尽管支持 Algorithm=INPLACE ,但因为数据实质上需要重新组织,因此操作的开销高昂。 |
12 | 修改各列顺序 | 支持 | 需要 | 允许 | 允许 | 尽管支持 Algorithm=INPLACE ,但因为数据实质上需要重新组织,因此操作的开销高昂。 |
13 | 修改 Row_Format 属性 | 支持 | 需要 | 允许 | 允许 | 尽管支持 Algorithm=INPLACE ,但因为数据实质上需要重新组织,因此操作的开销高昂。 |
14 | 修改 Key_Block_Size 属性 | 支持 | 需要 | 允许 | 允许 | 尽管支持 Algorithm=INPLACE ,但因为数据实质上需要重新组织,因此操作的开销高昂。 |
15 | 设置列为空值 Null | 支持 | 需要 | 允许 | 允许 | 尽管支持 Algorithm=INPLACE ,但因为数据实质上需要重新组织,因此操作的开销高昂。 |
16 | 设置列不为空值 NOT Null | 支持 | 需要 | 允许 | 允许 |
该操作需要将 SQL_MODE 参数设置为 STRICT_ALL_TABLES 或 STRICT_TRANS_TABLES 才能成功。如果列值中包含空值(NULL),则该 DDL 操作会失败。 尽管支持 Algorithm=INPLACE ,但因为数据实质上需要重新组织,因此操作的开销高昂。 |
17 | 修改列的数据类型 | 不支持 | 需要 | 不允许 | 允许 | |
18 | 添加主键 | 支持 | 需要 | 允许 | 允许 |
尽管支持 Algorithm=INPLACE ,但因为数据实质上需要重新组织,因此操作的开销高昂。 如果涉及的列需要转换为 NOT NULL,则不支持 Algorithm=INPLACE。 |
19 | 删除主键并添加新主键 | 支持 | 需要 | 允许 | 允许 |
仅当在同一个 Alter Table 语句中(删除主键的DDL语句)添加新主键才支持 Algorithm=INPLACE。 因为数据实质上需要重新组织,因此操作的开销高昂。 |
20 | 删除主键 | 不支持 | 需要 | 不允许 | 允许 | |
21 | Convert character set | 不支持 | 需要 | 不允许 | 允许 | 如果新的字符集编码不同,需要重建表。 |
22 | Specify character set | 不支持 | 需要 | 不允许 | 允许 | 如果新的字符集编码不同,需要重建表。 |
23 | 带 force 选项重建表 | 支持 | 需要 | 允许 | 允许 | 如果表上有全文索引,则不支持 Algorithm=Inplace 选项。 |
24 |
重建表 alter table ... engine=innodb |
支持 | 需要 | 允许 | 允许 | 如果表上有全文索引,则不支持 Algorithm=Inplace 选项。 |
25 |
设置表的 persistent statistics options (STATS_PERSISTENT, STATS_AUTO_RECALC STATS_SAMPLE_PAGES) |
支持 | 不需要 | 允许 | 允许 | 仅修改表的元数据 metadata。 |
- In-Place?:对应 DDL语句的 Algorithm 选项,通过 inplace 方式执行 DDL。相比表拷贝方式,可以减少空间和 I/O 消耗。
- Copies Table?:对应 DDL语句的 Algorithm 选项,通过 表拷贝 方式执行 DDL。DDL 执行期间会占用更大的磁盘空间和消耗更多的 I/O。
- 允许并发 DML?:对应 DDL语句的 Lock 选项,DDL 执行期间是否支持并发 DML 操作。
- 允许并发查询?:DDL 语句执行期间是否支持并发查询操作(通常都是支持的)。
- MySQL官方文档请参考:Online DDL 概览
- DDL 操作执行时需要修改表的元数据(metadata),有可能会遇到等待表元数据锁的情况(waiting for table metadata lock),该情况的处理方式请参考:RDS MySQL 表上 Metadata lock 的产生和处理
- Inplace 和 Copy Table 是相反的 2 种处理方式;但即使 DDL 支持 Inplace 选项,某些操作在整个执行过程中也会部分涉及到表拷贝,比如上表中的添加列操作。
2. Online DDL 建议的选项
- Algorithm=Inplace :为了避免表拷贝导致的实例性能问题(空间、I/O问题),建议在 DDL 中包含该选项。如果 DDL 操作不支持 Algorithm=Inplace 方式,DDL 操作会立刻返回错误。
-- 修改字段数据类型不支持 algorithm=inplace 选项
alter table area_bak algorithm=inplace, modify father text;
ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY.
- Lock=None :为了在 DDL 操作过程中不影响业务的 DML 操作,建议在 DDL 中包含该选项。如果 DDL 操作不支持 Lock=None (允许并行 DML 操作)选项,DDL 操作会立刻返回错误。
-- 转换字符集不支持并发 DML 操作
alter table area ALGORITHM=copy, lock=none,CONVERT TO CHARACTER SET utf8mb4;
ERROR 1846 (0A000): LOCK=NONE is not supported. Reason: COPY algorithm requires a lock. Try LOCK=SHARED.
默认情况下 RDS for MySQL 会尽量使用 algorithm=inplace , lock=none 来进行 DDL 操作。因此默认可以不指定这两个选项。
但如果担心 DDL 操作对系统负载有影响或阻塞对目标表的 DML 操作,建议使用 algorithm=inplace ,和/或lock=none 选项来操作;这样如果系统对某一个选项不支持,会立刻返回错误,避免影响业务。
所有的 DDL 操作均建议在 业务低峰期 进行,避免对业务产生影响。
--使用 algorithm=inplace, lock=none 选项成功创建索引的例子
alter table area algorithm=inplace, lock=none, add index idx_fa (father);
对不支持 Online DDL 的操作(比如RDS for MySQL 5.5),可以考虑通过 Percona 的 Schema Online Change 工具来操作。
Alter Table 语法请参考: ALTER TABLE Syntax
3. 异常处理
在对某些大表的 Online DDL 过程中,有时会碰到下面的错误:
-- 在 DML 操作频繁的 rd_order_rec 表上创建 idx_cr_time_detail 索引
alter table rd_order_rec add index idx_cr_time_detail (cr_time,detail);
ERROR 1799(HY000): Creating index 'idx_cr_time_detail' required more than 'innodb_online_alter_log_max_size' bytes of modification log. Please try again.
原因:
在进行 Online DDL(不阻塞并发 DML) 的过程中,每个被修改的表或者创建的索引都会使用一个临时日志来保存 DDL 过程中并发 DML 操作的记录。该临时日志文件的大小可以根据需要从参数 innodb_sort_buffer_size 指定的大小扩展到参数 innodb_online_alter_log_max_size 指定的大小。
如果有临时日志文件大小超过上限,则该 DDL 语句返回失败并且所有没有提交的并发 DML 操作会被回滚。因此增加 innodb_online_alter_log_max_size 参数的大小可以允许 DDL 过程中更多的并发 DML 操作,但是较大的值也会使在 DDL 操作末尾阶段的锁定表应用日志中的数据的过程持续更长的时间。
# | 参数名称 | 默认值 | 最小值 | 最大值 | 作用 |
1 | innodb_online_alter_log_max_size | 134217728 | 134217728 | 2147483647 |
Online DDL 存储并发 DML 信息的日志文件尺寸最大值。单位字节。 默认值 128 MB,最大值 2047 MB。 |
解决:
在 RDS 控制台 参数设置调高 innodb_online_alter_log_max_size 参数设置。