Anemometer 安装使用&过程中遇到的问题

时间:2024-05-22 19:54:47

项目地址:https://github.com/box/Anemometer

演示地址:http://lab.fordba.com/anemometer/

Anemometer 是一个图形化显示从MySQL慢日志的工具。

结合pt-query-digest,Anemometer可以很轻松的帮你去分析慢查询日志,让你很容易就能找到哪些SQL需要优化。

 

Anemometer 安装下载方式

A、下载安装:
下载路径:https://github.com/box/Anemometer
yum install -y unzip
wget https://github.com/box/Anemometer/archive/develop.zip
unzip develop.zip
mv Anemometer /var/www/html/anemometer

B、
下载安装:
安装目的:安装Anemometer应用
下载地址:https://github.com/box/Anemometer
下载包:git clone https://github.com/box/Anemometer.git
移动到对应路径:
mv Anemometer /var/www/html/anemometer ##目标慢查询数据库上,进行授权。


1、安装依赖包

yum install perl-DBI perl-DBD-MySQL perl-IO-Socket-SSL perl-Digest-MD5 -y

问题:

Removing mariadb-libs.x86_64 1:5.5.60-1.el7_5 - u due to obsoletes from installed mysql-community-libs-5.7.25-1.el7.x86_64
base/7/x86_64/filelists_db                                                                                                                                                 | 7.1 MB  00:00:22     
--> 正在使用新的信息重新解决依赖关系
--> 正在检查事务
---> 软件包 mariadb-libs.x86_64.1.5.5.60-1.el7_5 将被 安装
--> 正在处理依赖关系 libmysqlclient.so.18(libmysqlclient_18)(64bit),它被软件包 perl-DBD-MySQL-4.023-6.el7.x86_64 需要
--> 正在处理依赖关系 libmysqlclient.so.18(libmysqlclient_18)(64bit),它被软件包 2:postfix-2.10.1-7.el7.x86_64 需要
--> 正在处理依赖关系 libmysqlclient.so.18()(64bit),它被软件包 perl-DBD-MySQL-4.023-6.el7.x86_64 需要
--> 正在处理依赖关系 libmysqlclient.so.18()(64bit),它被软件包 2:postfix-2.10.1-7.el7.x86_64 需要
--> 正在处理依赖关系 libmysqlclient.so.18()(64bit),它被软件包 1:net-snmp-5.7.2-37.el7.x86_64 需要
--> 解决依赖关系完成
错误:软件包:1:net-snmp-5.7.2-37.el7.x86_64 (@anaconda)
          需要:libmysqlclient.so.18()(64bit)
错误:软件包:perl-DBD-MySQL-4.023-6.el7.x86_64 (base)
          需要:libmysqlclient.so.18(libmysqlclient_18)(64bit)
错误:软件包:2:postfix-2.10.1-7.el7.x86_64 (@anaconda)
          需要:libmysqlclient.so.18(libmysqlclient_18)(64bit)
错误:软件包:2:postfix-2.10.1-7.el7.x86_64 (@anaconda)
          需要:libmysqlclient.so.18()(64bit)
错误:软件包:perl-DBD-MySQL-4.023-6.el7.x86_64 (base)
          需要:libmysqlclient.so.18()(64bit)
您可以尝试添加 --skip-broken 选项来解决该问题
** 发现 3 个已存在的 RPM 数据库问题, 'yum check' 输出如下:
1:net-snmp-5.7.2-37.el7.x86_64 有缺少的需求 libmysqlclient.so.18()(64bit)
2:postfix-2.10.1-7.el7.x86_64 有缺少的需求 libmysqlclient.so.18()(64bit)
2:postfix-2.10.1-7.el7.x86_64 有缺少的需求 libmysqlclient.so.18(libmysqlclient_18)(64bit)

原因:

提示缺少 libmysqlclient.so.18()(64bit)
查询libmysqlclient.so

 [[email protected] data]# find / -name libmysqlclient.so*
