作為一名DBA，我們?cè)诠ぷ髦袝?huì)經(jīng)常遇到一些MySQL主從同步延遲的問題，這些同步慢的問題，其實(shí)原因非常多：可能是主從的網(wǎng)絡(luò)問題導(dǎo)致的，可能是網(wǎng)絡(luò)帶寬問題導(dǎo)致的，可能是大事務(wù)導(dǎo)致的，也可能是單線程復(fù)制導(dǎo)致的延遲。

近期筆者遇到了一個(gè)很典型的同步延遲問題，在此將分析過(guò)程寫出來(lái)，希望能夠?yàn)閺V大DBA在排查同步延遲問題時(shí)提供比較系統(tǒng)的方法論。

一、背景

最近有一組DB出現(xiàn)比較大的延遲，這組DB是專門用來(lái)存儲(chǔ)監(jiān)控?cái)?shù)據(jù)的，每分鐘會(huì)使用Load Data的方式導(dǎo)入大量的數(shù)據(jù)。為了節(jié)省空間，將原來(lái)使用壓縮表的InnoDB引擎轉(zhuǎn)換成了TokuDB引擎，使用的版本和引擎如下：

MySQL Version： 5.7

Storage Engine： TokuDB

轉(zhuǎn)換后，發(fā)現(xiàn)主從延遲逐漸增大，基本每天落后主機(jī)大概50個(gè)binlog左右，大概延遲7.5個(gè)小時(shí)左右的數(shù)據(jù)，主機(jī)每天大概產(chǎn)生160個(gè)binlog，binlog列表如下圖所示：

由于對(duì)該業(yè)務(wù)非常熟悉，因此很快就定位到造成主從同步延遲的原因，并很快就解決了延遲的問題。這里不直接說(shuō)解決辦法，而是想描述一套完整的解決主從延遲問題的思考方式，和大家一起來(lái)系統(tǒng)的做一些思考。

帶著問題去思考延遲的根本原因和解決辦法，這也許會(huì)更有意義。授人以魚，不如授人以漁。接下來(lái)我們就一起開腦洞吧。

二、思考

首先，既然產(chǎn)生了主從延遲，就說(shuō)明在從機(jī)上的消費(fèi)速度趕不上主機(jī)binlog產(chǎn)生的速度。我們先來(lái)思考一下可能的原因，并根據(jù)現(xiàn)場(chǎng)的蛛絲馬跡去驗(yàn)證猜想的正確性。其實(shí)所謂的問題排查，就是提出可能問題猜想，然后不斷去證明的過(guò)程。不同的是，每個(gè)人的經(jīng)驗(yàn)不同，排查的質(zhì)量也不盡頭相同，僅此而已。

1、網(wǎng)絡(luò)

網(wǎng)絡(luò)可能導(dǎo)致主從延遲的問題，比如主機(jī)或者從機(jī)的帶寬打滿、主從之間網(wǎng)絡(luò)延遲很大，有可能會(huì)導(dǎo)致主上的binlog沒有全量傳輸?shù)綇臋C(jī)，造成延遲。

筆者的那組DB的IO線程已經(jīng)將對(duì)應(yīng)的binlog近乎實(shí)時(shí)地拉取到了從機(jī)DB上，基本排除網(wǎng)絡(luò)導(dǎo)致的延遲，還可以結(jié)合網(wǎng)絡(luò)質(zhì)量相關(guān)監(jiān)控來(lái)進(jìn)一步確認(rèn)是網(wǎng)絡(luò)的問題。

2、機(jī)器性能

• 從機(jī)使用了爛機(jī)器？

之前有遇到過(guò)有的業(yè)務(wù)從機(jī)使用了很爛的機(jī)器，導(dǎo)致的主從延遲。比如主機(jī)使用SSD，而從機(jī)還是使用的SATA。從機(jī)用爛機(jī)器的觀念需要改改，隨著DB自動(dòng)切換的需求越來(lái)越高，尤其是筆者所在的金融行業(yè)，從機(jī)至少不要比主機(jī)配置差。

