怎么解決MySQL主從延遲問題

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

創(chuàng)新互聯(lián)公司是專業(yè)的來賓網(wǎng)站建設(shè)公司，來賓接單;提供成都網(wǎng)站制作、網(wǎng)站建設(shè),網(wǎng)頁設(shè)計(jì),網(wǎng)站設(shè)計(jì),建網(wǎng)站,PHP網(wǎng)站建設(shè)等專業(yè)做網(wǎng)站服務(wù);采用PHP框架,可快速的進(jìn)行來賓網(wǎng)站開發(fā)網(wǎng)頁制作和功能擴(kuò)展;專業(yè)做搜索引擎喜愛的網(wǎng)站,專業(yè)的做網(wǎng)站團(tuán)隊(duì),希望更多企業(yè)前來合作!

主從延遲的原因

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卡在一個點(diǎn)不動了），通過分析relay_log找到這個大事物：是對表A進(jìn)行刪除操作的一個事物。

Relay_Log_File: relay-bin.000010
Relay_Log_Pos: 95133771

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

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

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

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

2、調(diào)用procedure，該delete語句在procedure中執(zhí)行的時(shí)候會變成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í)行的時(shí)候，被MySQL自動加上了NAME_CONST函數(shù)，所以導(dǎo)致了以ROW模式記錄binlog格式。那為什么在procedure中會被改寫成這樣的SQL呢？怎么樣才能讓這條SQL記錄為statement的格式呢？

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

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

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

通過pstack抓取堆棧，找到SQL_thread線程對應(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)該是程序卡在了這個函數(shù)中，大概率是因?yàn)槎啻握{(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。看了一下定義m_tot_parts是分區(qū)表的總分區(qū)數(shù)?。?！

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

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

對比以statement格式回放，從機(jī)的堆棧信息，并不會進(jìn)入bitmap_get_next_set。

解決方案

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

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

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

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

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

新聞名稱：怎么解決MySQL主從延遲問題
文章源于：http://bm7419.com/article0/jdddio.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供商城網(wǎng)站、小程序開發(fā)、網(wǎng)站策劃、定制網(wǎng)站、企業(yè)建站、網(wǎng)站營銷

廣告

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