- the velocity and frequency that a query is submiited for execution
- the total execution time of the aggregated executions
This could be symptomatic of an application not properly configured for caching (see Darren Oldag's blog on this!), or just overall poor design. Regardless of why, when or how we all know it happens. The trouble with this particular problem is that when a query is tuned, or very simple, it is usually not suspect for being a resource hog. Pulling aggregates for number of execs and total exec time for specific queries is a little tricky and labor intensive with the Slow Query Log, and not really a good option for SHOW PROCESSLIST. With this in mind, we designed the Query Analyzer to aggregate these values for quick reference in the Enterprise Monitor. Take a look:
We have also been listening when Mark Callaghan talks about reporting rollups for the top-N objects that are consuming resources on the server. Given we will probably have to wait until 6.0 to get the SHOW STATS extensions for this, we are looking at creative ways we can do this now using the proxy and Monitor service agent.
10 comments:
Can the query analyzer capture all queries over a period of time and analyze them? An intermittent problem for us is a large workload spike from fast queries. They don't appear in SHOW PROCESSLIST and using the slow query log is painful (must login to get the output, must run Awk script to aggregate the queries). A tool to automate this is great. One analysis that we do is to normalize queries by removing extra white space and replacing literals and then running the equivalent of 'sort | uniq -c' to find the most popular query.
@mark:
that's exactly what it does. when 'enabled' it captures all queries, normalizes in a similar fashion as you describe, and reports aggregated stats on that normalized query back to the Monitor on a periodic basis.
Once on the monitor side, we can look at any arbitrary time range, and perform additional rollups, stats, etc. on the requested range.
That is even better. Does this require the use of log tables or log files? Log tables can be rotated. Log files cannot.
@mark:
I'm in the midst of writing an "insider's view" blog spilling some gory details. I'll cross post it here when it's done. But it should also show up on planetmysql.org.
But I'm not sure I understand your question fully. Do you mean log files/tables on the monitored mysql instance, or log files/tables in the Monitor's repository?
Nothing is stored with the agent/proxy or monitored mysql except in memory, until the data is sent to the Monitor. Then, the monitor uses his own storage for further reporting.
I wanted to know if you were extracting the slow query log output from the log file or log table. But I think you are doing neither which should avoid the problem of needing to rotate the slow query log when it gets too big.
@mark
indeed. however, ex post facto analysis of the various logs (slow query, or general) is on the road map as another source of query data, should the proxy not fit in some people's setups.
MarkC, Exactly! We are using the proxy to collect the statements/stats and aggregating everything in the Service Manager (central server) for analysis.
As promised, I've blogged on some innards of query analysis and how it works.
http://darren.oldag.net/2008/08/mysql-query-analyzer-peek-under-hood.html
When is this functionality going to be included in Mysql Enterprise Monitor?
David, Monitor 2.0 w/Query Analyzer is available for beta now to Enterprise subscribers
(https://enterprise.mysql.com) and will be posted for public beta in the next few weeks. GA is targeted for later this year, hopefully before Thanksgiving.
RobY
Post a Comment