1. 简介
索引可以我们更快速的执行查询,但是肯定存在不合理的索引,如果想找到那些索引 不是很合适的查询,并在它们成为问题前进行优化,则可以使用 pt-query-digest 的查询审查“review”功能,分析其 EXPLAIN 出来的执行计划。
pt-query-digest 是用于分析 mysql 慢查询的一个工具,它可以分析 binlog、General log、slowlog,也可以通过 SHOWPROCESSLIST 或者通过 tcpdump 抓取的 MySQL 协议数据来进行分析。可以把分析结果输出到文件中,分析过程是先对查询语句的条件进行参数化,然后对参数化以后的查询进行分组统计,统计出各查询的执行时间、次数、占比等,可以借助分析结果找出问题进行优化。
2. Install Percona Toolkit & pt-query-digest
percona-toolkit 是一组高级命令行工具的集合,用来执行各种通过手工执行非常复杂和麻烦的 mysql 和系统任务。这些任务包括:
- 检查 master 和 slave 数据的一致性
- 有效地对记录进行归档
- 查找重复的索引
- 对服务器信息进行汇总
- 分析来自日志和 tcpdump 的查询
- 当系统出问题的时候收集重要的系统信息
[root@ultrera ~]# wget percona.com/get/percona-toolkit.tar.gz
--2016-09-22 09:17:00-- http://percona.com/get/percona-toolkit.tar.gz
Resolving percona.com... 74.121.199.234, 74.121.199.234
Connecting to percona.com|74.121.199.234|:80... connected.
HTTP request sent, awaiting response... 301 Moved Permanently
Location: https://www.percona.com/get/percona-toolkit.tar.gz [following]
--2016-09-22 09:17:02-- https://www.percona.com/get/percona-toolkit.tar.gz
Resolving www.percona.com... 74.121.199.234, 74.121.199.234
Connecting to www.percona.com|74.121.199.234|:443... connected.
HTTP request sent, awaiting response... 302 Found
Location: https://www.percona.com/downloads/percona-toolkit/2.2.19/tarball/percona-toolkit-2.2.19.tar.gz [following]
--2016-09-22 09:17:03-- https://www.percona.com/downloads/percona-toolkit/2.2.19/tarball/percona-toolkit-2.2.19.tar.gz
Reusing existing connection to www.percona.com:443.
HTTP request sent, awaiting response... 200 OK
Length: 1425623 (1.4M) [application/x-gzip]
Saving to: “percona-toolkit.tar.gz”
100%[=======================>] 1,425,623 766K/s in 1.8s
2016-09-22 09:17:05 (766 KB/s) - “percona-toolkit.tar.gz” saved [1425623/1425623]
[root@ultrera ~]# tar xf percona-toolkit.tar.gz
[root@ultrera ~]# ls
percona-toolkit-2.2.19 percona-toolkit.tar.gz
[root@ultrera ~]# cd percona-toolkit-2.2.19
[root@ultrera percona-toolkit-2.2.19]# perl Makefile.PL
Warning: prerequisite DBD::mysql 3 not found.
Writing Makefile for percona-toolkit
[root@ultrera percona-toolkit-2.2.19]# make
cp bin/pt-mysql-summary blib/script/pt-mysql-summary
....
Manifying blib/man1/pt-index-usage.1p
Manifying blib/man1/pt-duplicate-key-checker.1p
Manifying blib/man1/pt-config-diff.1p
Manifying blib/man1/pt-stalk.1p
[root@ultrera percona-toolkit-2.2.19]# make install
Installing /usr/local/share/man/man1/pt-query-digest.1p
...
Installing /usr/local/bin/pt-query-digest
...
Appending installation info to /usr/lib64/perl5/perllocal.pod
运行工具可能会遇到下面的错误:Can't locate Time/HiRes.pm in @INC
# 解决办法:
[root@ultrera ~]# yum install -y perl-Time-HiRes
[root@ultrera ~]# pt-query-digest --version
pt-query-digest 2.2.19
3. 开启 mysql 慢日志
a. 查看当前‘slow_query_log’状态
mysql> show variables like '%query%';
+------------------------------+---------------------------------+
| Variable_name | Value |
+------------------------------+---------------------------------+
| binlog_rows_query_log_events | OFF |
| ft_query_expansion_limit | 20 |
| have_query_cache | YES |
| long_query_time | 10.000000 |
| query_alloc_block_size | 8192 |
| query_cache_limit | 1048576 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 1048576 |
| query_cache_type | OFF |
| query_cache_wlock_invalidate | OFF |
| query_prealloc_size | 8192 |
| slow_query_log | OFF |
| slow_query_log_file | /var/lib/mysql/ultrera-slow.log |
+------------------------------+---------------------------------+
13 rows in set (0.00 sec)
mysql> show variables like 'log_queries_not_using_indexes';
+-------------------------------+-------+
| Variable_name | Value |
+-------------------------------+-------+
| log_queries_not_using_indexes | OFF |
+-------------------------------+-------+
1 row in set (0.00 sec)
b. 启动 slow_log, 配置
# 设定记录大于2s的sql
mysql> set global long_query_time=2;
Query OK, 0 rows affected (0.00 sec)
# 设定log存放路径
mysql> set global slow_query_log_file='/tmp/ultraera-slow.log';
Query OK, 0 rows affected (0.00 sec)
# 启用慢日志
mysql> set global slow_query_log=ON;
Query OK, 0 rows affected (0.00 sec)
# 同时记录没有使用索引的sql
mysql> set global log_queries_not_using_indexes=on;
Query OK, 0 rows affected (0.00 sec)
等待一段时间,slow.log 增大的非常快,实际生产中,注意不要被 slow.log 将磁盘撑满,影响到正常生产使用。
4. 分析
pt-query-digest 可以从普通 MySQL 日志,慢查询日志以及二进制日志中分析查询,甚至可以从 SHOW PROCESSLIST 和 MySQL 协议的 tcpdump 中进行分析,如果没有指定文件,它从标准输入流(STDIN)中读取数据。
a. 简单使用方法
pt-query-digest slow.logs
输出信息如下:
- Overall 这个部分是一个大致的概要信息 (类似 loadrunner 给出的概要信息),通过它可以对当前 MySQL 的查询性能做一个初步的评估,比如各个指标的最大值 (max),平均值 (min),95% 分布值,中位数 (median),标准偏差 (stddev)
- 查询的执行时间(Exec time)
- 锁占用的时间(Lock time)
- MySQL 执行器需要检查的行数(Rows examine)
- 最后返回给客户端的行数(Rows sent)
- 查询的大小。
- Profile
- Rank:整个分析中该“语句”的排名,一般也就是性能最慢的
- Query ID:每个查询都有一个
- Response time: “语句”的响应时间以及整体占比情况。
- Calls:“语句”的执行次数
- R/Call:每次执行的平均响应时间。
- V/M
详细信息
列出上面 Profile 中每个 Query ID 的详细信息
b. 从 tcpdump 包中分析:通过 tcpdump 命令抓取一定时间网络数据包,然后进行分析
pt-query-digest --type tcpdump mysql.tcp.txt
c. pt-query-digest 还支持很对其他的数据包分析形势,但是我们主要使用的还是针对慢日志进行分析
更多的帮助文档,请查看官方文档:http://www.percona.com/doc/percona-toolkit/2.2/pt-query-digest.html
5. 使用 Anemometer 将 pt-query-digest 的 MySQL 慢查询可视化
- 需要安装 php 5.3 and over
- 需要预先配置 mysql 数据库
- 需要预先安装好 pt-query-digest
5.1 安装
[root@ultrera ~]# git clone https://github.com/box/Anemometer.git anemometer
[root@ultrera ~]# mv anemometer /var/www/html
[root@ultrera ~]# cd /var/www/html/anemometer/
[root@ultrera anemometer]# mysql -h localhost -u root -p < mysql56-install.sql
[root@ultrera anemometer]# mysql -h localhost -u root -p -e "grant all privileges on slow_query_log.* to 'anemometer'@'%' identified by 'anemometer';"
5.2 配置
[root@ultrera anemometer]# cp conf/sample.config.inc.php conf/config.inc.php
[root@ultrera anemometer]# vim conf/config.inc.php
# line 48,49 and line 284,285
设置数据库的用户名和密码;
[root@ultrera anemometer]# vim conf/config.inc.php
# line 7,8
设置数据库的用户名和密码;
5.3 导入
将 pt-query-digest 的分析结果到 anemometer;
pt-query-digest version < 2.2
$ pt-query-digest --user=anemometer --password=superSecurePass \
--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
pt-query-digest version >= 2.2
pt-query-digest --user=anemometer --password=superSecurePass \
--review h=db.example.com,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\"" \
/var/lib/mysql/db.example.com-slow.log
Pipeline 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.