• 從機(jī)高負(fù)載？

有很多業(yè)務(wù)會(huì)在從機(jī)上做統(tǒng)計(jì)，把從機(jī)服務(wù)器搞成高負(fù)載，從而造成從機(jī)延遲很大的情況，這種使用top命令即可快速發(fā)現(xiàn)。

• 從機(jī)磁盤有問題？

磁盤、Raid卡、調(diào)度策略有問題的情況下，有時(shí)會(huì)出現(xiàn)單個(gè)IO延遲很高的情況，比如Raid卡電池充放電時(shí)，在沒有設(shè)置強(qiáng)行write back的情況下得會(huì)將write back模式修改為write through。

使用iostat命令查看DB數(shù)據(jù)盤的IO情況，是否是單個(gè)IO的執(zhí)行時(shí)間很長(zhǎng)、塊大小和磁盤隊(duì)列情況等，可以比較一下DB盤的IO調(diào)度規(guī)則以及塊大小的設(shè)置等。

使用iostat查看IO運(yùn)行情況：

從IO情況看也沒什么問題，單個(gè)IO延遲很小，IOPS很低，寫帶寬也不大。調(diào)度規(guī)則（cat /sys/block/fioa/queue/scheduler）和塊大小等和主機(jī)設(shè)置是一樣的，排除掉磁盤的問題。

從運(yùn)行指標(biāo)看，機(jī)器負(fù)載很低，機(jī)器性能也可以排除。

3、大事務(wù)

• 是否經(jīng)常會(huì)有大事務(wù)？

這個(gè)可能DBA們會(huì)遇到比較多，比如在RBR模式下，執(zhí)行帶有大量的Delete操作，或者在MBR模式下刪除時(shí)添加了不確定語(yǔ)句（類似limit）或一個(gè)表的Alter操作等，都會(huì)導(dǎo)致延遲情況的發(fā)生。

這種可通過(guò)查看Processlist相關(guān)信息，以及使用mysqlbinlog查看binlog中的SQL就能快速進(jìn)行確認(rèn)。這個(gè)設(shè)想也被排除。

4、鎖

鎖沖突問題也可能導(dǎo)致從機(jī)的SQL線程執(zhí)行慢，比如從機(jī)上有一些select  ....  for update的SQL，或者使用了MyISAM引擎等。此類問題，可以通過(guò)抓去Processlist以及查看information_schema下面和鎖以及事務(wù)相關(guān)的表來(lái)查看。

經(jīng)過(guò)排查也并未發(fā)現(xiàn)鎖的問題。

5、參數(shù)

參數(shù)部分使用如果是InnoDB引擎，可以根據(jù)自己的使用環(huán)境調(diào)整innodb_flush_log_at_trx_commit、sync_binlog參數(shù)來(lái)提升復(fù)制速度，那組DB使用的TokuDB，則可以優(yōu)化tokudb_commit_sync、tokudb_fsync_log_period、sync_binlog等參數(shù)來(lái)做調(diào)整。這些參數(shù)調(diào)整后，復(fù)制的延遲情況會(huì)有一些作用。

備注：這種調(diào)整可能會(huì)影響數(shù)據(jù)的安全性，需要結(jié)合業(yè)務(wù)來(lái)考慮。

6、多線程

多線程問題可能是DBA們遇到最多的問題，之前在5.1和5.5版本，MySQL的單線程復(fù)制瓶頸就廣受詬病。從5.6開始MySQL正式支持多線程復(fù)制。

很容易想到，如果是單線程同步的話，單個(gè)線程存在寫入瓶頸，導(dǎo)致主從延遲。那就先調(diào)整為多線程試試效果。

可以通過(guò)Show Processlist查看是否有多個(gè)同步線程，也可以查看參數(shù)的方式查看是否使用多線程（show variables like '%slave_parallel%'）

當(dāng)你看到是上圖這種結(jié)果時(shí)，恭喜你，你使用的是單線程?？墒褂孟旅婺切忻罡脑斐啥嗑€程復(fù)制：

 
 
 
 

  
  
  
  
