MySQL主從復(fù)制的延時(shí)一直是業(yè)界困擾已久的問(wèn)題。延時(shí)的出現(xiàn)會(huì)降低主從讀寫(xiě)分離的價(jià)值，不利于數(shù)據(jù)實(shí)時(shí)性較高的業(yè)務(wù)使用MySQL。

UDB是UCloud推出的云數(shù)據(jù)庫(kù)服務(wù)，上線已達(dá)六年，運(yùn)營(yíng)了數(shù)以萬(wàn)計(jì)的UDB MySQL實(shí)例。除了提供高可用、高性能、便捷易用的產(chǎn)品特性，團(tuán)隊(duì)還平均每天幫助用戶解決2-3起MySQL實(shí)例主從復(fù)制延時(shí)的問(wèn)題。從大量實(shí)踐中我們總結(jié)了主從復(fù)制延時(shí)的各種成因和解決方法，現(xiàn)分享于此。

延時(shí)問(wèn)題的重要性

主從復(fù)制機(jī)制廣泛應(yīng)用在UDB的內(nèi)部實(shí)現(xiàn)中：UDB創(chuàng)建的從庫(kù)和主庫(kù)就采用了“主從復(fù)制”的數(shù)據(jù)復(fù)制；另外，UDB的主打產(chǎn)品“UDB MySQL高可用實(shí)例”，也是采用2個(gè)數(shù)據(jù)庫(kù)互為主從的“雙主模式”來(lái)進(jìn)行數(shù)據(jù)復(fù)制，而雙主模式的核心就是主從復(fù)制機(jī)制。

如果主從復(fù)制之間出現(xiàn)延時(shí)，就會(huì)影響主從數(shù)據(jù)的一致性。

在高可用復(fù)制場(chǎng)景下，我們?cè)赨DB高可用容災(zāi)設(shè)計(jì)上考慮到，若出現(xiàn)主備數(shù)據(jù)不一致的場(chǎng)景，默認(rèn)是不允許進(jìn)行高可用容災(zāi)切換的。因?yàn)樵谥鱾鋽?shù)據(jù)不一致的情況下，此時(shí)發(fā)生容災(zāi)切換，且在新的主庫(kù)寫(xiě)入了數(shù)據(jù)，那么從業(yè)務(wù)角度上，會(huì)產(chǎn)生意想不到的嚴(yán)重后果。

復(fù)制延時(shí)問(wèn)題，不僅在UDB高可用中會(huì)帶來(lái)不良后果，在只讀從庫(kù)的場(chǎng)景下，若從庫(kù)產(chǎn)生復(fù)制延時(shí)，也可能會(huì)對(duì)業(yè)務(wù)造成一定影響，比如在業(yè)務(wù)上表現(xiàn)為讀寫(xiě)不一致——新增/修改數(shù)據(jù)查不到等現(xiàn)象。

由此可見(jiàn)，主從復(fù)制的延時(shí)問(wèn)題在數(shù)據(jù)庫(kù)運(yùn)營(yíng)中需要特別關(guān)注。一般來(lái)說(shuō)，DBA在庫(kù)上執(zhí)行’SHOW SLAVE STATUS’，并且觀察

‘Seconds_Behind_Master’的值，就能夠了解當(dāng)前某個(gè)數(shù)據(jù)庫(kù)和它的主庫(kù)之間的數(shù)據(jù)復(fù)制延時(shí)。這個(gè)值是如此的重要，因此在UDB的監(jiān)控界面上，我們將這個(gè)值單獨(dú)抽取來(lái)，設(shè)計(jì)了“從庫(kù)同步延時(shí)”監(jiān)控項(xiàng)，以便于運(yùn)維人員能夠直接在控制臺(tái)上觀察。

生產(chǎn)環(huán)境中延時(shí)問(wèn)題的分析及解決

我們將最常見(jiàn)的主從復(fù)制延時(shí)案例總結(jié)為幾類，以下是相關(guān)案例的現(xiàn)象描述、原因分析和解決方法匯總。

◆ 案例一：主庫(kù)DML請(qǐng)求頻繁

某些用戶在業(yè)務(wù)高峰期間，特別是對(duì)于數(shù)據(jù)庫(kù)主庫(kù)有大量的寫(xiě)請(qǐng)求操作，即大量insert、delete、update等并發(fā)操作的情況下，會(huì)出現(xiàn)主從復(fù)制延時(shí)問(wèn)題。

現(xiàn)象描述

我們通過(guò)觀察主庫(kù)的寫(xiě)操作的QPS的值，會(huì)看到主庫(kù)的寫(xiě)操作的QPS值突然升高，伴隨主從復(fù)制延時(shí)的上升，可以判斷是由于主庫(kù)DML請(qǐng)求頻繁原因造成的。

