I've now been playing with MySQL Enterprise Monitor in its latest guise (which includes the Query Analyzer) for a number of days. To be honest, this is pretty amazing stuff.
Once installed, all you have to do is point your applications to port 4040 on the database server instead of the default of port 3306 and in a few minutes you'll start to see the queries being logged in the Query Analyzer tab. You can sort them by database, by execution time, by execution count, or any of the other table headings in the output. You can also filter by partial query as well as a variety of other filters.
"Ho hum" I hear you say? Yes you could probably just take a look at the processlist on your db server to see what it is doing now, but the statistics Query Analyzer provides can be extremely powerful. Two examples:
#1 - Badly performing query
I installed Quan (Query Analyzer) and within about 20 minutes it became clear I had a query that was averaging 14 seconds to run. This query doesn't get run all that often, every 5 minutes or so, but it uses a table that is the most active table on our system.
OK, click on the query and it pops up a full view, showing the query with literals replaced by place holders. If you have Example Queries configured, you can check to see if there is an example showing all of the data, and if you have Example Explain configured, you will also be able to get a full breakdown of what indexes will be used in resolving the query.
With a few minutes of checking the query and fine tuning it I was able to get it down to 0.15 seconds on average. That is almost a 100 fold improvement. Yes, I could probably have done something about it earlier, as I knew it was sub-optimal, but seeing it showing at the top of the list of queries hogging resources lifts the sense of urgency.
#2 - Excessive executions of query
While looking at the first one I noticed another query starting to work its way up the list. This one was a relatively simple query, but was clocking up more than 2,000 executions a minute. Now unlike the first I wasn't all that sure where this query was being run, but using the 'full' display format I could look for that exact string in the codebase, and was quickly able to identify where it was being called.
A little more searching and I found that the query was within a loop where it would get exactly the same information each time, because the variable data was set outside of the loop! Fix that and it now runs around 10-15 times a minute.
I could have discovered this one without Quan. Probably. If I'd done a code review that could have taken days if not weeks. If I wasn't so bored with the code review that I managed to understand the implications of the query within the loop. Instead I was able to find and fix it within minutes, and then be able to verify that the problem was resolved within a few more minutes.
Think on this one for a minute. To display the particular page in question there were up to 200 unnecessary queries. That is a lot of running around in circles. I'm now going hunting for the author of that gem with a big stick - and hoping that it wasn't me 
Quan should be in every DBA's toolkit. It is an absolute must.
If I get my act together I'll put up some before and after screen shots.
*DISCLAIMER: I am an employee of Sun Microsystems, part of the team responsible for the MySQL.com websites, so I am almost certainly biased. MySQL.com is a pretty busy website though and we like to think that it hits our software pretty hard.
