Monthly Archives: June 2015

Prepared Statements and pt-query-digest

IMG_6543I know I keep writing about pt-query-digest. It’s one of the best tools for MySQL. It’s complexity is a blessing and a curse and while the documentation is good, it’s always too general. I write these posts so I can find out what I did so I can share with others.

First off, prepared statements – WHO USES THESE? if you read the blog posts like this one:
4 Things to Know About MySQL Prepared Statements

or this one
Re-factoring some internals of prepared statements in 5.7

You would wonder why anyone would use prepared statements pre 5.7 (some of us wonder why we can’t just use 5.7 now? When will Oracle release this beauty to GA???!! )  But as a DB you have to show things like why and without performance_schema’s monitoring of memory leaks (comes in 5.7) it makes the weirdness that is prepared statements pre 5.7 scary. But lets just look at the performance of them. so off to pt-query-digest.

My slow log was not verbose and the long_query_time was set to 10, which means anything in my slow log should be looked at. This was on a staging server where P_S (performance_schema is fully turned on, something I will not do until maybe 5.7 if it is truly the lean machine as blog posts tout). I forgot to turn long_query_time to 0 until after it had been running all morning.

I did the usual

pt-query-digest –report-format=all ./slow.log > slow-log-report.txt

NOTHING related to the prepared statements showed up in the report except under this:

    # MISC 0xMISC              104.5362  2.4% 29940  0.0035   0.0 <435 ITEMS>

Adding –limit 100% added in ALL the queries on ALL the databases, not what I wanted. My prepared statements are in one schema by one user. I choose to filter based on user. I’m pretty sure I could have filtered on schema (or is it db?) but user seemed the easiest and the docs had an example.

pt-query-digest –limit 100%  –filter ‘($event->{user} || “”) =~ m/myuser/’ >qt-newdb.txt

Percona Toolkit 2.2