I had the chance to review the new Query Analyzer program from Sun over the last few days. I am very interested in how it performs as I have previously not had a chance to see the program in action (or the Enterprise Monitor program either for that matter). So, before getting into what the Query Analyzer can (and cannot) do let’s look at what Sun says it does.
From the November 19th, 2008 press release:
The MySQL Query Analyzer saves time and effort in finding and fixing problem queries by providing:
- Aggregated view into query execution counts, run time, result sets across all MySQL servers with no dependence on MySQL logs or
SHOW PROCESSLIST - Sortable views by all monitored statistics
- Searchable and sortable queries by query type, content, server, database, date/time, interval range, and “when first seen”
- Historical and real-time analysis of all queries across all servers
- Drill downs into sampled query execution statistics, fully qualified with variable substitutions, and
EXPLAINresults
Can you do all this with other tools or by hand?
Absolutely. Some of the those very tools I have discussed before. Most I have used at least once or twice. The question is really can Query Analyzer do this better? Find out the answer at the end!!
Installation
The Query Analyzer program is part of the Enterprise Monitor program. For those who are not familiar, Enterprise monitor is used for monitoring MySQL servers. Big surprise right? It works by having an “agent” installed on the server to be monitored which then passes information back to the central server which displays gathered information in an web-based interface.
I will confess. The installation of the service manager was performed by Sheeri. However, going through the notes she left it appears to have been rather straightforward. I picked it up where the configuration begins.
I will note one problem with configuration. Once the package is installed you have to do some setup before actually using the program. This is where it got a bit tricky. The test setup is on a system without direct Internet access (prevented by a firewall, for security reasons). The package has your key, your enterprise account name (email) and password. It connects to the Sun servers to verify your credentials and activate the program. Well, not if your server doesn’t have a direct connection to the Internet!
In that case you use a product key. Well, being new to this I had no idea where to get that from. I searched around a bit on the enterprise website and did not find it. On the enterprise website I opened a ticket with Sun with a level of S3 (meaning this is non-production impacting..testing setup). I had a correct reply in FOUR minutes.
Sun support response time is very good! On the downside, this information probably should have been in the knowledge base.
This actually finished up the configuration of the Service Manager (the core functionality). Now to install the Service Agent which goes on the server(s) you are going to monitor. In this case the Agent was also being installed on the test system. As with the Service Manager the actual install was straightforward.
The program started up correctly the first time I tried. The proxy by default starts on port 4040 and I kept this as the setting.
Running the Program
Once this little quirk was resolved I began running some queries and seeing them show up in the Query Analyzer.
First impressions are that Query Analyzer does exactly what Sun says that it does. As you queries pass through the proxy and on to the database they are also sent to the Service Manager program. It slices,dices and display the data gathered in close to real time on the Query Analyzer tab of the dashboard.
Here are the various ways you can sort your queries:
- By Query
- By Database
- By total execution count
- By Execution time
- total query execution time
- maximum query execution time
- average query execution time
- By Rows
- total rows
- maximum
- By Bytes
- total bytes
- maximum bytes
- average bytes
- by date/time query was first seen
One click is all it takes to change the sorting method.
The Query Analyzer table can let you see at a glance what is going on with your system. Here is a a screen shot of the Query Analyzer (click to enlarge):
By default the Query Analyzer displays data for up to 24 hours. This can be altered by pulling down the “Time Display” option in the filter bar and choosing the “From / To” option instead of “Interval”. This will change the filter bar to have date based pick lists so you can specify a longer date range. Be warned – with a significant amount of data this can impact performance.
Any query can be drilled down into and examined, even showing the EXPLAIN of the query if it took longer than 500 ms. Utilizing this you can begin your resolution of the problem in the typical MySQL manner.
The Final Analysis
I am not aware of anything else available either commercially or freely available that does this type of analysis for MySQL. The only similar functionality done by other products is through reading of the slow query log. While this can provide some of the information, the Query Analyzer does a better job at real-time analysis of the collected data with your sorting options.
While I am not familiar with other vendor’s database servers, I work with people who are. So I asked them. From what I understand there is somewhat similar functionality to the various programs that work with the slow query log, but nothing that is quite like the real-time analysis that Query Analyzer proves. If I am wrong, kindly suggest similar tools!
While the use of the proxy program provides added complexity, it also brings flexibility to the program.
Does the Query Analyzer do what it says it does? Yes! The only significant downside to this program is the cost. However, if you have Enterprise support it’s already included. So that’s not bad. I would not recommend that you buy Enterprise support JUST to get Query Analyzer — maybe Sun should package the Query Analyzer as a separate product? Reasonably priced (say, $200 or less) I would be the first person to tell you to buy it for any company with production systems.
As I said at the start, there other programs out there that can do grunt work that Query Analyzer does and help you resolve issues with slow queries. They don’t do this in real time and in addition the ease of use after configuration is what sets this apart. The simple graphical interface makes it dead simple for everyone to see what is causing traffic to snarl. It would be nice if it resolved the problems for you…but I don’t know of any software that does that!!
No comments