MySql slow query logs in production environment

Question

We're using MySql 5.5 in our production environment. Is it advisable to turn on slow query logs in production? What is the performance implication of doing so? I referred official doc here, but it doesn't say anything about performance.

Diagonal

If you found one of the answers helpful, could you please mark them as accepted to close out this question? – Merrie

Answer 1

I highly recommend turning ON the slow query log in production environment. No matter how much logging you have done in your dev/QA/pre-prod environment, nothing gives you a better idea of the true performance than a production environment. Performance hit, in my experience, has not been significant.

You can improve performance, if it becomes significant, by saving your slow query log on a different disk or disk array.

long_query_time dynamic variable helps MySQL determine what to write in the slow query log. If your long_query_time is 2 seconds and you see a whole bunch of queries being logged in the slow query log, you can raise the long_query_time using something like SET GLOBAL long_query_time=10 or change it in my.cnf and restart MySQL.

I prefer keeping long_query_time to around 3 seconds to start and see what gets logged, and resolve the slowness in a timely manner. After that I drop it down to 2 seconds and keep it there. Logging is one thing, but taking action on the logging - may it be resolving the long running queries, monitoring the IO with iostat -x or sar etc. - is vital.

Merrie

Answer 2

I never found any Performance penalties on using slow-query log so I think it's save to turn it on. The database just keeps an extra logfile where it will store query exceeding a certain runtime (configurable). You can Analyse the slow queries after that e.g. by doing an

EXPLAIN SELECT ... FROM ...

Leroylerwick