MySQL Server slows down with large slow query log

I’ve seen this on multiple mysql servers now, and I don’t really know why it happens, but let’s start in the beginning…

The MySQL Server provides a so-called “slow-query-log” which logs all queries that take longer than a certain time.

There are mainly two parameters which allow you to control the behaviour:

slow_query_log=[0|1] controls whether the slow query log is written or not.
long_query_time=N controls the minimum query execution time in seconds for which a query should be logged.

A lot of pages recommend to activate the slow query log since it let’s you easily identify which queries on your server take a lot of time, and it really is very good for it. However, if you don’t take care of it, then your log file might become really large, e.g. if you are running your server for more than a year and you have set a long_query_time of one second, you might finally end up with a log file of more then 50 GB. This seems to have quite an impact on the performance of the mysql server for all kinds of queries (actually causing more queries to be logged).

What usually helps is

  1. Delete the slow query log file on the server
  2. Flush the logs of the server by running flush logs; in the query browser. Take care, this might actually cause you server to get blocked for multiple seconds.
  3. Increase the long_query_time to a very high value so nothing is logged. You can always lower the value if you need data.

I saw three times more queries being processed per second after taking the steps above!

Leave a Reply

Your email address will not be published. Required fields are marked *

*


6 − 3 =

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>