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

Source: Internet
Author: User
Tags pear

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.