国产免费观看青青草原网站_久久精品视频色悠悠_影音先锋激情5566_日本一區二區三區免費高清在線_麻豆精品一区综合av_丰满的大屁股一区二区_男女啪啪免费网站_草莓视频app在线观看下载_午夜寂寞少妇AA片_混乱的生物课月老师后续视频

 
您現(xiàn)在的位置:首頁 ? 知識庫 ? 系統(tǒng)集成 ? 數(shù)據(jù)庫維護(hù) 數(shù)據(jù)庫維護(hù)
這套方法論,徹底終結(jié)MySQL同步延遲問題
發(fā)布日期:2018-02-03

作者介紹 

張秀云,網(wǎng)名飛鴻無痕,現(xiàn)任職于騰訊,負(fù)責(zé)騰訊金融數(shù)據(jù)庫的運(yùn)維和優(yōu)化工作。2007年開始從事運(yùn)維方面的工作,經(jīng)歷過網(wǎng)絡(luò)管理員、Linux運(yùn)維工程師、DBA、分布式存儲運(yùn)維等多個IT職位。對Linux運(yùn)維、MySQL數(shù)據(jù)庫、分布式存儲有豐富的經(jīng)驗。簡書地址:

https://www.jianshu.com/u/9346dc2e9d3e

 

作為一名DBA,我們在工作中會經(jīng)常遇到一些MySQL主從同步延遲的問題,這些同步慢的問題,其實原因非常多:可能是主從的網(wǎng)絡(luò)問題導(dǎo)致的,可能是網(wǎng)絡(luò)帶寬問題導(dǎo)致的,可能是大事務(wù)導(dǎo)致的,也可能是單線程復(fù)制導(dǎo)致的延遲。

 

近期筆者遇到了一個很典型的同步延遲問題,在此將分析過程寫出來,希望能夠為廣大DBA在排查同步延遲問題時提供比較系統(tǒng)的方法論。

 

一、背景 

 

最近有一組DB出現(xiàn)比較大的延遲,這組DB是專門用來存儲監(jiān)控數(shù)據(jù)的,每分鐘會使用Load Data的方式導(dǎo)入大量的數(shù)據(jù)。為了節(jié)省空間,將原來使用壓縮表的InnoDB引擎轉(zhuǎn)換成了TokuDB引擎,使用的版本和引擎如下:

 

MySQL Version 5.7

Storage Engine TokuDB

 

轉(zhuǎn)換后,發(fā)現(xiàn)主從延遲逐漸增大,基本每天落后主機(jī)大概50binlog左右,大概延遲7.5個小時左右的數(shù)據(jù),主機(jī)每天大概產(chǎn)生160binlog,binlog列表如下圖所示:

 


 

由于對該業(yè)務(wù)非常熟悉,因此很快就定位到造成主從同步延遲的原因,并很快就解決了延遲的問題。這里不直接說解決辦法,而是想描述一套完整的解決主從延遲問題的思考方式,和大家一起來系統(tǒng)的做一些思考。

 

帶著問題去思考延遲的根本原因和解決辦法,這也許會更有意義。授人以魚,不如授人以漁。接下來我們就一起開腦洞吧。

 

二、思考

 

首先,既然產(chǎn)生了主從延遲,就說明在從機(jī)上的消費(fèi)速度趕不上主機(jī)binlog產(chǎn)生的速度。我們先來思考一下可能的原因,并根據(jù)現(xiàn)場的蛛絲馬跡去驗證猜想的正確性。其實所謂的問題排查,就是提出可能問題猜想,然后不斷去證明的過程。不同的是,每個人的經(jīng)驗不同,排查的質(zhì)量也不盡頭相同,僅此而已。

 

1、網(wǎng)絡(luò)

 

網(wǎng)絡(luò)可能導(dǎo)致主從延遲的問題,比如主機(jī)或者從機(jī)的帶寬打滿、主從之間網(wǎng)絡(luò)延遲很大,有可能會導(dǎo)致主上的binlog沒有全量傳輸?shù)綇臋C(jī),造成延遲。

 

筆者的那組DBIO線程已經(jīng)將對應(yīng)的binlog近乎實時地拉取到了從機(jī)DB上,基本排除網(wǎng)絡(luò)導(dǎo)致的延遲,還可以結(jié)合網(wǎng)絡(luò)質(zhì)量相關(guān)監(jiān)控來進(jìn)一步確認(rèn)是網(wǎng)絡(luò)的問題。

 

2、機(jī)器性能

 