/usr/lib64/mysql/libmysqlclient.so.20
/usr/lib64/mysql/libmysqlclient.so.20.3.12
/usr/lib64/mysql/libmysqlclient.so.18
/usr/lib64/mysql/libmysqlclient.so.18.1.0
[[email protected] data]# 

维度缺少libmysqlclient.so.18
检查到没有安装 mysql-community-libs-compat-5.7.22-1.el7.x86_64.rpm。

解决办法:

安装相应 rpm。

[[email protected] install_mysql]# wget https://downloads.mysql.com/archives/get/file/mysql-community-libs-compat-5.7.25-1.el7.x86_64.rpm
--2019-05-15 16:33:03--  https://downloads.mysql.com/archives/get/file/mysql-community-libs-compat-5.7.25-1.el7.x86_64.rpm
正在解析主机 downloads.mysql.com (downloads.mysql.com)... 137.254.60.14
正在连接 downloads.mysql.com (downloads.mysql.com)|137.254.60.14|:443... 已连接。
已发出 HTTP 请求,正在等待回应... 302 Found
位置:https://cdn.mysql.com/archives/mysql-5.7/mysql-community-libs-compat-5.7.25-1.el7.x86_64.rpm [跟随至新的 URL]
--2019-05-15 16:33:06--  https://cdn.mysql.com/archives/mysql-5.7/mysql-community-libs-compat-5.7.25-1.el7.x86_64.rpm
正在解析主机 cdn.mysql.com (cdn.mysql.com)... 104.118.77.179
正在连接 cdn.mysql.com (cdn.mysql.com)|104.118.77.179|:443... 已连接。
已发出 HTTP 请求,正在等待回应... 200 OK
长度:2116628 (2.0M) [application/x-redhat-package-manager]
正在保存至: “mysql-community-libs-compat-5.7.25-1.el7.x86_64.rpm.1”
100%[========================================================================================================================================================>] 2,116,628   1.84MB/s 用时 1.1s   
2019-05-15 16:33:09 (1.84 MB/s) - 已保存 “mysql-community-libs-compat-5.7.25-1.el7.x86_64.rpm.1” [2116628/2116628])

[[email protected] install_mysql]# rpm -ivh mysql-community-libs-compat-5.7.25-1.el7.x86_64.rpm
警告:mysql-community-libs-compat-5.7.25-1.el7.x86_64.rpm: 头V3 DSA/SHA1 Signature, ** ID 5072e1f5: NOKEY
准备中...                          ################################# [100%]
正在升级/安装...
   1:mysql-community-libs-compat-5.7.2################################# [100%]

重新执行命令,安装依赖包

yum install perl-DBI perl-DBD-MySQL perl-IO-Socket-SSL perl-Digest-MD5 -y
……
Transaction test succeeded
Running transaction
警告:RPM 数据库已被非 yum 程序修改。
  正在安装    : perl-Mozilla-CA-20130114-5.el7.noarch                                                                                                                                         1/8
  正在安装    : perl-Digest-1.17-245.el7.noarch                                                                                                                                               2/8
  正在安装    : perl-Net-LibIDN-0.12-15.el7.x86_64                                                                                                                                            3/8
  正在安装    : perl-Net-SSLeay-1.55-6.el7.x86_64                                                                                                                                             4/8
  正在安装    : perl-IO-Socket-IP-0.21-5.el7.noarch                                                                                                                                           5/8
  正在安装    : perl-IO-Socket-SSL-1.94-7.el7.noarch                                                                                                                                          6/8
  正在安装    : perl-Digest-MD5-2.52-3.el7.x86_64                                                                                                                                             7/8
  正在安装    : perl-DBD-MySQL-4.023-6.el7.x86_64                                                                                                                                             8/8
  验证中      : perl-Digest-MD5-2.52-3.el7.x86_64                                                                                                                                             1/8
  验证中      : perl-IO-Socket-IP-0.21-5.el7.noarch                                                                                                                                           2/8
  验证中      : perl-DBD-MySQL-4.023-6.el7.x86_64                                                                                                                                             3/8
  验证中      : perl-Net-SSLeay-1.55-6.el7.x86_64                                                                                                                                             4/8
  验证中      : perl-Net-LibIDN-0.12-15.el7.x86_64                                                                                                                                            5/8
  验证中      : perl-Digest-1.17-245.el7.noarch                                                                                                                                               6/8
  验证中      : perl-IO-Socket-SSL-1.94-7.el7.noarch                                                                                                                                          7/8
  验证中      : perl-Mozilla-CA-20130114-5.el7.noarch                                                                                                                                         8/8
