Home > Admin > How to quickly identify queries with pt-query-digest and pt-query-advisor from rules ?

How to quickly identify queries with pt-query-digest and pt-query-advisor from rules ?

Today I’m working on integrate the Percona toolkit (instead of maatkit) in my own tools and I’m playing with pt-query-digest and pt-query-advisor.
These tools can be very interesting to identify some queries from established rules.
The  –review option is available for two of them and helps me to store a sample of each class of query and match them with an advice.

The rules (or advices) are available in the pt-query-advisor documentation and let you identify various problems such as queries with an argument with leading wildcard or with a table joined twice, for example.
There are 3 types of rules : note, warning and  critical.

The goal here is to quickly identify the queries corresponding to a rule, from the slow query log.
So, let me explain what was my method to do that, and, please, give me yours.

First, I need to create a table to store the queries (you can also use –create-review-table option) :

CREATE TABLE query_review (
   fingerprint  TEXT NOT NULL,
   sample       TEXT NOT NULL,
   first_seen   DATETIME,
   last_seen    DATETIME,
   reviewed_by  VARCHAR(20),
   reviewed_on  DATETIME,
   comments     TEXT

It’s interesting to note that the 3 last columns are used to annotate the queries. A simple PHP interface or script can be used to fill these fields (sorry, not enough time to write this script now).

Second easy step, put the queries from the slow query log to the review table with pt-query-digest :
(Note that my review table is in the mysql database and my slow query log is /var/log/slowq.log)

pt-query-digest /var/log/slowq.log --review <DSN OPTIONS>
DSN OPTIONS : u=<user>,p=<pass>,D=mysql,t=query_review,S=/tmp/mysql.sock

And obtain this result in database :
(fingerprint, sample and number of queries truncated for readability)

Now, generate an analyze file with pt-query-advisor from the review table :

pt-query-advisor --review <DSN OPTIONS> >/tmp/analyze_queries.log
DSN OPTIONS : u=<user>,p=<pass>,D=mysql,t=query_review,S=/tmp/mysql.sock

In this file, you can find a list of rules with associated queries (but you don’t need to edit the file, go to next step) :

CLA.007 0x4D97479E1774609A 0x6040F6055F330176 0x6BF38A80507513C8 0x7A4ACE71E642E539 0xCE21735A6006AB3D 0xEE13BCBC394FCD83

Finaly, to retrieve all the queries for a particular rule, use the command below (on 1 line) :

cat /tmp/analyze_queries.log | grep <RULE ID>
| awk '{for ( i = 2 ; i <= NF ; i++ ) print $i}'
| xargs -i mysql -u<user> -p<password> -E
-e"SELECT * FROM mysql.query_review WHERE CHECKSUM={}"

Replace the RULE ID, user and password with your informations.
The rules ID can be find in the pt-query-advisor documentation.

This is where the real work begins !

Hope that can help.

Congratulations, you've read this post till the end !
If you enjoyed this article, please leave a comment

You can also subscribe to RSS feed or by email to automatically receive real-time notification of new posts

Thanks for your visit on MySQL+. Cédric
Categories: Admin
  1. January 16th, 2012 at 18:05 | #1

    I’ve been working on a web interface to pt-query-digest called Query-Digest-UI ( https://github.com/kormoc/Query-Digest-UI ). I’m hoping people would find it useful and give ideas on how to improve it.

    Thanks much for the article!

  2. January 17th, 2012 at 15:06 | #3

    Hi Cedric! You should actually be able to do it more easily than that. The 0x syntax will cast into a bigint when it’s in a bigint context. For example, 0x4D97479E1774609A is really just hex-encoded 5591016206727667866, and

    mysql: select conv(’4D97479E1774609A’, 16, 10);
    | conv(’4D97479E1774609A’, 16, 10) |
    | 5591016206727667866 |

  3. January 17th, 2012 at 15:07 | #4

    Oops, I pasted the wrong thing.

    mysql: select 5591016206727667866 = 0x4D97479E1774609A;
    | 5591016206727667866 = 0x4D97479E1774609A |
    | 1 |

  1. January 6th, 2013 at 23:53 | #1