clearing (or marking) MySQL slow query log

The MySQL slow query log is a very useful feature which all applications in production should have enabled. It logs all queries which complete in more seconds than what the long_query_time server variable specifies.

Sometimes you need to clear this log without restarting mysqld. You could simply erase the file (in Linux) but the file handle would be lost and no further logging would take place.

The best way to purge the log in Linux without having to restart the server is:

cat /dev/null > /path/to/logfile

Another way is to erase or rename the logfile and then run the following statement in mysql:

flush logs;

references:
http://bugs.mysql.com/bug.php?id=3337
http://lists-archives.org/mysql/26837-purge-slow-query-log.html

When you want to do that in Windows though things are different.

I haven’t yet found a way to safely remove the log file while mysqld is running. What I’m left with is to mark the current position in the slow query log so I can look it up and examine the log from that point onwards. The way this can be done is by executing a slow query (slow enough to be logged) and the safest of all looks like:

select "prod-25", sleep(4);

I can now quickly navigate to “prod-25″ in the slow query log; which by the way is a marker of the 25th production release of a system which I’m tunning.

The deployment process could be automatically executing (probably via ant) such a marker query in order to keep all the historic (slow log) data grouped by releases for future analysis.

3 Responses to “clearing (or marking) MySQL slow query log”

  1. Brian Says:

    Hi, another windows alternative for marking the file could be to use the echo command and send it to the log file i.e.

    echo “**** log file checked [date] *****”>>path/to/logfile

  2. cherouvim Says:

    Thanks Brian

    Unfortunately the file is locked in Windows so you get the following message:
    The process cannot access the file because it is being used by another process.

  3. Toko Online Says:

    @Brian
    Interesting post. I was tried this tutorial. Maybe same case like Cherouvim the file is locked on windows. And i get the same message. Can you give me some suggest to solved this problem? Thanks