什麼情況會導致MySQL主從複製延遲？

01-12

什麼情況會導致MySQL主從複製延遲？場景一：由於DBA 限制了索引合併，發現隨後主從複製出現問題。有點想不明白為什麼資料庫性能降低會導致延遲？還有其他什麼情況能導致延遲嗎？

楊一回答的很好。

什麼情況會導致MySQL主從複製延遲？場景一：由於DBA 限制了索引合併，發現隨後主從複製出現問題。有點想不明白為什麼資料庫性能降低會導致延遲？還有其他什麼情況能導致延遲嗎？

由於DBA 限制了索引合併》》不知道你說的這個是在主上還是在從上？

發現隨後主從複製出現問題》》我先理解為你說的這個問題是主從延遲

Index merge和主從延時沒有必然關係（至少我是沒有遇到過）。

有點想不明白為什麼資料庫性能降低會導致延遲》》

我理解你說的是從庫的性能下降，整個從庫的性能都下降了，所以從庫執行主庫上的操作時（sql）時，執行的速度也就自然慢了。這個時候有可能會有主庫延遲（也不是必然）。

還有其他什麼情況能導致延遲嗎？》》

首先理解簡單的理解什麼是主從延遲？

我們一般說的主從延遲是指在從庫show slave statusG 看Seconds_Behind_Master這一列的值去看主從延遲情況。（也有一些是應用程序能判斷出來的，這裡我們就不討論）。我們簡單的說一主一從的情況，Seconds_Behind_Master可以簡單的理解為是已經接到的日誌（relay log）和從庫上已經同步到的位置之間的相差的時間。

那如何解決主從延遲？

1 網路的問題可能性較小，我們先忽略

2 接收到的sql在從庫上執行的慢

2.1 從庫硬體配置沒有主庫好

2.2 主庫binlog_format=row(或者主庫的binlog_format=maxed，但是符合轉化為row的條件)，並且對一個沒有索引(有索引的也可能會有這個問題，只是沒這麼嚴重)的列進行了大量的delete、update。

例如一張表有500W，我們通過一條sql對某個沒有索引的列更新50W行記錄，這個在主庫上是對錶進行了一次全表掃描。假設這個操作被轉化為了row模式，row模式時一次只操作一條記錄，這樣的binlog傳輸到從庫之後，從庫需要對這個500W行記錄的表，全表掃描50W次。這個在從庫上操作就需要了更長的時間，主從延遲就會發生

現象和解決方法可以參考本問題中楊一的回答。這個時候一般不好解決，常用兩個方法 1 跳過這個操作，加上合適索引，再補數據（要想跳過需要先stop slave，這個也許要很長時間）。 2 kill-9關掉從庫db，在配置文件中設置跳過主從自動開始，然後開啟從庫--加合適索引--開始同步

2.3 從庫上只有一個sql_thread. 主庫上可能有100個連接對資料庫進行了更改的操作，但是到從庫上之後，只有一個sql_thread進行操作，一個sql_thread可能不能執行太快（跟得上主庫100個連接），也會產生主從延時

官方版mysql 5.5之前，包含5.5 都是只有一個sql_thread。 5.6時有不同庫之間的並行。 5.7 有並行，也就是從庫上可以有多個sql_thread執行操作。

Seconds_Behind_Master這個值也並不是能完全代表主從延遲，有時網路的中斷會在從加上看show slave status時一直都處於沒有變化的狀態，雖然主庫在執行sql，執行binlog的位置一直在變，但是從庫根本沒有接受到binlog.(網上有文章可以參數，如果找不到的話，可以告訴我，我把url發給你，搜索的過程也是學習的過程)

ucloud(UCloud - 專業雲計算服務商)提供的udb(mysql)會自動監控主從之間的延遲的情況，並且達到客戶設置的閥值時，可以直接告警給客戶。你可以試著用下。

QQ 273002188 歡迎一起學習

QQ 群 236941212

oracle,mysql,PG 相互交流

拿之前的文章作為回答吧 http://blog.itpub.net/22664653/viewspace-775928/

