使用Box Anemometer基于pt-query-digest将MySQL慢查询可视化

转载自 https://blog.csdn.net/enweitech/article/details/80243722

最近玩MySQL，发现了一个很不错的工具，可以把MySQL慢查询可视化，方便我们去找出和分析慢询语句，搭建的步骤不多，但网上详细教程比较少，说得也不够详细，一不小心，估计得蛋痛一会，哈哈


Percona Toolkit 是一组高级的命令行工具，用来管理 MySQL 和系统任务，主要包括：

1、验证主节点和复制数据的一致性

2、有效的对记录行进行归档

3、找出重复的索引

4、总结 MySQL 服务器

5、从日志和 tcpdump 中分析查询

6、问题发生时收集重要的系统信息

一、PT安装：

方法一：rpm包安装

[root ~]$wgethttp://www.percona.com/downloads/percona-toolkit/LATEST/RPM/percona-toolkit-2.2.12-1.noarch.rpm[root ~]$yuminstallperl-IO-Socket-SSLperl-DBD-MySQLperl-Time-HiRes -y

[root ~]$ rpm -ivh percona-toolkit-2.2.12-1.noarch.rpm                   

warning: percona-toolkit-2.2.12-1.noarch.rpm: Header V4 DSA/SHA1 Signature, key ID cd2efd2a: NOKEY

Preparing...                ########################################### [100%]

  1:percona-toolkit        ########################################### [100%]

如果yum 安装不上依赖包，则检查下epel源，也可以查看http://www.cnblogs.com/xuanzhi201111/p/4040761.html去解决！

方法二：源码包安装

[root ~]$wgethttp://www.percona.com/downloads/percona-toolkit/2.2.12/deb/percona-toolkit_2.2.12.tar.gz[root ~]$tarzxf  percona-toolkit_2.2.12.tar.gz

[root ~]$cd percona-toolkit_2.2.12[root percona-toolkit-2.2.12]$perl Makefile.PL

[root percona-toolkit-2.2.12]$make&&makeinstall

工具安装目录在：/usr/local/bin

二、下载Anemometer

官网：https://github.com/box/Anemometer


三、安装httpd php,php版本要大于5.3，否则就报错，除此之外还需要：bcmath，php必须支持pdo_mysql、php_mysqli模块，下面我们来安装一下：

[root ~]$yuminstallhttpd php *bcmath* *mysqli*  -y

如果epel源像以下的，说明是旧的，该源没有php_mysqli相关模块的

[rootyum.repos.d]$ rpm -q epel-release

epel-release-5-4.noarch

以下版本才有：

[rootyum.repos.d]$ rpm -q epel-release

epel-release-6-8.noarch

我的mysql早已经安装好了的，这里就不多说了

四、将Anemometer文件包解压，重命名为anemometer，并移动到/var/www/html 下(apache默认路径)

[root ~]$unzipAnemometer-master.zip[root ~]$mvAnemometer-master /var/www/html/anemometer


五、导入anemometer目录下的install.sql，并给该库对应的权限：

[root anemometer]$pwd/var/www/html/anemometer

[root anemometer]$  mysql -uroot -p123456 -S /data/mysql-5.5.40/mysql.sock <./mysql56-install.sql

mysql>grantallonslow_query_log.*to'anemometer'@'%'identifiedby'123456';

Query OK, 0rows affected (0.03 sec)

mysql>grantallonslow_query_log.*to'anemometer'@'localhost'identifiedby'123456'; 

Query OK, 0rows affected (0.00 sec)

mysql>grantselecton*.*to'anemometer'@'%';

Query OK, 0rows affected (0.00 sec)

mysql>grantallonslow_query_log.*to'anemometer'@'localhost';

Query OK, 0rows affected (0.00 sec)

mysql>grantselecton*.*to'anemometer'@'localhost';

Query OK, 0rows affected (0.00sec)

六、修改可以视化界面的配置信息

修改php配置，vim /etc/php.ini添加以下内容，（如果本来就有;extension = mysqli.so,只需要把分号去掉即可，我的是yum安装的，本来没有，所以自己加）

修改apache的配置文件，vim /etc/httpd/conf/httpd.conf

[root conf]$cat/etc/httpd/conf/httpd.conf  |grep"ServerName"# ServerName gives the name and port that the server uses to identify itself.

