An alternative to the MySQL Query Analyzer
MySQL just released their new MySQL Query Analyzer (link to a trial), and recently wrote up an interview with Mark Matthews about it. If you haven’t read that article, go ahead and do it. I have not used this software, but I fully believe its functionality is quite nice.
But there is at least one alternative, which has been available for a long time. That is the Percona patch-set, plus analysis tools such as mysqlsla or Maatkit’s query analysis tools. This is a compelling alternative, if you can live without a point-and-click interface.
Percona’s patches put the metrics-gathering where it should be: in the server. That’s why Percona’s builds are able to measure a lot of statistics that a Proxy-based solution can’t capture. This information is not possible to get outside of the server. For example, you cannot use the MySQL Query Analyzer to measure the I/O caused by a query. Externally to the server, about all you can do is time queries and measure their size. Percona’s patches have no such limitations; they measure and expose an ever-richening set of meta-data about queries.
Guessing is not enough. You need to be able to measure what your queries are doing. The MySQL Query Analyzer’s way to know which queries cause I/O usage is to “…graph I/O usage on the system as a whole, and when you see a spike in I/O you can see what queries were running at the time.” So you’re essentially reduced to lining up graphs, picking time intervals, running EXPLAIN and guessing. If you use Percona’s patches, you can measure directly which queries cause I/O.
The article claims that “…With MySQL Query Analyzer we are watching from the sideline and capturing things that the MySQL server does not give you,” but the irony is that since Proxy-based solutions are outside the MySQL server, they actually can’t measure things the server already exposes internally. While would be possible to do so by running SHOW STATUS after each query, ask Mark Callaghan what he thinks of that idea.
If you’ve ever administered Microsoft SQL Server, you know what kind of insight you can get into a running server. Other databases have similar functionality. MySQL has decided not to build metrics into the server, and is now trying to build it outside the server — an effort that’s ultimately doomed to failure because the information is only available inside.
Let’s see a feature comparison. I’ve chosen features that were promoted in the tech article linked above, plus key features I know are in the Percona patches:
| Percona patches | MySQL Query Analyzer | |
|---|---|---|
| Has a point-and-click interface | ||
| Freely available | ||
| License | Free (GPL) | Proprietary |
| Integrated into the server | ||
| Requires a separate server | ||
| Requires an agent on monitored servers | ||
| Requires MySQL proxy with extra scripts loaded | ||
| Relays queries through a single-threaded proxy | ||
| Requires changing where your application connects[1] | ||
| Captures total execution time of all queries | ||
| Measures query execution time in microseconds | ||
| Permits sampling of only a fraction of sessions | ||
| Abstracts queries into similar forms | ||
| Aggregates similar queries together | ||
| Aggregates across multiple servers | ||
| Automatically generates EXPLAIN plans | ||
| Filters by query type (SELECT, UPDATE, etc) | ||
| Calculates statistical metrics (min, max, 95th percentile etc) | ||
| Measures per-query execution time | ||
| Measures per-query execution count | ||
| Measures per-query row counts | ||
| Measures per-query update counts | ||
| Measures per-query result set sizes | ||
| Measures per-query table lock waits | ||
| Measures per-query InnoDB lock waits | ||
| Measures per-query InnoDB read operations | ||
| Measures per-query InnoDB write operations | ||
| Measures per-query InnoDB I/O wait | ||
| Measures per-query InnoDB queue waits | ||
| Measures per-query InnoDB pages touched | ||
| Measures per-query filesorts caused | ||
| Measures per-query temp tables caused | ||
| Measures per-query temp tables on disk | ||
| Measures per-query table usage | ||
| Measures per-query index usage | ||
| Measures per-query query cache hits | ||
| Measures per-query full scans | ||
| Measures per-query full joins | ||
| Measures per-query sort merge passes | ||
| Measures queries executed by slave SQL thread[2] | ||
| Measures slave SQL thread utilization | ||
| Provides per-database stats | ||
| Provides per-table stats[3] | ||
| Provides per-index stats | ||
| Provides per-user stats | ||
| Is deployed and tested in large social network sites | ? | |
| Is demonstrated stable by years of real-world testing | ||
| Requires understanding MySQL source code |
Stay tuned. More is coming.
Footnotes
[1] From the article: “You basically have to redirect your application to connect to the Proxy port.”
[2] The slave SQL thread’s utilization is the amount of time it stays busy. This is different from measuring the queries the slave SQL thread executes. The Percona patches can do both; MySQL Query Analyzer does neither, since replication doesn’t go through a proxy. Both are extremely useful in predicting and measuring a replication slave’s workload.
[3] Aggregating queries and then filtering by table isn’t the same thing as measuring how many Handler operations are performed against the table. The Percona patches include SHOW TABLE_STATISTICS, SHOW INDEX_STATISTICS, and SHOW USER_STATISTICS, which are functionality ported from Google’s patches. These let you know exactly how much work is done. This is what I call per-object statistics.
I would mention Percona patches however requires patching source code, building binaries and restarting mysqld, that may be inappropriate in some case. Also most likely the Percona patches is not compatible with MySQL Enterprise, so one installed patches may lose Support contract from MySQL.
VadimTk
20 Nov 08 at 11:52 pm
The ridiculous quan spam on planetmysql lately sent me scurrying off just today to check out the latest mysqlsla. Hooray!
Mike
21 Nov 08 at 12:24 am
I don’t think this is an either/or situation. Your patches are extremely valuable to really dig in and help a knowledgeable developer/DBA understand precisely what is going on.
I have been beta testing Quan for several months, and the value I see in that product beyond what your patchset can do is that it can aid less experienced developers/testers/sysadmins/etc quickly isolate the main performance problems with an application. For a trained and knowledgeable user, the main benefit it can provide is provide a slick package around some of the things you already do.
Of course, that won’t stop me from using your patchset to take it to the next level. But if this can help our less specialized folks optimize their codebase, I can focus on harder (and more interesting) problems.
Ryan Thiessen
21 Nov 08 at 12:30 am
mysqlsla (especially 2.0) is indeed a great tool, and I use it for a while now, and have still not covered all it can offer.
Baron: can you please shed some light on the development and QA process for Percona’s MySQL patches?
I have never installed Percona’s patches, and tend to feel safer with a binary from MySQL, by assuming it has undergone an extensive QA.
The Percona patches always seemed to me like something that should have been integrated into MySQL long ago. Keep up the good work!
Shlomi Noach
21 Nov 08 at 12:43 am
The Percona patches look really cool. But the company where I work already gets grief from customers because we are using MySQL and not Oracle or SQL Server. So the mere suggestion of using a non-standard build of MySQL to management is like asking for a 3000% raise. It just won’t happen. I look forward to taking quan for a test drive and I know it won’t get to the granular detail as mysqlsla and the patches, I know we will find plenty of data to look at related to the poor performance of existing queries. :-)
Tom Krouper
21 Nov 08 at 12:58 am
That’s a great patches, for me personally. But I won’t take any risk to replace my stable server with the patched one.
I’ll be more happy if it’s available as separate application, is it possible?
William Anthony
21 Nov 08 at 2:20 am
From the comments above you might get the opinion that a lot of people are just not _smart enough to use the patchset or are scared that the patchset will break their super tested extensive QA passed MySQL standard builds. Perconna has a name for what it does. I’d trust them my MySQL servers instead of MySQL, Inc. itself. I’ve heard of Perconna fixing bugs, but not producing more of them :P
Žilvinas
21 Nov 08 at 4:53 am
I need and use both — server instrumentation to monitor resources per account and table so that I can find the top N busy accounts and tables AND query analysis to find the top N queries. Continually archiving a sample of queries lets me do both top N query analysis and other investigations into what was running on a server when something went bad. And it would be great to have all of this somewhat integrated.
Mark Callaghan
21 Nov 08 at 8:06 am
[...] 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 [...]
Mark Leith » Blog Archive » MySQL Query Analyzer vs. Percona’s Patches
21 Nov 08 at 8:14 am
For those who feel concerned about running the Percona patched server, yes, it’s always possible that we added some bug. That’s a tautology. But realize this: you use services every day that are running on our builds. They are installed in “big companies you use whose names I am not allowed to mention,” if you know what I mean.
The major concerns people usually cite with with Percona’s approach are increased I/O and lack of integration. Both are valid; however I would note that a) sampling a fraction of sessions is one way to reduce I/O, and b) people who want to say that logging is the wrong way to go will make I/O sound scarier than it is. It’s sequential (append-only) I/O that’s unflushed, so it’s cached in memory and the OS can batch and flush it at its leisure, which means the physical I/O is a lot less than the logical I/O. In the real world it’s generally not a big issue — and we do have customers who are pushing the server unbelievably hard.
Integration is another matter. There’s real value in an integrated, site-wide, point-and-click (or even without the point-and-click) tool.
Xaprb
21 Nov 08 at 9:28 am
Disclaimers: a) I use neither, so this is based on reading. b) I earn my pay not by running MySQL but by building it.
I feel you have technical inaccuracies, and you are comparing apples and oranges.
Inaccuracies (which I spotted):
1) The Query Analyzer will, AIUI, analyze exactly those queries which are passed though it, while server patches will measure all. So, shouldn’t the ticks for “Permits sampling of only a fraction of sessions” be exactly opposite?
2) “… where your application connects”: You may be right for the default, but is there anything preventing the admin from letting the QueryAnalyzer listen on the default server port 3306 and send to a server using a non-default port?
“Apples and oranges”:
Both are fruit and will feed you, but tastes differ – as do requirements.
The QueryAnalyzer as a GUI tool will serve different people and needs than detailed info provided by a patched server.
The 80/20 rule applies in tuning, too – and if the QueryAnalyzer allows the admin to start improving things, this *may* give sufficient results that s/he need not care about things for which server instrumentation is needed (whose evaluation is much more effort, I think).
Jörg Brühe
21 Nov 08 at 10:27 am
Do we need comparisons? I don’t deal with external users and customers so I don’t know. Percona has the opportunity to make possible and popular a great methodology for performance monitoring/tuning/debugging and to provide a tool chain that covers the entire process.
Mark Callaghan
21 Nov 08 at 11:39 am
[...] Analyzer, there has also been some community discussion on this, such as Baron Schwartz mentioning an alternative to the MySQL Query Analyzer, followed by Mark Leith’s response to [...]
MySQL :: Observations by Kaj Arnö @Sun
21 Nov 08 at 2:28 pm
“ever-richening”?
aprk
21 Nov 08 at 3:31 pm
Ever-richening means Percona keeps developing the patches. We have several full-time MySQL developers, including Yasufumi Kinoshita, who are working on the MySQL server and on InnoDB, solving our customers’ performance and other needs. The big weakness here is that we’re not all that good at publicizing it.
Percona is doing a lot of work on MySQL.
Xaprb
21 Nov 08 at 3:53 pm
Baron,
I used both tools at SUN-MySQL consulting and i just wanted to report that mysqlsla + general log are just good enough to generate a year of work to any dev team. Spending the effort on the most painful queries already filter 50% of my clients, an other 30% will then be happy with the performance, for an other 10% the question could raise of what are we doing wrong in the architecture or engineering that we need so much of rewriting code. For those left a deeper analyze with the Percona patches is effectively a rock solution. The Query Analyzer being out of the box solve a deployment and tracking historical data problem, but don’t miss the battle you need to put the Percona patches in drizzle. This is where innovation like this could take place building a better community database kernel. Paul from PBXT have done it understanding that Drizzle solve many editorial problem of a release live cycle driven by enterprise, multi partner and cross platform road map.
Stephane Varoqui
22 Nov 08 at 10:21 am
Jörg, the Percona patches don’t just log everything that passes through the server. There are many flexible ways to filter and limit what gets logged, and this is configurable at runtime with a trivial SET GLOBAL command. It’s a lot easier to do this than it is to change application code, modify iptables rules, or reconfigure load balancers. Mark Leith’s article (linked in a trackback above) discusses this in its comments.
Xaprb
22 Nov 08 at 10:52 am
Although you do have to swap out, and possibly build, your own binary… Which is a lot harder for some than it is to modify an application connect string.. :)
Mark Leith
23 Nov 08 at 12:44 pm
@Mark — for some of us it is easier to change the mysqld binary as we control that.
Mark Callaghan
23 Nov 08 at 2:14 pm
Mark Leith, are you comparing the process of installing a Percona build to the process of becoming a Sun customer and installing the MySQL Enterprise Monitor? I’m sure some customers would be happy to share their experience of how much work that is!
You can install our builds without becoming a customer. Just wget, unpack, and swap out /usr/libexec/mysqld. A 5-line bash script.
This is probably a comparison Sun doesn’t want to invite.
Xaprb
23 Nov 08 at 6:09 pm
No that’s not what I’m saying at all.. ;)
What I’m saying is that, for instance, you do not build for Windows, or Solaris (two largely used OS’s), only Linux 64bit as I understand (do correct me if I’m wrong) – so all those people would have to build the binaries themselves, after applying your patches.. And still restart your database..
@MarkC – sure, and many of us can build our own binaries as well on any of the above platforms, but not everybody can.
OurDelta is helping with this a little, with more binaries for the other Linux platforms as well – which is great, but still isn’t the panacea for *everybody* (as above).
I’m not sure that I would compare a binary swap to installing a full monitoring system, that’s hardly a fair comparison, is it?
And these are my own comments, not my companies.
Mark Leith
23 Nov 08 at 6:47 pm
@Mark — my point is that for some deployments, building or even qualifying a binary is trivial compared to the difficulty of getting all clients to change their connect string.
Mark Callaghan
23 Nov 08 at 6:54 pm
Mark: Yep that’s a perfectly valid point, and in those cases we’d say that you have to swap the proxy to the “old” database port (i.e set the proxy to listen on 3306), and the database port up a notch (i.e 3307)..
Mark Leith
23 Nov 08 at 6:58 pm
[...] An alternative to the MySQL Query Analyzer [...]
Alternativa a MySQL Query Analyzer : Notitodo
24 Nov 08 at 6:32 am
[...] well reviewed by MySQL bloggers. Baron Schwartz of xaprb has covered it himself, but now he posits an alternative to the MySQL Query Analyzer, based on the Percona patchset and mysqlsla, and tabulates the similarities and differences in the [...]
Log Buffer #125: a Carnival of the Vanities for DBAs
28 Nov 08 at 1:47 pm
The comparison clearly shows, that percona patches are worth a try for me. However i can’t find any usefull documentation. I read through various articles at the performanceblog, i looked at the manuals of mentioned tools. Still i can’t even figure how to enable, disable and configure all the nice features mentioned above. Is there some general documentation available describing how to use percona patches? Thank, Lars
Lars
11 Jan 09 at 10:19 am
Thanks for pointing that out. I just noticed that there aren’t any links to the documentation on the patches. There are now links to all of it from http://www.percona.com/percona-lab.html and the particular patch you’re looking for is documented at http://www.percona.com/docs/wiki/patches:microslow_innodb
You can download Percona’s enhanced version of the server at the links above, too.
Xaprb
11 Jan 09 at 1:54 pm
Thanks a lot!!
Lars
11 Jan 09 at 4:45 pm
We’ve got benchmarks now of the overhead.
http://www.mysqlperformanceblog.com/2009/02/10/impact-of-logging-on-mysql’s-performance/
The summary: When you’re I/O bound (which is when the I/O overhead of logging matters) it’s statistically undetectable.
Xaprb
10 Feb 09 at 8:04 pm