Anemometer graphically displays MySQL slow log tool construction and usage example analysis

Introduction: Anemometer is a tool for graphically displaying MySQL slow logs. Combined with pt-query-digest, Anemometer can easily help you analyze slow query logs, allowing you to easily find which SQL needs to be optimized

This is the Box Anemometer, the MySQL Slow Query Monitor. This tool is used to analyze slow query logs collected from MySQL instances to identify problematic queries

Related learning recommendations:PHP programming from entry to proficiency

Environment Overview

Take the latest version of percona-toolkit 3.0.10 at the time of writing this article as an example
The corresponding version of the mysql database is 5.7.21, and the binary installation
http and php are both built-in versions of the system CentOS Linux release 7.4.1708 (Core)

The steps that need to be installed are as follows:

1.Percona-toolkit tool installation

2.php web environment construction and installation

3.Anemometer and configuration

4.Import slow query log

5. Visit the interface and view slow queries

6. Other related and problem solving

0. Overall architecture

1. Installation of percona-toolkit

Installation purpose: pt-query-digest is percona -A tool in toolkit, its function is to analyze slow query logs, collect statistics on MySQL slow query logs and display them in a friendly manner

Download address: https://www.percona.com/downloads/ percona-toolkit/

Installation method (rpm):

1. Download the package, wget https://www.percona.com/ downloads/percona-toolkit/3.0.10/binary/redhat/7/x86_64/percona-toolkit-3.0.10-1.el7.x86_64.rpm

2. Install dependencies, yum install perl-DBI perl-DBD-MySQL perl-IO-Socket-SSL perl-Digest-MD5 -y

3. Formal installation, rpm -ivh percona-toolkit-3.0. 10-1.el7.x86_64.rpm

4. Verification of installation completion, pt-query-digest --version pt-query-digest 3.0.10

Installation method (tar binary)

1. Download the package, wget https://www.percona.com/downloads/percona-toolkit /3.0.10/binary/tarball/percona-toolkit-3.0.10_x86_64.tar.gz

2. Install dependencies, yum install perl-DBI perl-DBD-MySQL perl-IO-Socket-SSL perl-Digest-MD5 -y<br>

3. Unzip the package, tar xf percona-toolkit-3.0.10_x86_64.tar.gz

4. Use the tool directly, ./percona-toolkit-3.0.10/bin/pt-query-digest --version<br>pt-query-digest 3.0.10

2. Construction of php web environment

Installation purpose: Anemometer needs to depend on
LAMP environment Installation of LAMP environment:
1. Install apache, yum install httpd httpd-devel -y
2. Install php, yum install php php-mysql php-common php-bcmath php-dba php-cli php-gd php-mbstring php-mcrypt php-devel php-xml php-pdo -y<br>3. Modify the time zone, vim /etc/php.ini, change it to date.timezone = PRC

Startup of LAMP environment:
1. Start, systemctl start httpd
2. Shutdown, systemctl stop httpd
3. Restart, systemctl restart httpd
4. View, systemctl status httpd

##3. Install Anemometer and Configuration

1. Download and install:

Installation purpose: install Anemometer application
Download address:
https://github.com/box/Anemometer Download package:
git clone https://github.com/box/Anemometer.gitMove to the corresponding path:
mv Anemometer /var/www/html/anemometer2. The corresponding permissions of the Anemometer host need to be granted on the target slow query database.
1. Purpose, used to analyze the target slow query database explain execution plan.
2. Authorization,
grant select on *. * to 'anemometer'@'$ip' identified by '123456';flush privileges; ($ip is the IP address corresponding to the Anemometer host) 3. Modify the configuration file and add explain to read the user password information
cp conf/sample.config.inc.php conf/config.inc.php vim conf/config.inc.php\\

##4. Modify the configuration file Point to the data source file, vim conf/datasource_localhost.inc.php, of course you can also directly vim conf/config.inc.php

5、初始化数据源的数据库表的配置，mysql -uroot -p123456 -h127.0.0.1 -P5700

4. 导入慢查询日志

1、慢查询主机推送格式

