作者:黄稚禹
历史上看,MySQL 在 2007 年就完成了在线索引接口的设计。而 MySQL NDB Cluster、TokuDB 都早在 5.1 版本中就支持在线索引添加,然而直到 MySQL 5.6 用户才能真正享受到该功能(虽然 facebook 团队有推出 OSC),这也导致在 MySQL5.6 出现之前(5.5 版本及之前版本),MySQL 数据库长期被吐槽的原因之一(特别是 Oracle DBA)
MySQL5.5 版本及之前版本的 DDL 实现方式:
上图不难看出,5.5 及之前版本 DDL 实现的方式存在如下问题:
虽然在 MySQL5.5 版本中增加了 IN-Place 方式,但依然会阻塞 INSERT、UPDATE、DELETE 操作
MySQL5.5 中对添加索引操作引入了新特性 Fast Index Create(FIC 特性),在 MySQL5.6 中,开始支持更多的 alter table 类型操作来避免 copy data,同时支持了在线上 DDL 的过程中不阻塞 DML 操作,真正意义上的实现了 Online DDL。
但并不是所有的 DDL 操作都支持在线操作,这里附上 MySQL 官方文档对于 DDL 操作的总结:
http://dev.mysql.com/doc/refman/5.6/en/innodb-create-index-overview.html
虽然 MySQL 5.6 支持了在线 DDL 操作,不过还是有些需要注意的问题,最为重要的就是参数 innodb_online_alter_log_max_size 的配置。在进行 DDL 操作时会保存操作时间内产生的日志,这与 facebook OSC 的临时表,但是其保存在内存中,而该内存的大小由参数 innodb_online_alter_log_max_size 定义,默认大小为 128M。如果产生的日志超出该容量大小,则会抛出类似如下的异常提示:
Error:1799SQLSTATE:HY000(ER_INNODB_ONLINE_LOG_TOO_BIG)
Message: Creating index 'idx_aaa' required more than 'innodb_online_alter_log_max_size' bytes of modification log. Please try again.
根据测试经验,128M 通常在一个活跃的线上环境中是不够的,通常 512M 是比较推荐的值。好在该参数是动态的,可以基于会话级别进行调整。另外个人的体会是,在进行在线索引添加操作时,数据库性能会有 20~30%的下降。
另一个小技巧是使用 ALTER TABLE ... LOCK=NONE 来强制使用在线 DDL 操作,若操作不支持,则会抛出异常,如:
mysql> ALTER TABLE customers DROP PRIMARY KEY ,LOCK=NONE;
ERROR 1846 (0A000): LOCK=NONE is not supported. Reason: Dropping a primary key is not allowed without also adding a new primary key. Try LOCK=SHARED.
那到了 MySQL5.7,在 5.6 的基础上又增加了以下的新特性:
ALTER TABLE t1 ALGORITHM=INPLACE, CHANGE COLUMN c1 c1 VARCHAR(255);
ALTER TABLE t1 ADD COLUMN new_column ALGORITHM=INPLACE,LOCK=DEFAULT;
ALGORITHM 子句用来指定执行 DDL 采用的方式,可取值为 DEFAULT、INPLACE、COPY
ALGORITHM = DEFAULT| INPLACE| COPY
LOCK 子句描述持有的锁类型来控制 DML 的并发,取值 DEFAULT、NONE、SHARED、EXCLUSIVE
LOCK = DEFAULT| NONE | SHARED | EXCLUSIVE
同样,也附上 MySQL 官方文档对于 DDL 操作的总结:
http://dev.mysql.com/doc/refman/5.7/en/innodb-create-index-overview.html
中文翻译如下图:
1.仍然存在排他锁,有锁等待的风险。
2.跟 5.6 一样,增量日志大小是有限制的(由 innodb_online_alter_log_max_size 参数决定大小)
3.有可能造成主从延迟
4.无法暂停,只能中断
首先简单说下 Percona Toolkit:
percona-toolkit 源自 Maatkit 和 Aspersa 工具,这两个工具是管理 mysql 的最有名的工具,但 Maatkit 已经不维护了,全部归并到 percona-toolkit。Percona Toolkit 是一组高级的命令行工具,用来管理 MySQL 和系统任务,主要包括:
1.验证主节点和复制数据的一致性 【pt-table-checksum、pt-table-sync】 2.有效的对记录行进行归档 【pt-archiver】 3.分析索引使用情况 【pt-index-usage】 4.总结 MySQL 服务器 【pt-summary】 5.从日志和 tcpdump 中分析查询 【pt-query-digest】 6.问题发生时收集重要的系统信息 【pt-stalk】 7.kill 符合条件的 mysql thread 【pt-kill】 8.在线修改表结构 【pt-online-schema-change】
这里,主要介绍在线修改表结构工具:PT-OSC(pt-online-schema-change)
该工具特点与优势:
1.支持并发 DML 操作
2.经过多年生产环境验证,可靠稳定。
1.原表不能存在触发器。
2.原表必须存在主键 PRIMARY KEY 或者 UNIQUE KEY
3.外键的处理需要指定 alter-foreign-keys-method 参数,存在风险
4.在 pt-osc 的执行过程中,如果有对主键的更新操作则会出现重复的数据,见下面链接的 bug 说明:
https://bugs.launchpad.net/percona-toolkit/+bug/1646713
pt-online-schema-change --alter="add c1 varchar(200) " \
--no-check-replication-filters --recursion-method=none \
--user=root --password=123456--host=127.0.0.1 --port=3306 \
--set-vars innodb_lock_wait_timeout=50 --critical-load="Threads_running=200" \
D=d1,t=t1--execute
这块各个参数的含义具体可以参见下官方文档,特别注意:
--critical-load
--max-load
--set-vars
这几个较少用到的参数
GH-OST(gitHub’s Online Schema Transformer),意思是:GitHub 的在线表定义转换器
GH-OST 的优势:
gh-ost 工作时可以连上多个 MySQL 实例,同时也把自己以从库的方式连上其中一个实例来获取二进制日志事件。根据你的配置、数据库集群架构和你想在哪里执行修改操作,可以有许多种不同的工作模式。
a. 连接到从库,在主库做迁移
这是 gh-ost 默认的工作方式。gh-ost 将会检查从库状态,找到集群结构中的主库并连接,接下来进行迁移操作:
如果你的主库的日志格式是 SBR,工具也可以正常工作。但从库必须启用二级制日志(log_bin, log_slave_updates) 并且设置 binlog_format=ROW ( gh-ost 是读取从库的二级制文件)。
如果直接在主库上操作,当然也需要二进制日志格式是 RBR。
b. 连接到主库
如果你没有从库,或者不想使用从库,你可以直接在主库上操作。gh-ost 将会直接在主库上进行所有操作。你需要持续关注复制延迟问题。
c. 在从库迁移/测试
该模式会在从库执行迁移操作。gh-ost 会简单的连接到主库,此后所有的操作都在从库执行,不会对主库进行任何的改动。整个操作过程中,gh-ost 将控制速度保证从库可以及时的进行数据同步
对与 insert 和 update 是没有问题的,因为无论 copy old row 和 apply binlog 的先后顺序,如果 apply binlog 在后,会覆盖掉 copy old row,如果 apply binlog 在前面,copy old row 因为使用 insert ignore,因此会被 ignore 掉;(binlog 的数据优先级是最高的)
对与 delete 数据,我们可以演算一下,abc 三个操作,可能存在三种情况(b 肯定在 a 的后面):
cut-over 阶段(即 rename 表阶段)
使用案例:
./gh-ost \
--critical-load=Threads_running=64 \
--chunk-size=1000 \
--throttle-control-replicas="test02:3306" \
--max-lag-millis=1500 \
--initially-drop-old-table \
--initially-drop-ghost-table \
--initially-drop-socket-file \
--ok-to-drop-table \
--conf="./my.cnf" \
--host="test02" \
--port=3306 \
--user="admin" \
--password="admin" \
--database="test" \
--table="test" \
--verbose \
--alter="drop index id1" \
--switch-to-rbr \
--allow-master-master \
--cut-over=default \
--default-retries=120 \
--panic-flag-file=/tmp/ghost.panic.flag \
--postpone-cut-over-flag-file=/tmp/ghost.postpone.flag \
--execute
Online DDL 的测试:
PT-OSC 的测试:
另外,实际测试中还发现,如果使用 PT-OSC 修改的表,该表的主键不是自增字段,可以获得比较高的一个性能。
但在实际生产环境中,主键几乎都是自增字段,如果在写入较大的线上环境,且同时表主键为自增字段的话,使用 PT-OSC 可能会产生大量的自增锁,lock_mode=AUTO_INC
同时,这和 innodb 的参数:innodb_autoinc_lock_mode 的配置也有关系:
这里简单介绍下:
从 MySQL5.1.22 版本开始,InnoDB 存储引擎开始提供一种轻量级互斥量的自增长实现机制,这种机制大大提高了自增长值插入的性能。并且从该版本开始,InnoDB 存储引擎提供了一个参数 innodb_autoinc_lock_mode 来控制自增长的模式,该参数的默认值为 1.
在详细了解 innodb_autoinc_lock_mode 之前,我们需要对自增长的插入进行类别划分: 1、Insert-like : insert-like 指所有的插入语句,如 INSERT、REPLACE、INSERT....SELECT、REPLACE....SELECT、LOAD DATA 等
2、Simple inserts: simple inserts 指能在插入前就确定插入行数的语句。这些语句包括 INSERT、REPLACE 等。需要注意的是:simple inserts 不包含 INSERT... ON DUPLICATE KEY UPDATE 这类 SQL 语句
3、Bulk inserts: bulk inserts 指在插入前不能确定得到插入行数的语句,如 INSERT....SELECT、REPLACE....SELECT、LOAD DATA
4、Mixed-mode inserts: mixed-mode inserts 指插入中有一部分的值是自增长的,有一部分是确定的。如 INSERT INTO t1(c1,c2) VALUES(1,'a'),(NULL,'b'),(5,'c'),(NULL,'d'); 也可以是指 INSERT...ON DUPLICATE KEY UPDATE 这类 SQL 语句
在了解了以上的分类后,我们接着来分析 innodb_autoinc_lock_mode 参数的各个设置对自增锁的影响,总共就 3 个值可以设置,即 0、1、2
1、innodb_autoinc_lock_mode = 0 这是 MySQL5.1.22 版本之前的自增长实现方式,即通过表锁的 AUTO-INC Locking 方式。因为有了新的自增长方式,0 这个选项不应该是新版用户的首选项。
2、innodb_autoinc_lock_mode = 1 这是该参数的默认值。对于 simple inserts,该值会采用互斥量【mutex】去对内存中的计数器进行累加的操作。对于 bulk inserts,还是会使用传统表锁的 AUTO-INC Locking 方式。在这种配置下,如果不考虑回滚操作,对于自增列的增长还是连续的。并且在这种方式下,statement-based 方式的 replication 还是能很好的工作。需要注意的是,如果已经使用 AUTO-INC Locking 方式去产生自增长的值,而这是需要再进行 simple inserts 的操作时,还是需要等待 AUTO-INC Locking 的释放。
3、innodb_autoinc_lock_mode = 2 在这个模式下,对于所有 insert-like 自增长的产生都是通过互斥量,而不是 AUTO-INC Locking 的方式。显然,这是性能最高的方式。然而,这会带来一定的问题,因为并发插入的存在,在每次插入时,自增长的值可能不是连续的。此外,最重要的是,基于 statement-Base Replication(SBR) 会出现问题。因此,使用这个模式,任何时候都应该使用 row-base repliaction(RBR)。这样才能保证最大的并发性能及 replication 主从数据的一致。
测试结果,GH-OST 的使用过程对性能影响是最小的,果然,无触发器的设计在性能上可以最大程度的避免 MDL 锁和 DML 锁等待
原创声明:本文系作者授权腾讯云开发者社区发表,未经许可,不得转载。
如有侵权,请联系 cloudcommunity@tencent.com 删除。
原创声明:本文系作者授权腾讯云开发者社区发表,未经许可,不得转载。
如有侵权,请联系 cloudcommunity@tencent.com 删除。