Anemometer based on Pt-query-digest to visualize MySQL slow query

Reference article:

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

Official: Https://github.com/box/Anemometer

Single-node anemometer monitoring

1 Installing Anemometer

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

2 Creating tables and user names

# mysql-uroot-proot <install.sql# mysql-uroot-proot-e "Grant all on slow_query_log.* to ' anemometer ' @ ' localhost ' ID Entified by ' 123456 '; " # MYSQL-UROOT-PROOT-E "Grant SELECT On * * to ' anemometer ' @ ' localhost ' identified by ' 123456 ';" # mysql-uroot-proot-e "Flushprivileges;"

We can look at the following table structure as follows

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 Analyzing MySQL Slow log

# The PT version above 2.2 executes the following statement, putting the slow query log in a database named 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

At this time, the database of the Slow_query_log library, the inside of the Global_query_review_history and global_query_review the 2 tables already have some data.

4 modifying Anemometer configuration files and configuring virtual hosts for presentation logs

# cd/data/www/web3/anemometer/conf# CP sample.config.inc.php config.inc.php# vim config.inc.php mainly modifies the place as follows 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 "/>

Configure Nginx

# vim/usr/local/nginx/conf/vhost/anemometer.conf content is as follows:

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 Heavy-duty nginx configuration file

In the browser access http://192.168.0.88/can be as shown (these pictures are excerpts from others ' blogs, he did this in particular detail)

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 Automatic scrolling log

# 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

At this point, our anemometer is running through.

But the production environment, we can not be a node, ah, the following is the problem of multi-node deployment.

Multi-node MySQL monitoring slow query log

node1:192.168.2.11 MariaDB10.0.17 also deploys Nginx anemometer Web front-end

node2:192.168.2.12 MariaDB10.0.17

The my.cnf of each node is opened in slow query, the related configuration is as follows:

[mysqld]innodb_file_per_table = Onskip_name_resolve = Onslow_query_log=onslow_query_log_file = Localhost-slow.loglong_query_time = 2

1. installation anemometer

Node1 installed on the Nginx the Site Directory under

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

Node2 on anemometer installation directory is not required

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

2. Creating tables and user names

Node1 Execute on:

# 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 Execute on:

# 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. performed on two nodes PT The command parses the slow query log and writes to the respective database

Node1 Execute on:

# 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 Execute on:

# 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. in the Node1 Configure front end on

# cd /home/wwwroot/anemometer/conf# cp  sample.config.inc.php  config.inc.php# vim config.inc.php   The main changes in the following 2 "conf items, Plugins Item ": 

$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 (     Omit code ...        $conn [' User '] =  ' anemometer ';         $conn [' Password '] =  ' 123456 ';      Omit code ...

#/etc/init.d/nginx Restart Restart Nginx

Chrome View http://192.168.2.11/ as shown

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

5. Here's what I wrote myself . PT script for parsing slow query logs

(anemometer provided that the personal feeling is not used, he wrote a more simple )

vim/home/scripts/pt-digest.sh The contents are as follows:

#!/bin/bash#  I here directly to the configuration to write dead, feel not very good if you can refer to other articles to separate the connection configuration of the database  #  slow query log storage directory Sql_datadir= "/usr/local/ Mariadb/var " #  slow query log file name (basename) slow_log_file=$ ( mysql -uroot -proot -e "  show global variables like ' Slow_query_log_file ' " -b  | tail-n1 |  awk  ' { print $2 } '  )  #  get the native IP address ip_addr=$ (/sbin/ifconfig |  grep ' inet addr '   | egrep  ' 172.| 192. '  | awk ' {print $2} '  | awk -F  ': '   ' {print $2} '  cp $ sql_datadir/$SLOW _log_file/tmp/ #  Analysis log and deposit Slow_query_log this database/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} =&nbSp;length (\ $event->{arg})  and\ $event->{hostname}=\ "$HOSTNAME \" " /tmp/$SLOW _log_file rm  -f /tmp/$SLOW _log_file

after debugging through , in the crontab Add the following command to implement periodic collection of slow query logs to the database storage

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

This allows you to automatically analyze the slow query log every day.

In addition, the slow query log is recommended for day slicing, so that pt-query-digest make SQL Avoid repetitive analysis when querying log statistics slowly. The script for slow query by day is as follows:

Tips here is the slow query log slicing script :

Here is a poll cut MySQL scripts for slow queries and error logs ( /home/scripts/mysql_log_rotate ):

 "/usr/local/mariadb/var/localhost-slow.log" "/usr/local/mariadb/var/ Localhost_err " {     create 660 mariadb mariadb       #  This file permission and belong to the main group need to modify according to their own circumstances    dateext   notifempty    daily   maxage 60   rotate 30   missingok    olddir /usr/local/mariadb/var/oldlogs  #  This directory does not exist, it is necessary to create a new first, and modify the owner of the 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"/>

then configure a CRONTAB :

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

in this case, the daily slow query log, error log is automatically stored to /usr/local/mariadb/var/oldlogs/ It's under this directory.

Anemometer based on Pt-query-digest to visualize MySQL slow query