解決MySQL同步延遲

背景

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

MySQL Version：5.7

Storage Engine：TokuDB

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

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

圖1 binlog列表

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

網(wǎng)絡(luò)

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

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

機器性能

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

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

從機磁盤有問題？磁盤、raid卡、調(diào)度策略有問題的情況下，有時會出現(xiàn)單個IO延遲很高的情況，比如raid卡電池充放電的時候，在沒有設(shè)置強行write back的情況下得會將write back模式修改為write through。 使 用iostat命令查看DB數(shù)據(jù)盤的IO情況，是否是單個IO的執(zhí)行時間很長，塊大小和磁盤隊列情況等，可以比較一下DB盤的IO調(diào)度規(guī)則以及塊大小的設(shè)置等。使用iostat查看IO運行情況，如圖2所示。

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

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

大事務(wù)

圖2 查看IO運行情況

圖3 單線程

是否是經(jīng)常會有大事務(wù)？這個可能廣大DBA們會遇到比較多，比如在RBR模式下，執(zhí)行帶有大量的delete操作，或者在MBR模式下刪除的時候添加了不確定語句（類似limit），又或者一個表的alter操作等，都會導(dǎo)致延遲情況的發(fā)生。這種通過查看processlist相關(guān)信息以及使用mysqlbinlog查看binlog中的SQL就能快速進行確認。這個設(shè)想也被排除。

鎖

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

參數(shù)

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

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

多線程

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

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

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

當(dāng)你看到是圖3這種結(jié)果的時候，恭喜你，你使用的是單線程。使用下面那行命令改造成多線程復(fù)制：

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

改造后如圖4所示。本來就已經(jīng)是多線程復(fù)制了，因此問題的根源也不在是否開啟多線程復(fù)制上。但是當(dāng)我使用show processlist查看復(fù)制狀態(tài)的時候，大多數(shù)情況下發(fā)現(xiàn)只有1個SQL線程在執(zhí)行，如圖5所示。可以發(fā)現(xiàn)，基本都是一個線程在執(zhí)行，那么可以初步判定是多線程的威力沒有得到很好的發(fā)揮，為了更有力地說明問題，想辦法統(tǒng)計出來每個同步線程使用的比率。統(tǒng)計方法如下:

1.將線上從機相關(guān)統(tǒng)計打開（出于性能考慮默認是關(guān)閉的），打開方法可以如下如下SQL：

圖4 多線程

圖5 查看復(fù)制狀態(tài)

圖5 查看復(fù)制狀態(tài)

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)建一個查看各個同步線程使用量的視圖，代碼如下：

USE test;

CREATE VIEW rep_thread_count AS SELECT a.THREAD_ID AS THREAD_ID,a.COUNT_STAR AS COUNT_STAR FROM performance_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.一段時間后，統(tǒng)計各個同步線程的使用比率，SQL如下:

SELECT SUM(COUNT_STAR) FROMrep_thread_count INTO @total;

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

結(jié)果如下 ：58.15、10.53、7.77、6.66、5.88、5.39、5.08、4.57。

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

組提交

我們不妨從多線程同步的原理來思考，在5.7中，多線程復(fù)制的功能有很大的改善，支持LOGICAL_CLOCK的方式，在這種方式下，并發(fā)執(zhí)行的多個事務(wù)只要能在同一時刻commit，就說明線程之間沒有鎖沖突，那么master就可以將這一組的事務(wù)標(biāo)記并在slave機器上安全的進行并發(fā)執(zhí)行。因此，可以盡可能地使所有線程能在同一時刻提交，這樣就能很大程度上提升從機的執(zhí)行的并行度，從而減少從機的延遲。

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

binlog_group_commit_sync_delay

#參 數(shù) 說 明 見：https://dev.mysql.com/doc/refman/5.7/en/replication-optionsbinary-log.html#sysvar_binlog_group_commit_sync_delay

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

binlog_group_commit_sync_no_delay_count

#參 數(shù) 說 明 見：https://dev.mysql.com/doc/refman/5.7/en/replication-optionsbinary-log.html#sysvar_binlog_group_commit_sync_no_delay_count

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

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

SET GLOBAL binlog_group_commit_sync_delay= 1000000;

SET GLOBAL binlog_group_commit_sync_no_delay_count = 20;

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

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

設(shè)置完這兩個參數(shù)后，發(fā)現(xiàn)并發(fā)復(fù)制瞬間提升了好多，很多時候8個線程都能跑滿。于是將線程調(diào)整到16個。運行一段事件后，再次統(tǒng)計各個同步線程的使用比率，發(fā)現(xiàn)并發(fā)度提升了非常多，新的比率為：15.17、11.68、10.35、9.18、7.96、6.68、5.80、4.97、4.38、4.00、3.66、3.51、3.32、3.24、3.11、3.02。

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

總結(jié)

最后，簡單總結(jié)一下：

在遇到主從延遲的問題的時候，可以從如下幾個地方開腦洞，尋找蛛絲馬跡，找到問題的根源，對癥下藥，藥到病除，排查范圍包括但不限于如下幾方面：網(wǎng)絡(luò)方面、性能方面、配置方面（參數(shù)優(yōu)化）、大事務(wù)、鎖、多線程復(fù)制、組提交。

通過上面對整個問題排查的梳理，希望廣大DBA遇到類似復(fù)制延遲的問題都能徹底終結(jié)。