Anemometer and pt-query-digest combine to visualize MySQL slow query

Pt-query-digest although powerful, but after all, there is no visual display of the Web interface, we can use Anemometer as the display of pt-query-digest execution results, Anemometer is an open source software developed specifically for MySQL's slow query, with addresses in Http://www.oschina.net/p/anemometer

First, install lamp environment
1, installation

Yum install  httpd php *bcmath* *mysqli*-  y

2, view

[Email protected] ~]# Rpm-qa|grep httphttpd-tools-2.4.6-40.el7.centos.x86_64httpd-2.4.6-40.el7.centos.x86_64

[Email protected] ~]# Rpm-qa|grep phpphp-bcmath-5.4.16-36.el7_1.x86_64php-cli-5.4.16-36.el7_1.x86_ 64php-mysql-5.4.16-36.el7_1.x86_64php-pear-mdb2-driver-mysqli-1.5.0-0.8.b4.el7.noarchphp-common-5.4.16-36.el7_ 1.x86_64php-pdo-5.4.16-36.el7_1.x86_64php-process-5.4.16-36.el7_1.x86_ 64php-pear-mdb2-2.5.0-0.9.b5.el7.noarchphp-5.4.16-36.el7_1.x86_64php-xml-5.4.16-36.el7_1.x86_ 64php-pear-1.9.4-21.el7.noarch

[Email protected] ~]# Rpm-qa|grep bcmathphp-bcmath-5.4.16-36.el7_1.x86_64

4, modify the servername in httpd

Second, anemometer configuration
1, Execute SQL script

mysql-uroot-p123456 </var/www/html/anemometer/install.sql

2. Add permissions to use Anemometer

Grant all on slow_query_log.* to ' anemometer ' @ '% ' identified by ' 123456 '; Grant SELECT On *. * to ' anemometer ' @ '% ' IdentiFi Ed by ' 123456 ';

The permission given to ' on * * t ' is because each table may be in a different library when doing the execution plan, need use database;

3, set config file, modify Ip,host,port,db,user,password
Cd/var/www/html/anemometer/conf
CP sample.config.inc.php config.inc.php
VI config.inc.php

4, start

Servier httpd Start

5, visit

Http://192.168.44.130/anemometer

Third, the problems encountered
1, if the interface is blank, view the log

Vi/var/log/httpd/error_log

If you see an error like the following, turn off Selinux:setenforce 0 (preferably set in the configuration file so that the problem recurs after a reboot)

2, the interface shows no Global_query_review table

Because we do not have this table in the Slow_query_log Library of the profile definition, any data from the screen is obtained from the tables, and the format is the same as the result of pt-query-digest execution, so you want to see the data, you need to first execute Pt-query-digest and Import the results to the table command.

Pt-query-digest--user=root--password=123456--review h=127.0.0.1,d=slow_query_log,t=global_query_review--history H=127.0.0.1,d=slow_query_log,t=global_query_review_history  --create-review-table/usr/local/mysql/log/ Slow.log

Or

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

Commands that import data are typically configured with timed tasks that import slow logs into the database at regular intervals, which can be tailored to your needs

There will be two tables for Global_query_review and Global_query_review_history.
After execution, refresh the page

3. View SQL Specific information

After jumping to the screen, click Explain Plan, there may be "error inQuery Explain plugin:missing field host" errors, need to modify the configuration file
This should be dynamic, but now it's just a test library, so write the DB name directly

Modify complete, restart httpd

Deployment reference: Http://www.tuicool.com/articles/Vny2Y3Y

Anemometer and pt-query-digest combine to visualize MySQL slow query