pt-query-digest工具的使用
一 命令介绍
1.1 pt-query-digest
Analyze MySQL queries from logs, processlist, and tcpdump.
对多种日志进行汇总分析，如binlog，general log，tcpdump，slowlog，SQL文件，最常见的对慢日志进行分析
1.2 查看help帮助命令
[root@node3 ~]# pt-query-digest --help
pt-query-digest analyzes MySQL queries from slow, general, and binary log files.
It can also analyze queries from C and MySQL protocol data
from tcpdump. By default, queries are grouped by fingerprint and reported in
descending order of query time (i.e. the slowest queries first). If no C
are given, the tool reads C. The optional C is used for certain
options like L and L. For more details, please use the
–help option, or try ‘perldoc /root/percona-toolkit-3.6.0/bin/pt-query-digest’
for complete documentation.
Usage: pt-query-digest [OPTIONS] [FILES] [DSN]
Options:
–ask-pass Prompt for a password when connecting to MySQL
–attribute-aliases=a List of attribute|alias, etc (default db|Schema)
–attribute-value-limit=i A sanity limit for attribute values (default 0)
– -A Default character set
–config=A Read this comma-separated list of config files;
if specified, this must be the first option on
the command line
–[no]continue-on-error Continue parsing even if there is an error (
default yes)
–[no]create-history-table Create the --history table if it does not exist (
default yes)
–[no]create-review-table Create the --review table if it does not exist (
default yes)
–daemonize Fork to the background and detach from the shell
–database=s -D Connect to this database
–defaults-file=s -F Only read mysql options from the given file
–embedded-attributes=a Two Perl regex patterns to capture pseudo-
attributes embedded in queries
–expected-range=a Explain items when there are more or fewer than
expected (default 5,10)
–explain=d Run EXPLAIN for the sample query with this DSN
and print results
–filter=s Discard events for which this Perl code doesn’t
return true
–group-by=A Which attribute of the events to group by (
default fingerprint)
–help Show help and exit
–history=d Save metrics for each query class in the given
table. pt-query-digest saves query metrics (
query time, lock time, etc.) to this table so
you can see how query classes change over time
–host=s -h Connect to host
–ignore-attributes=a Do not aggregate these attributes (default arg,
cmd, insert_id, ip, port, Thread_id, timestamp,
exptime, flags, key, res, val, server_id,
offset, end_log_pos, Xid)
–inherit-attributes=a If missing, inherit these attributes from the
last event that had them (default db,ts)
–interval=f How frequently to poll the processlist, in
seconds (default .1)
–iterations=i How many times to iterate through the collect-
and-report cycle (default 1)
–limit=A Limit output to the given percentage or count (
default 95%:20)
–log=s Print all output to this file when daemonized
–max-hostname-length=i Trim host names in reports to this length. 0=Do
not trim host names (default 10)
–max-line-length=i Trim lines to this length. 0=Do not trim lines (
default 74)
–order-by=A Sort events by this attribute and aggregate
function (default Query_time:sum)
–outliers=a Report outliers by attribute:percentile:count (
default Query_time:1:10)
–output=s How to format and print the query analysis
results (default report)
–password=s -p Password to use when connecting
–pid=s Create the given PID file
–port=i -P Port number to use for connection
–preserve-embedded-numbers Preserve numbers in database/table names when
fingerprinting queries
–processlist=d Poll this DSN’s processlist for queries, with –
interval sleep between
–progress=a Print progress reports to STDERR (default time,
30)
–read-timeout=m Wait this long for an event from the input; 0 to
wait forever (default 0). Optional suffix s=
seconds, m=minutes, h=hours, d=days; if no
suffix, s is used.
–[no]report Print query analysis reports for each --group-by
attribute (default yes)
–report-all Report all queries, even ones that have been
reviewed
–report-format=A Print these sections of the query analysis
report (default rusage,date,hostname,files,
header,profile,query_report,prepared)
–report-histogram=s Chart the distribution of this attribute’s
values (default Query_time)
–resume=s If specified, the tool writes the last file
offset, if there is one, to the given filename
–review=d Save query classes for later review, and don’t
report already reviewed classes
–run-time=m How long to run for each --iterations. Optional
suffix s=seconds, m=minutes, h=hours, d=days; if
no suffix, s is used.
–run-time-mode=s Set what the value of --run-time operates on (
default clock)
–sample=i Filter out all but the first N occurrences of
each query
–set-vars=A Set the MySQL variables in this comma-separated
list of variable=value pairs
–show-all=H Show all values for these attributes
–since=s Parse only queries newer than this value (parse
queries since this date)
–slave-password=s Sets the password to be used to connect to the
slaves
–slave-user=s Sets the user to be used to connect to the slaves
–socket=s -S Socket file to use for connection
–timeline Show a timeline of events
–type=A The type of input to parse (default slowlog)
–until=s Parse only queries older than this value (parse
queries until this date)
–user=s -u User for login if not current user
–variations=A Report the number of variations in these
attributes’ values
–version Show version and exit
–[no]version-check Check for the ladb1 version of Percona Toolkit,
MySQL, and other programs (default yes)
–[no]vertical-format Output a trailing “\G” in the reported SQL
queries (default yes)
–watch-server=s This option tells pt-query-digest which server
IP address and port (like “10.0.0.1:3306”) to
watch when parsing tcpdump (for --type tcpdump);
all other servers are ignored
Option types: s=string, i=integer, f=float, h/H/a/A=comma-separated list, d=DSN, z=size, m=time
Rules:
This tool accepts additional command-line arguments. Refer to the SYNOPSIS and usage information for details.
DSN syntax is key=value[,key=value…] Allowable DSN keys:
KEY COPY MEANING
=== ==== =============================================
A yes Default character set
D yes Default database to use when connecting to MySQL
F yes Only read default options from the given file
P yes Port number to use for connection
S yes Socket file to use for connection
h yes Connect to host
p yes Password to use when connecting
t no The --review or --history table
u yes User for login if not current user
If the DSN is a bareword, the word is treated as the ‘h’ key.
Options and values after processing arguments:
二 实验部分：对慢日志进行分析
案例一：使用pt-query-digest对指定慢日志进行分析
2.1 执行pt-query-digest命令
pt-query-digest /data/mysql/log/query.log
[root@node3 log]# pt-query-digest /data/mysql/log/query.log
案例二：对实例当前正在执行的SQL进行分析
3.1 创建用户u1，赋权，验证远程登录
[root@node3 ~]# mysql -uroot -p123456
root@db 23:37: [db1]> create user ‘u1’@’%’ identified with mysql_native_password by ‘123456’;
GRANT all privileges ON . TO ‘u1’@’%’;
flush privileges;
show grants for ‘u1’@’%’\G
验证远程登陆是否成功:
[root@node3 ~]# mysql -uu1 -p123456 -h192.168.100.55 -P3306
3.2 查看连接connection_id
select connection_id();
u1@db 01:20: [(none)]> select connection_id();
±----------------+
| connection_id() |
±----------------+
| 21 |
±----------------+
1 row in set (0.00 sec)
3.3 模拟数据库插入100000条数据
set global innodb_flush_log_at_trx_commit=0;
create database db1 charset=utf8;
use db1;
create table t(id int primary key, a int, b int, index(a)) engine=innodb;
delimiter ;;
create procedure idata()
begin
declare i int;
set i=1;
while(i slow_report.log
(2)分析最近12小时内的查询：
pt-query-digest --since=12h /data/mysql/log/slow_mysql.log > slow_report2.log
(3)分析指定时间范围内的查询：
pt-query-digest /data/mysql/log/slow_mysql.log --since ‘2024-07-07 00:00:00’ --until ‘2024-07-07 23:59:59’ >slow_report3.log
(4)分析指含有select语句的慢查询
[root@node3 log]# pt-query-digest --filter ‘$event->{fingerprint} =~ m/^select/i’ /data/mysql/log/slow_mysql.log> slow_report4.log
(5) 针对某个用户的慢查询
pt-query-digest --filter ‘($event->{user} || “”) =~ m/^root/i’ /data/mysql/log/slow_mysql.log> slow_report5.log
(6) 查询所有所有的全表扫描或full join的慢查询
pt-query-digest --filter ‘((event−>Fullscan∣∣"")eq"yes")∣∣((event->{Full_scan} || "") eq "yes") ||((event−>Fullscan∣∣"")eq"yes")∣∣((event->{Full_join} || “”) eq “yes”)’ /data/mysql/log/slow_mysql.log> slow_report6.log
(7)把查询保存到query_review表
pt-query-digest --user=root –password=123456 --review h=192.168.100.55,D=db1,t=query_review --create-review-table /data/mysql/log/slow_mysql.log
(8)把查询保存到query_history表
pt-query-digest --user=root –password=123456 --review h=192.168.100.55,D=db1,t=query_ history --create-review-table /data/mysql/log/slow_mysql.log
pt-query-digest --user=root –password=123456–review h=192.168.100.55,D=db1,t=query_history --create-review-table /data/mysql/log/slow_mysql.log
(9)通过tcpdump抓取mysql的tcp协议数据，然后再分析
tcpdump -s 65535 -x -nn -q -tttt -i any -c 1000 port 3306 > mysql.tcp.txt
pt-query-digest --type tcpdump mysql.tcp.txt> slow_report9.log
(10)分析binlog
mysqlbinlog mysql-bin.000093 > mysql-bin000093.sql
pt-query-digest --type=binlog mysql-bin000093.sql > slow_report10.log
(11)分析general log
pt-query-digest --type=genlog /data/mysql/log/query.log > slow_report11.log
