Anemometer慢日志展示

Anemometer 是一个MySQL慢日志展示的工具，结合pt-query-digest，输出较为详细的MySQL慢查询信息，有助于性能优化分析。Anemometer为B/S架构，WEB端依赖于LAMP，因此我们需要安装配置MySQL、PHP、Apache，其数据来源于pt-query-digest，因此我们还需要安装percona-toolkit。

Apache安装

解压apache

1

$ tar -xvf httpd-2.2.34.tar.gz

编译安装

1
2
3


$ cd httpd-2.2.34
$ ./configure --prefix=/usr/local/apache
$ make && make install -j 4

编辑配置文件(/usr/local/apache/conf/httpd.conf)

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22


LoadModule php7_module        modules/libphp7.so
ServerName 10.0.139.161:80
<Directory />
    Options FollowSymLinks
    AllowOverride None
    Order deny,allow
    Allow from all
    Satisfy all
</Directory>
<Directory "/usr/local/apache/htdocs">
    Options Indexes FollowSymLinks
    AllowOverride all
    Order Deny,Allow
    Allow from all
</Directory>
<FilesMatch \.php$>
   SetHandler application/x-httpd-php
</FilesMatch>
<IfModule dir_module>
    DirectoryIndex index.html index.php
</IfModule>
AddType application/x-httpd-php .php

启动apache

1
2


$ cp /usr/local/apache/bin/apachectl  /etc/init.d/apache
$ /etc/init.d/apache start

PHP安装

解压php

1

$ tar -xvf php-7.2.0.tar.gz

编译安装

1
2
3


$ cd php-7.2.0
$ ./configure --prefix=/usr/local/php --with-apxs2=/usr/local/apache/bin/apxs
$ make && make install -j 4

安装bcmath插件

1
2
3
4


$ cd php-7.2.0/ext/bcmath
$ /usr/local/php/bin/phpize
$ ./configure --with-php-config=/usr/local/php/bin/php-config
$ make && make install -j 4

安装mysqli插件

1
2
3
4


$ cd php-7.2.0/ext/mysqli
$ /usr/local/php/bin/phpize
$ ./configure --with-php-config=/usr/local/php/bin/php-config --with-mysqli=/usr/local/mysql/bin/mysql_config
$ make && make install -j 4

make过程中可能会出现error: ext/mysqlnd/mysql_float_to_double.h: No such file or directory错误，需要手动编辑mysql_float_to_double.h替换为自身的路径

编辑配置文件(/usr/local/php/lib/php.ini)

1
2
3
4


include_path = "/usr/local/apache/htdocs/anemometer/lib"
date.timezone ="Asia/Shanghai"
extension=bcmath.so
extension=mysqli.so

pt-toolkit安装

解压toolkit

1
2


$ tar -xvf percona-toolkit-3.1_x86_64.tar.gz
$ mv percona-toolkit-3.1 /usr/local/toolkit

添加环境变量

1
2


$ echo "export PATH=$PATH:/usr/local/apache/bin:/usr/local/php/bin:/usr/local/toolkit/bin">>/etc/profile
$ source /etc/profile

Anemometer安装

解压Anemometer

1

$ tar -xvf Anemometer-master.zip

将项目拷贝到apache下

1

$ mv Anemometer-master /usr/local/apache/htdocs/anemometer

编辑配置文件

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56


$ cd /usr/local/apache/htdocs/anemometer
$ cp conf/sample.config.inc.php conf/config.inc.php
$ vi conf/config.inc.php

$conf['datasources']['localhost'] = array(
        'host'  => '10.0.139.161',
        'port'  => 33006,
        'db'    => 'slow_query_log',
        'user'  => 'anemometer',
        'password' => 'Abcd123#',
        'tables' => array(
                'global_query_review' => 'fact',
                'global_query_review_history' => 'dimension'
        ),
        'source_type' => 'slow_query_log'
);

