Dynamic General and Slow Query Log Before MySQL 5.1
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.
Category: MySQL
Tags: general log, log, MySQL, performance, Pythian, slow query, slow query log
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 [...]
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. [...]