滚蛋吧,MySQL主从复制延迟

本文转自公众帐号:破产码农
在内网看到同事写的文章,关于并行复制的调优,很具实战意义。之前写过并行复制的实现原理(见:MySQL 5.7并行复制实现原理与调优),并留下一道思考题给小伙伴。其实调优的思想和这个案例如出一辙。加之MySQL 8.0的发布,是时候和MySQL复制延迟说再见了。

延迟

由于历史原因,MySQL复制基于逻辑的二进制日志,而非重做日志。多次被问到何时MySQL能支持基于物理的复制,其实这就看MySQL各位大佬的想法。上次和赖老师脑暴,倏地说道:MySQL会不会来个基于Paxos的redo复制?物理复制的真正好处不在于正确性,因为基于ROW格式的日志复制也已能完全保证复制的正确性。由于物理日志的写入是在事务执行过程中就不断写入,而二进制日志的写入仅仅在事务提交时。因此物理日志的优势如下所示:

  • 复制架构下,大事务日志提交速度快;
  • 复制架构下,主从数据延迟小;

假设执行了1个小时的某大事务,在最后提交时,只需写入最后提交部分的重做日志(redo log可视为物理日志)。虽然此大事务重做日志写入的总量可能有1G,然而在提交时,数据主从复制仅需将最后一部分日志传输到远程从机,因为之前的重做日志已经在执行的1个小时内不断地同步到从机。

对于二进制日志,由于其写入时间发生在事务提交时,因此假设产生了1G的二进制日志,则需要事务提交时间会包含这1G日志的写入时间。在Oracle中有一种说法,事务的提交速度都是平的,不论事务的大小。这在MySQL数据库中是不成立的。即,MySQL的提交速度取决于事务产生的二进制日志的大小,事务提交的速度不是平的

更为糟糕的是,MySQL主从复制在大事务下的延迟。同样假设1个大事务在主服务器上执行了1个小时,则需要在最后的提交时间传送到从服务器。主从延迟的时间至少为1个小时,若从服务器执行还需1个小时,则主从复制延迟的最坏情况可能是2个小时。物理复制则不存在这样的限制,原因还是如前所述,事务提交过程中,日志已经在传输和回放。

物理复制虽好,但是也有自己的缺陷,就我自己的实际体验来看:

  • 物理复制下,主机坏块会导致主从服务器都无法启动;相信遇到过此问题的同学不在少数;
  • 此外,做ETL是有困难的,比如怎么将物理日志同步到Hadoop大数据平台呢?

一言以蔽之,对于MySQL数据库来说,任何时刻不允许有大事务执行。若要执行,则将大事务拆成一个个小的子事务来执行。这是最基本心法口诀,但却又和Oracle有着很大不同。总之,气宗、剑宗,本无好坏,学会理解其中的差异,融会贯通方可达风清扬般的致臻境界。

DDL延迟优化

DDL操作虽然不是事务,但也是一个耗时的操作,同样容易引起主从复制的延迟。对于DDL操作,通过pt-osc可以将DDL拆成一个个小事务执行。对于小表,业务量不大的情况,可以使用。若数据和业务压力都比较大的情况,这个方法并不是最优的选择。

我更倾向于利用逻辑复制的特点,从机先执行DDL,然后rolling upgrade给其他从机,最后主从切换,原主机再做DDL。这对线上业务的影响是最小的。然而到目前为止,没有一家云服务公司使用这种最优的自动化DDL方式,无不可惜。