ServerName 192.168.1.128:80

重启httpd，访问不了，看httpd的日志报以下错：

date_default_timezone_set(): Timezone ID'CST'is invalidin/var/www/html/anemometer/lib/Anemometer.php on line47[Fri Nov 2815:47:572014] [error] [client192.168.1.1] PHP Warning:  date_default_timezone_get(): It is not safe to rely on the system's timezone settings. You are *required* to use the date.timezone setting or the date_default_timezone_set()function. Incaseyou used any of those methods and you are still getting this warning, you most likely misspelled the timezone identifier. We selected'Asia/Chongqing'for'CST/8.0/no DST'instead in/var/www/html/anemometer/lib/Anemometer.php on line48

修改下/etc/php.ini

如果重启httpd，还是访问不了，日志还是报同时区的错误，则再修改以下的：

vim /var/www/html/anemometer/lib/Anemometer.php +47  添加下内容：


访问：http://192.168.1.128/anemometer，会提示没有global_query_review表，哈哈，别着急哈^.^



七、将慢查询日志通过pt-query-digest分析后存入数据库中：

[root ~]$ pt-query-digest --user=anemometer --password=123456--socket=/data/mysql-5.5.40/mysql.sock \> --review h=localhost,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/mysql-5.5.40/localhost-slow.log

如果你的mysql是通过sock方式连接的话，必须加上--socket指定sock文件，执行完以上的命令，数据库里就会多出global_query_review 表和global_query_review_history表了

详细导入可以查看官方帮助文档和表属性说明：

http://www.mysqlperformanceblog.com/2012/08/28/hidden-columns-of-query_review_history/

http://code.google.com/p/maatkit/wiki/EventAttributes

回到web端查看：


想了解更多功能，或者怎么使用，这要看大家了^.^

可以参考文章：

https://github.com/box/Anemometer

http://blog.itpub.net/26355921/viewspace-1162415/

【知识扩展】

MySQL 慢日志简介

MySQL慢日志（slow query log）想必大家都有听说，它是用来记录MySQL中的慢SQL（执行耗时超过 long_query_time 预设时间的SQL），并且扫描的行数超过min_examined_row_limit（如果也设置了这个选项的话），或者需要全表/全索引扫描的SQL（如果设置了 log_queries_not_using_indexes 选项的话，并不是指所有没使用索引的SQL），这些SQL效率通常较低，是可能影响MySQL的性能，DBA需要定期优化这些SQL。

在MySQL中，如果一个SQL需要长时间等待获取锁资源，那么这段获取锁的等待时间并不算执行时间，当SQL执行完成，释放相应的锁，才会记录到慢日志中，所以MySQL的慢日志中记录的顺序和实际的执行顺序可能不一样。

在默认情况下，MySQL的慢日志记录是关闭的，我们可以通过将设置slow_query_log=1来打开MySQL的慢查询日志，通过slow_query_log_file=file_name来设置慢查询的文件名，如果文件名没有设置，他的默认名字为host_name-slow.log。同时，我们也可以设置 log-output={FILE|TABLE}来指定慢日志是写到文件还是数据库里面（如果设置log-output=NONE，将不进行慢日志记录，即使slow_query_log=1）。

默认地，MySQL的管理维护命令的慢SQL并不会被记录到MySQL慢日志中。常见的管理维护命令包括ALTER TABLE,ANALYZE TABLE, CHECK TABLE, CREATE INDEX, DROP INDEX, OPTIMIZE TABLE, 和REPAIR TABLE。如果希望MySQL的慢日志记录这类长时间执行的命令，可以设置log_slow_admin_statements = 1。

通过设置log_queries_not_using_indexes=1，MySQL的慢日志也能记录那些没使用索引的SQL（并不需要超过long_query_time，两者条件满足一个即可）。但打开该选项的时候，如果你的数据库中存在大量没有使用索引的SQL，那么MySQL慢日志的记录量将非常大，所以通常还需要设置参数log_throttle_queries_not_using_indexes 。默认情况下，该参数为0，表示不限制，当设置改参数为大于0的值的时候，表示MySQL在一分钟内记录的没使用索引的SQL的数量，来避免慢日志记录过多的该类SQL。