已安装:
  perl-DBD-MySQL.x86_64 0:4.023-6.el7  perl-Digest-MD5.x86_64 0:2.52-3.el7  perl-IO-Socket-SSL.noarch 0:1.94-7.el7   

# 作为依赖被安装:
  perl-Digest.noarch 0:1.17-245.el7  perl-IO-Socket-IP.noarch 0:0.21-5.el7  perl-Mozilla-CA.noarch 0:20130114-5.el7  perl-Net-LibIDN.x86_64 0:0.12-15.el7  perl-Net-SSLeay.x86_64 0:1.55-6.el7

完毕!

 

2、安装 pt-query-digest工具

下载安装:

 https://www.percona.com/downloads/percona-toolkit/3.0.13/binary/redhat/7/x86_64/percona-toolkit-3.0.13-re85ce15-el7-x86_64-bundle.tar

 Wget https://www.percona.com/downloads/percona-toolkit/3.0.13/binary/redhat/7/x86_64/percona-toolkit-3.0.13-1.el7.x86_64.rpm

安装方式:

[[email protected] pt]# rpm -ivh percona-toolkit-3.0.10-1.el7.x86_64.rpm

警告:percona-toolkit-3.0.10-1.el7.x86_64.rpm: 头V4 DSA/SHA1 Signature, ** ID cd2efd2a: NOKEY
准备中...                          ################################# [100%]
正在升级/安装...
   1:percona-toolkit-3.0.10-1.el7     ################################# [100%]
[[email protected] pt]#

验证 pt 工具安装成功:

[[email protected] data]# pt-query-digest --version

pt-query-digest 3.0.10
[[email protected] data]#

 

3、anemometer 依赖的 LAMP 环境

A、关闭 selinx

setenforce 0

sed -i 's/enforcing/disabled/g' /etc/sysconfig/selinux

B、打开防火墙的443,13306,80端口

iptables -I INPUT -p tcp --dport 443 -j ACCEPT

iptables -I INPUT -p tcp --dport 80 -j ACCEPT

iptables -I INPUT -p tcp --dport 13306 -j ACCEPT

# service iptables save

systemclt save iptables

C、安装 Apache

yum install httpd httpd-devel -y

D、安装 php

yum install -y php php-mysql php-gd libjpeg* php-imap php-ldap php-odbc php-pear php-xml php-xmlrpc php-mbstring php-mcrypt php-bcmath php-mhash libmcrypt libmcrypt-devel php-fpm php-dba

E、修改时区

vim /etc/php.ini

修改为

date.timezone = PRC # 注意,前面逗号去掉

F、验证 httpd 服务的状态

[[email protected] pt]# systemctl start httpd.  #启动

[[email protected] pt]# systemctl stop httpd    #关闭

[[email protected] pt]# systemctl restart httpd #重启

[[email protected] pt]# systemctl status httpd  #查看

● httpd.service - The Apache HTTP Server
   Loaded: loaded (/usr/lib/systemd/system/httpd.service; disabled; vendor preset: disabled)
   Active: active (running) since 三 2019-05-15 16:48:37 CST; 4s ago
     Docs: man:httpd(8)
           man:apachectl(8)
Main PID: 10540 (httpd)
   Status: "Processing requests..."
   CGroup: /system.slice/httpd.service
           ├─10540 /usr/sbin/httpd -DFOREGROUND
           ├─10541 /usr/sbin/httpd -DFOREGROUND
           ├─10542 /usr/sbin/httpd -DFOREGROUND
           ├─10543 /usr/sbin/httpd -DFOREGROUND
           ├─10544 /usr/sbin/httpd -DFOREGROUND
           └─10545 /usr/sbin/httpd -DFOREGROUND
