主頁 > 知識庫 > MySQL數(shù)據(jù)庫主從復(fù)制延時超長的解決方法

MySQL數(shù)據(jù)庫主從復(fù)制延時超長的解決方法

熱門標(biāo)簽:電話機(jī)器人的價格多少錢一個月 徐涇鎮(zhèn)騰訊地圖標(biāo)注 天津公司外呼系統(tǒng)軟件 福建外呼電銷機(jī)器人加盟 自己做地圖標(biāo)注需要些什么 400電話申請廠家現(xiàn)貨 昌德訊外呼系統(tǒng) 百度地圖標(biāo)注要什么軟件 中國地圖標(biāo)注公司

前言

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

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

延時問題的重要性

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

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

在高可用復(fù)制場景下,我們在UDB高可用容災(zāi)設(shè)計上考慮到,若出現(xiàn)主備數(shù)據(jù)不一致的場景,默認(rèn)是不允許進(jìn)行高可用容災(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)過分析,我們認(rèn)為這是由于主庫大量的寫請求操作,在短時間產(chǎn)生了大量的binlog。這些操作需要全部同步到從庫,并且執(zhí)行,因此產(chǎn)生了主從的數(shù)據(jù)復(fù)制延時。

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

解決思路

如果是MySQL 5.7以下的版本,可以做分片(sharding),通過水平擴(kuò)展(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語句,對于一個很大的表進(jìn)行全表的UPDATE和DELETE等。
當(dāng)這個事務(wù)在從庫執(zhí)行回放執(zhí)行操作時,就有可能會產(chǎn)生主從復(fù)制延時。

現(xiàn)象描述

我們從SHOW SLAVE STATUS的結(jié)果進(jìn)行分析,會發(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ù)制延時,我們的改進(jìn)方法是:拆分大事務(wù)語句到若干小事務(wù)中,這樣能夠進(jìn)行及時提交,減小主從復(fù)制延時。

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

DDL全稱為 Data Definition Language ,指一些對表結(jié)構(gòu)進(jìn)行修改操作的語句,比如,對表加一個字段或者加一個索引等等。當(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ù)就可以進(jìn)行確認(rèn)。

DDL語句的執(zhí)行情況,可以進(jìn)一步細(xì)分現(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運維團(tuán)隊進(jìn)行協(xié)助操作。

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

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

現(xiàn)象描述

我們會詳細(xì)比對主庫和從庫的性能監(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機(jī)器的配置(包括硬件及選項參數(shù))。甚至對于某些OLAP業(yè)務(wù),從庫實例硬件配置需要略高于主庫。

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

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

現(xiàn)象描述

我們進(jì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進(jìn)程的CPU接近100%(無讀業(yè)務(wù)時),IO壓力不大。

這些現(xiàn)象出現(xiàn)的情況下,可以認(rè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負(fù)載過高,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ù)制模式進(jìn)行數(shù)據(jù)復(fù)制時,主從復(fù)制延時是一個需要考量的關(guān)鍵因素。它會影響數(shù)據(jù)的一致性,進(jìn)而影響數(shù)據(jù)庫高可用的容災(zāi)切換。

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

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

若Exec_Master_Log_Pos不變,考慮大事務(wù)、DDL、無主鍵,檢查主庫對應(yīng)的binlog及position即可;

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

本文來自:UCloud技術(shù),本文由 UCloud 資深專家丁順張?zhí)K寧分享。

好了,以上就是這篇文章的全部內(nèi)容了,希望本文的內(nèi)容對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價值,謝謝大家對腳本之家的支持。

您可能感興趣的文章:
  • mysql 主從復(fù)制如何跳過報錯
  • MySQL主從復(fù)制延遲原因以及解決方案
  • mysql主從復(fù)制配置過程
  • 全面解讀MySQL主從復(fù)制,從原理到安裝配置
  • MySQL 4種常用的主從復(fù)制架構(gòu)
  • 關(guān)于MySQL主從復(fù)制的幾種復(fù)制方式總結(jié)
  • MySQL主從復(fù)制與讀寫分離原理及用法詳解
  • Mysql主從復(fù)制作用和工作原理詳解
  • MYSQL 完全備份、主從復(fù)制、級聯(lián)復(fù)制、半同步小結(jié)
  • MySql主從復(fù)制實現(xiàn)原理及配置

標(biāo)簽:鄂爾多斯 駐馬店 梅河口 北京 陜西 荊門 昌都 黔西

巨人網(wǎng)絡(luò)通訊聲明:本文標(biāo)題《MySQL數(shù)據(jù)庫主從復(fù)制延時超長的解決方法》,本文關(guān)鍵詞  MySQL,數(shù)據(jù)庫,主從,復(fù)制,;如發(fā)現(xiàn)本文內(nèi)容存在版權(quán)問題,煩請?zhí)峁┫嚓P(guān)信息告之我們,我們將及時溝通與處理。本站內(nèi)容系統(tǒng)采集于網(wǎng)絡(luò),涉及言論、版權(quán)與本站無關(guān)。
  • 相關(guān)文章
  • 下面列出與本文章《MySQL數(shù)據(jù)庫主從復(fù)制延時超長的解決方法》相關(guān)的同類信息!
  • 本頁收集關(guān)于MySQL數(shù)據(jù)庫主從復(fù)制延時超長的解決方法的相關(guān)信息資訊供網(wǎng)民參考!
  • 推薦文章