利用Anemometer做mysql慢日志的查询与可视化

Anemometer 是一个图形化显示MySQL慢日志的工具。结合pt-query-digest，Anemometer可以很轻松的帮你去分析慢查询日志，让你很容易就能找到哪些SQL需要优化。

一、首先安装LNMP环境

二、percona-toolkit工具的安装

pt-query-digest是percona-toolkit里面一个工具，其作用就是分析慢查询日志，将MySQL慢查询日志进行统计并友好的显示出来

1、安装依赖包

[root@zabbix_server bin]# yum install perl-DBI perl-DBD-MySQL perl-IO-Socket-SSL perl-Digest-MD5 -y

2、下载percona-toolkit二进制包

[root@zabbix_server conf.d]# mkdir /usr/local/src/percona-toolkit
[root@zabbix_server conf.d]# cd /usr/local/src/percona-toolkit
[root@zabbix_server percona-toolkit]# wget https://www.percona.com/downloads/percona-toolkit/3.0.10/binary/tarball/percona-toolkit-3.0.10_x86_64.tar.gz
--2020-04-07 16:44:49--  https://www.percona.com/downloads/percona-toolkit/3.0.10/binary/tarball/percona-toolkit-3.0.10_x86_64.tar.gz
正在解析主机 www.percona.com... 74.121.199.234
正在连接 www.percona.com|74.121.199.234|:443... 已连接。
已发出 HTTP 请求，正在等待回应... 200 OK
长度：8170395 (7.8M) [application/x-gzip]
正在保存至: “percona-toolkit-3.0.10_x86_64.tar.gz”

