Mysql主从同步延迟问题及解决方案

对于主从正常执行，相应的延迟几乎是不存在的。但是在高QPS下，主从同步却出现了比较明显的延迟情况。

问题一：主库的从库太多，导致复制延迟：从库数据以3-5个为宜，要复制的从节点数量过多，会导致复制延迟

问题二：从库硬件比主库差，导致复制延迟：查看Master和Slave的系统配置，可能会因为机器配置不当，包括磁盘I/O、CPU、内存等各方面因素造成复制的延迟。一般发生在高并发大数据量写入场景中

问题三：慢SQL语句过多：假如一条SQL语句执行时间是20秒，那么从执行完毕到从库上能查到数据至少需要20秒，这样就延迟20秒了。一般要把SQL语句的优化作为常规工作不断地进行监控和优化，如果单个SQL的写入时间长，可以修改后分多次写入。通过查看慢查询日志或show full processlist命令，找出执行时间长的查询语句或大的事务

问题四：主从复制的设计问题：例如主从复制单线程，如果主库写并发太大，来不及传送到从库，就会导致延迟。更高版本的MySQL可以支持多线程复制，门户网站则会开发自己的多线程同步功能。

问题五：主从库之间的网络延迟：主从库的网卡、网线、交换机等网络设备都可能成为复制的瓶颈，导致复制延迟。另外，跨公网的主从复制很容易导致主从复制延迟

问题六：主库读写压力大，导致复制延迟：架构的前端要加buffer及缓存层

 

门户网站的解决方案：

1）优酷的解决方案：数据库分片技术，而抛弃了由于数据量的越来越多导致复制延迟的问题。按照user_id进行分片，这样必须有一个全局的表来管理用户与shard的关系,根据user_id可以得到share_id，然后根据share_id去指定的分片查询指定的数据

2）淘宝的解决方案：修改源码，对应的机制是Transfer机制，此处通过对Binlog日志重做采用多线程实现，从而提高slave的QPPS

备注：

1）如果还是经常性的短时间延迟，那就尝试加大从库的硬件配置，比如上sata SSD，pcie等

2）延迟的监控到位，可通过pt-heart-beat来准确监控延迟值，及时发现查看。

3）5.5以后版本的，可以考虑采用半同步复制，能解决少量延迟引起的问题，不过对tps性能损耗较大

4）升级到mysql 5.7吧，多线程复制，几乎完美解决单线程复制引起的从库延迟。

 

来源：http://blog.csdn.net/moonpure/article/details/52850947