如上圖，可以看出，在17:58分左右QPS突增，查看控制臺(tái)上的寫(xiě)相關(guān)QPS，也有相應(yīng)提升。而QPS突增的時(shí)間，對(duì)應(yīng)的延時(shí)也在逐步上升，如下圖所示。

原因分析

經(jīng)過(guò)分析，我們認(rèn)為這是由于主庫(kù)大量的寫(xiě)請(qǐng)求操作，在短時(shí)間產(chǎn)生了大量的binlog。這些操作需要全部同步到從庫(kù)，并且執(zhí)行，因此產(chǎn)生了主從的數(shù)據(jù)復(fù)制延時(shí)。

從深層次分析原因，是因?yàn)樵跇I(yè)務(wù)高峰期間的主庫(kù)寫(xiě)入數(shù)據(jù)是并發(fā)寫(xiě)入的，而從庫(kù)SQL Thread為單線程回放binlog日志，很容易造成relaylog堆積，產(chǎn)生延時(shí)。

解決思路

如果是MySQL 5.7以下的版本，可以做分片(sharding)，通過(guò)水平擴(kuò)展(scale out)的方法打散寫(xiě)請(qǐng)求，提升寫(xiě)請(qǐng)求寫(xiě)入binlog的并行度。

如果是MySQL 5.7以上的版本，在MySQL 5.7，使用了基于邏輯時(shí)鐘(Group Commit)的并行復(fù)制。而在MySQL 8.0，使用了基于Write Set的并行復(fù)制。這兩種方案都能夠提升回放binlog的性能，減少延時(shí)。

◆ 案例二：主庫(kù)執(zhí)行大事務(wù)

大事務(wù)指一個(gè)事務(wù)的執(zhí)行，耗時(shí)非常長(zhǎng)。常見(jiàn)產(chǎn)生大事務(wù)的語(yǔ)句有：

?使用了大量速度很慢的導(dǎo)入數(shù)據(jù)語(yǔ)句，比如：INSERT INTO $tb、SELECT * FROM $tb、LOAD DATA INFILE等；

?使用了UPDATE、DELETE語(yǔ)句，對(duì)于一個(gè)很大的表進(jìn)行全表的UPDATE和DELETE等。

當(dāng)這個(gè)事務(wù)在從庫(kù)執(zhí)行回放執(zhí)行操作時(shí)，就有可能會(huì)產(chǎn)生主從復(fù)制延時(shí)。

現(xiàn)象描述

我們從SHOW SLAVE STATUS的結(jié)果進(jìn)行分析，會(huì)發(fā)現(xiàn) Exec_Master_Log_Pos 字段一直未變，且second_behinds_master持續(xù)增加，而 Slave_SQL_Running_State 字段的值為”Reading event from the relay log”；同時(shí)，分析主庫(kù)binlog，看主庫(kù)當(dāng)前執(zhí)行的事務(wù)，會(huì)發(fā)現(xiàn)有一些大事務(wù)，這樣基本可以判定是執(zhí)行大事務(wù)的原因?qū)е碌闹鲝膹?fù)制延時(shí)。

原因分析

當(dāng)大事務(wù)記錄入binlog并同步到從庫(kù)之后，從庫(kù)執(zhí)行這個(gè)事務(wù)的操作耗時(shí)也非常長(zhǎng)，這段時(shí)間，就會(huì)產(chǎn)生主從復(fù)制延時(shí)。

舉個(gè)例子，假如主庫(kù)花費(fèi)200s更新了一張大表，在主從庫(kù)配置相近的情況下，從庫(kù)也需要花幾乎同樣的時(shí)間更新這張大表，此時(shí)從庫(kù)延時(shí)開(kāi)始堆積，后續(xù)的events無(wú)法更新。

解決思路

對(duì)于這種情況引起的主從復(fù)制延時(shí)，我們的改進(jìn)方法是：拆分大事務(wù)語(yǔ)句到若干小事務(wù)中，這樣能夠進(jìn)行及時(shí)提交，減小主從復(fù)制延時(shí)。

◆ 案例三：主庫(kù)對(duì)大表執(zhí)行DDL語(yǔ)句

DDL全稱為 Data Definition Language ，指一些對(duì)表結(jié)構(gòu)進(jìn)行修改操作的語(yǔ)句，比如，對(duì)表加一個(gè)字段或者加一個(gè)索引等等。當(dāng)DDL對(duì)主庫(kù)大表執(zhí)行DDL語(yǔ)句的情況下，可能會(huì)產(chǎn)生主從復(fù)制延時(shí)。

現(xiàn)象描述

從現(xiàn)象上，如果從庫(kù)執(zhí)行SHOW SLAVE STATUS的輸出中，檢查Exec_Master_Log_Pos一直未動(dòng)，在排除主庫(kù)執(zhí)行大事務(wù)的情況下，那么就有可能是在執(zhí)行大表的 DDL。這一點(diǎn)結(jié)合分析主庫(kù)binlog，看主庫(kù)當(dāng)前執(zhí)行的事務(wù)就可以進(jìn)行確認(rèn)。

