Anemometer基於pt-query-digest將MySQL慢查詢可視化

標籤：mysql 慢查詢分析

參考文章：

http://ourmysql.com/archives/1359?utm_source=tuicool&utm_medium=referral

官方：https://github.com/box/Anemometer

單節點Anemometer監控

1 安裝anemometer

# cd /data/www/web3# git clone https://github.com/box/Anemometer.gitanemometer && cd anemometer

 

2 建立表和使用者名稱

# mysql -uroot -proot <install.sql# mysql -uroot -proot -e"grant ALL ON slow_query_log.* to ‘anemometer‘@‘localhost‘ IDENTIFIED BY ‘123456‘;"# mysql -uroot -proot -e"grant SELECT ON *.* to ‘anemometer‘@‘localhost‘ IDENTIFIED BY ‘123456‘;"# mysql -uroot -proot -e"flushprivileges;"

 

我們可以看下錶結構如下

650) this.width=650;" src="http://s4.51cto.com/wyfs02/M01/82/C2/wKioL1dgsumQ5XJvAABppErvjtI393.png" style="float:none;" title="1.png" alt="wKioL1dgsumQ5XJvAABppErvjtI393.png" />

650) this.width=650;" src="http://s4.51cto.com/wyfs02/M02/82/C4/wKiom1dgsdfxCsuTAABkX-yIQYI703.png" style="float:none;" title="2.png" alt="wKiom1dgsdfxCsuTAABkX-yIQYI703.png" />

3 分析mysql慢日誌

# pt版本高於2.2的執行下面語句，將慢查詢日誌放入名為slow_query_log資料庫中

# pt-query-digest --user=anemometer -h 127.0.0.1 --password=123456 --review h=localhost,D=slow_query_log,t=global_query_review--history h=localhost,D=slow_query_log,t=global_query_review_history--no-report --limit=0% --filter=" \$event->{Bytes} = length(\$event->{arg}) and\$event->{hostname}=\"$HOSTNAME\"" /usr/local/mariadb/var/localhost-slow.log

這時候，資料庫的slow_query_log 庫，裡面的global_query_review_history和global_query_review這2張表已經已經有一些資料了。

 

4 修改anemometer設定檔及配置展示日誌用的虛擬機器主機

# cd /data/www/web3/anemometer/conf# cp sample.config.inc.php  config.inc.php# vim config.inc.php  主要修改的地方如下2個：

650) this.width=650;" src="http://s5.51cto.com/wyfs02/M02/82/C2/wKioL1dgsy-SLqdmAABI8kceUVM423.png" style="float:none;" title="1.png" alt="wKioL1dgsy-SLqdmAABI8kceUVM423.png" />

650) this.width=650;" src="http://s1.51cto.com/wyfs02/M00/82/C4/wKiom1dgshzjSQY9AAAcXiSnlyc209.png" style="float:none;" title="2.png" alt="wKiom1dgshzjSQY9AAAcXiSnlyc209.png" />

配置nginx

# vim /usr/local/nginx/conf/vhost/anemometer.conf  內容如下：

server {       listen   80;       server_name  192.168.0.88;       access_log  /home/wwwlogs/anemometer.log  access;       index index.php index.html;       root  /data/web3/anemometer;       include enable-php.conf;}

# /etc/init.d/nginx reload      重載nginx設定檔

 

在瀏覽器訪問http://192.168.0.88/ 即可如所示(這幾張圖片是從別人部落格摘錄的，他這個做的特別詳細)

650) this.width=650;" src="http://s5.51cto.com/wyfs02/M00/82/C2/wKioL1dgs26T9pGCAAQ4oxq_YqY188.png" style="float:none;" title="1.png" alt="wKioL1dgs26T9pGCAAQ4oxq_YqY188.png" />

650) this.width=650;" src="http://s1.51cto.com/wyfs02/M01/82/C4/wKiom1dgslyQV2DwAAHkLVvouXY404.png" style="float:none;" title="2.png" alt="wKiom1dgslyQV2DwAAHkLVvouXY404.png" />

650) this.width=650;" src="http://s4.51cto.com/wyfs02/M01/82/C2/wKioL1dgs3CwnyhLAACl46otCtw764.png" style="float:none;" title="3.png" alt="wKioL1dgs3CwnyhLAACl46otCtw764.png" />

