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 faa15121ab0f42e2ec5543ee5fa6a304 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!