主從復制延遲原因剖析！

寫在前面：

之前在維護線上主從復制架構的時候，遇到了一些主從延遲問題，筆者呢，也是比較好學的，哈哈！所以，查閱了諸多資料，然后去其糟粕，根據自己的理解和查閱的資料整理成了本文，在此申明，本文內容是筆者自己的理解，不代表權威性，僅供各位同行做個參考，自己呢，也做個學習記錄。本著實事求是的原則，對于網上的諸多資料，筆者自己也只是信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，不精準。