MySQL慢查询分析工具pt-query-digest

pt-query-digest是用于分析mysql慢日志的工具，它可以分析binlog、General log、slowlog，也可以通过SHOWPROCESSLIST或者通过tcpdump抓取的MySQL协议数据来进行分析。可以把分析结果输出到文件中，分析过程是先对查询语句的条件进行参数化，然后对参数化以后的查询进行分组统计，统计出各查询的执行时间、次数、占比等，可以借助分析结果找出问题进行优化。

下载Percona Toolkit

下载： https://www.percona.com/doc/percona-toolkit/3.0/pt-query-digest.html

安装perl模块：

yum -y install perl-CPAN perl-TermReadKey perl-Time-HiRes perl-IO-Socket-SSL.noarch 
参数说明： pt-query-digest --help
  --ask-pass                   FALSE        #连接到MySQL时提示输入密码
  --attribute-aliases          db|Schema    #属性列表|别名等（默认db | Schema）
  --attribute-value-limit      0            #属性值的限制（默认为0）
  --charset                    (No value)   #默认字符集
                                            #逗号分隔的配置文件列表；如果指定，则必须是命令行上的第一个选项
  --config                     /etc/percona-toolkit/percona-toolkit.conf,/etc/percona-toolkit/pt-query-digest.conf,/root/.percona-toolkit.conf,/root/.pt-query-digest.conf
  --continue-on-error          TRUE         #即使有错误也继续解析（默认为是）
  --create-history-table       TRUE         #创建--history表（如果不存在）（默认为yes）
  --create-review-table        TRUE         #创建--review表（如果不存在）（默认为yes）
  --daemonize                  FALSE        #Fork到后台并从shell分离
  --database                   (No value)   #连接到该数据库
  --defaults-file              (No value)   #仅从给定文件读取mysql选项
  --embedded-attributes        (No value)   #两个Perl正则表达式模式，用于捕获查询中嵌入的伪属性
  --expected-range             5,10         #当数量多于或少于预期时解释项目（默认值为5,10）
  --explain                    (No value)   #使用此DSN对示例查询运行EXPLAIN并打印结果
  --filter                     (No value)   #丢弃此Perl代码未返回true的事件
  --group-by                   fingerprint  #要对事件的哪个属性进行分组（默认fingerprint）
  --help                       TRUE         #显示帮助并退出
  --history                    (No value)   #在给定表中保存每个查询类的指标。 pt-query-digest将查询指标（查询时间，锁定时间等）保存到此表中，以便您查看查询类如何随时间变化
  --host                       (No value)   #连接到主机
  --ignore-attributes          arg,cmd,insert_id,ip,port,Thread_id,timestamp,exptime,flags,key,res,val,server_id,offset,end_log_pos,Xid  #不要聚合这些属性
  --inherit-attributes         db,ts        #如果丢失，则从具有属性的最后一个事件继承这些属性（默认db，ts）
  --interval                   .1           #轮询show processlist的频率，以秒为单位（默认为.1）
  --iterations                 1            #在收集和报告周期中迭代多少次（默认为1）
  --limit                      95%:20       #将输出限制为给定的百分比或计数（默认为95％：20）
  --log                        (No value)   #守护进程时将所有输出打印到此文件
  --max-hostname-length        10           #将报告中的主机名修剪到此长度。 0 =不修剪主机名（默认为10）
  --max-line-length            74           #将行修剪到此长度。 0 =不修剪线条（默认74）
  --order-by                   Query_time:sum   #按此属性:聚合函数对事件进行排序（默认Query_time：sum）
  --outliers                   Query_time:1:10  #按属性:百分比:计数报告异常值（默认查询时间：1：10）
  --output                     report           #如何格式化和打印查询分析结果（默认report）
  --password                   (No value)       #连接时使用的密码
  --pid                        (No value)       #创建给定的PID文件
  --port                       (No value)       #用于连接的端口号
  --preserve-embedded-numbers  FALSE            #查询时保留数据库/表名中的数字
  --processlist                (No value)       #使用--interval sleep轮询此DSN的进程列表以进行查询
  --progress                   time,30          #将进度报告打印到STDERR（默认时间30）
  --read-timeout               0                #等待的超时时间，等待来自输入的事件； 0表示永远等待（默认值为0）。 可选后缀s =秒，m =分钟，h =小时，d =天； 如果没有后缀，则使用s。
  --report                     TRUE             #打印每个--group-by属性的查询分析报告（默认为yes）
  --report-all                 FALSE            #报告所有查询，甚至包括已审核的查询
  --report-format              rusage,date,hostname,files,header,profile,query_report,prepared  #打印查询分析报告的这些部分
  --report-histogram           Query_time       #绘制此属性值的分布图（默认Query_time）
  --resume                     (No value)       #如果指定，该工具会将最后一个文件偏移（如果有的话）写入给定的文件名
  --review                     (No value)       #保存查询以供以后查看，并且不报告已查看的
  --run-time                   (No value)       #每个迭代要运行多长时间。可选后缀s =秒，m =分钟，h =小时，d =天；如果没有后缀，则使用s。
  --run-time-mode              clock            #设置--run-time的值所用的值（默认clock）
  --sample                     (No value)       #过滤掉每个查询中除前N个事件外的所有事件
  --set-vars                                    #在此以逗号分隔的（变量=值对）列表中设置MySQL变量
  --show-all                                    #显示这些属性的所有值
  --since                      (No value)       #解析仅查询比该值新的查询（自此日期以来解析查询）
  --slave-password             (No value)       #设置用于连接到从的密码
  --slave-user                 (No value)       #设置用于连接到从的用户
  --socket                     (No value)       #用于连接的套接字文件
  --timeline                   FALSE            #显示事件的时间表
  --type                       slowlog          #要解析的输入的类型（默认慢日志）
  --until                      (No value)       #仅解析早于此值的查询（直到此日期为止解析查询）
  --user                       (No value)       #用于登录的用户（如果不是当前用户）
  --variations                                  #报告这些属性值的变化数量
  --version                    FALSE            #显示版本并退出
  --version-check              TRUE             #检查最新版本的Percona Toolkit，MySQL和其他程序（默认为是）
  --vertical-format            TRUE             #在报告的SQL查询中输出尾随的“ \ G”（默认为是）
  --watch-server               (No value)       #此选项告诉pt-query-digest在解析tcpdump时要监视哪个服务器IP地址和端口（例如“ 10.0.0.1:3306”）（对于--type tcpdump）；其他所有服务器均被忽略
