各位数据库技术圈的朋友,好久不见,甚是想念!
今天就MySQL与Oracle在主从复制上面的技术原理差异以及带来的影响,包括我们的思考,略述一二,仅供参考。
说明:
Oracle的Dataguard有Physical Standby、Logical Standby之分,实际使用中大部分用户都会采用Physical standby,所以本文重点对比的是Oracle的Physical Standby。但是无论是物理复制还是逻辑复制,Oracle的redo log的生成、传输的机制都是一样的,区别仅在于备库是Redo Apply(media recovery)还是Sql Apply。
一、事务日志内部格式的差异,决定了是物理复制还是逻辑复制。
1、 Oracle的事务日志内部格式(Redo Log):
可以看到,Oracle的Redo日志包含了改变向量(change vector),改变向量的集合就是重做记录(Redo Record),Redo Record是Oracle数据库进行Media Recover的最小单位。利用这些信息Oracle的Media Recovery无需知道原始的SQL语句,直接可以定位到数据文件的block(change vector里面含数据块的地址(DBA)信息)进行准确的数据修改,这就是所谓的“物理复制”。
2、 MySQL的事务日志(Binlog)
MySQL的事务日志格式有三种:STATEMENT、ROW、MIXED,5.7.6之前默认是STATEMENT,5.7.7之后默认是ROW。但是无论是哪种格式,二进制日志里面记录的binlog events都是“逻辑记录”,例如:
Statement Event:
Row event:
正因为MySQL二进制日志记录的是“逻辑”记录,所以无论是statement、还是row,在slave端的从库都要还原为sql语句在slave一条一条执行,这就是所谓的“逻辑复制”
事务日志内部格式的差别是Oracle与MySQL关于主从同步因素中的最重要的差别,对下文阐述的二者主从复制时,事务日志同步到从库的时机、并行,甚至传输日志量的多少都有或多或少的影响。
二、二者什么时候向从库传输事务日志,决定了对大事务复制的影响。
1、 Oracle Dataguard架构原理图
Oracle(这里不谈OracleDG的三种保护模式的区别)可以做到一个事务在主库开始后,该事物的每一个Redo Record(请注意是Redo Record而不是SQL语句或Rows)在LGWR(Oracle负责写事务日志的进程)写本地online redo logfile时,同时通过LNS与RFS的通讯,也同步将这个Redo Entry写入到备库的stanby redo logfile中(同步接收),再配合使用备库的Real Time Apply技术,备库甚至可以做到跟主库近乎同步地将block的变化写入到备库的数据文件中(同步应用)。(触发LGWR写redo的条件:user commit、1/3的redolog buffer、1M、3S、ahead DBWR等)
2、 MySQL的Master-Slave Replication架构图
从Master主库的角度看,MySQL的Slave从库本质上跟一个从客户端连接到Master主库的会话没有区别,Slave的IO线程不过是一个能够在Master上执行转储和SQL语句的客户端线程,也就是说主库的Binlog里面目前有什么,它就拿什么。
MySQL的一个事务开始后,它的日志什么时候写入到Binlog file,是受sync_binlog这个参数控制,一般我们都会配置>0,即事务commit时写入Binlog file。假设一个大事务执行了30分钟,产生了1GB的binlog,那么master也要等到这个事务最终commit时,才会将这1GB的数据写入binlog,这个时候才会有机会被slave的IO线程dump到并写入到从库的relay log,这种机制对比Oracle在事务进行过程中(尚未commit)近乎实时地写入,带来的影响和效果是显而易见的。
所以有一种说法是Oracle的日志写是“平”的,而MySQL的主从复制则很容易受大事务的影响,其根源就在于此。所以对于批量业务场景,Oracle的主从同步效率一般都会大于MySQL。
三、二者并行复制技术的差异(Redo Apply、SQL Apply)
并行复制(主要指的是在从库并行执行的环节)最大的难题或者说最需要考虑的因素是,是如何确保不同复制进程(线程)之间没有因为并行地在从库执行,而打乱了这些事务在主库的执行顺序、锁冲突、关联依赖(主外键),从而造成主从数据的不一致。
1、MySQL并行复制:
对于MySQL,其并行复制的瓶颈出现在IO Thread上的较少,一般都是在SQL Thread上。最早的复制架构中只有1个SQL thread,也就是说主库上多个不同线程的并行会话产生的事务,在从库上都是串行回放执行的,那么主库事务量较大的情况下,从库都会落后于主库。
MySQL 5.6开始,SQL Thread开始有了多个的概念(slave_parallel_workers),也就是说SQL Apply可以多线程并行执行,但是这个并行只是基于不同schema的,也就是不同database的,同一个database内的事务依旧是串行复制的。这对于大多数业务场景其实是一个鸡肋。
MySQL 5.7开始,引入了基于组提交(group commit)的并行复制技术,也就是同一个database内的按照同一个group提交的不同事务可以在从库并行执行SQL Apply,粒度进一步缩小,提升了复制的并发度。但是也要依赖主库上的事务特点,只有在一个组内并行提交的事务才可以并行,也就是说从库并行效率依赖于主库有多少并行事务,提升场景有限。
MySQL 8.0开始,引入了基于Writeset的并行复制,这个可能才是真正意义上可以与Oracle并行复制技术一较高下的技术。Writeset的技术在MGR(MySQL组复制集群架构)中也起到了关键作用,它是直接检测不同事务之间是否有冲突来决定是否可以并行复制,粒度更小,使得从库的并行不再依赖于主库。具体可以看文后附的参考文章。
2、Oracle并行复制
Oracle是基于MediaRecovery的技术,直接读取Redo Record并根据其中记录的物理信息,对备库的数据文件进行block change操作。同时Oracle备库的MRP(Media Recover Process)进程可以通过增加parallel以及using current logfile参数,进行并行+实时的Redo Apply。Oracle执行并行复制在遵守事务提交顺序(SCN)的前提下,充分利用了物理复制的优势,可以在不同Redo Record的粒度上进行并行复制,大大提升了复制的效率。
这个parallel的特性在Oracle进行基于Rman备份进行恢复时有很好的直接数据证明,一个TB级的大库,在线上运行了2天的归档日志(例如300GB),在加上并行参数进行media recover时,可能在6-8小时内就可以追平。
四、简单总结:
1、关于并行复制:
MySQL被Oracle收购以后,Oracle以它在DB上面的深厚功力,不断将它的独门绝技传授给MySQL,从MySQL的并行复制技术的不断迭代更新的速度上窥见一斑(期待未来会Oracle会有更多例如AWR、Flashback的实用特性可以在MySQL上看到)。但毕竟一个技术从开端到成熟还需要时间与经验的积累,最重要是要有最终客户的大量使用、问题反馈来不断磨练。我们生产目前使用MySQL 5.7版本,因为经常出现因并行复制导致的SQL Thread线程失败报错,甚至直接实例宕机,我们自己的感受是当前MySQL并行复制技术并不是特别成熟,故选择了暂时关闭并行复制。后面升级到8时再看实际效果。
2、关于大事务:
就像第二章所述,目前Binlog的提交机制决定了一个MySQL的大事务没有办法像Oracle一样源源不断地同步复制记录到从库,只能一次性提交到从库,进而在大事务运行时MySQL的复制延时是无法避免的。目前的解决办法是尽量将大事务拆成小事务运行,虽然做得事情都一样多,但是相当于通过应用的事务拆分达到了“源源不断”的效果。
最后,鸣谢以下参考文档的作者:
https://yq.aliyun.com/articles/621197
https://mp.weixin.qq.com/s/oj-DzpR-hZRMMziq2_0rYg?spm=a2c4e.11153940.blogcont621197.16.74cf1ce5Ofmtx2
http://www.innomysql.com/%E5%86%8D%E8%A7%81%E5%90%A7%EF%BC%8Cmysql%E4%B8%BB%E4%BB%8E%E5%A4%8D%E5%88%B6%E5%BB%B6%E8%BF%9F/
https://mp.weixin.qq.com/s/-AL3b4DaXPBN4EKjjSYdUg
https://wenku.baidu.com/view/9612c27159eef8c75fbfb3da.html