STOP SLAVE SQL_THREAD; 
  
  
  
  
 
  
  
  
  
SET GLOBAL  
  
  
  
  
 
  
  
  
  
slave_parallel_type='LOGICAL_CLOCK'; 
  
  
  
  
 
  
  
  
  
SET GLOBAL  
  
  
  
  
 
  
  
  
  
slave_parallel_workers=8; 
  
  
  
  
 
  
  
  
  
START SLAVE SQL_THREAD; 
 
 
 
 

改造后如下圖所示：

我的環(huán)境如上圖所示，本來(lái)就已經(jīng)是多線程復(fù)制了，因此問題的根源也不在是否開啟多線程復(fù)制上。但當(dāng)我使用Show Processlist查看復(fù)制狀態(tài)時(shí)，大多數(shù)情況下發(fā)現(xiàn)只有1個(gè)SQL線程在執(zhí)行，如下圖所示：

通過(guò)上面的圖可發(fā)現(xiàn)，基本都是一個(gè)線程在執(zhí)行，那么可初步判定是多線程的威力沒有得到很好的發(fā)揮，為了更有力地說(shuō)明問題，想辦法統(tǒng)計(jì)出來(lái)每個(gè)同步線程使用的比率。統(tǒng)計(jì)方法如下:

1、將線上從機(jī)相關(guān)統(tǒng)計(jì)打開（出于性能考慮默認(rèn)是關(guān)閉的），打開方法如下： 

 
 
 
 

  
  
  
  
UPDATE performance_schema.setup_consumers SET ENABLED = 'YES' WHERE NAME LIKE 'events_transactions%'; 
  
  
  
  
 
  
  
  
  
UPDATE performance_schema.setup_instruments SET ENABLED = 'YES', TIMED = 'YES'WHERE NAME = 'transaction'; 
 
 
 
 

2、創(chuàng)建一個(gè)查看各同步線程使用量的視圖，代碼如下： 

 
 
 
 

  
  
  
  
USE test; 
  
  
  
  
 
  
  
  
  
CREATE VIEW rep_thread_count AS SELECT a.THREAD_ID AS THREAD_ID,a.COUNT_STAR AS COUNT_STAR FROMperformance_schema.events_transactions_summary_by_thread_by_event_name a WHERE a.THREAD_ID in (SELECT b.THREAD_ID FROM performance_schema.replication_applier_status_by_worker b); 
 
 
 
 

 3、一段時(shí)間后統(tǒng)計(jì)各個(gè)同步線程使用比率，SQL如下： 

 
 
 
 

  
  
  
  
SELECT SUM(COUNT_STAR) FROMrep_thread_count INTO @total; 
  
  
  
  
 
  
  
  
  
SELECT 100*(COUNT_STAR/@total) AS thread_usage FROMrep_thread_count; 
 
 
 
 

結(jié)果如下：

從上面的結(jié)果可以看出，絕大多數(shù)情況下，都是一個(gè)線程在跑，在監(jiān)控這種存在大量數(shù)據(jù)導(dǎo)入的場(chǎng)景下肯定容易出現(xiàn)瓶頸。如果能提高各個(gè)線程并發(fā)執(zhí)行的能力，也許能很好地改善同步延遲的情況，那如何解決呢？

7、組提交

我們不妨從多線程同步的原理來(lái)思考，在5.7中，多線程復(fù)制的功能有很很大的改善，支持LOGICAL_CLOCK的方式，在這種方式下，并發(fā)執(zhí)行的多個(gè)事務(wù)只要能在同一時(shí)刻commit，就說(shuō)明線程之間沒有鎖沖突，那么Master就可以將這一組的事務(wù)標(biāo)記并在slave機(jī)器上安全的進(jìn)行并發(fā)執(zhí)行。

因此，可以盡可能地使所有線程能在同一時(shí)刻提交，這樣就能很大程度上提升從機(jī)的執(zhí)行的并行度，從而減少?gòu)臋C(jī)的延遲。

