MYSQL主從同步異常匯總

問題描述：

程式上表現為對 主庫 更新操作之後，從 從庫 查詢資料沒發生改變。懷疑是主從庫同步延遲導致。上從庫查看主從同步狀態，發現Seconds_Behind_Master時間長達一千多秒。正常情況下主從庫延時個十幾秒還可以容忍，一千多秒顯然就有問題了麼。。。

 

問題分析：

我們在一個MYSQL執行個體上建立了四五個Database，其中一個Database資料量和壓力都比較大，從 從庫的processlist可以看到從庫在處理日誌時經常發生lock的狀況，但是lock只是壓力大database為何會影響到其他database也延遲呢？

 

原來從庫是單線程處理同步處理記錄，也就是說無論多少個database都是通過一個線程去執行更新操作，所以主從庫同步延遲的時間不是針對database的，是針對一個MYSQL執行個體的。

 

 

那麼，為何從庫在處理日誌時會發生lock的狀態呢？

 

一般我們都將主從庫讀寫分離，主庫負責寫操作，從庫負責讀操作。而一般的web應用讀資料的操作要遠遠大於寫資料的量，所以我們在主庫上幾乎看不到因為更新資料導致的lock。那麼從庫的lock怎麼發生的呢？

 

[c-sharp]
view plaincopyprint?

1. 對MyISAM表的讀操作（加讀鎖），不會阻塞其他進程對同一表的讀請求，但會阻塞對同一表的寫請求。只有當讀鎖釋放後，才會執行其它進程的寫操作。  
2. 對MyISAM表的寫操作（加寫鎖），會阻塞其他進程對同一表的讀和寫操作，只有當寫鎖釋放後，才會執行其它進程的讀寫操作。  

 

從上面可以看出，我們在select的時候預設是會阻塞寫請求的，當一個表資料量到達了千萬層級，那麼執行一個select很有可能就會變得比較費勁，再加上一定的壓力，不斷地select操作，雖然讀資料不會受到影響，但是卻阻塞了從庫處理同步處理記錄的操作。長此以往。。。可想而知。。。

 

問題處理：

1.首先一個MYSQL執行個體不要建立太多database，否則一旦其中一個庫壓力大經常被鎖，會導致所有庫同步都延遲，你傷不起啊。。。

2.壓力較大的情況下使用幾個從庫值得考量，如果使用多個從庫也是可以適當緩解上面lock的情況發生。

#################################

Mysql主從同步延遲與系統時間的關係

#################################################

ysql主從同步延遲受到多種因素影響， 比如大事務， 從庫查詢壓力，網路延遲等； 這些比較常見； 但還受到主從機器系統時鐘差的影響，這一點可能容易被忽視。
上周， 就遇到了這樣的情況， 主庫的系統時間由於某種原因落後於從庫幾十秒， 結果頻繁的出現大的主從延遲同步，查了N久業務方面的問題，都找不出原因；在和同事的交流中，發現大家對參數Seconds_Behind_Master的理解有點補一樣，基本有兩種理解：

一種理解是來源於Mysql手冊上的描述，大體意思是這個時間是從庫SQL線程處理的最近的日誌事件的時間戳記減去從庫IO線程處理的最近一條日誌記錄的時間戳記得到的,可以簡單理解為從庫SQL線程與IO線程所處理的最近的日誌事件的時間戳記差；這個計算方式給人的感覺不是在計算主從延遲，而是在計算從庫上兩個線程的處理的日誌的時差。
另一種理解來源於《High PerformaceMysql》上的的描述，大體意思這個參數反映的結果是當前系統時間減去從庫IO線程所處理的最近一條日誌記錄的時間戳記；但這個說法有一個明顯的不太讓人信服的地方，就是如果機器的系統時間相差比較大怎麼辦？ 顯然， 如果系統時間相差比較大的話，以這樣的方式計算主從延遲毫無意義。

