主从同步的延迟问题、原因及解决方案

MySQL的主从同步在实际使用过程中会有从库延迟的问题,那么为什么会有这种问题呢? 如何避免这种问题呢?

情况一: 从服务器配置过低导致延迟

这类延迟场景的出现往往是主节点拥有较大规格的配置，而只读节点却购买了一个最小规格的配置

只读节点的数据为了和主节点保持同步，采用了MySQL binlog复制技术，由一个IO线程和一个SQL线程来完成，IO线程负责将主库的binlog拉取到只读节点，SQL线程负责消费这些binlog日志，这两个线程会消耗掉只读节点的IO资源，所以当只读节点IOPS配置不够的时候，则会导致只读节点的数据出现延迟

解决办法: 升级从服务器的配置,让只读节点的配置大于或者等于主节点的配置即可

情况二: 主库的QPS过高导致只读节点延迟

由于只读节点与主库的同步采用的是单线程同步，而主库的压力是并发多线程写入，这样势必会导致只读节点的数据延迟

解决办法: 开启只读节点的并行复制 (mysql5.6.3以后支持多线程复制)

--------------------------------------------------------------------------------------------------------

拓展：

在MySQL5.6中，引入了并发复制，这个并发复制是数据库级别的，这意味着一个SQL线程可以处理一个数据库的连续事务，而不用等待其它数据库完成。

这个版本的并发复制，可以理解成一个数据库一个SQL线程。

其与并发有关的参数如下：

slave_parallel_workers #worker 线程个数

slave-checkpoint-group #隔多少个事务做一次

checkpointslave-checkpoint-period #隔多长时间做一次

checkpointslave-pending-jobs-size-max #分发给worker的、处于等待状态的event的大小上限

MySQL5.6基于DATABASE级别的并发复制可以解决业务表放在不同的database下同步延迟的问题

但是在实际生产中大部分表还是放在同一个库中的，这种情况即使设置slave_parallel_workers大于0，也无法进行并发。在高并发的情况下，依然会造成主从复制延迟.

MySQL 5.7版本才真正支持“真正”的并行复制功能.在MySQL5.7中，引入了新的并发复制方法，基于LOGICAL_CLOCK的并发复制，可以支持在一个database中，并发执行relaylog中的事务。

相同的二进制日志组在master上提交并行应用到slave节点上，没有跨数据库的限制，并且不需要把数据分割到多个数据库。

要实现这个功能，需要在master节点标记binlog中提交的事务哪些是可以并发执行，虽然的MySQL5.6中已经引入binarylog group commit,但是没有将可并发的事务标记出来。

在MySQL5.7中，已经解决了主从复制延迟的问题，具体配置参数如下：

slave-parallel-type=LOGICAL_CLOCK

slave-parallel-workers=16

master_info_repository=TABLE

relay_log_info_repository=TABLE

relay_log_recovery=ON

----------------------------------------------------------------------------------------------------

情况三: 主库的DDL语句导致只读节点延迟

可能1:只读节点与主库的DDL同步是串行进行的，如果DDL操作在主库执行时间很长，那么同样在备库也会消耗同样的时间.

比如在主库对一张500W的表添加一个字段耗费了10分钟，那么在只读节点上也同样会耗费10分钟，所以只读节点会延迟600S

可能2：只读节点上有一个执行时间非常长的的查询正在执行，那么这个查询会堵塞来自主库的DDL，读节点表被锁，直到查询结束为止，进而导致了只读节点的数据延迟。

在只读节点上可以通过执行show processlist命令查看连接的状态处于:

Waiting for table metadata lock

解决办法: 对于可能1，只能说执行操作之前对可能带来的影响要有考量; 对于情况2，可以kill掉只读节点上的大查询进行，就可以恢复只读节点与主节点的数据同步

情况四: 主库执行大事务导致延迟

主库执行了一条insert … select非常大的插入操作，该操作产生了近几百G的binlog文件传输到只读节点，进而导致了只读节点出现应用binlog延迟。

解决办法: 将大事务拆分成为小事务进行排量提交，这样只读节点就可以迅速的完成事务的执行，不会造成数据的延迟。

情况五：无主键的表进行DML操作导致延迟

如:mysql> update test set kk='fafa01';

由于表中没有主键，所以导致了每一个事务条目的更新都是全表扫描，如果表中很很多的数据，则备库执行该更新的事务条目的时候，就会出现很多的全表扫描更新；

进一步说明就是，由于表中没有主键，在ROW模式下，每删一条数据都会做全表扫，也就是说一条delete，如果删了10条，会做10次全表扫，所以slave会一直卡住；

解决办法: 每张表在设计的时候都加上一个主键

----------------------------------------------------------------------------------------------------------

拓展：

主键对于innodb来说，是非常重要的，每张表的设计的时候，都应该把主键默认的加上，不管你需不需要他

主键的设计最好选择自增型的主键

自增主键的好处：

a.自增型主键以利于插入性能的提高；

b.自增型主键设计(int,bigint)可以降低二级索引的空间，提升二级索引的内存命中率；

c.自增型的主键可以减小page的碎片，提升空间和内存的使用

------------------------------------------------------------------------------------------------------

四.总结

为了避免MySQL主从复制延迟,我们可以从以下几方面入手:

1.数据库设置: 主从同步加速

1).sync_binlog在slave端设置为0

2).–log-slave-updates 从服务器从主服务器接收到的更新不记入它的二进制日志.

3).直接禁用slave端的binlog

4).slave端,如果使用的存储引擎是innodb,innodb_flush_log_at_trx_commit =2

2.架构方面:在架构上做优化,尽量让主库的DDL快速执行,尽量减轻数据库的压力

1).业务的持久化层的实现采用分库架构,mysql服务可平行扩展,分散压力.

2).单个库读写分离,一主多从,主写从读,分散压力.这样从库压力比主库高,保护主库.

3).服务的基础架构在业务和mysql之间加入memcache或者redis的cache层.降低mysql的读压力.

4).不同业务的mysql物理上放在不同机器,分散压力.

3.硬件方面:使用比主库更好的硬件设备作为slave

1).采用好服务器,比如4u比2u性能明显好,2u比1u性能明显好.

2).存储用ssd或者盘阵或者san,提升随机写的性能.

3).主从间保证处在同一个交换机下面,并且是万兆环境.