MySQL問題定位-效能優化之我見

老楊伏櫪發表於2021-07-28

前言

首先任何一個資料庫不是獨立存在的,也不是憑空想象決定出來的。

資料庫的架構離不開應用的場景。所以,為了解決某些深入的問題,首先你得掌握資料庫的原理與架構。原理掌握得越深入,越能幫助你定位複雜與隱藏的問題。

其次,DBA不能只侷限於資料庫本身。因為問題的來源,很多時候都來自使用者表象(比如說使用者反映查詢某個東西很慢)。這個表象 問題,可能來自從應用到資料庫,到OS,儲存等方面。或者是網路鏈路的任一環節等。

最後,DBA常需要關注的層面,除了應用,更重要的還有OS硬體相關的層面。如記憶體,CPU,磁碟等。

背景知識:資料庫經典三層架構圖

 背景知識:MySQL體系架構

圖1即為MySQL的邏輯架構圖,可以簡單地歸結為四層結構:

第一層為客戶端連線層,主要是做一些連線處理、許可權認證、安全連線等處理。

第二層為服務管理層,實現了諸如SQL介面、解析、優化、快取以及備份恢復、複製等核心功能。

第三層為外掛儲存引擎層,這是MySQL區別於其它資料庫系統如ORACLE、MSSQL SERVER最重要的一點,MySQL中資料的儲存和提取最終是由儲存引擎來實現的,不同的儲存引擎存取資料的方式不一樣,它們通過統一的API與服務層進行通訊。

第四層為資料儲存層,確切地說它不屬於MySQL系統,只是MySQL生成的資料、日誌等檔案最終是要儲存在磁碟檔案系統中的。

 

 

 背景知識:MySQL體系架構

 

 背景知識:MySQL核心架構

 

MySQL定位問題關注方面

資料庫層面

  1. MySQL Slow log (80%的問題,都來自SQL應用的問題)
  2. Mysql error log
  3. MySQL統計狀態資訊
  4. Show global status;
  5. Show engine innodb status\G
  6. Show full processlist;
  7. Show master status
  8. Show slave status

OS層面

  1. 記憶體,磁碟,IO,網路等
  2. free, vmstat, iostat, top, sar -n DEV, demesg, perf等

有圖表的情況下,儘量多通過圖表方式來檢視指標變化趨勢。所謂一圖頂千言。

MySQL定位問題思路與方法

排除法

  1. 排除應用問題(檢視slow log)
  2. 排除OS問題(檢視OS各類輸出,圖表等)
  3. 排除資料庫本身問題(檢視資料庫,狀態,延時等)

搜尋路徑(知識爆炸的時代,誰也不可能記住所有的問題)

  1. 百度
  2. 谷歌
  3. Oracle Support知識庫  https://support.oracle.com/portal/

 

慢SQL問題定位方法

  1. Explain
  2. Show Profile
  3. mysqldumpslow

MySQL效能優化關注點

 SQL及索引優化

  • 根據需求寫出良好的SQL,並建立有效的索引,實現某一種需求可以多種寫法,這時候我們就要選擇一種效率最高的寫法。這個時候就要了解sql優化
  • 資料庫表結構優化
  • 根據資料庫的正規化,設計表結構,表結構設計的好直接關係到寫SQL語句。

系統配置優化

  • 大多數執行在Linux機器上,如tcp連線數的限制、開啟檔案數的限制、安全性的限制,因此我們要對這些配置進行相應的優化。

硬體配置優化

  • 選擇適合資料庫服務的cpu,更快的IO,更高的記憶體。
  • 但不意味著越強越好。因為我們總是在成本與收益之間權衡。配置過低,效能無法滿足要求。配置太高,造成浪費。
  • 我們應該在選擇合理的配置,並預留部分資源以應對突發流量。

注:通過下圖可以看出,該金字塔中,優化的成本從下而上逐漸增高,而優化的效果會逐漸降低。

 

效能優化的原則

  • 優化永遠不會結束(也即不需要做無畏的優化)
  • 2/8理論,短板理論(改善20%的短板,提升80%的效能)

所謂效能優化,在大部分情況而言,就是找到導致效能的瓶頸所在,並加以解決。

 

案例分享

某部門系統從ORACLE資料庫遷移到MySQL,碰到了嚴重的效能問題。實測TPS不足ORACLE DB的30%。

我的效能優化過程步驟:

  • 前期現場檢視資料表定義,配置檔案等。
  • 發現問題表沒有使用主鍵,insert values一條一條插入導致速度很慢,配置檔案引數不合適(innodb buffer pool, redo log, io_capacity等)等問題。
  • 現場測試,效能還是有問題(表象為Mysql伺服器IO,CPU負載都很小)

    採用排除法

    • 用sysbench壓測沒有問題,排除硬體OS問題
    • 資料庫查詢狀態正常,沒有問題
    • 剩下,只能是應用問題。

      檢視網路流量,抓取資料包,發現應用流量很小。

      程式碼排查,oracle以前用sequence。MySQL不支援,應用程式碼實現生成ID。

      這段程式碼有問題,出現鎖爭用,導致應用流量一直很小。

相關文章