5 自動滾動日誌

# vi /etc/logrotate.d/mysql

postrotatept-query-digest --user=anemometer --password=123456 --review D=slow_query_log,t=global_query_review --review-history D=slow_query_log,t=global_query_review_history --no-report --limit=0% --filter=" \$event->{Bytes} = length(\$event->{arg}) and\$event->{hostname}=\"$HOSTNAME\"" /usr/local/mariadb/var/localhost-slow.logendscript

至此，我們的anemometer算是跑通了。

但是生產環境的話，我們不可能就一個節點的啊，下面就是多節點部署的問題了。

多節點mySQL監控慢查詢日誌

node1:192.168.2.11   MariaDB10.0.17    還部署有nginx的anemometer web前端

node2:192.168.2.12  MariaDB10.0.17

各個節點的my.cnf裡面開啟慢查詢，相關配置如下：

[mysqld]innodb_file_per_table = ONskip_name_resolve = ONslow_query_log=ONslow_query_log_file =localhost-slow.loglong_query_time = 2

1.   安裝anemometer

node1上安裝到nginx的網站目錄下

# cd /home/wwwroot/# git clonehttps://github.com/box/Anemometer.git anemometer# cd anemometer

 

node2上anemometer的安裝目錄沒什麼要求

# cd /root# git clone https://github.com/box/Anemometer.gitanemometer# cd anemometer

2.   建立表和使用者名稱

node1上執行：

# mysql -uroot -proot <install.sql# mysql -uroot -proot -e"grant ALL ON slow_query_log.* to ‘anemometer‘@‘192.168.2.%‘ IDENTIFIED BY‘123456‘;"# mysql -uroot -proot -e "grantSELECT ON *.* to ‘anemometer‘@‘192.168.2.%‘ IDENTIFIED BY ‘123456‘;"# mysql -uroot -proot -e"flush privileges;"

 

node2上執行：

# mysql -uroot -proot <install.sql# mysql -uroot -proot -e"grant ALL ON slow_query_log.* to ‘anemometer‘@‘192.168.2.%‘ IDENTIFIED BY‘123456‘;"# mysql -uroot -proot -e"grant SELECT ON *.* to ‘anemometer‘@‘192.168.2.%‘ IDENTIFIED BY‘123456‘;"# mysql -uroot -proot -e"flush privileges;"

3.   在兩個節點執行pt命令分析慢查詢日誌，並寫入到各自的資料庫中

node1上執行：

# pt-query-digest --user=anemometer  --password=123456--host=192.168.2.11 --review h=192.168.2.11,D=slow_query_log,t=global_query_review--history h=192.168.2.11,D=slow_query_log,t=global_query_review_history--no-report --limit=0% --filter=" \$event->{Bytes} = length(\$event->{arg}) and \$event->{hostname}=\"$HOSTNAME\""localhost-slow.log

node2上執行：

# pt-query-digest --user=anemometer  --password=123456--host=192.168.2.12 --review h=192.168.2.12,D=slow_query_log,t=global_query_review --history h=192.168.2.12,D=slow_query_log,t=global_query_review_history --no-report --limit=0% --filter=" \$event->{Bytes} = length(\$event->{arg}) and\$event->{hostname}=\"$HOSTNAME\"" localhost-slow.log

4.   在node1上配置前端

# cd /home/wwwroot/anemometer/conf# cp sample.config.inc.php  config.inc.php# vim config.inc.php  主要修改的地方如下2個【conf項，plugins項】：

$conf[‘datasources‘][‘192.168.2.11‘] = array(        ‘host‘ => ‘192.168.2.11‘,        ‘port‘ => 3306,        ‘db‘   => ‘slow_query_log‘,        ‘user‘ => ‘anemometer‘,        ‘password‘ => ‘123456‘,        ‘tables‘ => array(                ‘global_query_review‘ =>‘fact‘,                ‘global_query_review_history‘=> ‘dimension‘        ),        ‘source_type‘ => ‘slow_query_log‘); $conf[‘datasources‘][‘192.168.2.12‘] = array(        ‘host‘ => ‘192.168.2.12‘,        ‘port‘ => 3306,        ‘db‘   => ‘slow_query_log‘,        ‘user‘ => ‘anemometer‘,        ‘password‘ => ‘123456‘,        ‘tables‘ => array(                ‘global_query_review‘ =>‘fact‘,                ‘global_query_review_history‘=> ‘dimension‘        ),        ‘source_type‘ => ‘slow_query_log‘); $conf[‘plugins‘] = array(    ...省略代碼...       $conn[‘user‘] = ‘anemometer‘;       $conn[‘password‘] = ‘123456‘;    ...省略代碼...

# /etc/init.d/nginx restart   重啟Nginx

Chrome查看http://192.168.2.11/ 如所示

650) this.width=650;" src="http://s5.51cto.com/wyfs02/M00/82/C2/wKioL1dgtIjyaghXAAD73YfyHgI708.png" title="1.png" alt="wKioL1dgtIjyaghXAAD73YfyHgI708.png" />

