Pt-query-digest query log analysis tool

Reprint: http://blog.csdn.net/seteor/article/details/24017913

4. usage examples

(1)Direct analysis of slow query files:

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

(2)Analyze queries in the last 12 hours:

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

(3)Analyze queries over a specified time range:

pt-query-digest slow.log --since '2014-04-17 09:30:00' --until '2014-04-17 10:00:00'> > slow_report3.log

(4)Parsing refers to slow queries with select statements

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

(5)Slow queries for a user

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

(6)Query all full table scans or full join slow queries

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

(7)Save query to query_review table

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

(8)Save query to query_history table

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

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

(9)Capture tcp protocol data of mysql through tcp dump, and then analyze it again

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)Analysis binlog

mysqlbinlog mysql-bin.000093 > mysql-bin000093.sql

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

(11)Analysis of general log

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