type=slowlog
属性参数：
$VAR1 = {
  Lock_time => '0.000122',
  Query_time => '6.405714',
  Rows_examined => '8',
  Rows_sent => '8',
  Thread_id => '165',
  arg => 'select user,host,sleep(0.8) from user',
  bytes => 37,
  cmd => 'Query',
  db => 'mysql',
  fingerprint => 'select user,host,sleep(?) from user',
  host => '192.168.100.112',
  ip => '192.168.103.102',
  pos_in_log => 4779,
  timestamp => '1783111641',
  ts => '2024-03-16T11:04:31',
  user => 'user-name'
分析慢查询日志文件：
pt-query-digest slow.log --type=slowlog
分析指定数据库的慢查询:
pt-query-digest slow.log --type=slowlog --filter '($event->{db} || "") =~ m/^mysql/i'
分析指定用户的慢查询：
pt-query-digest slow.log --type=slowlog --filter '($event->{user} || "") =~ m/^user-name/i'
分析最近时间的慢查询（最近10h，单位可以为s、和m）：
pt-query-digest slow.log --type=slowlog --since='10h'
分析指定IP的慢查询：
pt-query-digest slow.log --type=slowlog --filter '($event->{host} || $event->{ip} || "") =~ m/^192.168.100.*/i'
分析指定时间范围的慢查询：
pt-query-digest slow.log --type=slowlog --since='2024-03-16 16:00:00' --until='2024-03-21 15:00:00'
pt-query-digest slow.log --type=slowlog --since='1783048987' --until='1783049175'
分析指定查询类型的慢查询：
pt-query-digest slow.log_bak --type=slowlog --filter '$event->{arg} =~ m/^update/i'
type=genlog
属性参数：
$VAR1 = {
  Query_time => 0,
  Thread_id => '139',
  arg => 'SHOW FULL PROCESSLIST',
  bytes => 21,
  cmd => 'Query',
  db => 'mysql',
  fingerprint => 'show full processlist',
  pos_in_log => 191403,
  ts => '2024-03-16T12:01:03.128355Z'
分析所有general log：
pt-query-digest general.log --type=genlog
分析指定数据库的general log：
pt-query-digest general.log --type=genlog --filter '($event->{db} || "") =~ m/^db-name/i'
type=binlog
属性参数：
$VAR1 = {
  Query_time => '0',
  Thread_id => '177',
  arg => '...',
  bytes => 40,
  cmd => 'Query',
  db => 'db-name',
  end_log_pos => '697',
  error_code => '0',
  fingerprint => '...',
  offset => '584',
  pos_in_log => 1943,
  server_id => '1',
  timestamp => '1783133657',
  ts => '202403 15:00:58'
在分析binlog之前，需要先把binlog转换成文本：
mysqlbinlog --no-defaults -vv --base64-output=DECODE-ROWS mysql-bin.000001 >mysql-bin.000001.txt
分析所有binlog：
pt-query-digest /var/log/mysql/mysql-bin.000001.txt --type=binlog
分析指定数据库的bin log：
pt-query-digest /var/log/mysql/mysql-bin.000001.txt --type=binlog --filter '($event->{db} || "") =~ m/^db-name/i'
processlist
抓取show processlist的慢查，转存到指定文件：
-- 间隔1s执行show full processlist 拉取processlist中订阅到的慢查询转存到指定的文件。
pt-query-digest --processlist h=192.168.100.112,u=root,p=root --interval=1  --output=slowlog > /tmp/process.log
说明：比较适用于一些云数据库没有看慢查文件的权限。
tcpdump
tcpdump -s 65535 -x -nn -q -tttt -i any -c 1000 port 3306 > mysql.tcp.txt
pt-query-digest --type tcpdump mysql.tcp.txt
MySQL慢查询分析工具pt-query-digest
percona-toolkit 之 【pt-query-digest】介绍 
percona-toolkit 之 【pt-query-digest】使用