前言

MySQL主從復制的延時一直是業界困擾已久的問題。延時的出現會降低主從讀寫分離的價值，不利于數據實時性較高的業務使用MySQL。

UDB是UCloud推出的云數據庫服務，上線已達六年，運營了數以萬計的UDB MySQL實例。除了提供高可用、高性能、便捷易用的產品特性，團隊還平均每天幫助用戶解決2-3起MySQL實例主從復制延時的問題。從大量實踐中我們總結了主從復制延時的各種成因和解決方法，現分享于此。

延時問題的重要性

主從復制機制廣泛應用在UDB的內部實現中：UDB創建的從庫和主庫就采用了“主從復制”的數據復制；另外，UDB的主打產品“UDB MySQL高可用實例”，也是采用2個數據庫互為主從的“雙主模式”來進行數據復制，而雙主模式的核心就是主從復制機制。

如果主從復制之間出現延時，就會影響主從數據的一致性。

在高可用復制場景下，我們在UDB高可用容災設計上考慮到，若出現主備數據不一致的場景，默認是不允許進行高可用容災切換的。因為在主備數據不一致的情況下，此時發生容災切換，且在新的主庫寫入了數據，那么從業務角度上，會產生意想不到的嚴重后果。

復制延時問題，不僅在UDB高可用中會帶來不良后果，在只讀從庫的場景下，若從庫產生復制延時，也可能會對業務造成一定影響，比如在業務上表現為讀寫不一致——新增/修改數據查不到等現象。

由此可見，主從復制的延時問題在數據庫運營中需要特別關注。一般來說，DBA在庫上執行'SHOW SLAVE STATUS'，并且觀察

‘Seconds_Behind_Master'的值，就能夠了解當前某個數據庫和它的主庫之間的數據復制延時。這個值是如此的重要，因此在UDB的監控界面上，我們將這個值單獨抽取來，設計了“從庫同步延時”監控項，以便于運維人員能夠直接在控制臺上觀察。

生產環境中延時問題的分析及解決

我們將最常見的主從復制延時案例總結為幾類，以下是相關案例的現象描述、原因分析和解決方法匯總。

◆ 案例一：主庫DML請求頻繁

某些用戶在業務高峰期間，特別是對于數據庫主庫有大量的寫請求操作，即大量insert、delete、update等并發操作的情況下，會出現主從復制延時問題。

現象描述

我們通過觀察主庫的寫操作的QPS的值，會看到主庫的寫操作的QPS值突然升高，伴隨主從復制延時的上升，可以判斷是由于主庫DML請求頻繁原因造成的。

如上圖，可以看出，在17:58分左右QPS突增，查看控制臺上的寫相關QPS，也有相應提升。而QPS突增的時間，對應的延時也在逐步上升，如下圖所示。

原因分析

經過分析，我們認為這是由于主庫大量的寫請求操作，在短時間產生了大量的binlog。這些操作需要全部同步到從庫，并且執行，因此產生了主從的數據復制延時。

從深層次分析原因，是因為在業務高峰期間的主庫寫入數據是并發寫入的，而從庫SQL Thread為單線程回放binlog日志，很容易造成relaylog堆積，產生延時。

解決思路

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

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

◆ 案例二：主庫執行大事務

大事務指一個事務的執行，耗時非常長。常見產生大事務的語句有：

使用了大量速度很慢的導入數據語句，比如：INSERT INTO $tb、SELECT * FROM $tb、LOAD DATA INFILE等；
使用了UPDATE、DELETE語句，對于一個很大的表進行全表的UPDATE和DELETE等。
當這個事務在從庫執行回放執行操作時，就有可能會產生主從復制延時。

現象描述

我們從SHOW SLAVE STATUS的結果進行分析，會發現 Exec_Master_Log_Pos 字段一直未變，且second_behinds_master持續增加，而 Slave_SQL_Running_State 字段的值為”Reading event from the relay log”；同時，分析主庫binlog，看主庫當前執行的事務，會發現有一些大事務，這樣基本可以判定是執行大事務的原因導致的主從復制延時。

原因分析

當大事務記錄入binlog并同步到從庫之后，從庫執行這個事務的操作耗時也非常長，這段時間，就會產生主從復制延時。

舉個例子，假如主庫花費200s更新了一張大表，在主從庫配置相近的情況下，從庫也需要花幾乎同樣的時間更新這張大表，此時從庫延時開始堆積，后續的events無法更新。

解決思路

對于這種情況引起的主從復制延時，我們的改進方法是：拆分大事務語句到若干小事務中，這樣能夠進行及時提交，減小主從復制延時。

◆ 案例三：主庫對大表執行DDL語句

DDL全稱為 Data Definition Language ，指一些對表結構進行修改操作的語句，比如，對表加一個字段或者加一個索引等等。當DDL對主庫大表執行DDL語句的情況下，可能會產生主從復制延時。

現象描述

從現象上，如果從庫執行SHOW SLAVE STATUS的輸出中，檢查Exec_Master_Log_Pos一直未動，在排除主庫執行大事務的情況下，那么就有可能是在執行大表的 DDL。這一點結合分析主庫binlog，看主庫當前執行的事務就可以進行確認。

DDL語句的執行情況，可以進一步細分現象來更好地判斷：