$conf['plugins'] = array(

        'visual_explain' => '/usr/local/toolkit/pt-visual-explain',
        'show_create'   => true,
        'show_status'   => true,

        'explain'       =>      function ($sample) {
                $conn = array();

                if (!array_key_exists('hostname_max',$sample) or strlen($sample['hostname_max']) < 5)
                {
                        return;
                }

                $pos = strpos($sample['hostname_max'], ':');
                if ($pos === false)
                {
                        $conn['port'] = 33006;
                        $conn['host'] = $sample['hostname_max'];
                }
                else
                {
                        $parts = preg_split("/:/", $sample['hostname_max']);
                        $conn['host'] = $parts[0];
                        $conn['port'] = $parts[1];
                }

                $conn['db'] = 'mysql';
                if ($sample['db_max'] != '')
                {
                        $conn['db'] = $sample['db_max'];
                }

                $conn['user'] = 'anemomoeter';
                $conn['password'] = 'Abcd123#';

                return $conn;
        },
);

导入数据表

1

$ mysql -uroot -p -h127.0.0.1 -P33006 < install.sql

创建mysql用户

1

mysql> grant select on *.* to 'anemometer'@'%' identified by Abcd123#;

mysql配置

1
2
3
4


mysql> set global long_query_time=1;
mysql> set global slow_query_log=on;
mysql> set global log_slow_admin_statements=1;
mysql> set global slow_query_log_file='/service/mysql/data/mysqlslow.log'

需要注意的是sql_mode参数会影响实际效果，建议设置为空

重启apache

1

$ /etc/init.d/apache restart

推送慢查询

1

$ pt-query-digest --user=root --password=Abcd123# --port=33006 --review h=10.0.139.161,D=slow_query_log,t=global_query_review --history h=10.0.139.161,D=slow_query_log,t=global_query_review_history --no-report --limit=0% --filter=" \$event->{Bytes} = length(\$event->{arg}) and \$event->{hostname}=\"$HOSTNAME\"" /service/mysql/data/mysqlslow.log

访问Anemometer[http://10.0.139.161/anemometer] index

脚本推送(crontab)

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29


#config anemometer server, the purpose is to push slow query to the remote anemometer server and store it.
anemometer_host="127.0.0.1"
anemometer_user="root"
anemometer_password="Abcd123#"
anemometer_port=33006
anemometer_db="slow_query_log"

#config mysql server, the purpose is to get the path of the slow query log.
mysql_client="/usr/local/mysql/bin/mysql"
mysql_user="root"
mysql_password="Abcd123#"
mysql_port=33006

#config slowqury dir to cd, and then delete the expired slow query file.
slowquery_dir="/service/data/mysqlslow.log"

#get the  path of the slow query log.
slowquery_file=`$mysql_client  -u$mysql_user -p$mysql_password  -S $mysql_socket -e "show variables like 'slow_query_log_file'"|grep log|awk '{print $2}'`
pt_query_digest="/usr/local/toolkit/bin/pt-query-digest"

#collect mysql slowquery log into lepus database.
$pt_query_digest --user=$anemometer_user --password=$anemometer_password --port=$anemometer_port --review h=$anemometer_host,D=$anemometer_db,t=global_query_review --history h=$anemometer_host,D=$anemometer_db,t=global_query_review_history --no-report --limit=0% --filter=" \$event->{Bytes} = length(\$event->{arg}) and \$event->{hostname}=\"$HOSTNAME:$mysql_port\"" $slowquery_file

#generate a new slow query log, the below is generate a new slow file per hour.
tmp_log=`$mysql_client -u$mysql_user -p$mysql_password -e "select concat('$slowquery_dir','slowquery_',date_format(now(),'%Y%m%d%H'),'.log');"|grep log|sed -n -e '2p'`

#use new slow file to config mysql slowquery
$mysql_client -u$mysql_user -p$mysql_password  -S $mysql_socket -e "set global slow_query_log = 0;set global slow_query_log_file = '$tmp_log';"
$mysql_client -u$mysql_user -p$mysql_password  -S $mysql_socket -e "set global slow_query_log = 1; "