Visualize MySQL slow query using anemometer based on Pt-query-digest

If you ' re just completely itching to start using the This tool, here's what you need:

1, a MySQL database to store query analysis data in.

2, Pt-query-digest. You could as well just get the whole Percona Toolkit when you ' re at it:)

3, a slow query log from a MySQL server (see the slow query log for info on getting one)

4, a webserver with PHP

The first first

[Email protected] anemometer]# wget percona.com/get/percona-toolkit.rpm[[email protected] ~]# yum install-y percona-toolkit.rpm

Then,pt-query-digest reference configuration is given

[Code collapse= "false"]pt-query-digest--user= database user name--password= database password--review h= database IP and domain name, D=slow_query_log,t=global _query_review--history h= database IP and domain name, d=slow_query_log,t=global_query_review_history--no-report--limit=0%--filter= " \ $event-> {Bytes} = length (\ $event->{ ARG}) and \ $event-> {hostname}=\ "$HOSTNAME \" "Database log file address [/code]

Next,grammar and important options

pt-query-digest [OPTIONS] [FILES] [DSN]
--create-review-table when you use the--review parameter to output the analysis results to a table, it is created automatically if no tables are available.
--create-history-table when you use the--history parameter to output the analysis results to a table, it is created automatically if no tables are available.
--filter the input slow query is matched and filtered by the specified string for analysis
--limit limit the output percent or number, the default value is 20, the slowest 20 statement output, if 50% is the total response time from the largest to the small sort, the output to the sum of 50% position cutoff.
--host MySQL server address
--user MySQL user name
--password MySQL user password
--history the analysis results to the table, the analysis results are detailed, the next time you use--history, if the same statement exists, and the query is in a different time period and history table, it will be recorded in the data table, You can compare the historical changes of a type of query by querying the same checksum.
--review Save the analysis results to a table, this analysis only to the query criteria parameterized, a type of query a record, relatively simple. The next time you use--review, if you have the same statement analysis, it will not be recorded in the datasheet.
--output analysis Results output types, values can be report (standard analysis Reports), Slowlog (Mysql slow log), JSON, Json-anon, general use report for readability.
--since from when to parse, the value is a string, can be a specified "Yyyy-mm-dd [hh:mm:ss]" format point in time, or it can be a simple time value: s (seconds), H (Hours), M (minutes), D (days), such as 12h means starting from 12 hours ago statistics.
--until cut-off time, with-since can be analyzed for a period of time slow query.

Install anemometer

First up, grab the anemometer code from GitHub. Navigate to the document root of your Web server and snag a copy of the Box anemometer code.

[[email protected] ~]# git clone git://github.com/box/anemometer.git anemometer

Or, if you have 9418 port closed:

[[email protected] ~]# git clone

Then change your current working directory to the Anemometer directory:

[Email protected] ~]# CD anemometer

Next, you should connect to the MySQL database "looking to store" the analysis data in and issue the following Comman D:

[Email protected] anemometer]# mysql-uroot-p123456 < install.sql [[email protected] anemometer]# mysql-uroot-p1234 56-e "Grant all on slow_query_log.* to by ' 123456 ';"

Next, grab that slow the query log file you have (mine's called "Slow.log"!), and run Pt-query-digest on It:note:i ' m using a BASH 3.0 shell here on my MySQL database server! The "$HOSTNAME" variable properly replaces with "db.example.com")

[Email protected] ~]# Rpm-qa|grep percona-toolkit percona-toolkit-2.2.16-1.noarch

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.log

For pt-query-digest version >= 2.2

[Email protected] ~]# pt-query-digest--user=anemometer--password=123456--review d=slow_query_log,t=global_query_ Review--history d=slow_query_log,t=global_query_review_history--no-report--limit=0%--filter= "\ $event->{bytes } = Length (\ $event->{arg}) and \ $event->{hostname}=\ "$HOSTNAME \" "/usr/local/mysql/var/slow.log

You could see a error like above, that ' s okay! Todo:explain What the options above is doing.

Pipeline process One (aggregate fingerprint) caused an error:argument "57A" isn ' t numeric in numeric GT (>) at (eval 40 Line 6, <> line 27.Pipeline process one (aggregate fingerprint) caused an error:argument "57B" isn ' t numeric in Nu Meric GT (>) at (eval +) line 6, <> line 28.Pipeline process one (aggregate fingerprint) caused an error:argumen T "57C" isn ' t numeric in numeric GT (>) at (eval +) line 6, <> line 29.

View the data!

Now, navigate to the document root of your Web server and copy the sample config so you can edit it:

[[email protected] conf]# Cd/home/wwwroot/anemometer/conf[[email protected] conf]# CP sample.config.inc.php config.inc.php

The sample config explains every setting is want to change in it. At the very least, make sure you set the Datasource to the MySQL database you ' re storing the analyzed digest information I N:

 $conf [' datasources '] [' localhost '] = array (      ' host '   =>  ' db.example.com ',     ' Port '   => 3306,      ' db '     =>  ' slow_query_log ',     ' user '   =>  ' anemometer ',     ' password '  =>  ' Supersecurepass ',      ' Tables '  => array (         ' Global _query_review '  =>  ' fact ',         ' global_query_review_ History '  =>  ' dimension '     ); 

In addition, the "explain" plugin are enabled by default in the current release and your ' ll need to setup the username an d Password it uses to the account of that have privileges to explain queries on a given schema on a host. For example, if you ' re digesting slow logs that primarily contain queries from the "World" database on db.example.com, you ' ll need to ensure so the user account is put into the following section of the Config have the the necessary privileges on The ' World ' database on db.example.com. To does this, scroll the containing the plugins configuration and the ' user ' and ' Password ' parameters to a appropriate account:

$conf [' plugins '] = Array (...)        ' Explain ' = function ($sample) {$conn [' user '] = ' anemometer ';        $conn [' password '] = ' supersecurepass ';    return $conn; },);

Finally,you can look through

http://10.0.0.5/anemometer/

This article is from the "it--Brother" blog, please make sure to keep this source http://402753795.blog.51cto.com/10788998/1832976

Visualize MySQL slow query using anemometer based on Pt-query-digest