1.DDL未開始，被阻塞，這時SHOW SLAVE STATUS的結果能檢查到Slave_SQL_Running_State為waiting for table metadata lock，且Exec_Master_Log_Pos不變；

2.DDL正在執行，SQL Thread單線程應用導致延時增加。這種情況下觀察SHOW SLAVE STATU的結果能發現Slave_SQL_Running_State為altering table，而Exec_Master_Log_Pos不變。

如果有上述的現象，那么很有可能主庫對大表執行DDL語句，同步到從庫并在從庫回放時，就產生了主從復制延時。

原因分析

DDL導致的主從復制延時的原因和大事務類似，也是因為從庫執行DDL的binlog較慢而產生了主從復制延時。

解決思路

遇到這種情況，我們主要通過SHOW PROCESSLIST或對information_schema.innodb_trx做查詢，來找到阻塞DDL語句，并KILL掉相關查詢，讓DDL正常在從庫執行。

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

避免業務高峰，盡量安排在業務低峰期執行 ；

set sql_log_bin=0后，分別在主從庫上手動執行DDL（此操作對于某些DDL操作會造成數據不一致，請務必嚴格測試），這一條如果用戶使用云數據庫UDB，可以聯系UCloud UDB運維團隊進行協助操作。

◆ 案例四：主庫與從庫配置不一致

如果主庫和從庫使用了不同的計算資源和存儲資源，或者使用了不同的內核調教參數，可能會造成主從不一致。

現象描述

我們會詳細比對主庫和從庫的性能監控數據，如果發現監控數據差異巨大，結合查看主從的各個配置情況，即可作出明確判斷。

原因分析

各種硬件或者資源的配置差異都有可能導致主從的性能差異，從而導致主從復制延時發生：

硬件上：比如，主庫實例服務器使用SSD磁盤，而從庫實例服務器使用普通SAS盤，那么主庫產生的寫入操作在從庫上不能馬上消化掉，就產生了主從復制延時；
配置上：比如，RAID卡寫策略不一致、OS內核參數設置不一致、MySQL落盤策略不一致等，都是可能的原因。

解決思路

考慮盡量統一DB機器的配置（包括硬件及選項參數）。甚至對于某些OLAP業務，從庫實例硬件配置需要略高于主庫。

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

如果數據庫的表缺少主鍵或者合適索引，在主從復制的binlog_format設置為'row'的情況下，可能會產生主從復制延時。

現象描述

我們進行數據庫檢查時，會發現：

觀察SHOW SLAVE STATUS的輸出，發現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%（無讀業務時），IO壓力不大。

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

原因分析

在主從復制的binlog_format設置為'row'的情況下，比如有這樣的一個場景，主庫更新一張500萬表中的20萬行數據。binlog在row格式下，記錄到binlog的為20萬次update操作，也就是每次操作更新1條記錄。如果這條語句恰好有不好的執行計劃，如發生全表掃描，那么每一條update語句需要全表掃描。此時SQL Thread重放將特別慢，造成嚴重的主從復制延時。

解決思路

這種情況下，我們會去檢查表結構，保證每個表都有顯式自增主鍵，并協助用戶建立合適索引。

◆ 案例六：從庫自身壓力過大

有時候，從庫性能壓力很大的情況下，跟不上主庫的更新速度，就產生了主從復制延時。

現象描述

觀察數據庫實例時，會發現CPU負載過高，IO利用率過高等現象，這些導致SQL Thread應用過慢。這樣就可以判斷是因為從庫自身壓力過大引起主從復制延時。

原因分析

部分UCloud用戶對于數據庫的主從會使用讀寫分離模式，讀請求大部分在從庫上執行。在業務有大量讀請求的場景下，從庫會產生比主庫大得多的性能壓力。有的用戶甚至會在從庫運行十分耗費計算資源的OLAP業務，這也對從庫造成了更高的性能挑戰，這些都會造成主從復制的延時。

解決思路

這種情況下，我們會建議用戶建立更多從庫，打散讀請求，降低現有從庫實例的壓力。對于OLAP業務來說，可以專門建立一個從庫來做OLAP業務，并對這個從庫，允許適當的主從復制延時。

總結

在使用MySQL的主從復制模式進行數據復制時，主從復制延時是一個需要考量的關鍵因素。它會影響數據的一致性，進而影響數據庫高可用的容災切換。

在遇到數據庫之間出現主從復制延時的情況下，我們團隊基于過往經驗，歸納出以下方法與流程來協助排查問題：

通過SHOW SLAVE STATUS與SHOW PROCESSLIST查看現在從庫的情況。（順便也可排除在從庫備份時的類似原因）；

若Exec_Master_Log_Pos不變，考慮大事務、DDL、無主鍵，檢查主庫對應的binlog及position即可；

若Exec_Master_Log_Pos變化，延時逐步增加，考慮從庫機器負載，如IO、CPU等，并考慮主庫寫操作與從庫自身壓力是否過大。

本文來自：UCloud技術，本文由 UCloud 資深專家丁順張蘇寧分享。

好了，以上就是這篇文章的全部內容了，希望本文的內容對大家的學習或者工作具有一定的參考學習價值，謝謝大家對腳本之家的支持。