One may think one may use MySQL Slow query log to log all slow queries to catch problematic queries or for audit purposes. In fact however not all the queries are logged. I already mentioned mysql slave queries are not logged to slow query log and it looks like I was wrong connecting it just with replication.
In reality it is not the fact the thread is replication thread causes queries to be omitted from slow query log but the fact thread uses SET TIMESTAMP functionality. If you do this within normal connection result would be the same.
Why is this happening ? I guess it happens because of the way the code is structured. During query start current timestamp is stored to special value which will be used for the query execution by all NOW() calls internal timestamp assignments etc. At certain point someone thought it is nice idea to be able to go back to future or to the past and SET TIMESTAMP was implemented, which instead of patching bunch of function simply writes different timestamp to this variable. When query execution is completed, normally another time() is used to get current time and time difference between start and end time is used to check if query should be logged as slow query. This obviously does not work if start time can be in the future or in the past so slow query logging had to be disabled for such queries.
What does it mean ? Simply what you can’t really trust standard slow query log – some queries may be omitted from it. Also if someone would like to hide complex queries from you it can be easily done by using SET TIMESTAMP statement before running the query.
Percona’s widely read Percona Data Performance blog highlights our expertise in enterprise-class software, support, consulting and managed services solutions for both MySQL® and MongoDB® across traditional and cloud-based platforms. The decades of experience represented by our consultants is found daily in numerous and relevant blog posts.
Besides specific database help, the blog also provides notices on upcoming events and webinars.
Want to get weekly updates listing the latest blog posts? Subscribe to our blog now! Submit your email address below and we’ll send you an update every Friday at 1pm ET.