For pt-query-digest version < 2.2
$ pt-query-digest --user=anemometer --password=superSecurePass \
--review h=db.example.com,D=slow_query_log,t=global_query_review \
--review-history h=db.example.com,D=slow_query_log,t=global_query_review_history \
--no-report --limit=0% \ 
--filter=" \$event->{Bytes} = length(\$event->{arg}) and \$event->{hostname}=\"$HOSTNAME\"" \ 
/var/lib/mysql/db.example.com-slow.log

For pt-query-digest version >= 2.2
$ pt-query-digest --user=anemometer --password=superSecurePass \
--review h=db.example.com,D=slow_query_log,t=global_query_review \
--history h=db.example.com,D=slow_query_log,t=global_query_review_history \
--no-report --limit=0% \ 
--filter=" \$event->{Bytes} = length(\$event->{arg}) and \$event->{hostname}=\"$HOSTNAME\"" \ 
/var/lib/mysql/db.example.com-slow.log

2、慢查询主机推动脚本示例

#config anemometer server, the purpose is to push slow query to the remote anemometer server and store it.
anemometer_host="127.0.0.1"
anemometer_user="root"
anemometer_password="123456"
anemometer_port=5700
anemometer_db="slow_query_log"

#config mysql server, the purpose is to get the path of the slow query log.
mysql_client="/usr/local/mysql-5.7.21/bin/mysql"
mysql_user="root"
mysql_password="123456"
mysql_socket="/tmp/mysql_5700.sock"
mysql_port=5700

#config slowqury dir to cd, and then delete the expired slow query file.
slowquery_dir="/data/mysql_$mysql_port/"

#get the path of the slow query log.
slowquery_file=`$mysql_client -u$mysql_user -p$mysql_password -S $mysql_socket -e "show variables like 'slow_query_log_file'"|grep log|awk '{print $2}'`
pt_query_digest="/data/percona-toolkit-3.0.10/bin/pt-query-digest"

#collect mysql slowquery log into lepus database.
$pt_query_digest --user=$anemometer_user --password=$anemometer_password --port=$anemometer_port --review h=$anemometer_host,D=$anemometer_db,t=global_query_review --history h=$anemometer_host,D=$anemometer_db,t=global_query_review_history --no-report --limit=0% --filter=" \$event->{Bytes} = length(\$event->{arg}) and \$event->{hostname}=\"$HOSTNAME:$mysql_port\"" $slowquery_file

#generate a new slow query log, the below is generate a new slow file per hour.
tmp_log=`$mysql_client -u$mysql_user -p$mysql_password -S $mysql_socket -e "select concat('$slowquery_dir','slowquery_',date_format(now(),'%Y%m%d%H'),'.log');"|grep log|sed -n -e '2p'`

#use new slow file to config mysql slowquery
$mysql_client -u$mysql_user -p$mysql_password -S $mysql_socket -e "set global slow_query_log = 0;set global slow_query_log_file = '$tmp_log';"
$mysql_client -u$mysql_user -p$mysql_password -S $mysql_socket -e "set global slow_query_log = 1; "

#delete slow query file before 2 days
cd $slowquery_dir
/usr/bin/find ./ -name 'slowquery_*.log' -mtime +2|xargs rm -rf ;

####END####

5. 访问界面，查看慢查询

http://$ip/anemometer/ （$ip为Anemometer主机对应ip地址）

6、其他相关和问题解决

1、对于anemometer的主机上，需要进行慢查询主机hostname和ip的映射（修改/etc/hosts进行配置），目的在于慢查询explain执行计划的目标主机解析
#collect mysql slowquery log into lepus database步骤中，$HOSTNAME:$mysql_port
数据库存取的格式，hostname_max类似这种，cnwangdawei:5700
2、中文乱码的问题，在#collect mysql slowquery log into lepus database步骤中添加 --charset=utf8
3、慢查询主机数据库是5.7版本的数据库，可能出现界面ts_cnt不显示，替换percona toolkit为新版本，2.x.x -----> 3.x.x
4、表结构和状态字符集显示乱码，添加mysqli的字符集设定，vim /var/www/html/anemometer/lib/QueryExplain.php
新增（194行后增加），$this->mysqli->query("set names utf8");

The above is the detailed content of Anemometer graphically displays MySQL slow log tool construction and usage example analysis. For more information, please follow other related articles on the PHP Chinese website!