Shulou(Shulou.com)06/01 Report--

Introduction: Anemometer is a tool for graphically displaying MySQL slow logs. Combined with pt-query-digest,Anemometer, you can easily analyze slow query logs, so that you can easily find out 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

Official website: https://github.com/box/Anemometer

Environmental profile

Take the latest version 3.0.10 of percona-toolkit at the time of writing as an example.

The corresponding version of mysql database is 5.7.21, binary installation

Both http and php are built-in versions of system CentOS Linux release 7.4.1708 (Core)

The steps to install are as follows:

Installation of percona-toolkit tools installation of php web environment installation of Anemometer and configuration of import slow query log access interface to view other slow query related and problem solving

0. The overall architecture

1. Installation purpose of percona-toolkit tool: pt-query-digest is a tool in percona-toolkit, its function is to analyze slow query log, count MySQL slow query log and display it amicably. Download address: https://www.percona.com/downloads/percona-toolkit/ installation method (rpm): 1, download 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, installation dependency, yum install perl-DBI perl-DBD-MySQL perl-IO-Socket-SSL perl-Digest-MD5-y 3, official installation, rpm-ivh percona-toolkit-3.0.10-1.el7.x86_64.rpm 4, verification after installation Pt-query-digest-version pt-query-digest 3.0.10 installation (tar binary) 1, download package, wget https://www.percona.com/downloads/percona-toolkit/3.0.10/binary/tarball/percona-toolkit-3.0.10_x86_64.tar.gz 2, installation dependency, yum install perl-DBI perl-DBD-MySQL perl-IO-Socket-SSL perl-Digest-MD5-y 3, unzip package Tar xf percona-toolkit-3.0.10_x86_64.tar.gz 4. Direct use of tools,. / percona-toolkit-3.0.10/bin/pt-query-digest-- version pt-query-digest 3.0.102. Construction and installation purpose of php web environment: Anemometer needs to rely 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 3, modify time zone, vim / etc/php.ini, change to date.timezone = startup of PRCLAMP environment: 1. Systemctl start httpd 2, shutdown, systemctl stop httpd3, restart, systemctl restart httpd 4, View, systemctl status httpd3. Install Anemometer and configure 1, download installation: install purpose: install Anemometer application download address: https://github.com/box/Anemometer download package: git clone https://github.com/box/Anemometer.git move to the corresponding path: mv Anemometer / var/www/html/anemometer2, the target slow query database needs to grant the corresponding permissions to the Anemometer host 1, purpose Used to analyze 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 of the Anemometer host) 3. Modify the configuration file to 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 to point to the data source file, vim conf/datasource_localhost.inc.php, or you can vim conf/config.inc.php directly.

5. Configuration of database tables that initialize the data source, mysql-uroot-p123456-h227.0.0.1-P5700

< install.sql，每个datasource源头可以对应不同的数据库database（修改install.sql的内容） 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.logFor pt-query-digest version > = 2.2$ pt-query-digest-- user=anemometer-- password=superSecurePass\-- review examples. 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.log2, example of slow query host push script # 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=5700anemometer_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 slowquery file.slowquery_dir= "/ data/mysql_$mysql_port/" # get the path of the slowquery 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 No-report-- limit=0%-filter= "\ $event- > {Bytes} = length (\ $event- > {arg}) and\ $event- > {hostname} =\" $HOSTNAME:$mysql_port\ "$slowquery_file#generate a new slowquery 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 mysql_client 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 slowquery file before 2 dayscd $slowquery_dir/usr/bin/find. /-name 'slowquery_*.log'-mtime + 2 | xargs rm-rf; # END####5. Visit the interface to view slow queries

Http://$ip/anemometer/ ($ip is the ip address of the Anemometer host)

6. Other related and problem solving 1. On the host of anemometer, the mapping between hostname and ip of slow query host is required (modify / etc/hosts for configuration). The purpose is to slow query the target host of explain execution plan to parse the format of $HOSTNAME:$mysql_port database access in # collect mysql slowquery log into lepus database step, hostname_max similar to this, cnwangdawei:57002, Chinese garbled problem. Add-- charset=utf83 in the # collect mysql slowquery log into lepus database step, slow query host database is version 5.7 database, it may appear that the interface ts_cnt does not display, replace percona toolkit with the new version, 2.x.x-> 3.x.x4, table structure and status character set display garbled, add mysqli character set setting, add vim / var/www/html/anemometer/lib/QueryExplain.php (added after 194lines) $this- > mysqli- > query ("set names utf8")

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.