怎么解決MySQL主從延遲問題

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

主從延遲的原因

1、某用戶在使用數據庫過程中，出現主從延遲很大的情況，show slave status\G，已經差了60多個binlog了。

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

Relay_Log_File: relay-bin.000010
Relay_Log_Pos: 95133771

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

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

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

1、直接執行delete，SQL會以statement的格式出現，且不會產生主從延遲。

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

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

1、為什么同樣delete語句，直接執行和在procedure里面執行記錄的binlog格式不一樣（ROW格式的binlog導致回放慢，全局設置在mixed模式下，這條SQL應該走的是statement格式，為什么在procedure里執行就變成了ROW格式，怎么樣才能讓這條SQL再procedure里執行變成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內部執行的時候，被MySQL自動加上了NAME_CONST函數，所以導致了以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，再結合perf信息，可以看到從機回放慢是卡在了bitmap_get_next_set()。

看一下bitmap_get_next_set()的代碼。

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

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

try_semi_consistent_read() 這個函數中出現了可疑的循環，這里會調用m_tot_parts次get_next_used_partition。看了一下定義m_tot_parts是分區表的總分區數！！！

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

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

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

解決方案

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

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

• 方案2：修復內核。

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

到此，關于“怎么解決MySQL主從延遲問題”的學習就結束了，希望能夠解決大家的疑惑。理論與實踐的搭配能更好的幫助大家學習，快去試試吧！若想繼續學習更多相關知識，請繼續關注億速云網站，小編會繼續努力為大家帶來更多實用的文章！