共計 4383 個字符，預計需要花費 11 分鐘才能閱讀完成。

自動寫代碼機器人，免費開通

這篇文章給大家介紹 MySQL 數(shù)據(jù)庫主從復制延時超長如何解決，內容非常詳細，感興趣的小伙伴們可以參考借鑒，希望對大家能有所幫助。

延時問題的重要性

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

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

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

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

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

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

生產環(huán)境中延時問題的分析及解決

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

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

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

現(xiàn)象描述

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

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

原因分析

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

從深層次分析原因，是因為在業(yè)務高峰期間的主庫寫入數(shù)據(jù)是并發(fā)寫入的，而從庫 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 的性能，減少延時。

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

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

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

現(xiàn)象描述

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

原因分析

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

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

解決思路

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

◆ 案例三：主庫對大表執(zhí)行 DDL 語句

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

現(xiàn)象描述

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

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

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

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

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

原因分析

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

解決思路

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

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

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

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

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

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

現(xiàn)象描述

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

原因分析

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

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

解決思路

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

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

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

現(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è)務時），IO 壓力不大。

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

原因分析

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

解決思路

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

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

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

現(xiàn)象描述

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

原因分析

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

解決思路

這種情況下，我們會建議用戶建立更多從庫，打散讀請求，降低現(xiàn)有從庫實例的壓力。對于 OLAP 業(yè)務來說，可以專門建立一個從庫來做 OLAP 業(yè)務，并對這個從庫，允許適當?shù)闹鲝膹椭蒲訒r。

總結

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

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

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

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

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

關于 MySQL 數(shù)據(jù)庫主從復制延時超長如何解決就分享到這里了，希望以上內容可以對大家有一定的幫助，可以學到更多知識。如果覺得文章不錯，可以把它分享出去讓更多的人看到。