從機(jī)使用了爛機(jī)器?

 

之前有遇到過有的業(yè)務(wù)從機(jī)使用了很爛的機(jī)器,導(dǎo)致的主從延遲。比如主機(jī)使用SSD,而從機(jī)還是使用的SATA。從機(jī)用爛機(jī)器的觀念需要改改,隨著DB自動切換的需求越來越高,尤其是筆者所在的金融行業(yè),從機(jī)至少不要比主機(jī)配置差。

 

從機(jī)高負(fù)載?

 

有很多業(yè)務(wù)會在從機(jī)上做統(tǒng)計,把從機(jī)服務(wù)器搞成高負(fù)載,從而造成從機(jī)延遲很大的情況,這種使用top命令即可快速發(fā)現(xiàn)。

 

從機(jī)磁盤有問題?

 

磁盤、Raid卡、調(diào)度策略有問題的情況下,有時會出現(xiàn)單個IO延遲很高的情況,比如Raid卡電池充放電時,在沒有設(shè)置強(qiáng)行write back的情況下得會將write back模式修改為write through。

 

使用iostat命令查看DB數(shù)據(jù)盤的IO情況,是否是單個IO的執(zhí)行時間很長、塊大小和磁盤隊列情況等,可以比較一下DB盤的IO調(diào)度規(guī)則以及塊大小的設(shè)置等。

 

使用iostat查看IO運(yùn)行情況:

 


 

IO情況看也沒什么問題,單個IO延遲很小,IOPS很低,寫帶寬也不大。調(diào)度規(guī)則(cat /sys/block/fioa/queue/scheduler)和塊大小等和主機(jī)設(shè)置是一樣的,排除掉磁盤的問題。

 

從運(yùn)行指標(biāo)看,機(jī)器負(fù)載很低,機(jī)器性能也可以排除。

 

3、大事務(wù)

 

是否經(jīng)常會有大事務(wù)?

 

這個可能DBA們會遇到比較多,比如在RBR模式下,執(zhí)行帶有大量的Delete操作,或者在MBR模式下刪除時添加了不確定語句(類似limit)或一個表的Alter操作等,都會導(dǎo)致延遲情況的發(fā)生。

 

這種可通過查看Processlist相關(guān)信息,以及使用mysqlbinlog查看binlog中的SQL就能快速進(jìn)行確認(rèn)。這個設(shè)想也被排除。

 

4、鎖

 

鎖沖突問題也可能導(dǎo)致從機(jī)的SQL線程執(zhí)行慢,比如從機(jī)上有一些select  ....  for updateSQL,或者使用了MyISAM引擎等。此類問題,可以通過抓去Processlist以及查看information_schema下面和鎖以及事務(wù)相關(guān)的表來查看。

 

經(jīng)過排查也并未發(fā)現(xiàn)鎖的問題。

 

5、參數(shù)

 

參數(shù)部分使用如果是InnoDB引擎,可以根據(jù)自己的使用環(huán)境調(diào)整innodb_flush_log_at_trx_commit、sync_binlog參數(shù)來提升復(fù)制速度,那組DB使用的TokuDB,則可以優(yōu)化tokudb_commit_sync、tokudb_fsync_log_periodsync_binlog等參數(shù)來做調(diào)整。這些參數(shù)調(diào)整后,復(fù)制的延遲情況會有一些作用。

 

備注:這種調(diào)整可能會影響數(shù)據(jù)的安全性,需要結(jié)合業(yè)務(wù)來考慮。

 

6、多線程

 

多線程問題可能是DBA們遇到最多的問題,之前在5.15.5版本,MySQL的單線程復(fù)制瓶頸就廣受詬病。從5.6開始MySQL正式支持多線程復(fù)制。

 

很容易想到,如果是單線程同步的話,單個線程存在寫入瓶頸,導(dǎo)致主從延遲。那就先調(diào)整為多線程試試效果。

 

