First we need to install everything, here are the steps:
These steps are for centos
First step is to install percona-release:
yum install https://repo.percona.com/yum/percona-release-latest.noarch.rpm
Enable the package that contains the pt-query-digest tool:
percona-release enable tools
Next install percona-toolkit:
yum install percona-toolkit
How to use pt-query-digest:
pt-query-digest /../mysql_slow/slow-query.log
Set interval:
pt-query-digest --since '2019-02-07 08:09:00' --until '2019-02-07 08:02:03' /../mysql_slow/slow-query.log > output.out
Filter host, database or other things:
# Ignore queries from a specific database: ($event->{db} || "") && $event->{db} ne "ignored_schema"
# Ignore queries are not on a database:" ($event->{db} || "") , Ex PING
#Ignore queries that came from a specific IP address: ($event->{host} || "") && $event->{host} ne "192.168.2.1"
pt-query-digest --filter '($event->{db} || "") && $event->{db} && $event->{db} ne "ignored_schema" && $event->{cmd} ne "Admin" && ($event->{host} || "") && $event->{host} ne "192.168.2.1" && $event->{host} ne "192.168.0.1"' /../mysql_slow/slow-query.log > output.out
To see all components of event run this command:
# This will print the $event variable with all it's componets:
pt-query-digest pdbx5-slow-query.log --filter '($event->{db} || "") && $event->{db} && $event->{db} ne "ignored_database" && print Dumper $event' --no-report --sample 1
Uninstall:
yum remove percona-toolkit
percona-release disable tools
yum remove percona-release