MySQL因大事務(wù)導(dǎo)致的Insert慢實例分析
【問題】
INSERT語句是最常見的SQL語句之一，最近有臺MySQL服務(wù)器不定時的會出現(xiàn)并發(fā)線程的告警，從記錄信息來看，有大量insert的慢查詢，執(zhí)行幾十秒，等待flushing log，狀態(tài)query end
【初步分析】
從等待資源來看，大部分時間消耗在了innodb_log_file階段，懷疑可能是磁盤問題導(dǎo)致，經(jīng)過排查沒有發(fā)現(xiàn)服務(wù)器本身存在硬件問題
后面開啟線程上升時pstack的自動采集，定位MySQL線程等待的位置。
【分析過程】
部署了pstack的自動抓取后，出現(xiàn)過6次thread concurrency >=50的告警（每次告警時會有大量的慢查詢產(chǎn)生），有3次抓到了現(xiàn)場。
并發(fā)線程升高時，有50多個線程卡在Stage_manager::enroll_for函數(shù)，處于group commit階段
線程0x519c5940對應(yīng)的SQL語句如下，已經(jīng)執(zhí)行18秒
Stage_manager::enroll_for函數(shù)的作用實現(xiàn)了多個線程在flush_stage階段的排隊。簡單來說，對于一個分組的事務(wù)，是被leader線程去提交的，其他線程處于排隊等待狀態(tài)，等待leader線程將該線程的事務(wù)提交完成。
如果第一個線程執(zhí)行慢，后面的線程都處于等待狀態(tài)，整組事務(wù)無法提交。
流程也可以理解如下，
Session A COMMIT-->拿到鎖-->進(jìn)行binlog寫-->commit完成
Session B COMMIT-->等待鎖--------------------------->拿到鎖-->進(jìn)行binlog寫-->commit完成
第一個線程為什么執(zhí)行很慢，分析了發(fā)生告警時間段的日志文件，發(fā)現(xiàn)日志中存在2個15M和20M的大事務(wù)
查看日志明細(xì)，存在delete from的大事務(wù)刪除語句，約包含23W條記錄，ROW模式下刪除23W條記錄，會產(chǎn)生大約20M的日志文件，刷盤時間較長，阻塞了同一個分組下其他事務(wù)的提交。
事務(wù)的開始時間與告警時間吻合
積壓的分組下事務(wù)集中刷盤，反應(yīng)到磁盤指標(biāo)上可以看到在問題時間段的disk_write_kbytes指標(biāo)出現(xiàn)明顯的上升
【優(yōu)化方案】
1、 建議開發(fā)避免使用delete from 整表的大事務(wù)刪除語句
【其他變通方案】
2、 Binlog 記錄的ROW模式下會產(chǎn)生大量的日志，改為MIXED模式，理論上也可以解決問題
3、 更換性能好的磁盤
總結(jié)
以上就是這篇文章的全部內(nèi)容了，希望本文的內(nèi)容對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價值，如果有疑問大家可以留言交流，謝謝大家對腳本之家的支持。
相關(guān)文章
解析sql語句中l(wèi)eft_join、inner_join中的on與where的區(qū)別
以下是對在sql語句中l(wèi)eft_join、inner_join中的on與where的區(qū)別進(jìn)行了詳細(xì)的分析介紹，需要的朋友可以參考下2013-07-07
這篇文章主要介紹了Mysql中的單表最大記錄是多少問題，具有很好的參考價值，希望對大家有所幫助。如有錯誤或未考慮完全的地方，望不吝賜教2023-02-02
MySQL憑借著出色的性能、低廉的成本、豐富的資源，已經(jīng)成為絕大多數(shù)互聯(lián)網(wǎng)公司的首選關(guān)系型數(shù)據(jù)庫。本文給大家介紹mysql數(shù)據(jù)庫之索引優(yōu)化，感興趣的朋友一起學(xué)習(xí)吧2016-03-03
淺談MySQL中優(yōu)化sql語句查詢常用的30種方法
本篇文章是對MySQL中優(yōu)化sql語句查詢常用的30種方法進(jìn)行了詳細(xì)的分析介紹，需要的朋友參考下2013-06-06
這篇文章主要介紹了MySQL之高可用集群部署及故障切換實現(xiàn)，文中通過示例代碼介紹的非常詳細(xì)，對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價值，需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧2021-04-04
delete、truncate、drop的區(qū)別以及該如何選擇
這篇文章主要給大家介紹了關(guān)于delete、truncate、drop的區(qū)別以及該如何選擇的相關(guān)資料，文中通過示例代碼介紹的非常詳細(xì)，對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價值，需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧2020-11-11
一文學(xué)會Mysql數(shù)據(jù)庫備份與恢復(fù)
數(shù)據(jù)庫備份是在數(shù)據(jù)丟失的情況下能及時恢復(fù)重要數(shù)據(jù),防止數(shù)據(jù)丟失的一種重要手段,下面這篇文章主要給大家介紹了關(guān)于Mysql數(shù)據(jù)庫備份與恢復(fù)的相關(guān)資料,需要的朋友可以參考下2022-05-05
如何把本地mysql遷移到服務(wù)器數(shù)據(jù)庫
這篇文章主要介紹了如何把本地mysql遷移到服務(wù)器數(shù)據(jù)庫,文中通過示例代碼介紹的非常詳細(xì)，對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價值,需要的朋友可以參考下2020-11-11
在本篇文章里小編給大家整理的是一篇關(guān)于mysql中寫判斷語句的方法總結(jié)內(nèi)容，需要的朋友們可以學(xué)習(xí)參考下。2020-09-09
mysql 查詢數(shù)據(jù)庫響應(yīng)時長的方法示例
要查詢MySQL數(shù)據(jù)庫的響應(yīng)時長,通常我們需要測量查詢執(zhí)行的時間,本文主要介紹了mysql 查詢數(shù)據(jù)庫響應(yīng)時長的方法示例,具有一定的參考價值,感興趣的可以了解一下2024-06-06