MySQL主從復(fù)制的延時一直是業(yè)界困擾已久的問題。延時的出現(xiàn)會降低主從讀寫分離的價值，不利于數(shù)據(jù)實時性較高的業(yè)務(wù)使用MySQL。
UDB是UCloud推出的云數(shù)據(jù)庫服務(wù)，上線已達六年，運營了數(shù)以萬計的UDB MySQL實例。除了提供高可用、高性能、便捷易用的產(chǎn)品特性，團隊還平均每天幫助用戶解決2-3起MySQL實例主從復(fù)制延時的問題。從大量實踐中我們總結(jié)了主從復(fù)制延時的各種成因和解決方法，現(xiàn)分享于此。
延時問題的重要性
主從復(fù)制機制廣泛應(yīng)用在UDB的內(nèi)部實現(xiàn)中：UDB創(chuàng)建的從庫和主庫就采用了“主從復(fù)制”的數(shù)據(jù)復(fù)制；另外，UDB的主打產(chǎn)品“UDB MySQL高可用實例”，也是采用2個數(shù)據(jù)庫互為主從的“雙主模式”來進行數(shù)據(jù)復(fù)制，而雙主模式的核心就是主從復(fù)制機制。
如果主從復(fù)制之間出現(xiàn)延時，就會影響主從數(shù)據(jù)的一致性。
在高可用復(fù)制場景下，我們在UDB高可用容災(zāi)設(shè)計上考慮到，若出現(xiàn)主備數(shù)據(jù)不一致的場景，默認是不允許進行高可用容災(zāi)切換的。因為在主備數(shù)據(jù)不一致的情況下，此時發(fā)生容災(zāi)切換，且在新的主庫寫入了數(shù)據(jù)，那么從業(yè)務(wù)角度上，會產(chǎn)生意想不到的嚴(yán)重后果。
復(fù)制延時問題，不僅在UDB高可用中會帶來不良后果，在只讀從庫的場景下，若從庫產(chǎn)生復(fù)制延時，也可能會對業(yè)務(wù)造成一定影響，比如在業(yè)務(wù)上表現(xiàn)為讀寫不一致——新增/修改數(shù)據(jù)查不到等現(xiàn)象。
由此可見，主從復(fù)制的延時問題在數(shù)據(jù)庫運營中需要特別關(guān)注。一般來說，DBA在庫上執(zhí)行’SHOW SLAVE STATUS’，并且觀察
‘Seconds_Behind_Master’的值，就能夠了解當(dāng)前某個數(shù)據(jù)庫和它的主庫之間的數(shù)據(jù)復(fù)制延時。這個值是如此的重要，因此在UDB的監(jiān)控界面上，我們將這個值單獨抽取來，設(shè)計了“從庫同步延時”監(jiān)控項，以便于運維人員能夠直接在控制臺上觀察。
生產(chǎn)環(huán)境中延時問題的分析及解決
我們將最常見的主從復(fù)制延時案例總結(jié)為幾類，以下是相關(guān)案例的現(xiàn)象描述、原因分析和解決方法匯總。
◆ 案例一：主庫DML請求頻繁
某些用戶在業(yè)務(wù)高峰期間，特別是對于數(shù)據(jù)庫主庫有大量的寫請求操作，即大量insert、delete、update等并發(fā)操作的情況下，會出現(xiàn)主從復(fù)制延時問題。
現(xiàn)象描述
我們通過觀察主庫的寫操作的QPS的值，會看到主庫的寫操作的QPS值突然升高，伴隨主從復(fù)制延時的上升，可以判斷是由于主庫DML請求頻繁原因造成的。
如上圖，可以看出，在17:58分左右QPS突增，查看控制臺上的寫相關(guān)QPS，也有相應(yīng)提升。而QPS突增的時間，對應(yīng)的延時也在逐步上升，如下圖所示。
原因分析
經(jīng)過分析，我們認為這是由于主庫大量的寫請求操作，在短時間產(chǎn)生了大量的binlog。這些操作需要全部同步到從庫，并且執(zhí)行，因此產(chǎn)生了主從的數(shù)據(jù)復(fù)制延時。
從深層次分析原因，是因為在業(yè)務(wù)高峰期間的主庫寫入數(shù)據(jù)是并發(fā)寫入的，而從庫SQL Thread為單線程回放binlog日志，很容易造成relaylog堆積，產(chǎn)生延時。
解決思路
如果是MySQL 5.7以下的版本，可以做分片(sharding)，通過水平擴展(scale out)的方法打散寫請求，提升寫請求寫入binlog的并行度。
如果是MySQL 5.7以上的版本，在MySQL 5.7，使用了基于邏輯時鐘(Group Commit)的并行復(fù)制。而在MySQL 8.0，使用了基于Write Set的并行復(fù)制。這兩種方案都能夠提升回放binlog的性能，減少延時。
◆ 案例二：主庫執(zhí)行大事務(wù)
大事務(wù)指一個事務(wù)的執(zhí)行，耗時非常長。常見產(chǎn)生大事務(wù)的語句有：
?使用了大量速度很慢的導(dǎo)入數(shù)據(jù)語句，比如：INSERT INTO $tb、SELECT * FROM $tb、LOAD DATA INFILE等；
?使用了UPDATE、DELETE語句，對于一個很大的表進行全表的UPDATE和DELETE等。
當(dāng)這個事務(wù)在從庫執(zhí)行回放執(zhí)行操作時，就有可能會產(chǎn)生主從復(fù)制延時。
現(xiàn)象描述
我們從SHOW SLAVE STATUS的結(jié)果進行分析，會發(fā)現(xiàn) Exec_Master_Log_Pos 字段一直未變，且second_behinds_master持續(xù)增加，而 Slave_SQL_Running_State 字段的值為”Reading event from the relay log”；同時，分析主庫binlog，看主庫當(dāng)前執(zhí)行的事務(wù)，會發(fā)現(xiàn)有一些大事務(wù)，這樣基本可以判定是執(zhí)行大事務(wù)的原因?qū)е碌闹鲝膹?fù)制延時。
原因分析
當(dāng)大事務(wù)記錄入binlog并同步到從庫之后，從庫執(zhí)行這個事務(wù)的操作耗時也非常長，這段時間，就會產(chǎn)生主從復(fù)制延時。
舉個例子，假如主庫花費200s更新了一張大表，在主從庫配置相近的情況下，從庫也需要花幾乎同樣的時間更新這張大表，此時從庫延時開始堆積，后續(xù)的events無法更新。
解決思路
對于這種情況引起的主從復(fù)制延時，我們的改進方法是：拆分大事務(wù)語句到若干小事務(wù)中，這樣能夠進行及時提交，減小主從復(fù)制延時。
◆ 案例三：主庫對大表執(zhí)行DDL語句
DDL全稱為 Data Definition Language ，指一些對表結(jié)構(gòu)進行修改操作的語句，比如，對表加一個字段或者加一個索引等等。當(dāng)DDL對主庫大表執(zhí)行DDL語句的情況下，可能會產(chǎn)生主從復(fù)制延時。
現(xiàn)象描述
從現(xiàn)象上，如果從庫執(zhí)行SHOW SLAVE STATUS的輸出中，檢查Exec_Master_Log_Pos一直未動，在排除主庫執(zhí)行大事務(wù)的情況下，那么就有可能是在執(zhí)行大表的 DDL。這一點結(jié)合分析主庫binlog，看主庫當(dāng)前執(zhí)行的事務(wù)就可以進行確認。
DDL語句的執(zhí)行情況，可以進一步細分現(xiàn)象來更好地判斷：
1. DDL未開始，被阻塞，這時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)致延時增加。這種情況下觀察SHOW SLAVE STATU的結(jié)果能發(fā)現(xiàn)Slave_SQL_Running_State為altering table，而Exec_Master_Log_Pos不變。
如果有上述的現(xiàn)象，那么很有可能主庫對大表執(zhí)行DDL語句，同步到從庫并在從庫回放時，就產(chǎn)生了主從復(fù)制延時。
原因分析
DDL導(dǎo)致的主從復(fù)制延時的原因和大事務(wù)類似，也是因為從庫執(zhí)行DDL的binlog較慢而產(chǎn)生了主從復(fù)制延時。
解決思路
遇到這種情況，我們主要通過SHOW PROCESSLIST或?qū)nformation_schema.innodb_trx做查詢，來找到阻塞DDL語句，并KILL掉相關(guān)查詢，讓DDL正常在從庫執(zhí)行。
DDL本身造成的延時難以避免，建議考慮：
?避免業(yè)務(wù)高峰，盡量安排在業(yè)務(wù)低峰期執(zhí)行 ；
?set sql_log_bin=0后，分別在主從庫上手動執(zhí)行DDL（此操作對于某些DDL操作會造成數(shù)據(jù)不一致，請務(wù)必嚴(yán)格測試），這一條如果用戶使用云數(shù)據(jù)庫UDB，可以聯(lián)系UCloud UDB運維團隊進行協(xié)助操作。
◆ 案例四：主庫與從庫配置不一致
如果主庫和從庫使用了不同的計算資源和存儲資源，或者使用了不同的內(nèi)核調(diào)教參數(shù)，可能會造成主從不一致。
現(xiàn)象描述
我們會詳細比對主庫和從庫的性能監(jiān)控數(shù)據(jù)，如果發(fā)現(xiàn)監(jiān)控數(shù)據(jù)差異巨大，結(jié)合查看主從的各個配置情況，即可作出明確判斷。
原因分析
各種硬件或者資源的配置差異都有可能導(dǎo)致主從的性能差異，從而導(dǎo)致主從復(fù)制延時發(fā)生：
?硬件上：比如，主庫實例服務(wù)器使用SSD磁盤，而從庫實例服務(wù)器使用普通SAS盤，那么主庫產(chǎn)生的寫入操作在從庫上不能馬上消化掉，就產(chǎn)生了主從復(fù)制延時；
?配置上：比如，RAID卡寫策略不一致、OS內(nèi)核參數(shù)設(shè)置不一致、MySQL落盤策略不一致等，都是可能的原因。
解決思路
考慮盡量統(tǒng)一DB機器的配置（包括硬件及選項參數(shù)）。甚至對于某些OLAP業(yè)務(wù)，從庫實例硬件配置需要略高于主庫。
◆ 案例五：表缺乏主鍵或合適索引
如果數(shù)據(jù)庫的表缺少主鍵或者合適索引，在主從復(fù)制的binlog_format設(shè)置為’row’的情況下，可能會產(chǎn)生主從復(fù)制延時。
現(xiàn)象描述
我們進行數(shù)據(jù)庫檢查時，會發(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進程的CPU接近100%（無讀業(yè)務(wù)時），IO壓力不大。
這些現(xiàn)象出現(xiàn)的情況下，可以認為很可能有表缺乏主鍵或唯一索引。
原因分析
在主從復(fù)制的binlog_format設(shè)置為’row’的情況下，比如有這樣的一個場景，主庫更新一張500萬表中的20萬行數(shù)據(jù)。binlog在row格式下，記錄到binlog的為20萬次update操作，也就是每次操作更新1條記錄。如果這條語句恰好有不好的執(zhí)行計劃，如發(fā)生全表掃描，那么每一條update語句需要全表掃描。此時SQL Thread重放將特別慢，造成嚴(yán)重的主從復(fù)制延時。
解決思路
這種情況下，我們會去檢查表結(jié)構(gòu)，保證每個表都有顯式自增主鍵，并協(xié)助用戶建立合適索引。
◆ 案例六：從庫自身壓力過大
有時候，從庫性能壓力很大的情況下，跟不上主庫的更新速度，就產(chǎn)生了主從復(fù)制延時。
現(xiàn)象描述
觀察數(shù)據(jù)庫實例時，會發(fā)現(xiàn)CPU負載過高，IO利用率過高等現(xiàn)象，這些導(dǎo)致SQL Thread應(yīng)用過慢。這樣就可以判斷是因為從庫自身壓力過大引起主從復(fù)制延時。
原因分析
部分UCloud用戶對于數(shù)據(jù)庫的主從會使用讀寫分離模式，讀請求大部分在從庫上執(zhí)行。在業(yè)務(wù)有大量讀請求的場景下，從庫會產(chǎn)生比主庫大得多的性能壓力。有的用戶甚至?xí)趶膸爝\行十分耗費計算資源的OLAP業(yè)務(wù)，這也對從庫造成了更高的性能挑戰(zhàn)，這些都會造成主從復(fù)制的延時。
解決思路
這種情況下，我們會建議用戶建立更多從庫，打散讀請求，降低現(xiàn)有從庫實例的壓力。對于OLAP業(yè)務(wù)來說，可以專門建立一個從庫來做OLAP業(yè)務(wù)，并對這個從庫，允許適當(dāng)?shù)闹鲝膹?fù)制延時。
總結(jié)
在使用MySQL的主從復(fù)制模式進行數(shù)據(jù)復(fù)制時，主從復(fù)制延時是一個需要考量的關(guān)鍵因素。它會影響數(shù)據(jù)的一致性，進而影響數(shù)據(jù)庫高可用的容災(zāi)切換。
在遇到數(shù)據(jù)庫之間出現(xiàn)主從復(fù)制延時的情況下，我們團隊基于過往經(jīng)驗，歸納出以下方法與流程來協(xié)助排查問題：
?通過SHOW SLAVE STATUS與SHOW PROCESSLIST查看現(xiàn)在從庫的情況。（順便也可排除在從庫備份時的類似原因）；
?若Exec_Master_Log_Pos不變，考慮大事務(wù)、DDL、無主鍵，檢查主庫對應(yīng)的binlog及position即可；
?若Exec_Master_Log_Pos變化，延時逐步增加，考慮從庫機器負載，如IO、CPU等，并考慮主庫寫操作與從庫自身壓力是否過大。
UDB的高可用、高性能、便捷易用，可以大量減輕使用者的運維負擔(dān)。在使用過程中， UDB團隊也會利用多年累積的運營經(jīng)驗，幫助用戶及時分析、排查問題原因，并給出合理的解決方法。
申請創(chuàng)業(yè)報道，分享創(chuàng)業(yè)好點子。點擊此處，共同探討創(chuàng)業(yè)新機遇！