5.   下面是我自己寫pt分析慢查詢日誌的指令碼

(anemometer提供的那個個人感覺用不慣，自己照著寫了個更簡單的)

vim /home/scripts/pt-digest.sh 內容如下：

#!/bin/bash# 我這裡直接把配置寫死了，覺得不太好的話大家可以參考其它文章將資料庫的串連配置獨立出來 # 慢查詢日誌存放的目錄SQL_DATADIR="/usr/local/mariadb/var" # 慢查詢日誌的檔案名稱(basename)SLOW_LOG_FILE=$( mysql -uroot -proot -e " show global variables like‘slow_query_log_file‘" -B  | tail-n1 | awk ‘{ print $2 }‘ ) # 擷取本機IP地址IP_ADDR=$(/sbin/ifconfig | grep‘inet addr‘  | egrep ‘172.|192.‘ | awk‘{print $2}‘ | awk -F ":" ‘{print $2}‘) cp $SQL_DATADIR/$SLOW_LOG_FILE/tmp/ # 分析日誌並存入slow_query_log這個資料庫/usr/local/bin/pt-query-digest --user=anemometer --password=123456 --host=$IP_ADDR  --review h=$IP_ADDR,D=slow_query_log,t=global_query_review --history h=$IP_ADDR,D=slow_query_log,t=global_query_review_history --no-report --limit=0% --filter="\$event->{Bytes} = length(\$event->{arg}) and\$event->{hostname}=\"$HOSTNAME\"" /tmp/$SLOW_LOG_FILE rm -f /tmp/$SLOW_LOG_FILE

 

 

調試通過以後,在crontab添加如下命令實現定期採集慢查詢日誌到資料庫儲存

59 23 * * * /bin/bash /home/scripts/pt-digest.sh> /dev/null

 

這樣每天就能自動分析採集慢查詢日誌了。

 

另外，慢查詢日誌建議按天切分，這樣用pt-query-digest進行SQL慢查詢日誌統計的時候就避免重複分析了。慢查詢按天切分的指令碼如下：

Tips下面是慢查詢日誌切分指令碼:

 

下面是一個輪詢切割mySQL慢查詢和錯誤記錄檔的指令碼（/home/scripts/mysql_log_rotate）：

"/usr/local/mariadb/var/localhost-slow.log""/usr/local/mariadb/var/localhost_err" {     create 660 mariadb mariadb      # 這個檔案許可權和屬主屬組需要根據自己的情況修改   dateext   notifempty   daily   maxage 60   rotate 30   missingok   olddir /usr/local/mariadb/var/oldlogs  # 這個目錄不存在的話，要自己先建立好，並修改屬主為mariadb    postrotate        if /usr/local/mariadb/bin/mysqladminping -uroot -proot &>/dev/null; then            /usr/local/mariadb/bin/mysqladminflush-logs -uroot -proot        fi   endscript}

650) this.width=650;" src="http://s2.51cto.com/wyfs02/M00/82/C4/wKiom1dgs8HBGhbTAABRb71CZvY831.png" title="1.png" alt="wKiom1dgs8HBGhbTAABRb71CZvY831.png" />

再配置個CRONTAB：

00 00 * * * (/usr/sbin/logrotate-f /home/scripts/mysql_log_rotate >/dev/null 2>&1)

 

這樣的話，每天慢查詢日誌、錯誤記錄檔就自動儲存到/usr/local/mariadb/var/oldlogs/這個目錄下了。

Anemometer基於pt-query-digest將MySQL慢查詢可視化