mysql主从之主从延时监控及原因分析

6.1 外在因素

网络 
主从硬件差异较大
版本差异
参数因素

6.2 主库

(1) 二进制日志写入不及时
[rep]>select @@sync_binlog;
(2) CR(传统)的主从复制中,binlog_dump线程,事件为单元,串行传送二进制日志(5.6 5.5)

1. 主库并发事务量大,主库可以并行,传送时是串行
2. 主库发生了大事务,由于是串行传送,会产生阻塞后续的事务.
3. 主库本身就及其繁忙,如慢语句, 锁等待, 从库个数比较多

解决方案:
1. 5.6 开始,开启GTID,实现了GC(group commit)机制,可以并行传输日志给从库IO线程,这个还需要配合双一标准来实现
2. 5.7 开始,不开启GTID,会自动维护匿名的GTID,也能实现GC,我们建议还是认为开启GTID
3. 大事务拆成多个小事务,可以有效的减少主从延时.

6.3 从库

SQL线程导致的主从延时
在CR(传统)复制情况下: 从库默认情况下只有一个SQL线程,只能串行回放事务SQL
1. 主库如果并发事务量较大,从库只能串行回放
2. 主库发生了大事务,会阻塞后续的所有的事务的运行

解决方案:
1. 5.6 版本开启GTID之后,加入了SQL多线程的特性,但是只能针对不同库(database)下的事务进行并发回放.
2. 5.7 版本开始GTID之后,在SQL线程方面,提供了基于逻辑时钟(logical_clock),binlog方面加入了seq_no机制(同线程下的序列号),
　　真正实现了基于事务级别的并发回放,这种技术我们把它称之为MTS(enhanced multi-threaded slave).
　　总结: 5.6基于库级别的并发sql线程; 5.7基于事务级别的并发sql线程.
3. 大事务拆成多个小事务,可以有效的减少主从延时.
[https://dev.mysql.com/worklog/task/?id=6314]

6.3.1 如何确定sql线程执行了多少io线程拿来的日志呢?

1. 可以直接查看relay-log.info文件, 该文件中记录了relay-log和master-log之间的对应关系.
2. show relaylog events in '192-relay-bin.000005';


66.

1. 到数据路径, cat relay-log.info    这里记录有relay-log的pos对应read_master_log的pos号.

2. show slave status中还有个 Exec_Master_Log_Pos

　　参数值中明确指出执行到master_log_file中的哪个pos号.

总结: 排查主从延迟思路,先排除主库,可以从以下方面考虑

1. show maste status; 查看主库的position号记录到多少了.

2. 从库中执行show slave status; 查看从库现在获取到哪个position号了.

3. 如果主从库的postion号一致,则表示主库没有问题.

4. 如果从库的postion号远小于主库的position号,则表示主库dump线程传送二进制出问题了.

5. 如果是主库的原因, 参考上面6.2的解决办法.

6. 如果是从库的原因, 参考上面6.3的解决办法.