使用Anemometer基于pt-query-digest将MySQL慢查询可视化搭建文档

#使用Anemometer基于pt-query-digest将MySQL慢查询可视化搭建文档

参考资料: https://github.com/box/Anemometer http://www.jianshu.com/p/092fd6359e43 http://ourmysql.com/archives/1359

##部署环境 centos6.5+mysql5.5+php5.3 ##思路 在测试机上安装Anemometer+pt-query-digest,然后拉取线上的慢日志，让开发查看慢日志。

##安装步骤

1、安装percona-toolkit-2.2.12-1.noarch.rpm 2、yum install php-bcmath.x86_64 -y 3、建立一个slow.domain.com的虚拟主机，并在本地配置HOST 4、在线上数据库授权；1.1.1.1为测试机IP

mysql -uroot -p`cat /etc/sqlpass` -e "GRANT SELECT ON *.* TO \'box_explain\'@\'1.1.1.1\' IDENTIFIED BY \'superSecurePass\';" 5、把线上的slow.log拉一份到测试机 6、在测试机上下载安装Anemometer

cd /data/web/slow.domain.com git clone https://github.com/box/Anemometer.git cd Anemometer/

测试机数据库如果是5.6

mysql -uroot -p`cat /etc/sqlpass` < mysql56-install.sql

5.5的

mysql -uroot -p`cat /etc/sqlpass` < install.sql mysql -uroot -p`cat /etc/sqlpass` -e "grant all privileges ON slow_query_log.* to \'anemometer\'@\'%\' IDENTIFIED BY \'superSecurePass\';"

##编辑配置文件

cd conf/ cp sample.config.inc.php config.inc.php vim config.inc.php

主要配置

多个数据库我是用多张表来区分。 $conf[\'datasources\'][\'db_m\'] = array( \'host\' => \'2.2.2.1\', \'port\' => 3306, \'db\' => \'slow_query_log\', \'user\' => \'anemometer\', \'password\' => \'superSecurePass\', \'tables\' => array( \'global_query_review\' => \'fact\', \'global_query_review_history\' => \'dimension\' ), \'source_type\' => \'slow_query_log\' ); $conf[\'datasources\'][\'db_s1\'] = array( \'host\' => \'2.2.2.2\', \'port\' => 3306, \'db\' => \'slow_query_log\', \'user\' => \'anemometer\', \'password\' => \'superSecurePass\', \'tables\' => array( \'s1_query_review\' => \'fact\', \'s1_query_review_history\' => \'dimension\' ), \'source_type\' => \'slow_query_log\' ); 还需要添加一个可以explain的帐号 //$conf[\'plugins\'] = array(下 // $conn[\'host\'] = \'2.2.2.1\'; // $conn[\'port\'] = \'3306\'; $conn[\'user\'] = \'box_explain\'; $conn[\'password\'] = \'superSecurePass\';

7、访问http://slow.domain.com/Anemometer 现在还没有数据,只看到一个很高大上的界面

8、把pt-query-digest分析的数据写到测试机数据库里

pt-query-digest --user=anemometer --password=superSecurePass --review h=1.1.1.1,D=slow_query_log,t=global_query_review --history h=1.1.1.1,D=slow_query_log,t=global_query_review_history --no-report --limit=0% --filter=" $event->{Bytes} = length($event->{arg}) and $event->{hostname}="$HOSTNAME"" /root/slow.log 9、完成！

##自动生成

利用RSYNC，每5分钟把线上的数据库慢日志拉到测试机上

线上数据库的RSYNC模块

[slow_log] path=/data/mysql ignore errors read only = no hosts allow=1.1.1.1

测试机

[root[@localhost](http://my.oschina.net/u/570656) sh]# cat anemometer.sh rsync -av rsync://2.2.2.1/slow_log/slow.log /data/lydbm_slow_log/ pt-query-digest --user=anemometer --password=superSecurePass --review h=1.1.1.1,D=slow_query_log,t=global_query_review --history h=1.1.1.1,D=slow_query_log,t=global_query_review_history --no-report --limit=0% --filter=" $event->{Bytes} = length($event->{arg}) and $event->{hostname}="ly_db_m"" /data/lydbm_slow_log/slow.log rsync -av rsync://2.2.2.2/slow_log/slow.log /data/lydbs1_slow_log/ pt-query-digest --user=anemometer --password=superSecurePass --review h=1.1.1.1,D=slow_query_log,t=global_query_review --history h=1.1.1.1,D=slow_query_log,t=global_query_review_history --no-report --limit=0% --filter=" $event->{Bytes} = length($event->{arg}) and $event->{hostname}="ly_db_s1"" /data/lydbs1_slow_log/slow.log

定时

*/5 * * * * root /bin/bash /root/sh/anemometer.sh >/dev/null 2>&1

搭建过程出现的问题： Error in Query Explain Plugin: Missing field host

没有权限连接远程MYSQL，所以就explain不了

扩展阅读

mysql> desc global_query_review； +-----------------+---------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------------+---------------------+------+-----+---------+-------+ | checksum | bigint(20) unsigned | NO | PRI | NULL | | | fingerprint | text | NO | | NULL | | | sample | longtext | NO | | NULL | | | first_seen | datetime | YES | | NULL | | | last_seen | datetime | YES | | NULL | | | reviewed_by | varchar(20) | YES | | NULL | | | reviewed_on | datetime | YES | | NULL | | | comments | text | YES | | NULL | | | reviewed_status | varchar(24) | YES | | NULL | | +-----------------+---------------------+------+-----+---------+-------+ 9 rows in set (0.00 sec)

主要字段说明：

checksum : 主键， 是按fingerprint算出来的hash值。

finger : 是每条sql语句的指纹，去掉了一些变化的值（同一类型的sql语句，条件值不一样在该表中只会存在一条记录）。

sample: 符合finger的一条sql示例。

mysql> desc global_query_review_history; +------------------------------+---------------------+------+-----+---------------------+-------+ | Field | Type | Null | Key | Default | Extra | +------------------------------+---------------------+------+-----+---------------------+-------+ | hostname_max | varchar(64) | NO | PRI | NULL | | | db_max | varchar(64) | YES | | NULL | | | checksum | bigint(20) unsigned | NO | PRI | NULL | | | sample | longtext | NO | | NULL | | | ts_min | datetime | NO | PRI | 0000-00-00 00:00:00 | | | ts_max | datetime | NO | PRI | 0000-00-00 00:00:00 | | | ts_cnt | float | YES | | NULL | | | Query_time_sum | float | YES | | NULL | | | Query_time_min | float | YES | | NULL | | | Query_time_max | float | YES | | NULL | | | Query_time_pct_95 | float | YES | | NULL | | | Query_time_stddev | float | YES | | NULL | | | Query_time_median | float | YES | | NULL | | | Lock_time_sum | float | YES | | NULL | | | Lock_time_min | float | YES | | NULL | | | Lock_time_max | float | YES | | NULL

字段说明：

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：表示的是扫描的行数/返回的结果行数