高可用資料庫主從複製延時的解決

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等，並考慮主庫寫操作與從庫自身壓力是否過大。

 

UDB的高可用、高效能、便捷易用，可以大量減輕使用者的運維負擔。在使用過程中， UDB團隊也會利用多年累積的運營經驗，幫助使用者及時分析、排查問題原因，並給出合理的解決方法。

 

— END —

本文來自：UCloud技術