MySQL案例05：CPU負載優化

最近有套系統資料庫周末總是告警，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...