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 percona.com/get/pt-query-digest && chmod +x pt-query-digestYou 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.txtDepending 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.logIf 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.logIgnore 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.logDid 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.logOfcourse 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.logNote 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.
