一次較波折的MySQL調優

来源:https://www.cnblogs.com/Jcloud/archive/2022/09/01/16646031.html
-Advertisement-
Play Games

前一天從自建MySQL遷移到雲上RDS,在執行某個併發較高的業務時出現了大量鎖等待,客戶當時升級了實例到最高規格,但故障依舊。 ...


春節長假某日,陽光明媚,春暖花開,恰逢冬奧會開幕,想著一定是一個黃道吉日,必能順風順水。沒想到卻遇到一個有點小波折 的客戶報障。

01故障起因

故障起因是客戶前一天從自建MySQL遷移到雲上RDS,在執行某個併發較高的業務時出現了大量鎖等待,客戶當時升級了實例到最高規格,但故障依舊。客戶反饋升級後的實例規格比自建實例高了一倍,自建實例上從未發生過類似情況。後客戶根據當時的業務故障模擬了現場,主要是併發執行如下存儲過程的時候性能很差:

 

02初步診斷

從存儲過程的邏輯看,比較簡單,主要涉及兩個SQL,一個從表t(隱藏了真實表名)中meeting_id根據傳入參數值查詢,具體的入參由字元型變數p_meeting_id帶入;另外一個根據meeting_id和剛查出的phone_id去更新t中的phone_id為phone_id+3。表t數據量約40w左右。

第一感覺這是個簡單問題,估計兩個SQL的meeting_id索引沒有生效,查詢表上索引後果然發現meeting_id和phone_id上沒有索引,建議客戶在兩個欄位上分別創建了索引,且meeting_id為主鍵。此時用戶執行模擬的併發腳本反饋速度有了明顯提升,200個併發最高執行時間40s左右,但模擬500個併發的時候,超過了8分鐘還沒有執行完。用戶反饋在自建MySQL上併發500執行都是秒級完成。此時在控制台看,這個存儲過程在慢查詢日誌中批量出現,且掃描行數巨大,客戶端已經完全hang住:

 

 03進一步優化

雖然優化有了初步的效果, 但距離客戶自建環境性能描述還差距很大,由於併發高, 從監控看測試期間CPU到了100%,懷疑參數innodb_thread_concurrency的設置可能不當。此參數的作用是控制 InnoDB 的併發線程上限。也就是說,一旦併發線程數達到這個值,InnoDB 在接收到新請求的時候,就會進入等待狀態,直到有線程退出。RDS預設值為0,也就是沒有限制上限,在高併發的場景下可能會產生較多的上下文切換,導致CPU升高。和客戶咨詢了一下,他們自建環境的值設置為32,建議他們將RDS的值也改為32再看看效果。客戶很快反饋,修改後的確有效果,500個併發在3分鐘內完成,沒有再發生hang住不動的情況,性能有了進一步的提升。但參數innodb_thread_concurrency進一步調整效果不明顯。

04加trace診斷

客戶看到性能不斷提升也很有信心,但和自建環境差距還是很大,還有哪裡可能有問題?突然想到,創建索引後,在控制台的慢查詢列表中看到很多存儲過程的調用sql,且掃描記錄數巨大,如果是走meeting_id唯一索引,應該掃描很少的記錄數才對,難道沒有走索引?或者沒有走meeting_id主鍵索引?聯繫客戶,希望提供測試環境登陸測試。

 在測試環境,首先希望驗證一下兩個SQL的執行計划到底是怎麼樣的。登陸實例後,分別對兩個存儲過程中的SQL執行explain,發現走的確實是主鍵(meeting_id):

 

為了進一步確認SQL在存儲過程中的實際執行計劃,修改了一下測試的存儲過程邏輯,加入了SQL執行的explain結果和實際執行的trace,過程中主要增加的代碼如下:

 

 執行計劃結果如下:

 

從結果看,兩個SQL居然真的沒有走主鍵meeting_id索引,而是都走了phone_id這個普通的二級索引,其中第一個查詢SQL走的索引全掃描,掃描記錄數rows為397399,和表的記錄數一致,顯然走了全索引掃描,雖然比全表掃描好一些,但效率仍然低下;另外一個update的SQL走了正常的索引掃描,rows只有2,性能高效。為什麼兩個SQL沒有走meeting_id這個主鍵索引呢?看trace列印的部分內容:

 