DDL語(yǔ)句的執(zhí)行情況，可以進(jìn)一步細(xì)分現(xiàn)象來(lái)更好地判斷：

1. DDL未開(kāi)始，被阻塞，這時(shí)SHOW SLAVE STATUS的結(jié)果能檢查到Slave_SQL_Running_State為waiting for table metadata lock，且Exec_Master_Log_Pos不變；

2. DDL正在執(zhí)行，SQL Thread單線程應(yīng)用導(dǎo)致延時(shí)增加。這種情況下觀察SHOW SLAVE STATU的結(jié)果能發(fā)現(xiàn)Slave_SQL_Running_State為altering table，而Exec_Master_Log_Pos不變。

如果有上述的現(xiàn)象，那么很有可能主庫(kù)對(duì)大表執(zhí)行DDL語(yǔ)句，同步到從庫(kù)并在從庫(kù)回放時(shí)，就產(chǎn)生了主從復(fù)制延時(shí)。

原因分析

DDL導(dǎo)致的主從復(fù)制延時(shí)的原因和大事務(wù)類似，也是因?yàn)閺膸?kù)執(zhí)行DDL的binlog較慢而產(chǎn)生了主從復(fù)制延時(shí)。

解決思路

遇到這種情況，我們主要通過(guò)SHOW PROCESSLIST或?qū)nformation_schema.innodb_trx做查詢，來(lái)找到阻塞DDL語(yǔ)句，并KILL掉相關(guān)查詢，讓DDL正常在從庫(kù)執(zhí)行。

DDL本身造成的延時(shí)難以避免，建議考慮：

?避免業(yè)務(wù)高峰，盡量安排在業(yè)務(wù)低峰期執(zhí)行 ；

?set sql_log_bin=0后，分別在主從庫(kù)上手動(dòng)執(zhí)行DDL（此操作對(duì)于某些DDL操作會(huì)造成數(shù)據(jù)不一致，請(qǐng)務(wù)必嚴(yán)格測(cè)試），這一條如果用戶使用云數(shù)據(jù)庫(kù)UDB，可以聯(lián)系UCloud UDB運(yùn)維團(tuán)隊(duì)進(jìn)行協(xié)助操作。

◆ 案例四：主庫(kù)與從庫(kù)配置不一致

如果主庫(kù)和從庫(kù)使用了不同的計(jì)算資源和存儲(chǔ)資源，或者使用了不同的內(nèi)核調(diào)教參數(shù)，可能會(huì)造成主從不一致。

現(xiàn)象描述

我們會(huì)詳細(xì)比對(duì)主庫(kù)和從庫(kù)的性能監(jiān)控?cái)?shù)據(jù)，如果發(fā)現(xiàn)監(jiān)控?cái)?shù)據(jù)差異巨大，結(jié)合查看主從的各個(gè)配置情況，即可作出明確判斷。

原因分析

各種硬件或者資源的配置差異都有可能導(dǎo)致主從的性能差異，從而導(dǎo)致主從復(fù)制延時(shí)發(fā)生：

?硬件上：比如，主庫(kù)實(shí)例服務(wù)器使用SSD磁盤(pán)，而從庫(kù)實(shí)例服務(wù)器使用普通SAS盤(pán)，那么主庫(kù)產(chǎn)生的寫(xiě)入操作在從庫(kù)上不能馬上消化掉，就產(chǎn)生了主從復(fù)制延時(shí)；

?配置上：比如，RAID卡寫(xiě)策略不一致、OS內(nèi)核參數(shù)設(shè)置不一致、MySQL落盤(pán)策略不一致等，都是可能的原因。

解決思路

考慮盡量統(tǒng)一DB機(jī)器的配置（包括硬件及選項(xiàng)參數(shù)）。甚至對(duì)于某些OLAP業(yè)務(wù)，從庫(kù)實(shí)例硬件配置需要略高于主庫(kù)。

◆ 案例五：表缺乏主鍵或合適索引

如果數(shù)據(jù)庫(kù)的表缺少主鍵或者合適索引，在主從復(fù)制的binlog_format設(shè)置為’row’的情況下，可能會(huì)產(chǎn)生主從復(fù)制延時(shí)。

現(xiàn)象描述

我們進(jìn)行數(shù)據(jù)庫(kù)檢查時(shí)，會(huì)發(fā)現(xiàn)：

?觀察SHOW SLAVE STATUS的輸出，發(fā)現(xiàn)Slave_SQL_Running_State為Reading event from the relay log；

?SHOW OPEN TABLES WHERE in_use=1的表一直存在；

?觀察SHOW SLAVE STATUS的Exec_Master_Log_Pos字段不變；

