滚蛋吧,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.3性能大杀器 —— CATS
MySQL 8.0.3性能大杀器 —— CATS
2017年MySQL数据库技术嘉年华 —— 有态度的技术大会
2017年MySQL数据库技术嘉年华 —— 有态度…
IMG社区MySQL技术沙龙南京站圆满结束
IMG社区MySQL技术沙龙南京站圆满结束
改朝换代:MySQL Group Replication
改朝换代:MySQL Group Replication
数据库行业的朋友圈内幕,不知道就没法混了
数据库行业的朋友圈内幕,不知道就没法…
Inside MySQL Group社区启用新LOGO
Inside MySQL Group社区启用新LOGO
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年,数据库世界的诸神之战