trace顯示兩個SQL在優化器分析時,將meeting_id做了隱式轉換,轉換函數為convert('meeting_id' using utf8mb4),也就是將meeting_id做了字元集的轉換,熟悉索引機制的同學都清楚,這種情況下優化器是不會走meeting_id索引的。這也可以解釋了客戶第一次創建索引的時候為啥有性能提升,但效果並不明顯,原因就是只有update語句真正用到了索引帶來的性能提升,而且是phone_id索引帶來的提升,不是性能更高的主鍵meeting_id。

 

05真相大白

現在聚焦到最關鍵的問題,meeting_id為啥要做字元集的隱式轉換?查看了一下實例相關字元集的設置:

  1. 表和列的字元集都為utf8;

  2. 表所在庫的字元集為utf8mb4;

  3. server字元集((character_set_server))為utf8

  4. character_set_client/character_set_connection/character_set_results為utf8mb4

果然,server、database、table的字元集不完全一致,猜想一下實際流程應該是這樣的:存儲過程中傳入的字元參數字元集為utf8mb4,和表中字元集為utf8的欄位meeting_id比較時,meeting_id做了字元集的隱式轉換,轉換為utf8mb4後再和輸入參數比較,從而導致meeting_id上的索引無法使用。

根據這個猜測,建議用戶將表的字元集更改為utf8mb4,這樣應該可以避免字元集的轉換。由於這個功能還未上線,用戶直接對 表做了字元集的修改:

alter table zm_meeting convert to character set utf8mb4;

 

修改後讓用戶再次測試,預期效果終於出現,併發500測試在秒級完成,trace查看執行計劃,都走了meeting_id的主鍵索引,隱式轉換也隨之消失,性能問題得到了徹底解決。

06後續思考

存儲過程的入參為啥使用了utf8mb4?這是本次案例的核心,查閱mysql文檔,存儲過程介紹裡面有一段描述:

 

簡單說,就是存儲過程的字元型參數,如果沒有顯式指定字元集,預設將會使用所在資料庫的字元集,而本案例中表所在的資料庫字元集為utf8mb4,所以參數預設使用了utf8mb4,導致了匹配過程的隱式轉換。存儲過程外直接寫SQL為什麼沒有這種情況發生,我猜測比較的字元串應該會自動匹配‘=’左邊表欄位的字元集。

 

既然這樣,理論上直接修改參數的字元集應該也可以達到同樣結果,簡單測試下,將存儲過程參數加上表上的字元集屬性:

CREATE  PROCEDURE `zm_sp_next_phone_id`(IN `p_meeting_id` VARCHAR(36) character set utf8)

 

測試結果如我們預期,不會產生隱式轉換,執行計劃正確。

 

問題雖然解決了,原因也找到了,但反思一下整個過程,如果用戶的server、庫、表字元集能夠保持一致,將完全可以避免這個故障。與字元集相關的類似故障也可以大概率避免,所以客戶側還是要有一定的設計規範;產品側如果有一定的檢查規則可以幫客戶發現類似的隱患,對提升客戶體驗也是一種很有價值的服務。

 