?mysqld進(jìn)程的CPU接近100%（無(wú)讀業(yè)務(wù)時(shí)），IO壓力不大。

這些現(xiàn)象出現(xiàn)的情況下，可以認(rèn)為很可能有表缺乏主鍵或唯一索引。

原因分析

在主從復(fù)制的binlog_format設(shè)置為’row’的情況下，比如有這樣的一個(gè)場(chǎng)景，主庫(kù)更新一張500萬(wàn)表中的20萬(wàn)行數(shù)據(jù)。binlog在row格式下，記錄到binlog的為20萬(wàn)次update操作，也就是每次操作更新1條記錄。如果這條語(yǔ)句恰好有不好的執(zhí)行計(jì)劃，如發(fā)生全表掃描，那么每一條update語(yǔ)句需要全表掃描。此時(shí)SQL Thread重放將特別慢，造成嚴(yán)重的主從復(fù)制延時(shí)。

解決思路

這種情況下，我們會(huì)去檢查表結(jié)構(gòu)，保證每個(gè)表都有顯式自增主鍵，并協(xié)助用戶創(chuàng)建合適索引。

◆ 案例六：從庫(kù)自身壓力過(guò)大

有時(shí)候，從庫(kù)性能壓力很大的情況下，跟不上主庫(kù)的更新速度，就產(chǎn)生了主從復(fù)制延時(shí)。

現(xiàn)象描述

觀察數(shù)據(jù)庫(kù)實(shí)例時(shí)，會(huì)發(fā)現(xiàn)CPU負(fù)載過(guò)高，IO利用率過(guò)高等現(xiàn)象，這些導(dǎo)致SQL Thread應(yīng)用過(guò)慢。這樣就可以判斷是因?yàn)閺膸?kù)自身壓力過(guò)大引起主從復(fù)制延時(shí)。

原因分析

部分UCloud用戶對(duì)于數(shù)據(jù)庫(kù)的主從會(huì)使用讀寫(xiě)分離模式，讀請(qǐng)求大部分在從庫(kù)上執(zhí)行。在業(yè)務(wù)有大量讀請(qǐng)求的場(chǎng)景下，從庫(kù)會(huì)產(chǎn)生比主庫(kù)大得多的性能壓力。有的用戶甚至?xí)趶膸?kù)運(yùn)行十分耗費(fèi)計(jì)算資源的OLAP業(yè)務(wù)，這也對(duì)從庫(kù)造成了更高的性能挑戰(zhàn)，這些都會(huì)造成主從復(fù)制的延時(shí)。

解決思路

這種情況下，我們會(huì)建議用戶創(chuàng)建更多從庫(kù)，打散讀請(qǐng)求，降低現(xiàn)有從庫(kù)實(shí)例的壓力。對(duì)于OLAP業(yè)務(wù)來(lái)說(shuō)，可以專門(mén)創(chuàng)建一個(gè)從庫(kù)來(lái)做OLAP業(yè)務(wù)，并對(duì)這個(gè)從庫(kù)，允許適當(dāng)?shù)闹鲝膹?fù)制延時(shí)。

總結(jié)

在使用MySQL的主從復(fù)制模式進(jìn)行數(shù)據(jù)復(fù)制時(shí)，主從復(fù)制延時(shí)是一個(gè)需要考量的關(guān)鍵因素。它會(huì)影響數(shù)據(jù)的一致性，進(jìn)而影響數(shù)據(jù)庫(kù)高可用的容災(zāi)切換。

在遇到數(shù)據(jù)庫(kù)之間出現(xiàn)主從復(fù)制延時(shí)的情況下，我們團(tuán)隊(duì)基于過(guò)往經(jīng)驗(yàn)，歸納出以下方法與流程來(lái)協(xié)助排查問(wèn)題：

?通過(guò)SHOW SLAVE STATUS與SHOW PROCESSLIST查看現(xiàn)在從庫(kù)的情況。（順便也可排除在從庫(kù)備份時(shí)的類似原因）；

?若Exec_Master_Log_Pos不變，考慮大事務(wù)、DDL、無(wú)主鍵，檢查主庫(kù)對(duì)應(yīng)的binlog及position即可；

?若Exec_Master_Log_Pos變化，延時(shí)逐步增加，考慮從庫(kù)機(jī)器負(fù)載，如IO、CPU等，并考慮主庫(kù)寫(xiě)操作與從庫(kù)自身壓力是否過(guò)大。

UDB的高可用、高性能、便捷易用，可以大量減輕使用者的運(yùn)維負(fù)擔(dān)。在使用過(guò)程中， UDB團(tuán)隊(duì)也會(huì)利用多年累積的運(yùn)營(yíng)經(jīng)驗(yàn)，幫助用戶及時(shí)分析、排查問(wèn)題原因，并給出合理的解決方法。