对于妙级加列的DDL操作,腾讯互娱数据库团队已完成此功能,并已合并到MariaDB版本(见:MySQL秒级加列终成现实 #M1001#)。MySQL 8.0也将合并此特性,未来加列将不会再有延迟。不得不承认,这是一个具有跨时代意义的功能,完全解决在线加列的痛点。

DML延迟优化

若遵循基本的口诀,大事务拆小事务,则MySQL从服务器的I/O线程不会有特别大的延迟。然,SQL线程的延迟依然无法解决。

MySQL 5.7推出了多线程从机复制机制(下简称MTS,Multi-Thread Slave,又称并行复制 parallel replication)。之前多次举例到前公司的电商业务,在开启并行复制后,复制延迟从8小时下降为0,对于解决延迟问题立竿见影。

然而,目前MTS机制基于组提交实现,简单来说在主上是怎样并行执行的,从服务器上就怎么回放。这里存在一个可能,即若主服务器的并行度不够,则从机的并行机制效果就会大打折扣

怎样知道从机MTS的并行程度又是一个难度不小的面试题(至少是P7吧)。简单的一种方法,可以使用performance_schema库来观察,比如下面这条SQL可以统计每个Worker Thread执行的事务数量,在此基础上再做一个聚合分析就可得出每个MTS的并行度:

SELECT thread_id,count_start

FROM performance_schema.events_transactions_summary_by_thread_by_event_name

WHERE thread_id IN (

SELECT thread_id FROM performance_schema.replication_applier_status_by_worker

)

通过上述语句,观察到我们的监控系统MTS的并行度非常一般:

滚蛋吧,MySQL主从复制延迟-MySQL社区 Inside MySQL Group

超过58%的事务是单线程执行,反应在系统上的表现就是主从服务器的延迟超过7.5+小时。

这种场景下,可以通过调整主服务器上的参数binlog_group_commit_sync_delay、binlog_group_commit_sync_no_delay_count。前者表示延迟多少时间提交事务,后者表示组提交事务凑齐多少个事务再一起提交。总体来说,都是为了增加主服务器组提交的事务比例,从而增大从机MTS的并行度。

经过同事的调整,从服务器的延迟从超过7个小时降为0延迟(基本无延迟)。通过performance_schema的观察,MTS并行度大幅得到了提升:

滚蛋吧,MySQL主从复制延迟-MySQL社区 Inside MySQL Group

然而,若主服务器就是单线程或少量的线程并发执行,则目前的MTS机制还是会有延迟。MySQL 8.0最新的基于writeset的MTS才是最终的解决之道。即两个事务,只要更新的记录没有重叠(overlap),则在从机上就可并行执行,无需在一个组,即使主服务器单线程执行,从服务器依然可以并行回放。相信这是最完美的解决之道,MTS的最终形态。官方的测试结果如下:

滚蛋吧,MySQL主从复制延迟-MySQL社区 Inside MySQL Group

可以发现,单线程下从服务器的回放速度就能和基于256线程下的组提交速度一致,这是多么可怕的性能提升呢?MySQL 8.0一出,谁与争锋?

思考题:如何在不打开performance_schema的情况下,查看从服务器MTS的并行度呢?第一个答对者(给出具体方法),送签名版《MySQL内核:InnoDB存储引擎 卷1》

总结

  1. MySQL必须遵循大事务拆小事务的基本原则;
  2. 目前最优的DDL操作方式是通过从服务器roll upgrade方式;
  3. MySQL 5.7必须打开MTS功能,并升级到至少5.7.19版本;
  4. MySQL 8.0的writeset MTS是解决主从延迟的最终形态;
  5. 可期待MySQL 8.0的快速加列功能;

 

长期坚持原创真的很不容易,多次想放弃。坚持是一种信仰,专注是一种态度!点赞和转发是对作者最好的褒奖哟~~~

发表评论

坐等沙发
相关文章
MySQL 8.0 200W QPS!!!InnoDB大重构 #M1005#
MySQL 8.0 200W QPS!!!InnoDB大重构 …
MySQL 8.0.3性能大杀器 —— CATS
MySQL 8.0.3性能大杀器 —— CATS
2017年MySQL数据库技术嘉年华 —— 有态度的技术大会
2017年MySQL数据库技术嘉年华 —— 有态度…
IMG社区MySQL技术沙龙南京站圆满结束
IMG社区MySQL技术沙龙南京站圆满结束
改朝换代:MySQL Group Replication
改朝换代:MySQL Group Replication
数据库行业的朋友圈内幕,不知道就没法混了
数据库行业的朋友圈内幕,不知道就没法…
Oracle MySQL ACE. Author of Inside MySQL and MySQL Core Series. Great at MySQL performance tuning、troubleshooting、systems availability and scalability、capacity planning, etc.

一触即发,2017年,数据库世界的诸神之战