9

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)

Improve this question

2 Answers 2

Reset to default
9

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.

Improve this answer
1
  • 1
    Some good points -- in particular, yes, a single GET to our application can cause 100s of SELECTs, since although we try to do as much as we can in a single query sometimes we trade off the performance/clean-ness of this for more elegant structure, more readable code and a cleaner DB. (As an aside, this whole thing actually started from talking about logging contents of POSTs as well as the URL from GETs, since we see the params CGI.pm sees in one case and not the other, and from there into logging/performance in general). Anyway, it's been a few hours, so, answer accepted. Thanks!
    – James Green
    Nov 22, 2011 at 22:57
4

To add to the provided answer, you'll also see a performance hit if you are logging to the same device as your MySQL data stores are residing on - if it's the same disk, you're going to be reading and writing to multiple locations all the time, slowing down the whole process.

This is true even if it's a different partition on the same physical disk.

If logging is going to a different device, that should alleviate some of the performance issues.

Improve this answer
2
  • 1
    Not relevant to my situation -- it's a hosted VM, and the DBs are on a separate logical volume to /var, provided in turn from the same storage array. I suppose in theory they could be on the same spindles, but it'd feel like a helluva coincidence :-) That said, +1 aside, because this would absolutely be relevant to someone with e.g. a default Debian/Ubuntu setup (DBs in /var/mysql, logs in /var/log)!
    – James Green
    Nov 22, 2011 at 22:55
  • @jimbo - thanks for the props even if it's not directly applicable to your particular situation :)
    – warren
    Nov 23, 2011 at 2:01

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .