MySQL案例05：CPU負載最佳化

標籤：檔案中   寫入檔案   停止   代碼   連接埠   使用率   分離   commit   creat   

最近有套系統資料庫周末總是警示，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負載基本穩定，最佳化告一段落，需進一步監控。

 四、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/bashdate +"%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...

 

MySQL案例05：CPU負載最佳化