您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • Content 在用Docker進行持久化的存儲的時候,有兩種方式: 使用數據捲(volume) -v 容器絕對路徑 或者 -v 已經創建的volume名稱:容器絕對路徑 使用掛載點(共用宿主目錄,bind mount) -v 宿主絕對路徑:容器絕對路徑 由於兩種方法都可以使用-v和--mount參 ...
  • ​網上的教程,大多是教大家使用mac電腦製作ios證書,但是假如我們沒有mac電腦,就無法製作了。所以本站提供線上的ios證書製作工具幫助大家製作ios證書。 原來mac製作證書的流程,是先通過mac的鑰匙串訪問,生成一個csr文件,然後使用這個csr文件,去蘋果開發者中心生成一個cer證書文件,最 ...
  • 有沒有簡單好用的圖像處理軟體?Pixelmator Pro for Mac是一款專業的圖像處理軟體,通過廣泛的工具集來編輯和修飾照片,創建圖形設計,繪畫,繪製矢量圖形以及添加令人驚嘆的效果,它是您需要的唯一圖像編輯器。 詳情:Pixelmator Pro for Mac(專業的圖像編輯軟體) Pix ...
  • 哪個音頻處理軟體好用呢?iZotope RX 10是最新版本的音頻修複軟體,相對RX 9有很強的功能變化,無論您是編輯播客、錄製整個樂隊,還是製作大片,RX 10 都能讓您通過錄製的最佳音頻來吸引觀眾。 詳情:iZotope RX 10 for mac(強大的音頻修複工具) 為什麼是 RX? 去除手 ...
  • 澳大利亞標準委員會與2022年6月24日發佈了一系列新版標準。其中,AS/NZS 60335.1:2022 應在IEC 60335-1第六版對應的分標發佈之後使用。 此外,澳大利亞標準委員會發佈的標準也被用作紐西蘭標準。具體內容如下: • AS/NZS 60335.1:2022 – 家用和類似用途電 ...
  • Dockerfile 基本結構 Dockerfile 是一個文本格式的配置文件,用戶可以使用 Dockerfile 快速創建自定義鏡像。 Dockerfile 由一行行命令語句組成,並且支持以 # 開頭的註釋行。 Docker分為四部分: 基礎鏡像信息 維護者信息 鏡像操作指令 容器啟動時預設要執行 ...
  • 9月2日,本周五14:00 「創新的複利」 Sequoia Talk系列論壇,首期直播盛大啟動。在第一期科技專場,4位紅杉中國資深投資人、8位創新創業者將帶我們深入工業軟體、機器人、雲計算等領域,圍繞技術、商業、運營等多個維度進行探討。本次,玖章算術CEO葉正盛被業界稱為雲計算和資料庫技術領軍人,受 ...
  • 我們在日常程式設計中,經常會遇到樹狀結構的表示,例如組織機構、行政區劃等等。這些在資料庫中往往通過一張表進行展示。這裡我們以一張簡單的行政區劃表為例進行展示,在實際使用過程中,可以為其添加其他描述欄位以及層級。表中通過ID和PID關聯,實現樹狀結構的存儲。建表以及數據語句如下:-- Create t ...
一周排行
    -Advertisement-
    Play Games
  • 移動開發(一):使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說,近來想嘗試開發一款安卓APP,考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP,畢竟是使用Visual Studio開發工具,使用起來也比較的順手,結合微軟官方的教程進行了安卓 ...
  • 前言 QuestPDF 是一個開源 .NET 庫,用於生成 PDF 文檔。使用了C# Fluent API方式可簡化開發、減少錯誤並提高工作效率。利用它可以輕鬆生成 PDF 報告、發票、導出文件等。 項目介紹 QuestPDF 是一個革命性的開源 .NET 庫,它徹底改變了我們生成 PDF 文檔的方 ...
  • 項目地址 項目後端地址: https://github.com/ZyPLJ/ZYTteeHole 項目前端頁面地址: ZyPLJ/TreeHoleVue (github.com) https://github.com/ZyPLJ/TreeHoleVue 目前項目測試訪問地址: http://tree ...
  • 話不多說,直接開乾 一.下載 1.官方鏈接下載: https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe,運行開始下載SQL server; 二. ...
  • 前言 隨著物聯網(IoT)技術的迅猛發展,MQTT(消息隊列遙測傳輸)協議憑藉其輕量級和高效性,已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫,為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog支持多種接收器用於日誌存儲,增強器用於添加屬性,LogContext管理動態屬性,支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項,適用於不同需求。 ...
  • 目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據,爬取信息時需要模擬瀏覽器行為,否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下: 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • 1.前言 什麼是熱更新 游戲或者軟體更新時,無需重新下載客戶端進行安裝,而是在應用程式啟動的情況下,在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR,Xlua,ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles,Addressable, ...
  • 本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺,其實像阿裡雲,騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後,它會送 ...
  • 通過以下方式可以高效,並保證數據同步的可靠性 1.API設計 使用RESTful設計,確保API端點明確,並使用適當的HTTP方法(如POST用於創建,PUT用於更新)。 設計清晰的請求和響應模型,以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證,確保接收到的數據符合預期格 ...