"Reprint" uses anemometer to visualize MySQL slow query based on pt-query-digest

Original address: Use Anemometer to visualize MySQL slow query based on Pt-query-digest 84223932

This article mainly introduces the use of anemometer based on pt-query-digest MySQL slow query visualization, because the online data is relatively small, and are in English, encountered an error there is no relevant reference material, so write this article.
Welcome reprint, please indicate the author, source. Zhang blog:http://space.itpub.net/26355921 qq:176036317 If you have any questions, please contact us. Preparation conditions:
1.MySQL Turn on Slow query


2. Install Percona Toolkit
Rpm-qa|grep Percona
Percona-toolkit-2.2.7-1


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


4. Prepare the PHP related module (or yum)


5. Launch Apache Service
Rpm-qa|grep http
Httpd-2.2.3-45.el5


Installation:
1. Install Percona Toolkit:
RPM-IVH percona-toolkit-2.2.7-1.noarch.rpm


2. Unzip the anemometer package, rename it to Anemometer, and move to/var/www/html (Apache default path)
Pwd
/var/www/html/anemometer


3. Install PHP related modules:
Rpm-qa|grep PHP
Php53-common-5.3.3-1.el5
Php53-bcmath-5.3.3-1.el5
Php53-mysql-5.3.3-1.el5
Php53-dba-5.3.3-1.el5
Php53-cli-5.3.3-1.el5
Php53-gd-5.3.3-1.el5
Php53-5.3.3-1.el5
Php53-pdo-5.3.3-1.el5
Note:
PHP version is greater than 5.3, or error, such as:
Anemometer requires PHP 5.3 or newer. You have 5.1.6
In addition to: Bcmath
Rpm-qa|grep Bcmath
Php53-bcmath-5.3.3-1.el5


4. Execute the setup script to create the user:
[[Email Protected]/var/www/html/anemometer]mysql-uroot-pxxx < Install.sql
[[Email protected]/var/www/html/anemometer]mysql-uroot-pxxx
MySQL > Grant all on slow_query_log.* to ' anemometer ' @ '% ' identified by ' test ';
MySQL > Grant SELECT on * * to ' anemometer ' @ '% ';
MySQL > Grant all on slow_query_log.* to ' anemometer ' @ ' localhost ';
MySQL > Grant SELECT on * * to ' anemometer ' @ ' localhost ';




5. Store the slow query log in the database by Pt-query-digest analysis:
Statement with PT version less than version 2.2:
Pt-query-digest--user=anemometer--password=test \
--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 \ "\
/root/test/localhost-slow.log


Statement with PT version greater than version 2.2:
Pt-query-digest--user=anemometer--password=test--review H=hd-119-186,d=slow_query_log,t=global_query_review-- History h=hd-119-186,d=slow_query_log,t=global_query_review_history--no-report--limit=0%--filter= "\ $event->{ Bytes} = Length (\ $event->{arg}) and \ $event->{hostname}=\ "$HOSTNAME \" "/root/test/localhost-slow.log


After performing this operation, the slow log analysis will be stored in the MySQL slow_query_log database.


Note:
Remember to ensure that the corresponding ' user ' @ ' host ' access rights




6. Modify the configuration information of the visual interface:
Cd/var/www/html/anemometer/conf
CP sample.config.inc.php config.inc.php
VI config.inc.php

And:


Note:
The hostname must be modified correctly and cannot be left as localhost, otherwise it will cause the final interface to be inaccessible.






7. Final local access via IP:
Http://192.168.44.130/anemometer



The results of the slow query log can be obtained after querying:









Reference article:
Https://github.com/box/Anemometer


https://rtcamp.com/tutorials/mysql/slow-query-log-anemometer/

"Reprint" uses anemometer to visualize MySQL slow query based on pt-query-digest