{"id":3120,"date":"2023-12-30T07:03:33","date_gmt":"2023-12-30T07:03:33","guid":{"rendered":"https:\/\/kedar.nitty-witty.com\/blog\/?p=3120"},"modified":"2024-01-27T17:21:42","modified_gmt":"2024-01-27T17:21:42","slug":"how-to-filter-mysql-slow-queries-using-pt-query-digest","status":"publish","type":"post","link":"https:\/\/kedar.nitty-witty.com\/blog\/how-to-filter-mysql-slow-queries-using-pt-query-digest","title":{"rendered":"How to filter MySQL slow queries using pt-query-digest"},"content":{"rendered":"\r\n

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.<\/p>\r\n\r\n\r\n\r\n

Save Time: If you’re already aware of pt-query-digest, jump straight to Filtering MySQL slow query logs<\/strong><\/a> section.<\/p>\r\n\r\n\r\n

\r\n
\"\"<\/figure>\r\n<\/div>\r\n\r\n\r\n

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.<\/p>\r\n\r\n\r\n\r\n

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.<\/p>\r\n\r\n\r\n\r\n

Get pt-query-digest<\/h3>\r\n\r\n\r\n\r\n

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

wget percona.com\/get\/pt-query-digest && chmod +x pt-query-digest<\/code><\/pre>\r\n\r\n\r\n\r\n
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.<\/p>\r\n\r\n\r\n\r\n
Generating Slow Query Digest<\/h3>\r\n\r\n\r\n\r\n
Generating slow query digest is straight forward task.<\/p>\r\n\r\n\r\n\r\n
pt-query-digest slow.log > digest.txt<\/code><\/pre>\r\n\r\n\r\n\r\n
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.<\/p>\r\n\r\n\r\n\r\n
Filtering MySQL slow query logs<\/h2>\r\n\r\n\r\n\r\n
Let’s explore top 5 use cases while parsing MySQL slow query log, focusing on the pt-query-digest’s filtering capabilities.<\/p>\r\n\r\n\r\n\r\n
Filtering slow queries by user using pt-query-digest<\/strong><\/p>\r\n\r\n\r\n\r\n
To analyze slow queries excluding a specific user (e.g., “peter_the_sos”)<\/p>\r\n\r\n\r\n\r\n
pt-query-digest --filter '$event->{user} ne \"peter_the_sos\"' slow.log<\/code><\/pre>\r\n\r\n\r\n\r\n
If you need to include queries only by a specific user (eg. abuelo_vago), you may consider using “eq” instead of “ne”!<\/p>\r\n\r\n\r\n\r\n
Generating slow query digest for specific user<\/strong><\/h4>\r\n\r\n\r\n\r\n
pt-query-digest --filter '$event->{user} eq \"abuelo_vago\"' slow.log<\/code><\/pre>\r\n\r\n\r\n\r\n
Ignore specific query in pt-query-digest<\/strong><\/h4>\r\n\r\n\r\n\r\n
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.<\/p>\r\n\r\n\r\n\r\n
pt-query-digest --filter '$event->{fingerprint} !~ m\/CREATE DATABASE lab\/i' slow.log<\/code><\/pre>\r\n\r\n\r\n\r\n
Did you note the “i”? That’s ignoring the case while comparing strings. Guess you got the idea before I tell you!<\/p>\r\n\r\n\r\n\r\n
Ignore queries coming from specific server using pt-query-digest<\/strong><\/h4>\r\n\r\n\r\n\r\n
If you want to disregard queries from a particular host (e.g., “localhost”) you will need to use the “host” filter as follows:<\/p>\r\n\r\n\r\n\r\n
pt-query-digest --filter '$event->{host} ne \"localhost\"' slow.log<\/code><\/pre>\r\n\r\n\r\n\r\n
Ofcourse you know now, using “eq” will allow you to parse slow queries for specific server.<\/p>\r\n\r\n\r\n\r\n
Filtering slow queries by database using pt-query-digest<\/strong><\/h4>\r\n\r\n\r\n\r\n
To focus on queries related to a specific database (e.g., “alok_the_pop”) and generate digest only based on that database<\/p>\r\n\r\n\r\n\r\n
pt-query-digest --filter '$event->{db} eq \"alok_the_pop\"'<\/code><\/pre>\r\n\r\n\r\n\r\n
Filtering slow queries by table using pt-query-digest<\/strong><\/h4>\r\n\r\n\r\n\r\n
To analyze slow queries pertaining to a specific table (e.g., “specialk”) use “fingerprint” event filter.<\/p>\r\n\r\n\r\n\r\n
pt-query-digest --filter '$event->{fingerprint} =~ m\/specialk\/' slow.log<\/code><\/pre>\r\n\r\n\r\n\r\n
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<\/a>. 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.<\/p>\r\n","protected":false},"excerpt":{"rendered":"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…\n","protected":false},"author":1,"featured_media":3118,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"om_disable_all_campaigns":false,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"footnotes":""},"categories":[8,870,377],"tags":[33,829,832,834,427,833,830,825,828,602,831,635,827,599],"class_list":{"0":"post-3120","1":"post","2":"type-post","3":"status-publish","4":"format-standard","5":"has-post-thumbnail","7":"category-mysql","8":"category-mysql-tools","9":"category-mysql-articles","10":"tag-database","11":"tag-database-administration","12":"tag-filtering-queries","13":"tag-how-to-filter-mysql-queries","14":"tag-mysql","15":"tag-mysql-performance","16":"tag-mysql-tools","17":"tag-parse-mysql-slow-log","18":"tag-performance-tuning","19":"tag-pt-query-digest","20":"tag-query-analysis","21":"tag-query-optimization","22":"tag-slow-queries","23":"tag-slow-query-log"},"aioseo_notices":[],"amp_enabled":true,"_links":{"self":[{"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/posts\/3120","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/comments?post=3120"}],"version-history":[{"count":6,"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/posts\/3120\/revisions"}],"predecessor-version":[{"id":3127,"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/posts\/3120\/revisions\/3127"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/media\/3118"}],"wp:attachment":[{"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/media?parent=3120"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/categories?post=3120"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/tags?post=3120"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}