使用Anemometer基于pt-query-digest將MySQL慢查詢可視化

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

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

2、pt-query-digest.You may as well just get the whole Percona Toolkit while 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

[root@xiaoya anemometer]# wget percona.com/get/percona-toolkit.rpm
[root@xiaoya ~]# yum install -y percona-toolkit.rpm

Then,Pt-query-digest reference configuration is given

[code collapse="false"]
pt-query-digest --user=數據庫用戶名 --password=數據庫密碼 --review h=數據庫IP和域名,D=slow_query_log,t=global_query_review --history h=數據庫IP和域名,D=slow_query_log,t=global_query_review_history --no-report --limit=0% --filter=" \$event->{Bytes} = length(\$event->{arg}) and \$event->{hostname}=\"$HOSTNAME\"" 數據庫日志文件地址
[/code]

next,Grammar and important options

pt-query-digest [OPTIONS] [FILES] [DSN]
--create-review-table 當使用--review參數把分析結果輸出到表中時，如果沒有表就自動創建。
--create-history-table 當使用--history參數把分析結果輸出到表中時，如果沒有表就自動創建。
--filter 對輸入的慢查詢按指定的字符串進行匹配過濾后再進行分析
--limit限制輸出結果百分比或數量，默認值是20,即將最慢的20條語句輸出，如果是50%則按總響應時間占比從大到小排序，輸出到總和達到50%位置截止。
--host MySQL服務器地址
--user mysql用戶名
--password mysql用戶密碼
--history 將分析結果保存到表中，分析結果比較詳細，下次再使用--history時，如果存在相同的語句，且查詢所在的時間區間和歷史表中的不同，則會記錄到數據表中，可以通過查詢同一CHECKSUM來比較某類型查詢的歷史變化。
--review 將分析結果保存到表中，這個分析只是對查詢條件進行參數化，一個類型的查詢一條記錄，比較簡單。當下次使用--review時，如果存在相同的語句分析，就不會記錄到數據表中。
--output 分析結果輸出類型，值可以是report(標準分析報告)、slowlog(Mysql slow log)、json、json-anon，一般使用report，以便于閱讀。
--since 從什么時間開始分析，值為字符串，可以是指定的某個”yyyy-mm-dd [hh:mm:ss]”格式的時間點，也可以是簡單的一個時間值：s(秒)、h(小時)、m(分鐘)、d(天)，如12h就表示從12小時前開始統計。
--until 截止時間，配合—since可以分析一段時間內的慢查詢。

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.

[root@xiaoya ~]# git clone git://github.com/box/Anemometer.git anemometer

or ,if you have 9418 port closed:

[root@xiaoya ~]# git clone

then change your current working directory to the anemometer directory:

[root@xiaoya ~]# cd anemometer

Next, you should connect to the MySQL database you're looking to store the analysis data in and issue the following command:

[root@xiaoya anemometer]# mysql -uroot -p123456 < install.sql 
[root@xiaoya anemometer]# mysql -uroot -p123456 -e "grant ALL ON slow_query_log.* to 
 BY '123456';"

 Next, grab that slow 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! This is so the "$HOSTNAME" variable properly replaces with "db.example.com")

[root@xiaoya ~]# 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

[root@xiaoya ~]# 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 may see an error like above, that's okay! TODO: explain what the options above are doing.

Pipeline process 11 (aggregate fingerprint) caused an error: Argument "57A" isn't numeric in numeric gt (>) at (eval 40) line 6, <> line 27.

Pipeline process 11 (aggregate fingerprint) caused an error: Argument "57B" isn't numeric in numeric gt (>) at (eval 40) line 6, <> line 28.

Pipeline process 11 (aggregate fingerprint) caused an error: Argument "57C" isn't numeric in numeric gt (>) at (eval 40) 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:

[root@xiaoya conf]# cd /home/wwwroot/anemometer/conf
[root@xiaoya conf]# cp sample.config.inc.php config.inc.php

The sample config explains every setting you may 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 in:

$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 is enabled by default in the current release and you'll need to setup the username and password it uses to an account that has 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 that the user account you put into the following section of the config has the necessary privileges on the "world" database on db.example.com. To do this, scroll down in the sample config to the section containing the plugins configuration and change the 'user' and 'password' parameters to an 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/