跳到主要内容

HowTo Use pt-query-digest

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

输出信息如下：

image

    1. Overall 这个部分是一个大致的概要信息 (类似 loadrunner 给出的概要信息)，通过它可以对当前 MySQL 的查询性能做一个初步的评估，比如各个指标的最大值 (max)，平均值 (min)，95% 分布值，中位数 (median)，标准偏差 (stddev)
    2. 查询的执行时间（Exec time）
    3. 锁占用的时间（Lock time）
    4. MySQL 执行器需要检查的行数（Rows examine）
    5. 最后返回给客户端的行数（Rows sent）
    6. 查询的大小。
    1. Profile
    2. Rank：整个分析中该“语句”的排名，一般也就是性能最慢的
    3. Query ID：每个查询都有一个
    4. Response time： “语句”的响应时间以及整体占比情况。
    5. Calls：“语句”的执行次数
    6. R/Call：每次执行的平均响应时间。
    7. 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.