在MySQL 5.7.2 之后，如果设置了慢日志是写到文件里，需要设置log_timestamps 来控制写入到慢日志文件里面的时区（该参数同时影响general日志和err日志），不设置的话，log文件中的记录采用UTC时间，而非本地时间。如果设置慢日志是写入到数据库中，该参数将不产生作用。

所以，总结下哪些SQL能被MySQL慢日志记录：

不会记录MySQL中的管理维护命令，除非明确设置log_slow_admin_statements=1;

SQL执行时间必须超过long_query_time，（不包括锁等待时间）

参数log_queries_not_using_indexes设置为1，且SQL没有用到索引，同时没有超过log_throttle_queries_not_using_indexes 参数的设定。

查询examine的行数必须超过min_examined_row_limit

注1：如果表没有数据或者只有1条数据，优化器觉得即便走索引对效率并没帮助，哪怕 log_queries_not_using_indexes=1 也不会记录到慢日志中。

注2：如果SQL在QC命中了，也不会记录到慢日志中。

注3：修改密码之类的维护操作，密码部分将会被星号代替，避免明文显示。

Anemometer 简介

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

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

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

如果你想要使用Anemometer这个工具，那么你需要准备以下环境：

一个用来存储分析数据的MySQL数据库

pt-query-digest. (doc: Percona Toolkit )

MySQL数据库的慢查询日志 (doc: The Slow Query Log )

PHP版本为 5.5+，apache或者nginx等web服务器均可。

安装

下载Anemometer

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

载入数据

首先创建表结构，将global_query_review 以及global_query_review_history 创建出来。由于表定义中存在0000-00-00 00:00:00 的日期默认值，需要修改sql_mode，将其zero_date的sql_mode 关闭，同时关闭only_full_group_by

cd /www/lab/anemometermysql -f < ./install.sql

现在需要使用pt-query-digest 抓取MySQL的慢查询日志，然后将数据插入到slow_query_log 数据库的相应表中。

使用如下方式载入数据，h表示主机名或者ip地址，D表示database，t表示表名，再最后面跟上慢日志路径。

如果 pt-query-digest version > 2.2：

$ pt-query-digest --user=anemometer --password=superSecurePass --review h=127.0.0.1,D=slow_query_log,t=global_query_review --review-history h=127.0.0.1,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-query.log

如果 pt-query-digest version <= 2.2

$ pt-query-digest --user=root --password=root --review h=127.0.0.1,D=slow_query_log,t=global_query_review --history h=127.0.0.1,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-query.logPipeline process 11 (aggregate fingerprint) caused an error: Argument "57A" isn't numeric in numeric gt (>) at (eval 40) line 6, <>; line 27.Pipeline process 11 (aggregate fingerprint) caused an error: Argument "57B" isn't numeric in numeric gt (>) at (eval 40) line 6, <> line 28.Pipeline process 11 (aggregate fingerprint) caused an error: Argument "57C" isn't numeric in numeric gt (>) at (eval 40) line 6, <> line 29.

如果你看到一些报错如上面例子所示，脚本并没有出现问题，他只是输出当前的操作。

配置Anemometer

修改Anemometer配置文件

$ cd anemometer/conf$ cp sample.config.inc.php config.inc.php

示例的配置文件中，你需要进行部分修改，用来连接数据库获取慢查询的分析数据。

修改 datasource_localhost.inc.php 文件中的配置，主要为主机

$conf['datasources']['localhost'] = array( 'host' => '127.0.0.1', 'port' => 3306, 'db' => 'slow_query_log', 'user' => 'root', 'password' => 'root', 'tables' => array( 'global_query_review' => 'fact', 'global_query_review_history' => 'dimension' ), 'source_type' => 'slow_query_log');

然后访问127.0.0.1/anemometer 的时候出现

Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'slow_query_log.dimension.sample' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by (1055)

需要将sql_mode 中only_full_group_by 关闭。

如果你想利用Anemometer 的explain功能来获取执行计划，修改配置文件的以下部分。

$conf['plugins'] = array( 'visual_explain' => '/usr/local/bin/pt-visual-explain', --这里需要修改为正确的路径 ... 'explain' => function ($sample) { $conn['user'] = 'anemometer'; $conn['password'] = 'superSecurePass'; return $conn; },);

结果展示

