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

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.

Answer 2

Set it to whatever you like. The only problem is that in a stock MySQL, it can only be set in increments of 1 second, which is too slow for some people.

Most heavily used production servers execute far too many queries to log them all. The slow log is a way of filtering the log so that we can see the ones which take a long time (most queries are likely to be executed almost instantly). It's a bit of a blunt instrument.

Set it to 1 sec if you like, you're probably not going to run out of disc space or create a performance problem by doing that.

It's really about the risk of enabling the slow log- don't do it if you feel it's likely to cause further disc or performance problems.

Of course you could enable the slow log on a non-production server and put simulated load through, but that is never quite the same.

Answer 3

Not only is it a blunt instrument as far as resolution is concerned, but also it is MySQL-instance wide, so that if you have different databases with differing performancy requirements you're kind of out of luck. Obviously there are ways around that, but it's important to keep that in mind when setting your slow log setting.

Aside from performance requirements of your application, another factor to consider is what you're trying to log. Are you using the log to catch queries that would threaten the stability of your db instance (ones that cause deadlocks or Cartesian joins, for instance) or queries that affect the performance for specific users and that might require a little tuning? That will influence where you set your threshold.

Answer 4

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 5

Peter Zaitsev posted a nice article about using the slow query log. One thing he notes is important is to also consider how often a certain query is used. Reports run once a day are not important to be fast. But something that is run very often might be a problem even if it takes half a second. And you cant detect that without the microslow patch.