Our tool was at first a total hack: Bash scripts sampled SHOW PROCESSLIST, Python stripped literals from queries and then Awk scripts aggregated the results. I ran it from my desktop on demand. Yet this tool found many problems that resulted in a significant reduction in load on servers. It was eventually replaced with something much better, but the replacement still isn't as pretty as the Query Analyzer.
I like that the Query Analyzer makes query analysis available to more users. I also like that it doesn't rely on SHOW PROCESSLIST sampling as such sampling misses more short running queries than you might expect. It is good that it doesn't (or won't) require deployment of the MySQL Proxy to gather workload data as that will limit the use of it:
- Many of us can't get applications to switch the port they use to connect to the proxy instead of a server, at least not switch quickly.
- It takes a long time to qualify software for use and I have yet to do that for the Proxy. Any software deployed between clients and the db server is yet another point of failure and opportunity for misconfiguration.
- How does the proxy impact performance? Is it still single-threaded? Does it invoke Lua for every query or query result? Where are benchmark results for dbt2 or sysbench for the Proxy?
Posts
As MarkM already wrote we are working an several ways to feed data into the Query Analyzer. We can also add slow-query-log sampling, a pcap based protocol sniffer or a application/connector driven data-source.
ReplyDeleteIt is designed to support that. The proxy is just the first data-source.
Hmm, unless they've changed something, this *does* require using a different port - either that or having your MySQL instance running on another port and the proxy interface on 3306 (or whatever your normal port is).
ReplyDeleteIt may not actually be the MySQL Proxy product (I don't know one way or the other, but it probably makes sense to re-use that technology here), but it certainly is proxying requests on another port. Again, unless something has changed since the beta that I'm not aware of.
You said "won't" - are you aware of plans of a different approach? Or did I already miss a newer release that has a different approach?
@Don -- as Jan has stated the QA won't require the Proxy.
ReplyDeleteOne other point, after using our tool to monitor top 10 queries in conjunction with SHOW USER_STATISTICS, workload growth on many critical servers changed from 2X per year to no growth. Most of the growth had been from misbehaving apps.
Recursion -- http://www.dbms2.com/2008/11/19/mysql-query-analyzer
ReplyDeleteQuery Analyzer definitely uses Proxy - the agent runs proxy as a plugin which accepts MySQL connections on port 4040. Unless you are willing to change your application, or swap your server listening ports, Query Analyzer won't record anything. In the docs they estimate a 1-2% overhead by just running proxy and 10-20% if you have query analyzise enabled - you can switch this on and off as required.
ReplyDeleteAlso, Proxy doesn't currently work for 4.0.x databases, but MySQL are working on this...
Other than that, I think this will be a great add-on to MySQL and I'm looking forward to the point release that fixes the 4.0 proxy problems.