5月 15 16:48:37 localhost.localdomain systemd[1]: Starting The Apache HTTP Server...
5月 15 16:48:37 localhost.localdomain httpd[10540]: AH00558: httpd: Could not reliably determine the server's fully qualified domain name, using localhost.localdomain. Set the 'S...this message
5月 15 16:48:37 localhost.localdomain systemd[1]: Started The Apache HTTP Server.
Hint: Some lines were ellipsized, use -l to show in full.
[[email protected] pt]#

G、修改 httpd 服务名

[[email protected] pt]# cat /etc/httpd/conf/httpd.conf  |grep "ServerName"

Anemometer 安装使用&过程中遇到的问题

 

4、安装配置 anemometer

A、下载

下载:

git clone https://github.com/box/Anemometer.git

移动到对应路径:

mv Anemometer /var/www/html/anemometer

B、执行存储日志信息的 MySQL实例的初始化Anemometer 安装使用&过程中遇到的问题

执行 install.sql:

[[email protected] anemometer]# mysql -hlocalhost -uroot -proot -P3306 --default-character-set=utf8 < install.sql

该 install.sql 中创建的库slow_query_log,即实际慢日志记录所写入、存储的库。每个产生慢日志的 database源可以对应anemometer 写入实例中的一个 database,即修改 install.sql 内容。

问题 1:

执行脚本遇到问题,修改 用于记录分析后慢日志记录的 MySQL 实例的 sql_mode

[email protected]|mysql>select @@sql_mode;
+------------+
| @@sql_mode |
+------------+
|'ONLY_FULL_GROUP_BY,NO_AUTO_VALUE_ON_ZERO,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'|
+------------+
1 row in set (0.00 sec)

解决办法:

修改sql_mode 为空:

set global sql_mode='';

C、授权

在目标数据库(anemometer 应用所部署在的MySQL服务器,该 MySQL 用于存储分析统计后的 slow log 记录)上为 anemometer 应用授予相应权限

[email protected]|mysql>grant all privileges on *.* to 'anemometer'@'192.168.225.131';

Query OK, 0 rows affected (0.02 sec)  #ip 为anemometer应用部署的主机ip,我这里 anemometer和用于存储解析后的慢日志信息的 MySQL实例,在同一台机器上。

D、修改配置文件

增加explain读取用户密码信息

[[email protected] pt]# cd /var/www/html/anemometer/

[[email protected] anemometer]# cp conf/sample.config.inc.php conf/config.inc.php

[[email protected] anemometer]#  vim conf/config.inc.php

Anemometer 安装使用&过程中遇到的问题

E、修改配置文件

pt 工具解析后的慢日志信息存储实例相关信息

该配置文件指明,pt 分析后的慢日志信息存储的MySQL 实例和 user 相关信息

[[email protected] conf]# pwd

/var/www/html/anemometer/conf

vim datasource_localhost.inc.php

Anemometer 安装使用&过程中遇到的问题

F、访问

http://192.168.225.131/anemometer 

# IP 是部署 anemometer 应用的服务器 IP

Anemometer 安装使用&过程中遇到的问题

目前还没数据。因为还未导入目标 MySQL 实例的慢SQL信息。

 

G、导入慢日志

慢查询主机推送格式:

For pt-query-digest version < 2.2

pt-query-digest --user=anemometer --password=123456 \
--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

pt-query-digest --user=anemometer --password=123456 \ 
--review h=192.168.225.129,P=3306,D=slow_query_log,t=global_query_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\"" \
/data/mysql/slow.log

环境实际执行命令如下:

pt-query-digest --user=root --password=xxxx --review h=localhost,P=3306,D=slow_query_log,t=global_query_review --history h=localhost,D=slow_query_log,t=global_query_review_history --no-report --limit=0% --filter=" \$event->{Bytes} = length(\$event->{arg}) and \$event->{hostname}=\"$HOSTNAME\"" /data/contract-trade_slowlog_20190927.log