有了這個(gè)猜想后，很自然想到了人為控制盡可能多地使所有線程在同一時(shí)刻提交，其實(shí)官方已經(jīng)給我們提供了類似的參數(shù)，參數(shù)如下： 

 
 
 
 

  
  
  
  
binlog_group_commit_sync_delay 
 
 
 
 

備注：這個(gè)參數(shù)會(huì)對(duì)延遲SQL的響應(yīng)，對(duì)延遲非常敏感的環(huán)境需要特別注意，單位是微秒。

參數(shù)說(shuō)明見：

https://dev.mysql.com/doc/refman/5.7/en/replication-options-binary-log.html#sysvar_binlog_group_commit_sync_delay 

 
 
 
 

  
  
  
  
binlog_group_commit_sync_no_delay_count 
 
 
 
 

備注：這個(gè)參數(shù)取到了一定的保護(hù)作用，在達(dá)到binlog_group_commit_sync_no_delay_count設(shè)定的值的時(shí)候，不管是否達(dá)到了binlog_group_commit_sync_delay設(shè)置定的閥值，都立即進(jìn)行提交。

參數(shù)說(shuō)明見：

https://dev.mysql.com/doc/refman/5.7/en/replication-options-binary-log.html#sysvar_binlog_group_commit_sync_no_delay_count

由于是監(jiān)控的DB，主要是load數(shù)據(jù)，然后進(jìn)行展示，1秒左右的導(dǎo)入延遲對(duì)業(yè)務(wù)沒什么影響，因此將兩個(gè)參數(shù)調(diào)整為： 

 
 
 
 

  
  
  
  
SET GLOBAL binlog_group_commit_sync_delay = 1000000; 
  
  
  
  
 
  
  
  
  
SET GLOBAL binlog_group_commit_sync_no_delay_count = 20; 
 
 
 
 

備注：這兩個(gè)參數(shù)請(qǐng)根據(jù)業(yè)務(wù)特性進(jìn)行調(diào)整，以免造成線上故障。

為了防止導(dǎo)入SQL堆積，設(shè)置SET GLOBAL binlog_group_commit_sync_no_delay_count為20，在達(dá)到20個(gè)事務(wù)時(shí)不管是否達(dá)到了1秒都進(jìn)行提交，來(lái)減少對(duì)業(yè)務(wù)的影響。

設(shè)置完這兩個(gè)參數(shù)后，發(fā)現(xiàn)并發(fā)復(fù)制瞬間提升了好多，很多時(shí)候8個(gè)線程都能跑滿。于是將線程調(diào)整到16個(gè)。運(yùn)行一段事件后，再次統(tǒng)計(jì)各個(gè)同步線程的使用比率，發(fā)現(xiàn)并發(fā)度提升了非常多，新的比率如下圖所示：

通過(guò)show slave status查看，發(fā)現(xiàn)從機(jī)延遲越來(lái)越小，目前已經(jīng)完全追上，并穩(wěn)定運(yùn)行了一周。

三、總結(jié)

最后簡(jiǎn)單總結(jié)一下，在遇到主從延遲的問題時(shí)，可從以下幾個(gè)地方開腦洞，尋找蛛絲馬跡，找到問題的根源，對(duì)癥下藥才能藥到病除，排查范圍包括但不限于如下幾方面：

• 網(wǎng)絡(luò)方面
• 性能方面
• 配置方面（參數(shù)優(yōu)化）
• 大事務(wù)
• 鎖
• 多線程復(fù)制
• 組提交

通過(guò)上面對(duì)整個(gè)問題排查的梳理，希望能夠幫助廣大DBA在遇到類似復(fù)制延遲的問題時(shí)徹底終結(jié)問題。

參考資料

• https://dev.mysql.com/doc/refman/5.7/en/replication-options-binary-log.html
• https://www.percona.com/blog/2016/02/10/estimating-potential-for-mysql-5-7-parallel-replication/

名稱欄目：這套方法論，徹底終結(jié)MySQL同步延遲問題！
本文地址：http://uogjgqi.cn/article/djjgohi.html