一种MySQL主从同步加速方案

一、问题起源

MySQL的主从同步一直有从库延迟的问题，背景资料网上很多，原因简单描述如下：

    1、 MySQL从库上有一个IO线程负责从主库取binlog到写到本地。另外有一个SQL线程负责执行这些本地日志，实现命令重放；

   2、 正常网络状况下IO线程没有性能问题（这个待会会用到），问题是SQL线程只有一个，更新速度跟不上。所以经常会看到从库的CPU idle很高，但同步性能就是上不去。

 

 

二、方案雏形

单线程的SQL线程是造成这个问题的主要原因。比较直接的想法是把它改成多线程版本，这个据说官方版本开发中，其实我们也有一个这样的patch，但是直接写大片代码在线上提供服务的slave机器上这种事儿，都会因为担心稳定性而很难推动（写patch的和运维的同学，你们懂的）。

所以打算用一个“第三方”工具中转，来实现多线程同步。基本结构如下：

 

       说明：

1、这些transefer从master上各自同步一部分的数据，分别独立更新slave。多进程还是多线程均可。

2、Transfer与master之间异步更新日志，transfer与slve之间同步更新数据

3、从这可以看出这个方案的缺点之一：更新能够被独立分开。比较直观的想法是，按照表分。

 

三、关于transfer

作为这个关键的转发工具transfer，需要提供如下功能：

1、能够指定同步master中的哪部分数据，并且能够方便地修改这个配置以应对master的加表需求；

2、支持stop slave、start slave。支持快速切换到新主库的change master命令。

3、能够记录读取点，transfer自己重启或master重启后能够按照记录点继续读后面的binlog；

4、能够记录分发点，transfer自己重启或slave重启后能够按照记录点继续同步给slave

用起来就会发现还有好多要求。。。

 

四、方案实现

Transfer的这么多功能，自己造轮子就累了。这里直接用MySQL来充当此角色。为了方便描述，下文还将之称为transfer。Transfer更新slave在功能上可以使用federated引擎，但由于其纠结的实现导致性能上达不到要求，因此在MySQL框架层中作了一点修改――读到同步日志后，直接发送给slave。

方案简单描述如下：

    1、 Slave机器上搭另外的若干个MySQL(transfer)，将其设为Master的从库，且设置replicate-do-table, 每个transfer承担一部分的表。

   2、 所有Transfer的更新目标都设置为slave，其更新方式是读到日志后直接mysql_real_query执行到slave上。

从这可以看出这个方案的缺点之二：只能支持statement格式的同步方式。其实row也能支持，后面再说。

 

五、仍然延迟？

在transfer放弃federated引擎改用直接发送后，性能提升不少，从库同步性能增加一倍，但从本文第一个图的数据对比就知道，延迟还很大。

发现这个时候slave的机器cpu已经很忙了，idle 20%一下――这个算是好消息，总比idle很高但性能上不去好。

实际上是因为每个transfer，虽然设置只同步其中的部分表，但在实现上是IO线程把master上的所有命令都备份到本地，然后在SQL线程执行的时候再判断，若不符合replicate-do-table，再放弃。

这样存在的问题，是n个transfer，磁盘写了n倍，更严重的是导致SQL线程空转。

我们上文提到整个流程中IO线程是比较空闲的，因此修改IO线程逻辑，在写入磁盘前先判断，若不符合本transfer的replicate-do-table设置，不写盘，直接放弃。

六、效果

从库的QPS由于线程切换会有抖动，但总的执行时间与主库相同。从库的cpu idle下降，与主库几乎同时恢复到100。

 

七、小结

描述完了，总结一下，方案的代价：

1、要求在slave机器上多配置n个transfer（是否在从库上均可）

2、目前只能支持statement的binlog格式，实际上row可以支持，方案定了，开发计划中。

3、跨表更新的语句，会按照其更新的第一个表，分发到唯一一个transfer，没有重复更新的问题，但有时序性问题。

 

方案的好处：

1、功能比较齐全。直接使用MySQL，原有的管理功能基本都能用，主库从库重启/换库的代价比较小。

2、开发量小，只在transfer上修改两处，不包括配置读取部分，300行以内

3、风险相对小。不直接修改master和slve上的代码，线上比较容易接收。

 

 

评论

2 楼 丁林.tb 2011-09-26  

berserker_12 写道

通常mysql都会存在热表，实际环境中，热表的更新可能占80%，如果能提前分析binlog的sql语句，让同表的可以并行执行sql语句也能并行同步，效果可能会更好。

容灾的时候，主备切换的时候，是否可以保证transfer的数据能正确同步？

是的，但是同表并行就会导致失误顺序不能保证，会导致主从数据不一致。
trasfer的转向问题，只要transfer支持动态修改master和slave的信息就行了吧。修改master可以直接用change master命令，  修改slave的目前还没有实现，不过已经有方案了，会继续完善，感谢关注

1 楼 berserker_12 2011-09-25  

通常mysql都会存在热表，实际环境中，热表的更新可能占80%，如果能提前分析binlog的sql语句，让同表的可以并行执行sql语句也能并行同步，效果可能会更好。

容灾的时候，主备切换的时候，是否可以保证transfer的数据能正确同步？