How to filter MySQL slow queries using pt-query-digest


The pt-query-digest is my go-to tool in order to perform slow query analysis, improve query performance and thus overall MySQL performance. In this blog post, I’m going to highlight the filter option which allows you to parse slow queries on various conditions for custom analysis.

Save Time: If you’re already aware of pt-query-digest, jump straight to Filtering MySQL slow query logs section.

Filtering capacity of pt-query-digest allows us to concentrate on specific condition and scenarios only. For example by excluding specific users, DBAs can identify slow queries linked to individual users. It is also possible to generate digest to analyse MySQL slow queries for specific database or table for targeted query reviews. You may also want to exclude a specific set of queries from the analysis, if they’re affecting the overall statistics. This level of granularity is good for DBAs trying to concentrate on answering specific slow queries within the MySQL environment.

I am going to keep it short, covering top 5 slow log filtering options in this post. I’m going to start with setup and basic execution command.

Get pt-query-digest

The query analysis tool, pt-query-digest comes with Percona Toolkit. You may consider installing it from the official installation page. Though the easiest and my favourite way to get any Percona tool is,

wget && chmod +x pt-query-digest

You should be good to go as long as you have Perl, DBI, DBD::mysql, and some core packages that ought to be installed in any reasonably new version of Perl.

Generating Slow Query Digest

Generating slow query digest is straight forward task.

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

Depending on the size of slow log I’d consider executing this in screen session. There are plenty of options you can use to parse the slow log though that’s not the main topic of this blog.

Filtering MySQL slow query logs

Let’s explore top 5 use cases while parsing MySQL slow query log, focusing on the pt-query-digest’s filtering capabilities.

Filtering slow queries by user using pt-query-digest

To analyze slow queries excluding a specific user (e.g., “peter_the_sos”)

pt-query-digest --filter '$event->{user} ne "peter_the_sos"' slow.log

If you need to include queries only by a specific user (eg. abuelo_vago), you may consider using “eq” instead of “ne”!

Generating slow query digest for specific user

pt-query-digest --filter '$event->{user} eq "abuelo_vago"' slow.log

Ignore specific query in pt-query-digest

Let’s say if you have to ignore a specific query that’s repeating a lot in the digest, you may consider the following expression.

pt-query-digest --filter '$event->{fingerprint} !~ m/CREATE DATABASE lab/i' slow.log

Did you note the “i”? That’s ignoring the case while comparing strings. Guess you got the idea before I tell you!

Ignore queries coming from specific server using pt-query-digest

If you want to disregard queries from a particular host (e.g., “localhost”) you will need to use the “host” filter as follows:

pt-query-digest --filter '$event->{host} ne "localhost"' slow.log

Ofcourse you know now, using “eq” will allow you to parse slow queries for specific server.

Filtering slow queries by database using pt-query-digest

To focus on queries related to a specific database (e.g., “alok_the_pop”) and generate digest only based on that database

pt-query-digest --filter '$event->{db} eq "alok_the_pop"'

Filtering slow queries by table using pt-query-digest

To analyze slow queries pertaining to a specific table (e.g., “specialk”) use “fingerprint” event filter.

pt-query-digest --filter '$event->{fingerprint} =~ m/specialk/' slow.log

Note that these are just 5 options to highlight the capabilities of pt-query-digest tool for analysing MySQL queries. I invite you to read more options available here the official documentation. Note that I am not including any other options and only filter, consider adjusting the command as you please. Also note that you will have to redirect this to a specific file to store the digest thus generated.

Leave a Reply

Your email address will not be published. Required fields are marked *