在5.7中，默认SQL_MODE是启用ONLY_FULL_GROUP_BY的，需要将其关闭，否则Anemometer将报错。

选择相应的列，然后点击search，就可以显示结果

sql执行计划查看以及历史

当我们选择一个sql的hash值的时候，能看到他的一个具体的执行计划，同时也能看到匹配该sql的历史sql，消耗，表的统计信息，建表语句等。

同时也能针对sql进行评论，为sql优化提交建议等。

创建自动收集慢日志脚本

在anemometer下面的文件中有个收集脚本，可以通过crontab进行定时收集慢日志，语法如下：

Usage: ./s/anemometer_collect.sh --interval Options: --socket -S The mysql socket to use --defaults-file The defaults file to use for the client --interval -i The collection duration --rate Set log_slow_rate_limit (For Percona MySQL Only) --history-db-host Hostname of anemometer database server --history-db-port Port of anemometer database server --history-db-name Database name of anemometer database server (Default slow_query_log) --history-defaults-file Defaults file to pass to pt-query-digest for connecting to the remote anemometer database

示例脚本：

cd anemometer mkdir etccd etcvi anemometer.local.cnf --这里创建配置文件，添加用户名密码[client]user=anemometer_localpassword=superSecurePass./s/anemometer_collect.sh --interval 30 --history-db-host=127.0.0.1

叶师傅补充

我以前的Anemometer玩法是这样的。

把多个主机的slow log汇聚到一起，调用pt-query-digest解析并写入db，方便slow log的统一管理、解析、展示，而不是每个实例都单独部署一套；

每个主机上可能会跑多实例，其slow log的文件命名，会至少体现IP、端口、业务名，比如：slowquery-192.168.0.1:3306-yejr.log；

调用pt-query-digest解析时，传递给 hostname 参数的值为 "192.168.0.1:3306"，这样一来，每个实例都可以被单独处理列出，更加直观；

通常，每个实例都有统一的监控账号，Anemometer可以利用这个账号来连接远程实例，查看解析SQL执行计划。

©著作权归作者所有,转载或内容合作请联系作者
  • 人面猴
    序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
    沈念sama阅读 217,185评论 6赞 503
  • 死咒
    序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
    沈念sama阅读 92,652评论 3赞 393
  • 救了他两次的神仙让他今天三更去死
    文/潘晓璐 我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
    开封第一讲书人阅读 163,524评论 0赞 353
  • 道士缉凶录：失踪的卖姜人
    文/不坏的土叔 我叫张陵，是天一观的道长。 经常有香客问我，道长，这世上最难降的妖魔是什么？ 我笑而不...
    开封第一讲书人阅读 58,339评论 1赞 293
  • 港岛之恋（遗憾婚礼）
    正文 为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
    茶点故事阅读 67,387评论 6赞 391
  • 恶毒庶女顶嫁案：这布局不是一般人想出来的
    文/花漫 我一把揭开白布。 她就那样静静地躺着，像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上，一...
    开封第一讲书人阅读 51,287评论 1赞 301
  • 城市分裂传说
    那天，我揣着相机与录音，去河边找鬼。 笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。 我是一名探鬼主播，决...
    沈念sama阅读 40,130评论 3赞 418
  • 双鸳鸯连环套：你想象不到人心有多黑
    文/苍兰香墨 我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
    开封第一讲书人阅读 38,985评论 0赞 275
  • 万荣杀人案实录
    序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
    沈念sama阅读 45,420评论 1赞 313
  • 护林员之死
    正文 独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 37,617评论 3赞 334
  • 白月光启示录
    正文 我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 39,779评论 1赞 348
  • 活死人
    序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
    沈念sama阅读 35,477评论 5赞 345
  • 日本核电站爆炸内幕
    正文 年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
    茶点故事阅读 41,088评论 3赞 328
  • 男人毒药：我在死后第九天来索命
    文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 31,716评论 0赞 22
  • 一桩弑父案，背后竟有这般阴谋
    文/苍兰香墨 我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 32,857评论 1赞 269
  • 情欲美人皮
    我被黑心中介骗来泰国打工， 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。 一个月前我还...
    沈念sama阅读 47,876评论 2赞 370
  • 代替公主和亲
    正文 我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
    茶点故事阅读 44,700评论 2赞 354