怎么解決MySQL主從延遲問題-創(chuàng)新互聯(lián)

這篇文章主要介紹“怎么解決MySQL主從延遲問題”，在日常操作中，相信很多人在怎么解決MySQL主從延遲問題問題上存在疑惑，小編查閱了各式資料，整理出簡單好用的操作方法，希望對大家解答”怎么解決MySQL主從延遲問題”的疑惑有所幫助！接下來，請跟著小編一起來學習吧！

成都創(chuàng)新互聯(lián)是一家朝氣蓬勃的網(wǎng)站建設公司。公司專注于為企業(yè)提供信息化建設解決方案。從事網(wǎng)站開發(fā)，網(wǎng)站制作，網(wǎng)站設計，網(wǎng)站模板，微信公眾號開發(fā)，軟件開發(fā)，小程序設計，十載建站對成都搬家公司等多個方面，擁有豐富設計經(jīng)驗。

主從延遲的原因

1、某用戶在使用數(shù)據(jù)庫過程中，出現(xiàn)主從延遲很大的情況，show slave status\G，已經(jīng)差了60多個binlog了。

2、觀察發(fā)現(xiàn)，應該是卡在一個大事物上面（Retrieved_Gtid_Set一直在上升，但是Executed_Gtid_Set卡在一個點不動了），通過分析relay_log找到這個大事物：是對表A進行刪除操作的一個事物。

Relay_Log_File: relay-bin.000010
Relay_Log_Pos: 95133771

看到這里，感覺又是一例在ROW模式下表沒有主鍵，引起的主從延遲?？纯幢斫Y(jié)構(gòu)確認一下，發(fā)現(xiàn)這張表不小，字段有上百個，有主鍵，且是一張分區(qū)表，分區(qū)很多。這就有意思了！并不是我們碰到過多次的由于ROW模式下沒有主鍵，DML引起的主從延遲（PS：為什么這種情況下會引起延遲？而是有主鍵，且走了二級索引，那為什么回放還會這么慢呢？）。

后來了解到用戶是在存儲過程里面調(diào)用detele語句來進行歸檔數(shù)據(jù)清理，看了一下存儲過程，現(xiàn)在的問題就可以簡化為：在存儲過程中調(diào)用delete語句，走了二級索引刪除有主鍵的分區(qū)表，從機回放延遲。

這個時候，我們需要拆解一下問題，控制好變量，一個一個的查：

1、直接執(zhí)行delete，SQL會以statement的格式出現(xiàn)，且不會產(chǎn)生主從延遲。

2、調(diào)用procedure，該delete語句在procedure中執(zhí)行的時候會變成ROW格式，且會導致延遲。

OK，有以上兩個測試，我們的問題可以聚焦為：

1、為什么同樣delete語句，直接執(zhí)行和在procedure里面執(zhí)行記錄的binlog格式不一樣（ROW格式的binlog導致回放慢，全局設置在mixed模式下，這條SQL應該走的是statement格式，為什么在procedure里執(zhí)行就變成了ROW格式，怎么樣才能讓這條SQL再procedure里執(zhí)行變成statement記錄到binlog里面）。

delete from xxxxx
where update_datetime < DATE_ADD(B_DATE,INTERVAL -1 day)
and DATE_FORMAT(update_datetime,'%i') not in ('00','05','10','15','20','25','30');

通過show processlist，可以看到這條delete在procedure內(nèi)部執(zhí)行的時候，被MySQL自動加上了NAME_CONST函數(shù)，所以導致了以ROW模式記錄binlog格式。那為什么在procedure中會被改寫成這樣的SQL呢？怎么樣才能讓這條SQL記錄為statement的格式呢？

看了MySQL官方在procedure里面的限制描述，MySQL會自動加上NAME_CONST主要是為了從機可以識別到B_DATE這個SP的Local vairable，不至于從機回放的時候報錯。

2、為什么ROW模式的binlog在從庫回放的時候，即使delete的這張表有主鍵也很慢。

我們先看一下SQL線程回放是卡在哪里了？為什么會慢？

通過pstack抓取堆棧，找到SQL_thread線程對應的thread 15，再結(jié)合perf信息，可以看到從機回放慢是卡在了bitmap_get_next_set()。

看一下bitmap_get_next_set()的代碼。

bitmap_get_next_set()都是一些位運算，速度按理來說應該很快。所以不應該是程序卡在了這個函數(shù)中，大概率是因為多次調(diào)用了這個函數(shù)。所以我們再往上層繼續(xù)看代碼。

get_next_used_partition(uint part_id) 直接調(diào)用了bitmap_get_next_set（），繼續(xù)往上看。

try_semi_consistent_read() 這個函數(shù)中出現(xiàn)了可疑的循環(huán)，這里會調(diào)用m_tot_parts次get_next_used_partition?？戳艘幌露xm_tot_parts是分區(qū)表的總分區(qū)數(shù)！?。?/p>

看到這里，就真相大白了。

這個delele的SQL變更的行數(shù)大約在300W行左右，總共的分區(qū)表數(shù)是7200個。那么這里調(diào)用bitmap_get_next_set的次數(shù)就被放大成了216億次！

對比以statement格式回放，從機的堆棧信息，并不會進入bitmap_get_next_set。

解決方案

分析了這么久，怎么處理這么問題呢？

• 方案1：我們最后在SP中強制制定了session的binlog_format=statement，讓這條delete在從機以statement的模式回放，這樣就避免觸發(fā)MySQL中的這個bug。

• 方案2：修復內(nèi)核。

• 方案3：在shell里面去調(diào)度，而不使用存儲過程。

到此，關于“怎么解決MySQL主從延遲問題”的學習就結(jié)束了，希望能夠解決大家的疑惑。理論與實踐的搭配能更好的幫助大家學習，快去試試吧！若想繼續(xù)學習更多相關知識，請繼續(xù)關注創(chuàng)新互聯(lián)-成都網(wǎng)站建設公司網(wǎng)站，小編會繼續(xù)努力為大家?guī)砀鄬嵱玫奈恼拢?/p>

名稱欄目：怎么解決MySQL主從延遲問題-創(chuàng)新互聯(lián)
瀏覽路徑：http://muchs.cn/article26/eijjg.html

成都網(wǎng)站建設公司_創(chuàng)新互聯(lián)，為您提供響應式網(wǎng)站、Google、品牌網(wǎng)站建設、企業(yè)建站、做網(wǎng)站、網(wǎng)站制作

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權請盡快告知，我們將會在第一時間刪除。文章觀點不代表本網(wǎng)站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時需注明來源： 創(chuàng)新互聯(lián)