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.

Answer 2

The long_query_time parameter is expressed in seconds, so you could start by setting it to any number of seconds (the default is 10 seconds) and then reviewing the log. If the signal (i.e. slow queries) to noise (i.e. other queries) ratio is too high, set it to a larger number to log only the queries that require more time to complete.

From the 5.7 Reference Manual:

The slow query log consists of SQL statements that took more than long_query_time seconds to execute and required at least min_examined_row_limit rows to be examined. The minimum and default values of long_query_time are 0 and 10, respectively. The value can be specified to a resolution of microseconds. For logging to a file, times are written including the microseconds part. For logging to tables, only integer times are written; the microseconds part is ignored.