Advanced MySQL Slow Query Logging. Part 1: Why, What, How

PROPER MySQL Query Optimization starts with a proper Slow Query Logging session. In this short series you will learn how to do slow query logging the right way.

Parts 2 and 3, as well as the cheat sheet mentioned in the video are currently in the making and if you're anxious to have them released sooner, let us know in the comments below and we'll prioritise it!

[osamaa.h.altameemi]

You should write a book on the topic. The way you connect the information is so on point. You write it, I am buying it :) Keep up the good work.

[Speedemy]

I got a few questions to my email, so I thought I'll answer them here as well. Rick is asking:

1. FLUSH LOGS does not touch the slowlog in older versions.

Flush logs should still open a new log if you rename the current one even in the very old versions of MySQL. I assume you are not referring to 3.23 - I don't really remember how it works in that one! :))

2. Hundreds of the same 100ms-query -- occasionally one will be longer than 1 sec. That is, setting long_query_time=1 is not a total loss.

Regarding 100ms -vs- 1s - it is possible that query would sometimes take 1s rather than 100ms, but it's very unlikely (rather, it will happen very rarely), but more important is that you may not realise how much time MySQL is wasting on 100ms or even 10ms queries until you have all of them logged. In fact, in part 2 of the video you'll see a report from one real customer slow log and the top query consuming over 40% of resources will be one taking 0.11s on average and 143ms max (that is, during the sampling, it never took more than 143ms - not a single time). Part 2 should be ready in a few days.

3. log_slow_rate_limit is Percona only, correct? And it may fail to catch some long queries, correct? So, it is different, but not necessarily better, than a non-zero long_query_time?

log_slow_rate_limit - correct, it's from Percona only, however it has an additional configuration parameter which instructs to log ALL slow queries even if they would otherwise be filtered. So with that you get the best of both worlds so to speak :)

[bikhoda]

Thanks for the excellent tutorial. Please update the cheatsheet page. It is not downloadable:

[RA-klsp]

Awesome video. Look forward to more MySQL series

[devilsgaming]

Pt digest will work in Oracle MySQL or only percona MySQL

[diegofernandomorillovelepu]

When I execute the pt-query-digest command, I get the following error: Pipeline process 2 (input) caused an error: Redundant argument in printf

[saylijadhav]

The query-mysql> wget is giving error in mysql command line in windows...what is the correct syntax?