在有分歧的情況下， 去查看了一下Mysql的原始碼， 結果發現手冊上的描述居然不那麼準確， 代碼大致如下：
......if ((mi->slave_running ==MYSQL_SLAVE_RUN_CONNECT)&&mi->rli.slave_running){
 

     long time_diff= ((long)(time(0) -mi->rli.last_master_timestamp)       -mi->clock_diff_with_master);
     protocol->store((longlong)(mi->rli.last_master_timestamp?    max(0, time_diff) :0));
      ......}else{
      protocol->store_null();
}
從代碼看， 如果從庫IO線程到主庫的串連有問題或者SQL線程沒有在運行， Seconds_Behind_Master直接返回NULL;否則的話，用從庫當前系統時間減去IO線程處理的最近的事件的時間戳記；代碼裡用mi->clock_diff_with_master來排除系統時間差對計算的影響，那這個值又是怎麼計算來的呢？ 繼續看代碼：

......if (!mysql_real_query(mysql, STRING_WITH_LEN("SELECTUNIX_TIMESTAMP()")) &&(master_res=mysql_store_result(mysql))&&(master_row=mysql_fetch_row(master_res))){       mi->clock_diff_with_master=  (long) (time((time_t*) 0)
-strtoul(master_row[0], 0, 10));}else if(!check_io_slave_killed(mi->io_thd, mi,NULL)){       mi->clock_diff_with_master= 0;       ......}

原來這個值是通過在主庫上執行SELECT UNIX_TIMESTAMP()來取得主庫的系統時間，然後去減從庫的當前系統時間。

原來系統時間差還真的對主從同步延遲參數Seconds_Behind_Master有影響。
轉載：http://hi.baidu.com/zhencaishu/blog/item/355f8c1078d537f5c2ce79c2.html
在今天的一次主從部署中，發現主從庫所在系統時間差別較大時（差別20天左右），同步的效能也非常差。但沒有找到證據，暫且如此懷疑，以後留意

問題描述：

程式上表現為對 主庫 更新操作之後，從 從庫 查詢資料沒發生改變。懷疑是主從庫同步延遲導致。上從庫查看主從同步狀態，發現Seconds_Behind_Master時間長達一千多秒。正常情況下主從庫延時個十幾秒還可以容忍，一千多秒顯然就有問題了麼。。。

 

問題分析：

我們在一個MYSQL執行個體上建立了四五個Database，其中一個Database資料量和壓力都比較大，從 從庫的processlist可以看到從庫在處理日誌時經常發生lock的狀況，但是lock只是壓力大database為何會影響到其他database也延遲呢？

 

原來從庫是單線程處理同步處理記錄，也就是說無論多少個database都是通過一個線程去執行更新操作，所以主從庫同步延遲的時間不是針對database的，是針對一個MYSQL執行個體的。

 

 

那麼，為何從庫在處理日誌時會發生lock的狀態呢？

 

一般我們都將主從庫讀寫分離，主庫負責寫操作，從庫負責讀操作。而一般的web應用讀資料的操作要遠遠大於寫資料的量，所以我們在主庫上幾乎看不到因為更新資料導致的lock。那麼從庫的lock怎麼發生的呢？

 

[c-sharp]
view plaincopyprint?

1. 對MyISAM表的讀操作（加讀鎖），不會阻塞其他進程對同一表的讀請求，但會阻塞對同一表的寫請求。只有當讀鎖釋放後，才會執行其它進程的寫操作。  
2. 對MyISAM表的寫操作（加寫鎖），會阻塞其他進程對同一表的讀和寫操作，只有當寫鎖釋放後，才會執行其它進程的讀寫操作。  

 

從上面可以看出，我們在select的時候預設是會阻塞寫請求的，當一個表資料量到達了千萬層級，那麼執行一個select很有可能就會變得比較費勁，再加上一定的壓力，不斷地select操作，雖然讀資料不會受到影響，但是卻阻塞了從庫處理同步處理記錄的操作。長此以往。。。可想而知。。。

 

問題處理：

1.首先一個MYSQL執行個體不要建立太多database，否則一旦其中一個庫壓力大經常被鎖，會導致所有庫同步都延遲，你傷不起啊。。。

2.壓力較大的情況下使用幾個從庫值得考量，如果使用多個從庫也是可以適當緩解上面lock的情況發生。