MySQL Query Analyzer vs. Percona’s Patches

So it’s been a while since I’ve hit my blog, but I feel compelled to respond to Baron’s post, and many of the other (perhaps short sighted) criticisms out there against this new functionality that we’ve been working on for so long.

Everybody seems to be saying that this functionality should be implemented in the server, or that the better way to do this is to use these patches which add functionality to the logging that MySQL already provides. Well guess what people – what does that give you, other than some more details on you queries?

More I/O.

What’s bad on a database server?

More I/O.

Query Analyzer, whilst it does currently use a proxy to collect the statistics, doesn’t hit your disk at all. Everything is collected and aggregated in memory, it doesn’t require you to turn on logging at all to collect this information. Everybody already knows that turning on the logging on MySQL can give some “serious” performance decreases – which is why we initially looked at using the proxy, rather than log monitoring and aggregation.

Yes, we are still looking at feeding information in to the Query Analysis page from different sources, such as the logs, or even DTrace, for people that don’t want to use the proxy, but we think we’ve actually come up with the better solution for overall database performance here in the short term.

And guess what:

  • It works on 4.1, 5.0, 5.1, 6.0
  • It gets the EXPLAIN plan at the time the query was at it’s worst, not at some random time afterwards when you decide to run a log analysis
  • You can redirect away from the proxy when you don’t need it – no extra overhead at all (whilst server instrumentation is more code in the server, more overhead, whether you want it or not…)
  • Now, we do have some limitations, which are perfectly valid – we can’t monitor the queries that a slave SQL thread executes for example, and we can’t measure the IO, lock waits, etc. for each statement – but we have some plans on server instrumentation without I/O in the works for that, and Query Analysis is sure to snap that up when available.

    I’ll also note that the user and table statistics that were created by Google “get this”, and I’d very much like to see those integrated in to the server. We could really make some good use of those within MySQL Enterprise Monitor as well, and I’m hopeful that we will get these integrated at some point.

    So Percona – here’s a challenge – how about creating your extra query logging patches in a way that doesn’t give the I/O overhead of using logging, in the way that Google’s User/Table/Index monitoring does? That would make them compelling, for me. :) The statistics that have been implemented are fantastic, no doubt about that, but don’t add I/O or lock overhead (such as the current mysql.general_log and mysql.slow_log in 5.1). ;)