【MySQL】second behind master不準確的處理(監控主從延遲) pt-heartbeat

second_behind_master：

1、 當從庫不斷的處理更新的時候，(正在複製的時候)這個值顯示從庫當前主機時間戳和來自主庫的二進位制中記錄的時間戳之間的差異。

2、 當從庫沒有任何需要處理的更新時，例如主庫不寫入時，如果I/O執行緒和SQL執行緒都是yes，則這個值是0，否則為NULL

3、如果主庫和從庫之間的網路非常快， 那麼從庫的I/O執行緒讀取的binlog會與主庫中最新的binlog非常接近，這樣計算出來的值會

很小，單位為秒，基本上可以替代主從之間的資料延遲時間，這個時候是可靠的。 相反，如果主庫和從庫之間的網路特別慢，則

從庫中的binlog時間可能遠遠落後於主庫最新的binlog，但是二者的真實偏差時間非常小（由於網路慢導致看著偏差比較大），

這個時候，這個欄位的值是不可靠的。即使顯示為0，但是也有可能是因為主庫網路不好導致大量的binlog沒有傳過來，所以這個

值在網路比較慢的時候不可靠。

4、如果主庫與從庫本身的時間不一致，那麼只要從庫的複製執行緒啟動之後，沒有做過任何的時間變更，那麼這個欄位的值也是

可靠的，一旦修改了伺服器的時間，那麼這個值就變的不可靠了。

5、如果從庫的SQL執行緒沒有執行，則該欄位顯示為NULL；

如果從庫的SQL執行緒已經消費完了relay log而I/O執行緒沒有執行，則該欄位顯示為NULL；

如果I/O執行緒已經停止，但是relay log還沒有重放完成的時候，仍舊會顯示出複製時間。直到relay log被消費完，顯示為NULL；

如果SQL執行緒和I/O執行緒都執行著，但是處於空閒狀態，則該欄位為0；

6、正常情況下，主庫和從庫中的binlog event值都來自於主庫。如果在單執行緒複製的時候，我們在從庫上做了一些操作，

則有可能導致這個值產生一些波動。因為有時候event的值來自於主庫，有時候來自於從庫。如果是多執行緒複製，則這個值是

基於Exec_Master_Log_Pos的enent時間戳來計算的。

    binglog日誌中event的event header（event header的前4個位元組）記錄了event的時間戳， SQL回放的時間減去這個event產生的時間，就是Seconds_Behind_Master的時間。但是這個時間不準確，線上有時延時是幾千秒(3600s)，但是突然就變成了0，有這種情況。

上面是官方文件中的說明，我從一些研究MySQL原始碼的文章中得到了下面這樣的計算公式：

* clock_of_slave - last_timestamp_executed_by_SQL_thread - clock_diff_with_master

也就是"從庫的當前系統（主機）時間 - 從庫 SQL 執行緒正在執行的event的時間戳 - 主從庫的系統（主機）之間的時間差",其中最

後一項diff的值，就是主庫和從庫的主機時差。這個時差在I/O執行緒啟動的時候只計算一次，如果此時更改了系統時間，那麼無疑會

導致複製的SBM值不可靠。

當Seconds_Behind_Master計算結果為負數的時候，直接歸零

透過上面的描述，我們可以得到以下的結論：

1.  如果 I/O 和 SQL執行緒同時為 Yes，且SQL執行緒沒有做任何事情（沒有需要被執行的event），此時直接判定複製延遲結果為0，

不會走公式計算延遲時間，否則會走公式計算延遲時間

2.  如果 SQL執行緒為Yes，且還存在著 I/O 執行緒已經讀取的relay log未應用完成的，則會走公式計算延遲時間，而不管 I/O執行緒是

否正在執行，但當SQL執行緒重放完成了所有relay log時，如果 I/O執行緒不為Yes，直接判定複製延遲結果為NULL

3.  任何時候，如果SQL執行緒不為Yes，直接判定複製延遲結果為NULL。當計算出的複製延遲為負數時，直接歸0

4. 當SQL執行緒重放大事務時，SQL執行緒的時間戳更新相當於被暫停了（因為一個大事務的event在重放時需要很長時間才能完成，

雖然這個大事務也可能會有很多event，但是這些event的時間戳可能全都相同），此時，根據計算公式可以得出，無論主庫是否

有新的資料寫入，從庫複製延遲仍然會持續增大，會出現主庫停止寫入之後，從庫複製延遲逐漸增大到某個最高值之後突然變為

0的情況

5.根據公式計算，如果主庫持續不斷產生二進位制日誌（持續不斷有資料變更），則複製延遲的計算結果不會出現誤差

（或者說誤差可以忽略不計，因為從庫的系統時鐘是正常向後推進的，除非主從庫的系統時間被改動了），如果在慢速網路中

主庫斷斷續續寫入資料，甚至主庫突然停止任何資料寫入，之後實際上主庫並沒有新的資料寫入（也就不會有新的binlog event時

間戳產生），但是由於計算公式中並不感知這個變化，所以隨著從庫的系統時鐘繼續向前推進，就會導致在追趕上主庫的資料之

前，計算出的延遲時間值越來越大。

pt-heartbeat，下載通用二進位制包

建立監控資料庫：

mysql> create database monitor;
Query OK, 1 row affected (0.02 sec)

下載安裝
./pt-heartbeat -D monitor --update -uroot -p oracle -P3306 -h 10.10.60.60 --create-table --daemonize

引數的意義：

• --update表示要實時更新時間戳的資料，這就是和之前的seconds_behind_master不同，seconds_behind_master並不是實時更新。

• --daemonize放到後臺執行

• --create-table第一次需要建立heartbeat名的表。

  
  pt-heartbeat建立一個帶有時間戳的表，並且因為是主從，這樣表會複製到從上。
  

並且我們可以看到，每次查詢的時候時間戳和position都是變化的，
備庫上heartbeat表的ts列時間和主庫heartbeat表中ts列的時間差就是主從複製的延遲時間
並且工具中還提供了monitor監控工具。

監控：

./pt-heartbeat -D monitor --monitor --master-server-id  603306 -uroot -p oracle -P3306 -h 10.10.60.60

看精確的看第一列，後幾列分別為1min、5min、15min內的延遲時間。