【問題】

INSERT語句是最常見的SQL語句之一，最近有臺(tái)MySQL服務(wù)器不定時(shí)的會(huì)出現(xiàn)并發(fā)線程的告警，從記錄信息來看，有大量insert的慢查詢，執(zhí)行幾十秒，等待flushing log，狀態(tài)query end

【初步分析】

從等待資源來看，大部分時(shí)間消耗在了innodb_log_file階段，懷疑可能是磁盤問題導(dǎo)致，經(jīng)過排查沒有發(fā)現(xiàn)服務(wù)器本身存在硬件問題

后面開啟線程上升時(shí)pstack的自動(dòng)采集，定位MySQL線程等待的位置。

【分析過程】

部署了pstack的自動(dòng)抓取后，出現(xiàn)過6次thread concurrency >=50的告警（每次告警時(shí)會(huì)有大量的慢查詢產(chǎn)生），有3次抓到了現(xiàn)場(chǎng)。

并發(fā)線程升高時(shí)，有50多個(gè)線程卡在Stage_manager::enroll_for函數(shù)，處于group commit階段

線程0x519c5940對(duì)應(yīng)的SQL語句如下，已經(jīng)執(zhí)行18秒

Stage_manager::enroll_for函數(shù)的作用實(shí)現(xiàn)了多個(gè)線程在flush_stage階段的排隊(duì)。簡單來說，對(duì)于一個(gè)分組的事務(wù)，是被leader線程去提交的，其他線程處于排隊(duì)等待狀態(tài)，等待leader線程將該線程的事務(wù)提交完成。

如果第一個(gè)線程執(zhí)行慢，后面的線程都處于等待狀態(tài)，整組事務(wù)無法提交。

流程也可以理解如下，

Session A COMMIT-->拿到鎖-->進(jìn)行binlog寫-->commit完成

Session B COMMIT-->等待鎖--------------------------->拿到鎖-->進(jìn)行binlog寫-->commit完成

第一個(gè)線程為什么執(zhí)行很慢，分析了發(fā)生告警時(shí)間段的日志文件，發(fā)現(xiàn)日志中存在2個(gè)15M和20M的大事務(wù)

查看日志明細(xì)，存在delete from的大事務(wù)刪除語句，約包含23W條記錄，ROW模式下刪除23W條記錄，會(huì)產(chǎn)生大約20M的日志文件，刷盤時(shí)間較長，阻塞了同一個(gè)分組下其他事務(wù)的提交。

事務(wù)的開始時(shí)間與告警時(shí)間吻合

積壓的分組下事務(wù)集中刷盤，反應(yīng)到磁盤指標(biāo)上可以看到在問題時(shí)間段的disk_write_kbytes指標(biāo)出現(xiàn)明顯的上升

【優(yōu)化方案】

1、 建議開發(fā)避免使用delete from 整表的大事務(wù)刪除語句

【其他變通方案】

2、 Binlog 記錄的ROW模式下會(huì)產(chǎn)生大量的日志，改為MIXED模式，理論上也可以解決問題

3、 更換性能好的磁盤

總結(jié)

以上就是這篇文章的全部內(nèi)容了，希望本文的內(nèi)容對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，如果有疑問大家可以留言交流，謝謝大家對(duì)本站的支持。

國外穩(wěn)定服務(wù)器

版權(quán)聲明：本站文章來源標(biāo)注為YINGSOO的內(nèi)容版權(quán)均為本站所有，歡迎引用、轉(zhuǎn)載，請(qǐng)保持原文完整并注明來源及原文鏈接。禁止復(fù)制或仿造本網(wǎng)站，禁止在非maisonbaluchon.cn所屬的服務(wù)器上建立鏡像，否則將依法追究法律責(zé)任。本站部分內(nèi)容來源于網(wǎng)友推薦、互聯(lián)網(wǎng)收集整理而來，僅供學(xué)習(xí)參考，不代表本站立場(chǎng)，如有內(nèi)容涉嫌侵權(quán)，請(qǐng)聯(lián)系alex-e#qq.com處理。