使用pt-query-digest分析慢查询日志

介绍

pt-query-digest 属于 Percona Toolkit 工具集中较为常用的工具，用于分析 slow log，可以分析 MySQL 数据库的 binary log 、 general log 日志，同时也可以使用 show processlist 或从 tcpdump 抓取的 MySQL 协议数据来进行分析。

安装

wget https://www.percona.com/downloads/percona-toolkit/3.2.1/binary/redhat/7/x86_64/percona-toolkit-3.2.1-1.el7.x86_64.rpm
yum -y localinstall percona-toolkit-3.2.1-1.el7.x86_64.rpm

sql配置检查

  mysql> show variables;
  +----------------------------------------+--------------------------------------------+
  | Variable_name                          | Value                                      |
  +----------------------------------------+--------------------------------------------+
  | log_slow_admin_statements              | OFF                                        |
  | log_slow_slave_statements              | OFF                                        |
  | slow_launch_time                       | 2                                          |
  | slow_query_log                         | ON                                         |
  | slow_query_log_file                    | /data/mysql/log/mysql-slow.log             |
  | log_queries_not_using_indexes          | ON                                         |
  +----------------------------------------+--------------------------------------------+

• log_queries_not_using_indexes: 开启会记录没有使用index的SQL语句
• slow_query_log： 慢日志是否开启
• slow_launch_time：慢日志登记时间

使用

pt-query-digest  --since='2019-02-27 09:30:00'   --until=2019-02-27 09:31:00'   mysql-slow.log  >  mysql-slow-report.log

说明

• Rank：所有语句的排名，默认按查询时间降序排列
• Query ID：语句的ID
• Response：总的响应时间
• time：该查询在本次分析中总的时间占比
• calls：执行次数，即本次分析总共有多少条这种类型的查询语句
• R/Call：平均每次执行的响应时间
• V/M：响应时间Variance-to-mean的比率

  常用用法

1.直接分析慢查询文件:

pt-query-digest slow.log > slow_report.log

2.分析最近12小时内的查询：

pt-query-digest  --since=12h   slow.log > slow_report2.log

3.分析指定时间范围内的查询：

pt-query-digest slow.log  --since '2017-01-07 09:30:00'   --until '2017-01-07 10:00:00' > slow_report3.log

4.分析指含有select语句的慢查询

pt-query-digest  --filter   '$event->{fingerprint} =~ m/^select/i'   slow.log  > slow_report4.log

5针对某个用户的慢查询

pt-query-digest --filter '($event->{user} || "") =~ m/^root/i'  slow.log > slow_report5.log

6.查询所有全表扫描或full join的慢查询

pt-query-digest  --filter '(($event->{Full_scan} || "") eq "yes") ||(($event->{Full_join} || "") eq "yes")' slow.log  > slow_report6.log

7.把查询保存到query_review表

pt-query-digest  --user=root  -password=abc123  --review h=localhost,D=test,t=query_review  --create-review-table  slow.log

8.分析binlog

mysqlbinlog mysql-bin.000093    > mysql-bin000093.sql
pt-query-digest   --type=binlog  mysql-bin000093.sql  >  slow_report10.log

9.分析general log

pt-query-digest  --type=genlog  localhost.log  > slow_report11.log