可以通過Show Processlist查看是否有多個同步線程,也可以查看參數(shù)的方式查看是否使用多線程(show variables like '%slave_parallel%'

 


 

當(dāng)你看到是上圖這種結(jié)果時,恭喜你,你使用的是單線程。可使用下面那行命令改造成多線程復(fù)制:

STOP SLAVE SQL_THREAD;

SET GLOBAL

slave_parallel_type='LOGICAL_CLOCK';

SET GLOBAL

slave_parallel_workers=8;

START SLAVE SQL_THREAD;

 

改造后如下圖所示:

 


 

我的環(huán)境如上圖所示,本來就已經(jīng)是多線程復(fù)制了,因此問題的根源也不在是否開啟多線程復(fù)制上。但當(dāng)我使用Show Processlist查看復(fù)制狀態(tài)時,大多數(shù)情況下發(fā)現(xiàn)只有1SQL線程在執(zhí)行,如下圖所示:

 


 

通過上面的圖可發(fā)現(xiàn),基本都是一個線程在執(zhí)行,那么可初步判定是多線程的威力沒有得到很好的發(fā)揮,為了更有力地說明問題,想辦法統(tǒng)計出來每個同步線程使用的比率。統(tǒng)計方法如下:

 

1、將線上從機(jī)相關(guān)統(tǒng)計打開(出于性能考慮默認(rèn)是關(guān)閉的),打開方法如下:

UPDATE performance_schema.setup_consumers SET ENABLED = 'YES' WHERE NAME LIKE 'events_transactions%';

 

UPDATE performance_schema.setup_instruments SET ENABLED = 'YES', TIMED = 'YES'WHERE NAME = 'transaction';

 

2、創(chuàng)建一個查看各同步線程使用量的視圖,代碼如下:

 

USE test;

 

CREATE VIEW rep_thread_count AS SELECT a.THREAD_ID AS THREAD_ID,a.COUNT_STAR AS COUNT_STAR FROMperformance_schema.events_transactions_summary_by_thread_by_event_name a WHERE a.THREAD_ID in (SELECT b.THREAD_ID FROM performance_schema.replication_applier_status_by_worker b);

3、一段時間后統(tǒng)計各個同步線程使用比率,SQL如下:

SELECT SUM(COUNT_STAR) FROMrep_thread_count INTO @total;

 

SELECT 100*(COUNT_STAR/@total) AS thread_usage FROMrep_thread_count;

 

結(jié)果如下:



 

從上面的結(jié)果可以看出,絕大多數(shù)情況下,都是一個線程在跑,在監(jiān)控這種存在大量數(shù)據(jù)導(dǎo)入的場景下肯定容易出現(xiàn)瓶頸。如果能提高各個線程并發(fā)執(zhí)行的能力,也許能很好地改善同步延遲的情況,那如何解決呢?

 

7、組提交

 

我們不妨從多線程同步的原理來思考,在5.7中,多線程復(fù)制的功能有很很大的改善,支持LOGICAL_CLOCK的方式,在這種方式下,并發(fā)執(zhí)行的多個事務(wù)只要能在同一時刻commit,就說明線程之間沒有鎖沖突,那么Master就可以將這一組的事務(wù)標(biāo)記并在slave機(jī)器上安全的進(jìn)行并發(fā)執(zhí)行。

 

因此,可以盡可能地使所有線程能在同一時刻提交,這樣就能很大程度上提升從機(jī)的執(zhí)行的并行度,從而減少從機(jī)的延遲。

 

有了這個猜想后,很自然想到了人為控制盡可能多地使所有線程在同一時刻提交,其實官方已經(jīng)給我們提供了類似的參數(shù),參數(shù)如下:

binlog_group_commit_sync_delay

 

備注:這個參數(shù)會對延遲SQL的響應(yīng),對延遲非常敏感的環(huán)境需要特別注意,單位是微秒。

 

參數(shù)說明見:

https://dev.mysql.com/doc/refman/5.7/en/replication-options-binary-log.html#sysvar_binlog_group_commit_sync_delay

 

binlog_group_commit_sync_no_delay_count

 

備注:這個參數(shù)取到了一定的保護(hù)作用,在達(dá)到binlog_group_commit_sync_no_delay_count設(shè)定的值的時候,不管是否達(dá)到了binlog_group_commit_sync_delay設(shè)置定的閥值,都立即進(jìn)行提交。

 

參數(shù)說明見:

https://dev.mysql.com/doc/refman/5.7/en/replication-options-binary-log.html#sysvar_binlog_group_commit_sync_no_delay_count

 

由于是監(jiān)控的DB,主要是load數(shù)據(jù),然后進(jìn)行展示,1秒左右的導(dǎo)入延遲對業(yè)務(wù)沒什么影響,因此將兩個參數(shù)調(diào)整為:

SET GLOBAL binlog_group_commit_sync_delay = 1000000;

SET GLOBAL binlog_group_commit_sync_no_delay_count = 20;

 

備注:這兩個參數(shù)請根據(jù)業(yè)務(wù)特性進(jìn)行調(diào)整,以免造成線上故障。

 

為了防止導(dǎo)入SQL堆積,設(shè)置SET GLOBAL binlog_group_commit_sync_no_delay_count20,在達(dá)到20個事務(wù)時不管是否達(dá)到了1秒都進(jìn)行提交,來減少對業(yè)務(wù)的影響。

 

設(shè)置完這兩個參數(shù)后,發(fā)現(xiàn)并發(fā)復(fù)制瞬間提升了好多,很多時候8個線程都能跑滿。于是將線程調(diào)整到16個。運(yùn)行一段事件后,再次統(tǒng)計各個同步線程的使用比率,發(fā)現(xiàn)并發(fā)度提升了非常多,新的比率如下圖所示:

 


 

通過show slave status查看,發(fā)現(xiàn)從機(jī)延遲越來越小,目前已經(jīng)完全追上,并穩(wěn)定運(yùn)行了一周。

 

三、總結(jié)

 

最后簡單總結(jié)一下,在遇到主從延遲的問題時,可從以下幾個地方開腦洞,尋找蛛絲馬跡,找到問題的根源,對癥下藥才能藥到病除,排查范圍包括但不限于如下幾方面:

網(wǎng)絡(luò)方面

性能方面

配置方面(參數(shù)優(yōu)化)

大事務(wù)

多線程復(fù)制

組提交

 

通過上面對整個問題排查的梳理,希望能夠幫助廣大DBA在遇到類似復(fù)制延遲的問題時徹底終結(jié)問題。

 

參考資料

https://dev.mysql.com/doc/refman/5.7/en/replication-options-binary-log.html

 

https://www.percona.com/blog/2016/02/10/estimating-potential-for-mysql-5-7-parallel-replication/

  • 1.公司登記注冊于2003年1月27日,清遠(yuǎn)市桑達(dá)電子網(wǎng)絡(luò)媒體有限公司
    2.公司2006年起成為清遠(yuǎn)市政府定點(diǎn)協(xié)議供貨商,電子采購供貨商
    3.公司2007年被清遠(yuǎn)市相關(guān)政府部門評為安防行業(yè)狀元
    4.公司2007年起成為長城電腦清遠(yuǎn)如意服務(wù)站(SP368)
    5.公司2007年承建清遠(yuǎn)市橫河路口電子警察工程,開創(chuàng)清遠(yuǎn)電子警察先河。
  • 6.公司2007年起成為IBM合作伙伴、公司2010年底成為金蝶軟件清遠(yuǎn)金牌代理(伙伴編號:30030013)
    7.公司組團(tuán)隊參加南方都市報組織的創(chuàng)富評選,獲廣東80強(qiáng)。公司申請多項軟件著作權(quán)、專利權(quán)
    8.2016年起公司成為粵東西北地區(qū)為數(shù)不多的雙軟企業(yè),確立“讓軟件驅(qū)動世界,讓智能改變生活!"企業(yè)理想
    9.2016-01-29更名為廣東互動電子網(wǎng)絡(luò)媒體有限公司
    10.2021-01-13更名為廣東互動電子有限公司
  • 投資合作咨詢熱線電話:0763-3391888 3323588
  • 做一個負(fù)責(zé)任的百年企業(yè)! 天行健,君子以自強(qiáng)不息;地勢坤,君子以厚德載物;
    為用戶創(chuàng)造價值! 讓軟件驅(qū)動世界; 讓智能改變生活; 超越顧客期望,幫助顧客成功;
    對客戶負(fù)責(zé),對員工負(fù)責(zé),對企業(yè)命運(yùn)負(fù)責(zé)!幫助支持公司的客戶成功;幫助忠誠于公司的員工成功!
  • 聯(lián)系電話:0763-3391888 3323588 3318977
    服務(wù)熱線:18023314222 QQ:529623964
  • 工作QQ:2501204690 商務(wù)QQ: 602045550
    投資及業(yè)務(wù)投訴QQ: 529623964
    微信:小米哥 微信號:qysed3391888
    騰訊微博:桑達(dá)網(wǎng)絡(luò)-基石與起點(diǎn)
  • E-MAIL:222#QYSED.CN ok3391888#163.com (請用@替換#)
在線客服
  • 系統(tǒng)集成咨詢
    點(diǎn)擊這里給我發(fā)消息
  • 網(wǎng)站\微信\軟件咨詢
    點(diǎn)擊這里給我發(fā)消息
  • 售后服務(wù)
    點(diǎn)擊這里給我發(fā)消息
  • 投資合作
    點(diǎn)擊這里給我發(fā)消息