Dynamic General and Slow Query Log Before MySQL 5.1

Oct 7, 2009 / By Sheeri Cabral

Tags:

This is a hack, but it is a good one. I was looking at some machines on a new client, and they had the general log turned on. I was surprised, because it was a fairly busy server, and they had had many problems with the server a few months ago. I thought perhaps they had turned on the general log to diagnose a problem and forgotten to turn it off, or something similar.

When I looked at the log on disk, I saw it was a symlink to /dev/null. They were running MySQL 5.0, so I immediately realized that it was a hack to have a general log they could “turn on” without having to restart mysqld.

On a different server, I saw the same link to /dev/null done with a slow query log.

The drawbacks to doing it this way is that MySQL still has the overhead of writing to the log file. The I/O overhead is greatly reduced because the writes are to /dev/null, but there’s still overhead from other resources such as RAM, CPU, etc.

But if you are in a similar position — where you frequently need to turn the general log or slow query log on temporarily for debugging purposes — this tip just might help.

6 Responses to “Dynamic General and Slow Query Log Before MySQL 5.1”

  • raymond says:

    Hi…I am begineer of Mysql,

    According to the hack which you mention, it General and slow query log is turn on ‘all the while’ but spool to /dev/null.

    So when they needed the actual output , will it mean just change the location will do ?

  • [...] just wrote about Dynamic General and Slow Query Log Before MySQL 5.1 which apart from the File I/O overhead is an idea I’d not considered before. What may be a [...]

  • Sheeri Cabral says:

    Raymond — yes, so if the general query log is in

    /var/lib/mysql/mysql-general.log

    then an ls -l would show

    /var/lib/mysql/mysql-general.log -> /dev/null

    And to “turn the query log on” all they need to do is:

    rm /var/lib/mysql/mysql-general.log

    and at the mysql command prompt:

    mysql> FLUSH LOGS;

  • [...] Sheeri Cabral relates a little hack she found to simulate a dynamic general and slow query log before MySQL 5.1. She writes, “If you are in a similar position — where you frequently need to turn the [...]

  • [...] Dynamic general and slow query log before MySQL 5.1 – This is an interesting way of handling the slow and general query logs on pre-5.1 MySQL instances. We don’t need this on slow, but there’s been occasions that we’ve needed the general query log, but enabling it and disabling it requires a full restart of the service on 5.0 and earlier. You still take a performance hit because you’re always logging, but I would think it to be fairly minimized on modern fast hardware. [...]

  • [...] Dynamic general and slow query log before MySQL 5.1 – This is an interesting way of handling the slow and general query logs on pre-5.1 MySQL instances. We don’t need this on slow, but there’s been occasions that we’ve needed the general query log, but enabling it and disabling it requires a full restart of the service on 5.0 and earlier. You still take a performance hit because you’re always logging, but I would think it to be fairly minimized on modern fast hardware. [...]

Leave a Reply

  • (will not be published)

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