一序言

在運維線上M-M 架構的MySQL資料庫時,接收的比較多關於主備延時的報警

check_ins_slave_lag (err_cnt:1)critical-slavelag on ins:3306=39438

相信slave 延遲是MySQL dba 遇到的一個老生長談的問題了。先來分析一下slave延遲帶來的風險

1. 異常情況下,主從HA無法切換。HA 軟體需要檢查數據的一致性，延遲時，主備不一致。

2. 備庫複製hang會導致備份失敗(flush tables with read lock會900s超時）

3. 以 slave 為基準進行的備份，數據不是最新的,而是延遲。

二如何解決

面對此類問題我們如何解決，如何規避？分析一下導致備庫延遲的幾種原因

1. ROW模式無主鍵、無索引或索引區分度不高.有如下特徵

a. show slave status 顯示position一直沒有變

b. show open tables 顯示某個表一直是 in_use 為 1

c. show create table 查看錶結構可以看到無主鍵，或者無任何索引，或者索引區分度很差。

解決方法：

a. 找到表區分度比較高的幾個欄位, 可以使用這個方法判斷：

select count(*) from xx;

select count(*) from (select distinct xx from xxx) t;

如果2個查詢count(*)的結果差不多，說明可以對這些欄位加索引

b. 備庫stop slave;

可能會執行比較久，因為需要回滾事務。

c. 備庫

set sql_log_bin=0;

alter table xx add key xx(xx);

老的版本slave應用binlog時只會選擇第一個索引，需要把新加的索引放在最前面，可以先把老的索引刪掉，建新的索引，再把老的索引建上。可以放到一個sql中執行。

d. 備庫start slave

如果是innodb,可以通過show innodb status來查看 rows_inserted,updated,deleted,selected這幾個指標來判斷。

如果每秒修改的記錄數比較多，說明複製正在以比較快的速度執行。

2 MIXED模式無索引或SQL慢

在從庫上show full processlist 查看到正在執行的SQL。

解決方法：

a. SQL比較簡單, 則檢查是否缺少索引，並添加索引。

b. 另一類是 insert into select from的語句，如果select 里包含group by，多表關聯，可能效率會比較低。

這類可以到主庫把binlog_format改成row。

3 主庫上有大事務,導致從庫延時

現象解析binlog 發現類似於下圖的情況看

解決方法：

與開發溝通,增加緩存,非同步寫入資料庫,減少直接對db的大量寫入。

4. 主庫寫入頻繁,從庫壓力跟不上導致延時

此類原因的主要現象是資料庫的 IUD 操作非常多，slave由於sql_thread單線程的原因追不上主庫。

解決方法：

a 升級從庫的硬體配置,比如ssd,fio.

b 使用@丁奇的預熱工具-relay fetch

在備庫sql線程執行更新之前，預先將相應的數據載入到內存中,並不能提高sql_thread線程執行sql的能力，也不能加快io_thread線程讀取日誌的速度。

c 使用多線程複製阿里MySQL團隊實現的方案--基於行的並行複製。

該方案允許對同一張表進行修改的兩個事務並行執行，只要這兩個事務修改了表中的不同的行。這個方案可以達到事務間更高的並發度，但是局限是必須使用Row格式的binlog。因為只有使用 Row格式的binlog才可以知道一個事務所修改的行的範圍，而使用Statement格式的binlog只能知道修改的表對象。

5. 資料庫中存在大量myisam表，在備份的時候導致slave 延遲

由於xtrabackup 工具備份到最後會執行flash tables with read lock ，對資料庫進行鎖表以便進行一致性備份，然後對於myisam表鎖，會阻礙salve_sql_thread 停滯運行進而導致hang

該問題目前的比較好的解決方式是修改表結構為innodb存儲引擎的表。

6 網路丟包重傳

這個是剛剛遇到的，博客裡面沒有記錄，和網路環境有關。怎麼解決？拉專線，換更好的交換機擴容網路帶寬等等，

常見情況：大事務操作或者事務中大數據的增刪改