28% [============================>                                                                             ] 2,312,153    693K/s eta(英国中部时33% [==================================>                                                                       ] 2,770,905    759K/s eta(英国中部时39% [========================================>                                                                 ] 3,229,657    817K/s eta(英国中部时45% [==============================================>                                                           ] 3,688,409    871K/s eta(英国中部时50% [====================================================>                                                     ] 4,147,161    916K/s eta(英国中部时56% [==========================================================>                                               ] 4,605,913    953K/s eta(英国中部时61% [================================================================>                                         ] 5,064,665    986K/s eta(英国中部时67% [======================================================================>                                   ] 5,523,417   1014K/s eta(英国中部时73% [============================================================================>                             ] 5,982,169   1.02M/s eta(英国中部时77% [=================================================================================>                        ] 6,326,233   1.01M/s eta(英国中部时83% [=======================================================================================>                  ] 6,850,521   1.10M/s eta(英国中部时87% [============================================================================================>             ] 7,178,201   1.15M/s eta(英国中部时91% [================================================================================================>         ] 7,505,881   1.25M/s eta(英国中部时96% [=====================================================================================================>    ] 7,866,329   1.29M/s eta(英国中部时100%[=========================================================================================================>] 8,170,395   1.37M/s   in 7.1s    

2020-04-07 16:44:59 (1.09 MB/s) - 已保存 “percona-toolkit-3.0.10_x86_64.tar.gz” [8170395/8170395])

[root@zabbix_server percona-toolkit]#

解压

[root@zabbix_server percona-toolkit]# tar -zxvf percona-toolkit-3.0.10_x86_64.tar.gz 
percona-toolkit-3.0.10/
percona-toolkit-3.0.10/CONTRIBUTE.md
percona-toolkit-3.0.10/Makefile.PL
percona-toolkit-3.0.10/docker-compose.yml
percona-toolkit-3.0.10/CONTRIBUTING.md
percona-toolkit-3.0.10/Gopkg.lock
percona-toolkit-3.0.10/README.md
percona-toolkit-3.0.10/bin/
percona-toolkit-3.0.10/bin/pt-summary
percona-toolkit-3.0.10/bin/pt-slave-delay
percona-toolkit-3.0.10/bin/pt-mongodb-query-digest
percona-toolkit-3.0.10/bin/pt-slave-restart
percona-toolkit-3.0.10/bin/pt-variable-advisor
percona-toolkit-3.0.10/bin/pt-fingerprint
percona-toolkit-3.0.10/bin/pt-secure-collect
percona-toolkit-3.0.10/bin/pt-index-usage
percona-toolkit-3.0.10/bin/pt-archiver
percona-toolkit-3.0.10/bin/pt-find
percona-toolkit-3.0.10/bin/pt-heartbeat
percona-toolkit-3.0.10/bin/pt-fifo-split
percona-toolkit-3.0.10/bin/pt-fk-error-logger
percona-toolkit-3.0.10/bin/pt-mysql-summary
percona-toolkit-3.0.10/bin/pt-online-schema-change
percona-toolkit-3.0.10/bin/pt-table-usage
percona-toolkit-3.0.10/bin/pt-align
percona-toolkit-3.0.10/bin/pt-query-digest
percona-toolkit-3.0.10/bin/pt-ioprofile
percona-toolkit-3.0.10/bin/pt-visual-explain
percona-toolkit-3.0.10/bin/pt-stalk
percona-toolkit-3.0.10/bin/pt-mext
percona-toolkit-3.0.10/bin/pt-table-checksum
percona-toolkit-3.0.10/bin/pt-show-grants
percona-toolkit-3.0.10/bin/pt-pmp
percona-toolkit-3.0.10/bin/pt-upgrade
percona-toolkit-3.0.10/bin/pt-diskstats
percona-toolkit-3.0.10/bin/pt-sift
percona-toolkit-3.0.10/bin/pt-config-diff
percona-toolkit-3.0.10/bin/pt-slave-find
percona-toolkit-3.0.10/bin/pt-kill
percona-toolkit-3.0.10/bin/pt-duplicate-key-checker
percona-toolkit-3.0.10/bin/pt-deadlock-logger
percona-toolkit-3.0.10/bin/pt-mongodb-summary
percona-toolkit-3.0.10/bin/pt-table-sync
percona-toolkit-3.0.10/lib/
percona-toolkit-3.0.10/docs/
percona-toolkit-3.0.10/docs/percona-toolkit.pod
percona-toolkit-3.0.10/Gopkg.toml
percona-toolkit-3.0.10/MANIFEST
percona-toolkit-3.0.10/COPYING
percona-toolkit-3.0.10/Changelog
percona-toolkit-3.0.10/INSTALL
[root@zabbix_server percona-toolkit]# mv percona-toolkit-3.0.10 /usr/local/

3、运行查看版本

[root@zabbix_server bin]# ./pt-query-digest --version
pt-query-digest 3.0.10
[root@zabbix_server bin]#

三、安装anemometer

1、下载源码

[root@zabbix_server www]# cd /var/www/
[root@zabbix_server www]# git clone git://github.com/box/Anemometer.git anemometer
Initialized empty Git repository in /var/www/anemometer/.git/
remote: Enumerating objects: 1218, done.
remote: Total 1218 (delta 0), reused 0 (delta 0), pack-reused 1218
Receiving objects: 100% (1218/1218), 1.51 MiB | 2 KiB/s, done.
Resolving deltas: 100% (627/627), done.
[root@zabbix_server www]#

2、在nginx配置anemometer根目录

server {
  2     listen       8083;
  3     server_name  172.28.18.75;
  4 
  5     #charset koi8-r;
  6     #access_log  /var/log/nginx/host.access.log  main;
  7 
  8     location / {
  9       root   /var/www/anemometer;
 10       index  index.php index.html index.htm;
 11     }
 12

浏览器打开http://172.28.18.75:8083

3、配置目标数据库源

首先复制一份示例配置文件sample.config.inc.php并改名为config.inc.php

[root@zabbix_server conf]# cp sample.config.inc.php config.inc.php 
[root@zabbix_server conf]#

首先修改数据库连接配置文件datasource_localhost.inc.php

[root@zabbix_server conf]# vim datasource_localhost.inc.php 

  1 <?php
  2 $conf['datasources']['localhost'] = array(
  3     'host'  => 'localhost',
  4     'port'  => 3306,
  5     'db'    => 'slow_query_log',
  6     'user'  => 'root',
  7     'password' => 'xxxxxxxx',
  8     'tables' => array(
  9         'global_query_review' => 'fact',
 10         'global_query_review_history' => 'dimension'
 11     ),
 12     'source_type' => 'slow_query_log'
 13 );
 14

db：slow_query_log :这是慢查询日志存放的数据库，由/var/www/anemometer/install.sql脚本创建，下面的操作将会有导入这个SQL文件

4、导入install.sql`初始化数据源的数据库表的配置`

mysql> source /var/www/anemometer/install.sql;
Query OK, 0 rows affected, 1 warning (0.05 sec)

Query OK, 1 row affected (0.00 sec)

Database changed
Query OK, 0 rows affected (0.10 sec)

ERROR 1067 (42000): Invalid default value for 'ts_min'

报错，查看install.sql,发现ts_min列的定义为`ts_min` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',

这是mysql5.7以上版本里设置的sql_mode参数的关系，查看sql_mode参数设置

mysql> show variables like '%sql_mode%';
+---------------+-------------------------------------------------------------------------------------------------------------------------------------------+
| Variable_name | Value                                                                                                                                     |
+---------------+-------------------------------------------------------------------------------------------------------------------------------------------+
| sql_mode      | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+---------------+-------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

NO_ZERO_IN_DATE,NO_ZERO_DATE：表示不允许日期数据为零值，将sql_mode重新设置

mysql> set sql_mode='STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show variables like '%sql_mode%';
+---------------+-------------------------------------------------------------------------------------------+
| Variable_name | Value                                                                                     |
+---------------+-------------------------------------------------------------------------------------------+
| sql_mode      | STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+---------------+-------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

再导入install.sql

mysql> source /var/www/anemometer/install.sql;
Query OK, 1 row affected (0.00 sec)

Query OK, 1 row affected (0.00 sec)

Database changed
Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.03 sec)

mysql>

新建了一个slow_query_log数据库里面新建了两个表:global_query_review和global_query_review_history，用于保存从目标数据库利用pt-query-digest工具收集的慢日志数据。

5、在目标数据库上使用pt-query-digest工具收集日志推送到slow_query_log数据库中

[root@zabbix_server bin]# ./pt-query-digest --user=root --password=Zaq1xsw@ --review h=172.28.18.75,D=slow_query_log,t=global_query_review --history h=172.28.18.75,D=slow_query_log,t=global_query_review_history --no-report --limit=0% --filter=" $event->{Bytes} = length($event->{arg}) and $event->{hostname}="$HOSTNAME"" /home/mysql_data/mysql/zabbix_server-slow.log

执行完上面语句，global_query_review和global_query_review_history就会有统计的数据信息

刷新页面，可以看到慢日志数据展示

四、数据库说明

主要字段说明：

hostname_max : MySQL服务所在主机名称
db_max: 数据库名称
checksum : 同global_query_review表中的checksum，两张表通过该值关联
sample : sql示例
ts_min : 本次统计(每10分钟一次)该类型sql语句出现的最小时间
ts_max: 本次统计(每10分钟一次)该类型sql语句出现的最大时间
ts_cnt : 本次统计该sql语句出现的次数
Query_time_sum : 本次统计该类型sql语句花费的总时间
Query_time_min : 本次统计该类型sql语句执行最快的那个sql语句花费的时间
Query_time_max: 本次统计该类型sql语句执行最慢的那个sql语句花费的时间
Query_time_pct_95: 本次统计该类型sql语句执行时间位于95%分位的sql执行时间
Query_time_stddev: 本次统计该类型sql语句执行时间标准差（统计学概念）
Query_time_median: 本次统计该类型sql语句执行时间位于中位数位置的sql执行时间
index_ratio：表示的是扫描的行数/返回的结果行数

五、配置anemometer

1、首先将“custom_fields“数组里的”'snippet' => 'LEFT(dimension.sample,50)'改为'snippet' => 'dimension.sample',

2、history表里的ts_cnt列是Sum出来的，如果我们5分钟收集一次日志，这个结果完全不是想要的，增加一个数组元素'query_cnt' => 'MAX(ts_cnt)',这个数据为语句的执行次数，并且将history_defaults和report_defaults的table_fields配置中将ts_cnt改为了query_cnt。

3、单个查询的过去90天历史记录里，它是以ts_min为group by的，因为history表里ts_min只有一个值，既语句第一次执行的时间，所以这会只显示一条数据，而不是每天的数据，这里改成ts_max。既语句最后执行时间将'date' => 'DATE(ts_min)改为'ate' => 'DATE(ts_max)'

4、$conf['report_defaults'] = array数组保存页面统计列表里的数据字段和查询条件，可以在这里进行个性化设置，来显示需要的数据

// custom fields
339     'custom_fields' => array(
340         'checksum' => 'checksum',
341         'snippet' => 'dimension.sample',
342         'query_cnt' => 'MAX(ts_cnt)',
343         'query_max' => 'ROUND(MAX(query_time_max),2)',
344         'query_min' => 'ROUND(MIN(Query_time_min),2)',
345         'query_avg' => 'ROUND(SUM(Query_time_sum)/SUM(ts_cnt),2)',
346         'lock_max' => 'ROUND(MAX(Lock_time_max),4)',
347         'lock_min' => 'ROUND(MIN(Lock_time_min),4)',
348         'lock_avg' => 'ROUND(SUM(Lock_time_sum)/SUM(ts_cnt),4)',
349         'rows_sent_avg' => 'ROUND(SUM(Rows_sent_sum)/SUM(ts_cnt))',
350         'rows_examined_avg' => 'ROUND(SUM(Rows_examined_sum)/SUM(ts_cnt))',
351         'date'  => 'DATE(ts_max)',
352         'hour'  => 'substring(ts_min,1,13)',
353         'hour_ts'   => 'round(unix_timestamp(substring(ts_min,1,13)))',
354         'minute_ts'     => 'round(unix_timestamp(substring(ts_min,1,16)))',
355         'minute'        => 'substring(ts_min,1,16)',

5、对于某些慢日志里Query ID 是0xFF6BA40AE1ADBA294B52E89F1929E3F8类似这样的ID，在页面显示中会查询不到历史记录，这是因为Anemometer把checksum列设置为了bigint类型，所以，我们需要将checksum列设置为varchar(60)字符型，并修改PHP代码中相应的地方

修改config.inc.php文件，搜索"dec2hex"

将这句注释掉

360     'callbacks'     => array(
361         'table' => array(
362             'date'  => function ($x) { $type=''; if ( date('N',strtotime($x)) >= 6) { $type = 'weekend'; } return array($x,$type); },
363             #'checksum' => function ($x) { return array(dec2hex($x), ''); }
364         )
365     )

修改lib/Anemometer.php文件搜索"translate_checksum()"函数，将里面的"return $this->bchexdec($checksum);"注释掉，在下面增加一句"return $checksum;"

private function translate_checksum($checksum)
359     {
360     if (!in_array($this->data_model->get_source_type(), array('slow_query_log','default')))
361     {
362         return $checksum;
363     }
364 
365         if (preg_match('/^[0-9]+$/', $checksum))
366         {
367             return $checksum;
368         }
369         else if (preg_match('/^[0-9A-Fa-f]+$/', $checksum))
370         {
371             #return $this->bchexdec($checksum);
372             return $checksum;
373         }
374         else if (strlen($checksum) == 0)
375         {
376             return null;
377         }
378         else
379         {
380             throw new Exception("Invalid query checksum");
381         }
382     }

修改AnemometerModel.php 文件里相关使用$checksum作为条件查询的语句，在变量的两边加上单引号，表示引用的是字符串

搜索"$checksum"进行相应修改

public function checksum_exists($checksum) {
200         $checksum_field_name = $this->get_field_name('checksum');
201         $query = "SELECT `{$checksum_field_name}` FROM `{$this->fact_table}` WHERE `{$checksum_field_name}`='" . $this->mysqli->real_escape_string($checksum) . "'";

public function update_query($checksum, $fields) {
218         $mysqli = $this->mysqli;
219         $checksum_field_name = $this->get_field_name('checksum');
220         $sql = "UPDATE `{$this->fact_table}` SET ";
221         $sql .= join(
222                 ',', array_map(
223                         function ($x, $y) use ($mysqli) {
224                             if ($y == 'NULL') {
225                                 return "{$x} = NULL";
226                             }
227                             return "`{$x}` = "" . $mysqli->real_escape_string($y) . '"';
228                         }, array_keys($fields), array_values($fields)
229                 )
230         );
231         $sql .= " WHERE `{$checksum_field_name}`='" . $this->mysqli->real_escape_string($checksum) . "'";

public function get_query_by_checksum($checksum) {
244         $checksum_field_name = $this->get_field_name('checksum');
245         $result = $this->mysqli->query("SELECT * FROM `{$this->fact_table}` WHERE `{$checksum_field_name}`='{$checksum}'");
246         check_mysql_error($result, $this->mysqli);
247         if ($row = $result->fetch_assoc()) {
248             return $row;
249         }
250         return null;
251     }

public function get_query_samples($checksum, $limit = 1, $offset = 0) {
262         $checksum_field_name = $this->get_field_name('checksum');
263         $time_field_name = $this->get_field_name('time');
264         $table = $this->dimension_table;
265         if ($this->get_source_type() == 'performance_schema')
266         {
267             $table = $this->fact_table;
268         }
269         $sql = "SELECT * FROM `{$table}` WHERE `{$checksum_field_name}`='{$checksum}' ORDER BY `{$time_field_name}` DESC LIMIT {$limit} OFFSET     {$offset}";
270         return $this->mysqli->query($sql);
271     }

public function checksum_exists($checksum) {
200         $checksum_field_name = $this->get_field_name('checksum');
201         $query = "SELECT `{$checksum_field_name}` FROM `{$this->fact_table}` WHERE `{$checksum_field_name}`='" . $this->mysqli->real_escape_string($checksum) . "'";
202         //print "query: {$query}<br>"; 
203         $result = $this->mysqli->query($query);
204         check_mysql_error($result, $this->mysqli);
205         if ($result->num_rows) {
206             return true;
207         }
208         return false;
209     }

保存退出后，即可查询正常