RDS for MySQL Online DDL 使用

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 参数设置。

 

时间: 2024-09-08 05:55:05

RDS for MySQL Online DDL 使用的相关文章

RDS for MySQL 表上 Metadata lock 的产生和处理

RDS for MySQL 表上 Metadata lock 的产生和处理 1. Metadata lock wait 出现的场景 2. Metadata lock wait 的含义 3. 导致 Metadata lock wait 等待的活动事务 4. 解决方案 5. 如何避免出现长时间 Metadata lock wait 导致表上相关查询阻塞,影响业务 1. Metadata lock wait 出现的场景 创建.删除索引 修改表结构 表维护操作(optimize table.repair

RDS for MySQL 通过分区自动归档历史数据

RDS for MySQL 通过分区归档历史数据 原始表 分区 用于分区维护的存储过程 每月调用存储过程的事件 随着数据的积累,数据量的增加,越来越多的表体积变的庞大,不但影响查询的执行时间,而且使得管理工作(比如添加删除索引)变的越发复杂和困难. 本文介绍一个通过分区滑动来归档历史数据(以便可以有效控制表的尺寸)的方法,仅供大家参考. 1. 原始表 未分区的原始表:order_history. create table order_history ( id bigint unsigned au

Generation and Handling of Metadata Locks on RDS for MySQL Tables

Generation and Handling of Metadata Locks on RDS for MySQL Tables Source Article: www.alibabacloud.com/articles/93807 Introduction ApsaraDB for RDS, also known as Alibaba Cloud ApsaraDB for RDS (Relational Database System) is an on-demand database se

RDS for MySQL查询缓存 (Query Cache) 的设置和使用

功能和适用范围 原理 限制 设置 验证效果 1. 功能和适用范围 功能: 降低 CPU 使用率 降低 IOPS 使用率(某些情况下) 减少查询响应时间,提高系统的吞吐量 适用范围: 表数据修改不频繁.数据较静态 查询(Select)重复度高 查询结果集小于 1 MB 注: 查询缓存并不一定带来性能上的提升,在某些情况下(比如查询数量大,但重复的查询很少)开启查询缓存会带来性能的下降. 2. 原理 RDS for MySQL 对来自客户端的查询(Select)进行 Hash 计算得到该查询的Has

RDS for MySQL Mysqldump 常见问题和处理

RDS for MySQL Mysqldump 常见问题和处理   GTID 特性相关 避免表级锁等待 设置导出字符集 其他导出时需要注意的选项 举例 RDS for MySQL 不支持的选项 RDS for MySQL 逻辑备份 1. GTID 特性相关 MySQL 5.6 引入了 GTID 特性,因此随 5.6 版本分发的 mysqldump 工具增加了 --set-gtid-purged 选项. # 选项名称 默认值 可选值 作用 1 set-gtid-purged AUTO ON, OF

RDS for MySQL 字符序(collation)引发的性能问题

在帮客户排查问题的时候,经常会遇到的 RDS 实例性能问题(比如 RDS 实例 CPU 使用率高),而其中有一类是由于字符集的字符排序规则不一致导致的.从处理的过程中可以看出来,这类问题比较容易出现但不容易定位排查,所以今天通过两个实战案例来分析的下"RDS for MySQL 字符序(collation)引发的性能问题". 首先介绍下背景知识: 字符集 和 字符序. 1. 字符集(character set)和字符序(collation) 字符集是一组符号和编码,用来保存和解释 My

RDS for MySQL 使用 utf8mb4 字符集存储 emoji 表情

RDS for MySQL 使用 utf8mb4 字符集存储 emoji 表情 1. 基本原则 2. 三个条件的说明 2.1 应用客户端 2.2 应用到 RDS MySQL 实例的连接 2.3 RDS 实例配置 3. 通过 set names 命令设置会话字符集 1. 基本原则 如果要实现存储 emoji 表情到 RDS for MySQL 实例,需要应用客户端.到 RDS for MySQL 实例的连接.RDS 实例内部 3 个方面统一使用或者支持 utf8mb4 字符集. 注:关于 utf8

RDS for MySQL CPU 性能问题浅析

RDS for MySQL CPU 性能问题浅析 1. 原因 1.1 应用负载高 1.2 查询执行成本高 2. 解决方法2.1 相关工具 2.2 应用负载高 2.3 查询语句执行成本高 3. 避免出现的一般原则 RDS for MySQL 实例在日常使用中,会碰到 CPU 使用率达到 100% 的情况.比如: 1. 原因 根本原因:应用提交的查询访问的 逻辑读(逻辑 IO) 总量 (需要访问的 表 数据) 过高. 大量逻辑读会导致数据缓存 Buffer Pool 中用于维护数据一致性的 Latc

RDS for MySQL InnoDB 行锁等待和锁等待超时的处理

RDS for MySQL InnoDB 行锁等待和锁等待超时的处理   1. InnoDB 引擎表行锁等待和等待超时发生的场景 2.InnoDB 引擎行锁等待情况的处理 2.1 InnoDB 行锁等待超时参数 innodb_lock_wait_timeout 2.2 大量行锁等待和行锁等待超时的处理 1. InnoDB 引擎表行锁等待和等待超时发生的场景 当一个 RDS for MySQL 连接会话等待另外一个会话持有的互斥行锁时,会发生 InnoDB 引擎表行锁等待情况. 通常情况下,持有该