MySQL案例05：CPU負載優化

Rangle發表於2018-05-22
MySql負載優化

最近有套系統資料庫週末總是告警，CPU使用率超過90%，開始由開發那邊再跟進處理，我也就沒參與，後來發現沒進展就登入上去看了下，然後進行了部分優化，優化後效果還是比較明顯的，具體優化過程本文會做詳細的闡述。

一、現象描述

資料庫伺服器CPU使用率超過90%，而此資料庫架構為mycat對應的一主三從（之前一主二從，由於CPU使用率高，開發那邊對庫做了擴充套件，從負載均衡的角度降低CPU壓力，從效果上看沒達到應有的效果），其中mycat的負載策略是3，即所有讀操作分配到從庫上完成，但實際是主庫抓包發現也會有大量的查詢操作。

zabbix監控資料：

二、問題原因

 1、mycat讀寫分離，讀操作為何會分發到主庫

經過tcpdump抓包和general_log抓包分析，確實存在著大量的select操作，開始我以為是mycat的讀寫負載的配置有問題（設定了讀寫負載2，導致所有資料庫均分發select），後來檢查發現mycat配置的是1，後改成3，現象依舊。這裡我就想到了是否呼叫儲存過程、事務的開啟和關閉，經過測試和跟蹤發現，確實在事務手動開啟和關閉的情況下，select查詢操作會分發到主庫。這種原因是mysql預設開始autocommit=1，也就是預設自動提交，而一旦程式通過set autocommit=0;update ..../select ..../select ...;commit;set autocommit=1;類似這種操作，那麼這些事務會分發到主庫，導致主庫有了大量的select查詢。

2、cpu負載之所以高，是由於大量的select操作導致的

在併發量大的情況下，大量的sql操作會導致cpu資源消耗嚴重，尤其是在sql執行較慢的情況下，所以我們首先要做的就是對負載較高的時間段，抓取相關的SQL進行分析，針對mysql我們當然要分析的就是慢日誌。

三、處理過程

 根據監控發現CPU負載在週一到週日，週末的負載尤其嚴重，和開發溝通後得知此係統主要面對的群體是一些週末上班的同事，這裡就對負載較高的14點到22點做慢日誌分析，發現居然慢日誌很少，這種情況是不正常的。

慢日誌分析：pt-query-digest  --since='2018-05-20 14:00:00' --until='2018-05-20 22:00:00' slow.log  >slow.txt

備註：mysql5.7的慢日誌時間格式預設是utc時間（比我們慢8小時，可通過下面的引數調整）

SHOW GLOBAL VARIABLES LIKE 'log_timestamps';
set global log_timestamps=SYSTEM;

 

經過分析，這些慢日誌不是導致CPU負載高的主要原因，那麼為什麼這個時間段慢日誌這麼少，又導致CPU負載這麼嚴重呢？去檢視mysql引數發現，慢日誌設定的是3秒，這裡動態調整為1秒後，一段時間後，抓取到很多相關的慢SQL,具體慢日誌分析和優化我這裡就不說了，無非是索引優化，SQL改寫什麼的。

優化後效果如下：（5.21日18:30做的優化）

 至此CPU負載基本穩定，優化告一段落，需進一步監控。

20180522一天執行資料如下：

優化後幾天的觀察效果如下：

 四、TCPDUMP抓包指令碼

tcpdump引數：

　　　-a 　　　將網路地址和廣播地址轉變成名字；
　　　-d 　　　將匹配資訊包的程式碼以人們能夠理解的彙編格式給出；
　　　-dd 　　將匹配資訊包的程式碼以c語言程式段的格式給出；
　　　-ddd 　　將匹配資訊包的程式碼以十進位制的形式給出；
　　　-e 　　　在輸出行列印出資料鏈路層的頭部資訊，包括源mac和目的mac，以及網路層的協議；
　　　-f 　　　將外部的Internet地址以數字的形式列印出來；
　　　-l 　　　使標準輸出變為緩衝行形式；
　　　-n 　　　指定將每個監聽到資料包中的域名轉換成IP地址後顯示，不把網路地址轉換成名字；
      -nn：   指定將每個監聽到的資料包中的域名轉換成IP、埠從應用名稱轉換成埠號後顯示
　　　-t 　　　在輸出的每一行不列印時間戳；
　　　-v 　　　輸出一個稍微詳細的資訊，例如在ip包中可以包括ttl和服務型別的資訊；
　　　-vv 　　輸出詳細的報文資訊；
　　　-c 　　　在收到指定的包的數目後，tcpdump就會停止；
　　　-F 　　　從指定的檔案中讀取表示式,忽略其它的表示式；
　　　-i 　　　指定監聽的網路介面；
     -p：    將網路卡設定為非混雜模式，不能與host或broadcast一起使用
　　　-r 　　　從指定的檔案中讀取包(這些包一般通過-w選項產生)；
　　　-w 　　　直接將包寫入檔案中，並不分析和列印出來；
     -s snaplen         snaplen表示從一個包中擷取的位元組數。0表示包不截斷，抓完整的資料包。預設的話 tcpdump 只顯示部分資料包,預設68位元組。
　　　-T 　　　將監聽到的包直接解釋為指定的型別的報文，常見的型別有rpc （遠端過程呼叫）和snmp（簡單網路管理協議；）
     -X      告訴tcpdump命令，需要把協議頭和包內容都原原本本的顯示出來（tcpdump會以16進位制和ASCII的形式顯示），這在進行協議分析時是絕對的利器。
     -G      寫入輸出報告時間間隔，單位秒
     src host ipaddr   源地址
     dst host ipaddr    目標地址

 tcpdump抓包指令碼

#!/bin/bash

date +"%Y-%m-%d %H:%M:%I"
tcpdump -i eth1 -s0 -G600 -l -w - dst port 3306 | strings | perl -e '
while(<>) { chomp; next if /^[^ ]+[ ]*$/;
    if(/^(SELECT|UPDATE|DELETE|INSERT|SET|COMMIT|ROLLBACK|CREATE|DROP|ALTER|CALL)/i)
    {
        if (defined $q) { print "$q\n"; }
        $q=$_;
    } else {
        $_ =~ s/^[ \t]+//; $q.=" $_";
    }
}'
date +"%Y-%m-%d %H:%M:%I"

五、MYSQL執行順序

執行順序：from... where...group by... having.... select ... order by...

 

相關文章