主从复制延迟原因剖析！ - MySQL数据库

写在前面：

之前在维护线上主从复制架构的时候，遇到了一些主从延迟问题，笔者呢，也是比较好学的，哈哈！所以，查阅了诸多资料，然后去其糟粕，根据自己的理解和查阅的资料整理成了本文，在此申明，本文内容是笔者自己的理解，不代表权威性，仅供各位同行做个参考，自己呢，也做个学习记录。本着实事求是的原则，对于网上的诸多资料，笔者自己也只是信5分，怀疑5分，就算是官方的资料，有的也有bug不是，只有自己动手实践过了，才能相信，才有发言权，其他的，一切都是虚的！

MySQL主从复制过程：

1）主库 Binlog Dump线程在binlog有变化时，主动发送最新的binlog到从库。

2）从库 I/O线程被动接收主库传来的binlog之后，记录到从库的relay log中，当没有数据传入的时候则会等待。与此同时SQL线程重放 relay log。

3）当从库长时间未收到主库传来的数据，并且等待时间超过了slave_net_timeout定义的时间（默认3600秒）后，Slave_IO_State的状态将会置为No。在此之后，每隔MASTER_CONNECT_RETRY [Connect_Retry: 30]定义的时间（默认60秒）将会尝试重新连接，直到连接成功或超过重试次数MASTER_RETRY_COUNT [Master_Retry_Count: 6666]（默认86400次）。

slave_net_timeout可以在配置文件中修改或set variable在线设置

而 MASTER_CONNECT_RETRY、MASTER_RETRY_COUNT 需要在CHANGE MASTER TO建立复制关系时提前指定

在线变更 slave_net_timeout：

SHOW VARIABLES LIKE ’slave_net_timeout’

Variable_name          `Value`

slave_net_timeout      3600

SET GLOBAL slave_net_timeout=1800

修改MASTER_CONNECT_RETRY=30,MASTER_RETRY_COUNT值：

mysql> change master to MASTER_CONNECT_RETRY=30,MASTER_RETRY_COUNT=6666;

ERROR 1198 (HY000): This operation cannot be performed with a running slave; run STOP SLAVE first

mysql> stop slave;

Query OK, 0 rows affected (0.01 sec)

mysql> change master to MASTER_CONNECT_RETRY=30,MASTER_RETRY_COUNT=6666;

Query OK, 0 rows affected (0.01 sec)

mysql> start slave;

Query OK, 0 rows affected (0.02 sec)

MySQL主从复制延迟是怎样形成的：

1、主库的worker线程在写binlog的时候是并发工作的（并行写入），而主库的dump线程和从库的IO线程都是单线程推拉binlog，特别是SQL线程是拿着relay log中的event逐一单线程回放的（5.6版本开启slave_parallel_workers支持特定情况下的并行复制，5.7版本之后全面支持并行复制后在复制层面已极大改善了延迟问题）。因此即使不考虑网络延迟，主流MySQL版本在高并发的情况下，消费很可能赶不上生产，采用异步复制的从库很有可能跟不上主库的进度。

2、在复制期间，无论是主库或从库负载高（特别是从库落盘压力大，关系到sync_binlog、innodb_flush_log_at_trx_commit的设置）或者是网络传输慢（特别是跨机房的同步）等情况发生时，都会产生主从延迟，并且是不可避免的。如果要实现强一致性，可采用Semi-sync，但采用该plugin也无法保证持续强一致性（rpl_semi_sync_master_timeout会引起复制模式的降级）

根据MySQL主从复制延迟的形成原因，以下情况可能导致MySQL主从复制延迟：

1）MASTER高并发，形成大量事务

2）网络状况差

3）从库的硬件配置没有主库的好

4）本来就是异步复制

关于落盘时的部分参数解释：

sync_binlog：

        MySQL官方文档参考：https://dev.mysql.com/doc/refman/5.6/en/replication-options-binary-log.html

解释：

        Controls how often the MySQL server synchronizes the binary log to disk

 控制MySQL将二进制日志（binary log）同步到磁盘的频率

sync_binlog=0:

    Disables synchronization of the binary log to disk by the MySQL server. Instead, the MySQL server relies on the operating system to flush the binary log to disk from time to time as it does for any other file. This setting provides the best performance, but in the event of a power failure or operating system crash, it is possible that the server has committed transactions that have not been synchronized to the binary log.

sync_binlog=1:

    Enables synchronization of the binary log to disk before transactions are committed. This is the safest setting but can have a negative impact on performance due to the increased number of disk writes. In the event of a power failure or operating system crash, transactions that are missing from the binary log are only in a prepared state. This permits the automatic recovery routine to roll back the transactions, which guarantees that no transaction is lost from the binary log.

sync_binlog=N:

     where N is a value other than 0 or 1: The binary log is synchronized to disk after N binary log commit groups have been collected. In the event of a power failure or operating system crash, it is possible that the server has committed transactions that have not been flushed to the binary log. This setting can have a negative impact on performance due to the increased number of disk writes. A higher value improves performance, but with an increased risk of data loss.

innodb_flush_log_at_trx_commit：

        MySQL官方文档参考：https://dev.mysql.com/doc/refman/5.6/en/innodb-parameters.html#sysvar_innodb_flush_log_at_trx_commit

        其他参考文档：

        https://blog.csdn.net/codepen/article/details/52160715

        https://www.cnblogs.com/mayipapa/p/4685313.html

解释：

        是 InnoDB 引擎特有的，ib_logfile的刷新方式

MySQL日志写入顺序：

