MySQL因大事務(wù)導致Insert慢怎么辦

這篇文章主要介紹了因大事務(wù)導致Insert慢怎么辦，具有一定借鑒價(jià)值，感興趣的朋友可以參考下，希望大家閱讀完這篇文章之后大有收獲，下面讓小編帶著(zhù)大家一起了解一下。

【問(wèn)題】

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

【初步分析】

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

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

【分析過(guò)程】

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

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

線(xiàn)程0x519c5940對應的SQL語(yǔ)句如下，已經(jīng)執行18秒

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

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

流程也可以理解如下，

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

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

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

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

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

積壓的分組下事務(wù)集中刷盤(pán)，反應到磁盤(pán)指標上可以看到在問(wèn)題時(shí)間段的disk_write_kbytes指標出現明顯的上升

【優(yōu)化方案】

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

【其他變通方案】

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

3、 更換性能好的磁盤(pán)