天天看点

RDS for MySQL Online DDL 使用RDS for MySQL Online DDL 使用

<a href="#1">online ddl 的限制</a>

<a href="#2">online ddl 建议的选项</a>

<a href="#3">异常处理</a>

rds for mysql 5.6、5.7 版本支持 online ddl 特性。

online ddl 功能允许在表上执行 ddl 的操作(比如创建索引)的同时不阻塞并发的 dml (insert、update、delete、replace)操作 和 查询(select)操作。

注:

从 rds for mysql 5.5 升级到 rds for mysql 5.6,第一次执行 ddl 时有可能会因为表数据的文件格式仍旧是 5.5 版本而不支持 online ddl 特性。这种情况可以通过执行下面的命令来转换下:

#

操作

in-place?

rebuilds

table?

并发

dml?

仅修改

元数据?

注释

1

添加二级索引

支持

不需要

允许

2

删除索引

仅修改表元数据 metadata

3

重命名索引 (5.7)

4

添加全文索引

不允许

第一个全文索引需要通过 table copy 的方式创建;其后的全文索引可以通过 in-place 方式创建

5

添加空间索引 (5.7)

6

修改索引类型

7

添加主键

需要

仅当 sql_mode 参数设置包含 strict_trans_tables 或 strict_all_tables 才支持 algorithm=inplace

如果涉及的列需要转换为 not null,则不支持 algorithm=inplace

8

删除主键

不支持

9

删除主键并添加新主键

仅当在同一个 alter table 语句中(删除主键的 ddl语句)添加新主键才支持 algorithm=inplace

因为实质上需要重新组织数据,因此开销高昂

10

添加列

在添加 auto_increment 自增列时,是不允许并发 dml 操作的

11

删除列

12

重命名列

如果仅修改字段名称,不修改字段类型,则支持并发 dml 操作

13

修改列顺序

14

设置列默认值

仅修改表云数据 metadata

15

修改列数据类型

仅支持 algorithm=copy

16

增加 varchar 类型字段长度 (5.7)

仅在存储字段长度所需的字节数不变的情况下支持 algorithm=inplace,0 - 255 字节需要 1 个字节保存长度,256 字节及以上需要 2 个字节保存长度

17

删除列默认值

18

修改自增列值

仅修改内存中的保存值

19

设置列为空值 null

20

设置列不为空值 not null

仅当 sql_mode 参数设置包含 strict_trans_tables 或 strict_all_tables 才支持 algorithm=inplace;

如果列值中包含空值 null,则该 ddl 操作会失败

21

修改 enum 或 set 列定义

如果增加的元素导致存储长度变化,会需要 table copy

22

添加一个 stored 列 (5.7)

generated column

23

修改 stored 列顺序 (5.7)

24

删除 stored 列 (5.7)

25

添加一个 virtual 列 (5.7)

分区表不支持 inplace 方式

不能和其他 ddl 一起执行

26

修改 virtual 列顺序 (5.7)

27

删除 virtual 列 (5.7)

28

添加外键约束

必须 set foreign_key_checks=0; 关闭 foreign_key_checks,来支持 inplace 方式

29

删除外键约束

foreign_key_checks 选项开启或者关闭都可以

30

修改 row_format

31

修改 key_block_size

32

设置表的 persistent statistics 选项

仅修改表的元数据 metadata

33

指定表字符集

如果新的字符集编码不同,需要重建表

34

转换表字符集

35

optimize table

如果表上创建有全文索引,则不支持 inplace 方式;

optimize 语句不支持指定 algorithm 和 lock 选项

36

带 force 选项重建表

如果表上有全文索引,则不支持 algorithm=inplace 选项;

alter table table_name force, algorithm=inplace, lock=none

37

重建表

alter table table_name engine=innodb, algorithm=inplace, lock=none

38

重命名表

仅修改表的元数据 metadata;

表名修改后不保留对该表的特殊赋权,必须重新赋权

in-place?:对应 ddl语句的 algorithm 选项,通过 inplace 方式执行 ddl。相比表拷贝方式,可以减少空间和 i/o 消耗。

允许并发 dml?:对应 ddl语句的 lock 选项,ddl 执行期间是否支持并发 dml 操作。

仅修改元数据?:ddl 语句执行期间是否仅修改存储在表的 .frm 文件中的元数据信息。

inplace 和 copy table 是相反的 2 种处理方式;但即使 ddl 支持 inplace 选项,某些操作在整个执行过程中也会部分涉及到表拷贝。

algorithm=inplace :为了避免表拷贝导致的实例性能问题(空间、i/o问题),建议在 ddl 中包含该选项。如果 ddl 操作不支持 algorithm=inplace 方式,ddl 操作会立刻返回错误。

lock=none :为了在 ddl 操作过程中不影响业务的 dml 操作,建议在 ddl 中包含该选项。如果 ddl 操作不支持 lock=none (允许并行 dml 操作)选项,ddl  操作会立刻返回错误。

默认情况下 rds for mysql 会尽量使用 algorithm=inplace , lock=none 来进行 ddl 操作。因此默认可以不指定这两个选项。

但如果担心 ddl 操作对系统负载有影响或阻塞对目标表的 dml 操作,建议使用 algorithm=inplace ,和 lock=none 选项来操作;这样如果系统对某一个选项不支持,会立刻返回错误,避免影响业务。

所有的 ddl 操作均建议在 业务低峰期 进行,避免对业务产生影响。

对不支持 online ddl 的操作(比如 rds for mysql 5.5),可以考虑通过 percona 的 schema online change 工具来操作。

a.  在对某些大表的 online ddl 过程中,有时会碰到下面的错误:

原因:

在进行 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 操作末尾阶段的锁定表应用日志中的数据的过程持续更长的时间。

参数名称

默认值

最小值

最大值

作用

innodb_online_alter_log_max_size

134217728

2147483647

online ddl 存储并发 dml 信息的日志文件尺寸最大值,单位字节。默认值 128 mb,最大值 2047 mb。

解决:

在 rds 控制台 

RDS for MySQL Online DDL 使用RDS for MySQL Online DDL 使用

 参数设置调高 innodb_online_alter_log_max_size 参数设置。

RDS for MySQL Online DDL 使用RDS for MySQL Online DDL 使用

b.  在对大表的 online ddl 过程中,有时会碰到下面的错误:

同时,由于 ddl 增量期间的数据被保存在日志文件中,在此过程中会临时忽略掉一致性检查,因此合并数据时候有可能会碰到 duplicate entry 重复数据的错误。

对该错误只能通过重试来完成 ddl 的执行。