log buffer => MySQL(write) => log file => OS刷新(flush) => disk

innodb_flush_log_at_trx_commit取值解释：（从一些博客之中参考的）

0，延迟写：

log buffer => 每隔1秒 => log file => OS 实时flush => disk

1，实时写，实时刷：

       log buffer => 实时 => log file => OS实时flush => disk

       这样的话，数据库对IO的要求就非常高了，如果底层的硬件提供的IOPS比较差，那么MySQL数据库的并发很快就会由于硬件IO的问题而无法提升

2，实时写，延迟刷：

       log buffer => 实时 => log file => OS每隔1秒 => disk

       如果只是MySQL数据库挂掉了，由于文件系统没有问题，那么对应的事务数据并没有丢失。只有在数据库所在的主机操作系统损坏或者突然掉电的情况下，数据库的事务数据可能丢失1秒之类的事务数据。这样的好处，减少了事务数据丢失的概率，而对底层硬件的IO要求也没有那么高(log buffer写到文件系统中，一般只是从log buffer的内存转移的文件系统的内存缓存中，对底层IO没有压力)。

官方文档中文解释：（笔者自己的理解）

       当innodb_flush_log_at_trx_commit，被设置为0，日志缓冲(log buffer)每秒一次地被写入到日志文件(log file)，并且对日志文件做磁盘刷新(flush disk)，该模式下在事务提交的时候，不会主动触发写入磁盘的操作。

       当innodb_flush_log_at_trx_commit，被设置为1，在每个事务提交时，日志缓冲(log buffer)被写入到日志文件(log file)，并且对日志文件做磁盘刷新(flush disk) [同时进行]

       当innodb_flush_log_at_trx_commit，被设置为2，在每个事务提交时，日志缓冲(log buffer)被写入到日志文件(log file)，但不对日志文件做磁盘刷新(flush disk) [不同时进行]，该模式下，MySQL会每秒执行一次 flush(刷到磁盘)操作

       尽管如此，当innodb_flush_log_at_trx_commit值为2时，对日志文件(log file)的磁盘刷新(flush disk)也每秒发生一次。

       因为进程安排问题，每秒一次的刷新不是100%保证都会发生。可以通过设置innodb_flush_log_at_trx_commit值不为1来获得较好的性能，但如果你设置此值为0，那么MySQL崩溃会丢失崩溃前1秒的事务（该模式下性能最好，但不×××全）；如果设置此值为2，当操作系统崩溃或断电时才会丢失最后1秒的事务（该模式下性能较好，也比0模式安全）。如果设置此值为0，该模式性能最低，但是最安全的模式。在MySQL服务崩溃或者操作系统crash的情况下，binlog只会丢失一个语句或一个事务。

注意，许多操作系统和一些磁盘硬件会欺骗刷新到磁盘操作(flush disk)。尽管刷新没有进行，也会告诉MySQL刷新已经进行。即使设置此值为1，事务的持久性也不被保证，且在最坏的情况下断电甚至会破坏数据库。在SCSI磁盘控制器中，或在磁盘自身中，使用有后备电池的磁盘缓存会加速文件刷新并且使得操作更安全。可以试着使用hdparm命令在硬件缓存中禁止磁盘写缓存，或者使用其他一些对硬件提供商专用的命令

最后MySQL官方建议：

A higher value improves performance, but with an increased risk of data loss

For the greatest possible durability and consistency in a replication setup that uses InnoDB with transactions, use these settings:

sync_binlog=1

innodb_flush_log_at_trx_commit=1

MySQL主从延迟如何计算：

第一种计算方式：position（简单粗暴，仅仅能看出是否有延迟）

mysql> show slave status\G;

Read_Master_Log_Pos: 4

Exec_Master_Log_Pos: 0

第二种计算方式：Seconds_Behind_Master

参考MySQL官方文档：

https://dev.mysql.com/doc/refman/5.6/en/replication-administration-status.html

https://dev.mysql.com/doc/refman/5.6/en/show-slave-status.html

参考其他文章：

https://blog.csdn.net/leonpenn/article/details/76489480

1） 当SQL线程执行event时，从库执行时刻的timestamp值 减去 该event上附带的时间戳（当时主库上的timestamp），这两者的差值

2） 一旦SQL线程未在执行event，则Seconds_Behind_Master为0

3） IO线程或SQL线程 is not running，则Seconds_Behind_Master为NULL

4） Seconds_Behind_Master为0时，表示master slave 复制没有延迟（绝大情况下如此）

在网络很快的情况下，io thread 很快的从master获取binlog到slave的realy log，然后sql thread replay，此时Seconds_Behind_Master值能真正表示slave落后于master的秒数。

在网络很差的情况下，io thread同步master的binlog很慢，而sql thread replay很快，此时Seconds_Behind_Master也是0，但真正情况是，slave落后于master很多

假如有以下3种情况发生，虽然Seconds_Behind_Master仍然存在非NULL的值，但已经变得不准确

1、主从时间不一致（虽然引入了clock_diff_with_master，尽量调节时间差带来的影响，但该值仅在从库与主库建立连接之时获取，后续不再更新，若之后再去修改主从机的时间，该值就不可靠了）。

2、主从库间网络问题或者从库IO线程未发现主库的binlog dump 线程挂了，仍然在等待数据传输过来，SBM长时间持续为零。

3、主库有较大的binlog event执行前后，从库上看到的SBM将会有大的跳动（监控图中将很可能产生毛刺）

4、对于并行复制，SMB是基于Exec_Master_Log_Pos，不精准。