Skip to main content

One post tagged with "analise slow queries"

View All Tags

· 2 min read
Hreniuc Cristian-Alexandru

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