【系統優化】資料庫系統load飆高問題解決思路

楊奇龍發表於2014-09-03
優化資料庫
工作過程中有時候會接收到資料庫伺服器器load 飆高的報警,比如:
 load1 15.25 base: 8.52,collect time:2014-08-30
如何處理load 異常飆高的報警呢？ 本文嘗試從原理，原因，解決方法來闡述這類問題的解決思路。

一 原理分析
     CPU作為伺服器的關鍵資源經常成為效能瓶頸的根源,CPU使用率高並不總是意味著CPU工作繁忙，它有可能是正在等待其他子系統。在進行效能分析時，將所有子系統當做一個整體來看是非常重要的，因為在子系統中可能會出現瀑布效應。衡量CPU 系統負載的指標是load,load 就是對計算機系統能夠承擔的多少負載的度量,簡單的說是程式佇列的長度。簡單的例子比如食堂有五個視窗，當有小於五個學生來打飯，五個視窗都能及時處理，但是當學生個數超過5個，必然會出現等待的學生。請求大於當前的處理能力，會出現等待，引起load升高。
Load Average 就是一段時間(1min,5min,15min)內平均Load。平均負載的最佳值是1，這意味著每個程式都可以在一個完整的CPU 週期內完成。
14:50:31 up 166 days,  1:54, 295 users,  load average: 0.05, 0.04, 0.00

二 原因分析
   
   一般導致MySQL伺服器load飆高的原因可能有以下幾種情況:
    1 業務併發呼叫全表掃描/帶有order by 排序的SQL語句.
    2 SQL語句沒有合適索引/執行計劃出錯/update/delete where掃描全表,阻塞其他訪問相同表的sql執行.
    3 存在秒殺類似的業務比如聚划算10點開團或者雙十一秒殺,瞬時海量訪問給資料庫帶來衝擊。
    4 資料庫做邏輯備份(需要全表掃描)或者多例項的壓縮備份(壓縮時需要大量的cpu計算,會導致系統伺服器load飆高)
    5 磁碟寫入方式改變 比如有writeback 變為 write through
       RAID卡都有寫cache(Battery Backed Write Cache),寫cache對IO效能的提升非常明顯,因為掉電會丟失資料,所以必須由電池提供支援。
       電池會定期充放電,一般為90天左右,當發現電量低於某個閥值時,會將寫cache策略從writeback置為writethrough,相當於寫cache會失效,這時如果系統有大量的IO操作,可能會明顯感覺到IO響        應速度變慢,cpu 佇列堆積系統load 飆高。
    6 其他 歡迎補充 。

三 解決方法
   
    在Load average 高的情況下如何鑑別系統瓶頸？如何判斷系統是否已經Over Load呢？要去檢查判斷是CPU不足，還是io不夠快造成或是記憶體不足？
這裡筆者處理的方式 一般根據cpu數量去判斷,也就是Load平均要小於CPU的數量,負載的正常值在不同的系統中有著很大的差別。在單核處理器的工作站中,1或2都是可以接受的。多核處理器的伺服器(比如24核)上,load 會到達20 ，甚至更高。以多例項混合公用一臺24核物理機為例,當DBA收到資料庫伺服器load 飆高報警後，一般的處理步驟
   
 a) 資料庫層面
     1 top -u mysql -c 檢查當前佔用cpu資源最多的程式命令。-c 是為了顯示出程式對應的執行命令語句，方便檢視是什麼操作導致系統load飆高。
     2 根據不同的情況獲取pid 或者MySQL的埠號
     3 如果是MySQL 資料庫服務導致laod 飆高，則可以使用如下命令
         show processlist;
         SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST WHERE COMMAND <> 'sleep' AND TIME>100;
        或
         orzdba 工具檢查邏輯讀/thread active的值。用法orzdba --help
         orztop 工具檢查當前正在執行的慢sql,用法orztop -P $port 
     4 獲取異常的sql之後，剩下的比較好解決了。結合第一部分中的幾條原因
        a 選擇合適的索引
        b 調整sql 語句 比如對應order by 分頁採用延遲關聯
        c 業務層面增加快取，減少對資料庫的直接訪問等

 b) OS 系統層面 檢查系統IO
    使用iostat 命令檢視r/s(讀請求),w/s(寫請求),avgrq-sz(平均請求大小),await(IO等待), svctm(IO響應時間)
    r/s ,w/s是每秒讀/寫請求的次數。
    util是裝置的利用率。如果它接近100%，通常說明裝置能力趨於飽和(並不絕對，比如裝置有寫快取)。有時候可能會出現大於100%的情況，這多半是計算時四捨五入引起的。
    svctm是平均每次請求的服務時間。這裡有一個公式：(r/s+w/s)*(svctm/1000)=util。舉例子：如果util達到100%，那麼此時  svctm=1000/(r/s+w/s)，假設IOPS是1000，則svctm大概在1毫秒左右，如果長時間大於這個數值，說明系統出了問題。
    await是平均每次請求的等待時間。這個時間包括了佇列時間和服務時間，也就是說，一般情況下，await大於svctm，它們的差值越小，佇列時間越短，反之差值越大，佇列時間越長，說明系統出了問題。
  avgqu-sz是平均請求佇列的長度。毫無疑問，佇列長度越短越好。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/22664653/viewspace-1262635/，如需轉載，請註明出處，否則將追究法律責任。

相關文章