共計(jì) 2203 個(gè)字符，預(yù)計(jì)需要花費(fèi) 6 分鐘才能閱讀完成。
這篇文章主要介紹“怎么解決 MySQL 主從延遲問題”，在日常操作中，相信很多人在怎么解決 MySQL 主從延遲問題問題上存在疑惑，丸趣 TV 小編查閱了各式資料，整理出簡(jiǎn)單好用的操作方法，希望對(duì)大家解答”怎么解決 MySQL 主從延遲問題”的疑惑有所幫助！接下來，請(qǐng)跟著丸趣 TV 小編一起來學(xué)習(xí)吧！
主從延遲的原因
1、某用戶在使用數(shù)據(jù)庫(kù)過程中，出現(xiàn)主從延遲很大的情況，show slave status\G，已經(jīng)差了 60 多個(gè) binlog 了。
2、觀察發(fā)現(xiàn)，應(yīng)該是卡在一個(gè)大事物上面（Retrieved_Gtid_Set 一直在上升，但是 Executed_Gtid_Set 卡在一個(gè)點(diǎn)不動(dòng)了），通過分析 relay_log 找到這個(gè)大事物：是對(duì)表 A 進(jìn)行刪除操作的一個(gè)事物。
Relay_Log_File: relay-bin.000010
Relay_Log_Pos: 95133771看到這里，感覺又是一例在 ROW 模式下表沒有主鍵，引起的主從延遲。看看表結(jié)構(gòu)確認(rèn)一下，發(fā)現(xiàn)這張表不小，字段有上百個(gè)，有主鍵，且是一張分區(qū)表，分區(qū)很多。這就有意思了！并不是我們碰到過多次的由于 ROW 模式下沒有主鍵，DML 引起的主從延遲（PS：為什么這種情況下會(huì)引起延遲？而是有主鍵，且走了二級(jí)索引，那為什么回放還會(huì)這么慢呢？）。
后來了解到用戶是在存儲(chǔ)過程里面調(diào)用 detele 語句來進(jìn)行歸檔數(shù)據(jù)清理，看了一下存儲(chǔ)過程，現(xiàn)在的問題就可以簡(jiǎn)化為：在存儲(chǔ)過程中調(diào)用 delete 語句，走了二級(jí)索引刪除有主鍵的分區(qū)表，從機(jī)回放延遲。
這個(gè)時(shí)候，我們需要拆解一下問題，控制好變量，一個(gè)一個(gè)的查：
1、直接執(zhí)行 delete，SQL 會(huì)以 statement 的格式出現(xiàn)，且不會(huì)產(chǎn)生主從延遲。
2、調(diào)用 procedure，該 delete 語句在 procedure 中執(zhí)行的時(shí)候會(huì)變成 ROW 格式，且會(huì)導(dǎo)致延遲。
OK，有以上兩個(gè)測(cè)試，我們的問題可以聚焦為：
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í)行的時(shí)候，被 MySQL 自動(dòng)加上了 NAME_CONST 函數(shù)，所以導(dǎo)致了以 ROW 模式記錄 binlog 格式。那為什么在 procedure 中會(huì)被改寫成這樣的 SQL 呢？怎么樣才能讓這條 SQL 記錄為 statement 的格式呢？
看了 MySQL 官方在 procedure 里面的限制描述，MySQL 會(huì)自動(dòng)加上 NAME_CONST 主要是為了從機(jī)可以識(shí)別到 B_DATE 這個(gè) SP 的 Local vairable，不至于從機(jī)回放的時(shí)候報(bào)錯(cuò)。
2、為什么 ROW 模式的 binlog 在從庫(kù)回放的時(shí)候，即使 delete 的這張表有主鍵也很慢。
我們先看一下 SQL 線程回放是卡在哪里了？為什么會(huì)慢？
通過 pstack 抓取堆棧，找到 SQL_thread 線程對(duì)應(yīng)的 thread 15，再結(jié)合 perf 信息，可以看到從機(jī)回放慢是卡在了 bitmap_get_next_set()。
看一下 bitmap_get_next_set() 的代碼。
bitmap_get_next_set() 都是一些位運(yùn)算，速度按理來說應(yīng)該很快。所以不應(yīng)該是程序卡在了這個(gè)函數(shù)中，大概率是因?yàn)槎啻握{(diào)用了這個(gè)函數(shù)。所以我們?cè)偻蠈永^續(xù)看代碼。
get_next_used_partition(uint part_id) 直接調(diào)用了 bitmap_get_next_set（），繼續(xù)往上看。
try_semi_consistent_read() 這個(gè)函數(shù)中出現(xiàn)了可疑的循環(huán)，這里會(huì)調(diào)用 m_tot_parts 次 get_next_used_partition。看了一下定義 m_tot_parts 是分區(qū)表的總分區(qū)數(shù)！！！
看到這里，就真相大白了。
這個(gè) delele 的 SQL 變更的行數(shù)大約在 300W 行左右，總共的分區(qū)表數(shù)是 7200 個(gè)。那么這里調(diào)用 bitmap_get_next_set 的次數(shù)就被放大成了 216 億次！
對(duì)比以 statement 格式回放，從機(jī)的堆棧信息，并不會(huì)進(jìn)入 bitmap_get_next_set。
解決方案
分析了這么久，怎么處理這么問題呢？
方案 1：我們最后在 SP 中強(qiáng)制制定了 session 的 binlog_format=statement，讓這條 delete 在從機(jī)以 statement 的模式回放，這樣就避免觸發(fā) MySQL 中的這個(gè) bug。
方案 2：修復(fù)內(nèi)核。
方案 3：在 shell 里面去調(diào)度，而不使用存儲(chǔ)過程。
到此，關(guān)于“怎么解決 MySQL 主從延遲問題”的學(xué)習(xí)就結(jié)束了，希望能夠解決大家的疑惑。理論與實(shí)踐的搭配能更好的幫助大家學(xué)習(xí)，快去試試吧！若想繼續(xù)學(xué)習(xí)更多相關(guān)知識(shí)，請(qǐng)繼續(xù)關(guān)注丸趣 TV 網(wǎng)站，丸趣 TV 小編會(huì)繼續(xù)努力為大家?guī)砀鄬?shí)用的文章！