再次访问 anemometer,并执行查询:

Anemometer 安装使用&过程中遇到的问题

 

 

配置过程中遇到的问题:

问题 2 :

[[email protected] anemometer]# pt-query-digest --user=root --password=root --review h=localhost,P=3306,D=slow_query_log,t=global_query_review --history h=localhost,D=slow_query_log,t=global_query_review_history --no-report --limit=0% --filter=" \$event->{Bytes} = length(\$event->{arg}) and \$event->{hostname}=\"$HOSTNAME\"” /data/contract-trade_slowlog_20190927.log

Reading from STDIN ...

原因:

日志路径错误

解决方法:

[[email protected] anemometer]# pt-query-digest --user=root --password=root --review h=localhost,P=3306,D=slow_query_log,t=global_query_review --history h=localhost,D=slow_query_log,t=global_query_review_history --no-report --limit=0% --filter=" \$event->{Bytes} = length(\$event->{arg}) and \$event->{hostname}=\"$HOSTNAME\"" ./contract-trade_slowlog_20190927.log

[[email protected] anemometer]#

 

 

问题 3 :

Pipeline process 5 (iteration) caused an error: DBD::mysql::st execute failed: Out of range value for column 'checksum' at row 1 [for Statement "REPLACE INTO `slow_query_log`.`global_query_review_history`(`checksum`, `sample`, `hostname_max`, `db_max`, `ts_min`, `ts_max`, `ts_cnt`, `query_time_sum`, 

……

Will retry pipeline process 4 (iteration) 2 more times.

Pipeline process 5 (iteration) caused an error: DBD::mysql::st execute failed: Incorrect integer value: 'BDA124B35C75F2E7083E2FC6655FD7B4' for column 'checksum' at row 1 [for Statement "REPLACE INTO `slow_query_log`.`global_query_review_history`(`checksum`, `sample`, `hostname_max`, `db_max`, `ts_min`, `ts_max`, `ts_cnt`, `query_time_sum`, `query_time_min`

……

?, ?, ?)" with ParamValues: 0='BDA124B35C75F2E7083E2FC6655FD7B4', 1=‘select count(1)

……

Terminating pipeline because process 4 (iteration) caused too many errors.

截图如下:

Anemometer 安装使用&过程中遇到的问题

原因:

sql_mode开始并未设为空,因为某些原因,也无法修改该MySQL实例的 sql_mode

[email protected]|(none)>show variables like 'sql_mode';
+---------------+-----------------------------------------------------------------------------------------------------------+
| Variable_name | Value                                                                                                     |
+---------------+-----------------------------------------------------------------------------------------------------------+
| sql_mode      | STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+---------------+-----------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

Pt 解析后的 checksum 值为字符串,

Anemometer 安装使用&过程中遇到的问题

而表的字段类型为 bigint。

处理方法:

alter table global_query_review_history modify checksum varchar(100) not null;

 

 

问题 4:

Pipeline process 5 (iteration) caused an error: Redundant argument in sprintf at /usr/bin/pt-query-digest line 2556. Will retry pipeline process 4 (iteration) 2 more times. Pipeline process 5 (iteration) caused an error: Redundant argument in sprintf at /usr/bin/pt-query-digest line 2556. Will retry pipeline process 4 (iteration) 2 more times. The pipeline caused an error: Pipeline process 5 (iteration) caused an error: Redundant argument in sprintf at /usr/bin/pt-query-digest line 2556. Terminating pipeline because process 4 (iteration) caused too many errors.

原因:

旧版本pt-query-digest 的 bug

解决方法:

# wget percona.com/get/percona-toolkit.tar.gz

# tar zxvf percona-toolkit-<version>.tar.gz

# cd percona-toolkit-<version>

# perl Makefile.PL

# make

# make test

# make install

 

 

问题 5:

执行perl Makefile.PL 报错

原因:

缺少依赖包

解决办法:

yum install perl-ExtUtils-CBuilder perl-ExtUtils-MakeMaker