怎么解決MySQL主從延遲問題

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

主從延遲的原因

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

2、觀察發(fā)現(xiàn)，應(yīng)該是卡在一個大事物上面（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格式，且會導(dǎo)致延遲。

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

1、為什么同樣delete語句，直接執(zhí)行和在procedure里面執(zhí)行記錄的binlog格式不一樣（ROW格式的binlog導(dǎo)致回放慢，全局設(shè)置在mixed模式下，這條SQL應(yīng)該走的是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ù)，所以導(dǎo)致了以ROW模式記錄binlog格式。那為什么在procedure中會被改寫成這樣的SQL呢？怎么樣才能讓這條SQL記錄為statement的格式呢？

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

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

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

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

看一下bitmap_get_next_set()的代碼。

bitmap_get_next_set()都是一些位運算，速度按理來說應(yīng)該很快。所以不應(yīng)該是程序卡在了這個函數(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ù)?。?！

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

這個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：修復(fù)內(nèi)核。

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

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