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). πŸ˜‰

    10 thoughts on “MySQL Query Analyzer vs. Percona’s Patches”

    1. Mark,

      Few things

      1) Percona Patches allow you to do the sampling causing as little overhead as you like. While if you run the Proxy you have to route all traffic through it (or none). How about overhead in case of having 10000 queries/sec ?

      2) We do not use logging tables just because of the overhead.

      3) You can enable and disable logging (or the fraction of queries logged) online.

    2. Hey Peter,

      Sampling is nice, but really that’s only there because of the downfall of writing *everything* to the logs. It can also lead to skewed results in some instances..

      And hey – you can sample with Query Analysis as well – just load balance your connections between the proxy port and the real database port. Or only put the proxy on one or a percentage of your slaves, in a scaled out environment..

      You do not *have* to route all your traffic through the proxy, that’s just nonsense.. πŸ˜‰

      It’s good that you guys don’t use the tables in 5.1 – yes they do have too much overhead. A storage engine without the locking overhead, entirely in memory, is what’s needed for this kind of information. Then tools (MEM, maatkit, whatever other tools are out there) would be able to select from it and store it for historical reporting in another repository – you don’t need to persist this information on the database server that’s being monitored. Maybe we will have one some time soon!

      Thanks for the discussion!

      Mark

    3. Mark, I agree[1] with your points, and I agree with the need for a better solution. In fact, I think we need to do this a lot better than EITHER Percona or Sun/MySQL are doing.

      I also hear a little bit of a hurt tone in your first sentence. This isn’t an attack. This is what Marten Mickos calls co-opetition. If the state of the art isn’t good enough, we need to spur each other on to better things, period. When I look at the Query Analyzer, I see Sun/MySQL striking a balance between making money from a proprietary system and giving customers ways to analyze their performance problems, and that tips towards a solution that’s external to the server. Percona makes money from services, and believes in open-source software. That in itself explains a lot.

      To your points in the comments about sampling, using a load balancer to sample is far from ideal. Percona’s patches let you log every hundredth, thousandth, whatever-th portion of the traffic. And this is all without the need for load balancers ($$$$$$) or separate systems or what have you. Oh, and it works on a single server — if you want to sample a thousandth of the traffic via your suggestion, you need a thousand servers. And like Peter said, you can enable and disable all of the Percona stuff online; if you want to rejigger to either include or exclude Proxy as a middleman, you have to restart mysqld to flip around which ports things are listening on, or reconfigure your load balancer — this is much more awkward and difficult than just SET GLOBAL foo.

      Thanks for your thoughtful blog post.

      [1] I would soften the tone of “I/O is bad” because in reality, this is cheap — it’s sequential, non-synced I/O. It goes into memory until the OS decides it can be flushed at leisure. We know about I/O. We have customers pushing MySQL to its limits just as hard as Sun’s customers are.

    4. Hey Baron,

      Not hurt – I just felt it fair that whilst others pointed out our deficiencies, it only fair for “us” (though these are my own comments) to point out those in the other solutions as well. Reciprocal co-opetition. πŸ˜‰

      I’d like to make clear one of the primary reasons why the Enterprise Tools team decided to put this in to the proxy as well – it was not to add value outside of the server so that we could get people to pay for it, though that is a “happy nice side effect” (for us and for the community, it hopefully allows us to make more money, to fuel more development, to give you guys and us more people to work with) – it was to be able to give this solution to people *now*, without having to wait for the MySQL Server’s development/release cycles, as well as to provide the value to people even running on versions such as 4.1.

      As another happy side effect, we gave away MySQL Proxy as GPL to the community.

      We’ve considered adding sampling right in to Query Analysis as well (just pass through unless we want to collect stats), maybe that will come in a later version. Right now without sampling though we’ve helped people find statements that are running too often (even though they are very very quick), and allowed them to better cache these statements as well. I think that has value in itself (I’m sure you guys can do this without sampling as well, but again it’s opening the fire hose in the logs, without normalization/aggregation up front).

      There’s actually some neat tricks with redirecting traffic as well – you don’t need to restart the mysqld server to listen on a different port, you can just change the port the application connects to via it’s connect string, which is typically in a central place for an application (or via the load balancer), or you could in fact use some smart iptables rules as well (as Giuseppe shows here: http://forge.mysql.com/tools/tool.php?id=8). Yes, maybe a little more difficult than a SET GLOBAL command, however just as effective, and still “online”.

      As for both of us doing better – YES! πŸ™‚ Both of the solutions could be better, and there are ways that this can be done better within the server as I’ve eluded to. In fact, I’ve seen a really cool internal prototype that blows these things (your patches, SHOW PROFILE, QUAN, et al) out of the water. Hopefully we can *all* be consumers of that, though again, we need to wait for server development timelines to see this come to fruition (hopefully!).

    5. Mark, I’d love to see that happen, too. But I can’t wait for that. That could easily be 2012 or 2015, or just canceled altogether. It wouldn’t be the first time. Remember failsafe replication?

    6. Oh I *live* it. πŸ˜‰ And really that’s why we’re all looking at these alternate solutions – for you guys it’s inside the server, because you can use different releases, for us it’s outside of the server, because that’s where we have to release it, and that’s where we can add value, *right now*..

      As for the prototype – this is actually one of those things I’m actually really hopeful about, it’s had a lot of interest, and who knows – you may even see it in the 6 time frame!

    7. Actually, I like having 2 options. With the Percona/OurDelta patches there are many pros and cons, not just technical ones (for example, I have to replace my binary!). With the Query Analyzer, I have to pay money, and use the proxy (not just replication, but cron jobs that use the socket for connecting also don’t get analyzed).

      But I like having a choice! I could have both if I wanted πŸ™‚

      There’s also a difference between an attack and “here’s us in comparison to them and why we’re the better option”, and I did not particularly like seeing the Percona posts, as they did feel like more of an attack than a comparison.

    Got something to say?