Dynamic General and Slow Query Log Before MySQL 5.1
Oct 7, 2009 / By Sheeri Cabral
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.
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 ?
Pingback: Take a look at mk-query-digest | MySQL Expert | MySQL Performance | MySQL Scalability
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;
Pingback: Log Buffer #165: a Carnival of the Vanities for DBAs | Pythian Group Blog
Pingback: » Four Short Links, Oct 14, 2009 Just Another ITS Systems Guy
Pingback: Four Short Links, Oct 14, 2009 | ghostar