I'm quite surprised that I can't see an answer to this anywhere on the site already, nor in the MySQL documentation (section 5.2 seems to have logging otherwise well covered!)
If I enable binlogs, I see a small performance hit (subjectively), which is to be expected with a little extra IO — but when I enable a general query log, I see an enormous performance hit (double the time to run queries, or worse), way in excess of what I see with binlogs. Of course I'm now logging every SELECT as well as every UPDATE/INSERT, but, other daemons record their every request (Apache, Exim) without grinding to a halt.
Am I just seeing the effects of being close to a performance "tipping point" when it comes to IO, or is there something fundamentally difficult about logging queries that causes this to happen? I'd love to be able to log all queries to make development easier, but I can't justify the kind of hardware it feels like we'd need to get performance back up with general query logging on.
I do, of course, log slow queries, and there's negligible improvement in general usage if I disable this.
(All of this is on Ubuntu 10.04 LTS, MySQLd 5.1.49, but research suggests this is a fairly universal issue)
Best Answer
General query logs are a lot more IO than binary logs. Besides the fact that most SQL servers are 90% reads to 10% writes, the binary logs are stored in a binary format rather than plain text that uses less disk space. (How much less space? I'm not sure. Sorry.)
There are two aspects to why Apache and Exim can record every request without significant performance impact. The first is that they record the fact that a request took place but what they put in the log is usually significantly smaller than the actual request. An HTTP request is often twice as large as the line that goes in the log and even a short, plain text email is 10 or 20 times larger than the log line that accompanies it. An email with a 10MB attachment will still only have a few lines written in the log.
The second part to this is that in a normal web application there are usually dozens of SQL queries associated with a single HTTP page. Emails tend to come in even smaller numbers than HTTP requests. Your MySQL server is probably trying to log much more than either Apache or Exim.
Look at the size (uncompressed) of your MySQL binary and general logs and your Apache and Exim logs at the end of the day. I'll bet you find the MySQL general log is the largest one by a factor of at least 5.