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 slow_query_log_always_write_time 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 :)
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.
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 slow_query_log_always_write_time 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 :)
Thanks for the excellent tutorial. Please update the cheatsheet page. It is not downloadable:
Awesome video. Look forward to more MySQL series
Pt digest will work in Oracle MySQL or only percona MySQL
Just a quick update, I have just released part 3 of this video which you can find here: th-cam.com/video/f6fiy_dVYxw/w-d-xo.html
very awesome , thank you very much
do you give mysql optimization
Yes, you can contact me here for consulting services: www.speedemy.com/mysql/mysql-performance-consulting/
When I execute the pt-query-digest command, I get the following error: Pipeline process 2 (input) caused an error: Redundant argument in printf
Thanks
The query-mysql> wget percona.com/get/pt-query-digest is giving error in mysql command line in windows...what is the correct syntax?
Dude u have to run wget percona.com/get/pt-query-digest on linux terminal, not in the mysql xD