0%

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; "