What is Ideal value to set for long_query_time in slow query log for magento?

Question

To optimize magento we enable slow query log in mysql.

What is ideal value for query_long_time especially for magento?

Answer 1

I always set long_query_time to some high value by default, for example 60 seconds or even higher. So the usual behavior would be very low overhead because it's writing to the log infrequently. I certainly hope most of your queries take much less than 60 seconds, but with Magento maybe that's not the case. :-)

Then when I want to collect logs to be analyzed, I change long_query_time to 0 temporarily, so the log contains all queries. Then set it back when you're done collecting logs.

I wrote a script to help automate this process: https://github.com/billkarwin/bk-tools/blob/master/full-slow-log