MySQL slow query log - how slow is slow?

Question

What do you find is the optimal setting for mysql slow query log parameter, and why?

Answer 1

I recommend these three lines

 log_slow_queries set-variable = long_query_time=1 log-queries-not-using-indexes 

The first and second will log any query over a second. As others have pointed out a one second query is pretty far gone if you are a shooting for a high transaction rate on your website, but I find that it turns up some real WTFs; queries that should be fast, but for whatever combination of data it was run against was not.

The last will log any query that does not use an index. Unless your doing data warehousing any common query should have the best index you can find so pay attention to its output.

Although its certainly not for production, this last option

 log = /var/log/mysql/mysql.log 

will log all queries, which can be useful if you are trying to tune a specific page or action.

Answer 2

Whatever time /you/ feel is unacceptably slow for a query on your systems.

It depends on the kind of queries you run and the kind of system; a query taking several seconds might not matter if it's some back-end reporting system doing complex data-mining etc where a delay doesn't matter, but might be completely unacceptable on a user-facing system which is expected to return results promptly.