介绍一下这个慢sql分析工具pt-query-digest
访问http://www.percona.com/software/percona-toolkit/下载最新版本的 Percona Toolkit。或者，从命令行获取最新版本：
wget percona.com/get/percona-toolkit.tar.gz
wget percona.com/get/percona-toolkit.rpm
wget percona.com/get/percona-toolkit.deb
您还可以从最新版本中获取单独的工具：
wget percona.com/get/TOOL
下载完后解压即用 找到bin下的pt-query-digest 弄个slow文件解析一下看看效果
Usage: pt-query-digest [OPTIONS] [FILES] [DSN]
常用的查询语句：
pt-query-digest JOEONE-IPOSDS-SLOW.LOG> JOEONE-IPOSDS-SLOW1.log 把当前的结果输出到文件slow_report.log
pt-query-digest --since=24h JOEONE-IPOSDS-SLOW.LOG > slow_report2.log 分析最近24小时的结果
pt-query-digest --since '2015-01-09 09:30:00' --until '2015-02-01 09:30:00' JOEONE-IPOSDS-SLOW.LOG> slow_report3.log 分析规定时间段的结果
pt-query-digest --filter '(($event->{Full_scan} || "") eq "yes") ||(($event->{Full_join} || "") eq "yes")' JOEONE-IPOSDS-SLOW.LOG> slow_report6.log 所有的全表扫描或full join的慢查询
加入参数create-review-table，可以把结果写到mysql的表中
pt-query-digest --user=root --password=123456 --review h=localhost,D=test,t=query_table --create-review-table JOEONE-IPOSDS-SLOW.LOG
./pt-query-digest /usr/local/docker/mysql-ee/myee57134/logs/slow/slow.log >/tmp/slow.sql
报告结果分三部分整体概要、总体的sql语句、每条sql语句的详细情况
1 # 150ms user time, 10ms system time, 26.03M rss, 220.56M vsz 2 # Current date: Fri Apr 1 16:46:56 2022 3 # Hostname: App02 4 # Files: /usr/local/docker/mysql-ee/myee57134/logs/slow/slow.log 5 # Overall: 185 total, 21 unique, 0.00 QPS, 0.00x concurrency _____________ ##总计执行了185次sql语句，其中21个不重复的,qps,迸发数 6 # Time range: 2022-03-24T20:00:07 to 2022-03-31T13:05:15 7 # Attribute total min max avg 95% stddev median 8 # ============ ======= ======= ======= ======= ======= ======= ======= 9 # Exec time 2s 187us 605ms 11ms 28ms 46ms 2ms 10 # Lock time 331ms 58us 82ms 2ms 6ms 8ms 236us 11 # Rows sent 327.26k 0 316.64k 1.77k 463.90 22.94k 14.52 12 # Rows examine 79.21k 0 3.01k 438.42 1.26k 550.85 271.23 13 # Query size 30.21k 28 568 166.29 271.23 92.47 143.84
1 # Profile 2 # Rank Query ID Response time Calls R/Call V/M 3 # ==== =================================== ============= ===== ====== ==== 4 # 1 0x3A65D7BD8EF89A1A81A1CAE915E702E2 0.6048 29.4% 1 0.6048 0.00 SELECT variables_? 5 # 2 0x64EF0EA126730002088884A136067321 0.4873 23.7% 10 0.0487 0.04 6 # 3 0xE7A2FFE52D80EF843E6603AD093ACE36 0.2826 13.8% 5 0.0565 0.01 SELECT information_schema.columns 7 # 4 0x733019F33E3027F6693EB59382A27420 0.1310 6.4% 75 0.0017 0.00 SELECT INFORMATION_SCHEMA.PROFILING 8 # 5 0x9277183CCDDC5C9148701A8733C04373 0.1164 5.7% 67 0.0017 0.00 SELECT INFORMATION_SCHEMA.PROFILING 9 # 6 0xEE257EEE0031F7D567E5D9A14DCC0119 0.0801 3.9% 5 0.0160 0.00 SELECT information_schema.columns 10 # 7 0x3C2A84A184121E5898947CCF9160CAC9 0.0626 3.0% 2 0.0313 0.03 SELECT UNION information_schema.TABLES information_schema.COLUMNS information_schema.ROUTINES 11 # 8 0x6F6BF6643DB9AF5C80602115D928C2FF 0.0504 2.5% 2 0.0252 0.00 SELECT variables_? 12 # 9 0x0F4DD36E261C3C9BA74C738BC1E2EBEB 0.0372 1.8% 2 0.0186 0.00 SELECT information_schema.COLUMNS 13 # 10 0xCEC8BBF354E47FB74612333A12CC75C8 0.0295 1.4% 1 0.0295 0.00 SELECT variables_? 14 # 11 0x78C6ED221F892C7468721E120B46CF7E 0.0293 1.4% 1 0.0293 0.00 SELECT variables_? 15 # 12 0x469F3E2CF8D9E04088D2574FA42CF5A9 0.0286 1.4% 1 0.0286 0.00 SELECT variables_? 16 # 13 0x15512F8EEEA0D61B767CE77BED8C357A 0.0264 1.3% 1 0.0264 0.00 SELECT variables_? 17 # MISC 0xMISC 0.0885 4.3% 12 0.0074 0.0 <7 ITEMS>
1 # Query 1: 0 QPS, 0x concurrency, ID 0x3A65D7BD8EF89A1A81A1CAE915E702E2 at byte 50897 2 # This item is included in the report because it matches --limit. 3 # Scores: V/M = 0.00 4 # Time range: all events occurred at 2022-03-29T20:24:45 5 # Attribute pct total min max avg 95% stddev median 6 # ============ === ======= ======= ======= ======= ======= ======= ======= 7 # Count 0 1 8 # Exec time 29 605ms 605ms 605ms 605ms 605ms 0 605ms 9 # Lock time 0 216us 216us 216us 216us 216us 0 216us 10 # Rows sent 96 316.64k 316.64k 316.64k 316.64k 316.64k 0 316.64k 11 # Rows examine 1 1.12k 1.12k 1.12k 1.12k 1.12k 0 1.12k 12 # Query size 0 144 144 144 144 144 0 144 13 # String: 14 # Databases test 15 # Hosts 10.0.0.254 16 # Users root 17 # Query_time distribution 18 # 1us 19 # 10us 20 # 100us 21 # 1ms 22 # 10ms 23 # 100ms ################################################################ 24 # 1s 25 # 10s+ 26 # Tables 27 # SHOW TABLE STATUS FROM `test` LIKE 'variables_5734'\G 28 # SHOW CREATE TABLE `test`.`variables_5734`\G 29 # EXPLAIN /*!50100 PARTITIONS*/ 30 select a.VARIABLE_NAME as name57, b.VARIABLE_NAME as name58 from variables_5734 a , variables_8027 b where a.VARIABLE_NAME<>b.VARIABLE_NAME\G
浙公网安备 33010602011771号