Less time finding, more time fixing! Enterprise Monitor 2.1, Updated Query Analyzer Now GA!

I just wanted to tip my hat to the MySQL Enterprise Tools Engineering team for another great release of the Enterprise Monitor. Not to name names, but I want to give a special thanks to a team that always over delivers on a collective commitment to producing quality software. So, a mega thanks to:

Andy Bang, Sloan Childers, Darren Oldag, Eric Herman, Jan Kneschke, Kay Roepke, Mark Matthews, Bill Weber, Diego Medina, Marcos Palacios, Carsten "Pino" Segieth, Josh Sled, Keith Russell, Mark Leith, Heidi Bergh-Hoff, and Gary Whizin (and also welcome Michael Schuster!)

Yet another great job guys!

The new version, 2.1, was posted as GA early on Tuesday and it is quite possibly the best release of the Enterprise Monitor to date.

For those not familiar with the Enterprise Monitor, it is included in a MySQL Enterprise subscription and is a distributed web-based app that users deploy in their environment to monitor and tune the security, performance and availability of their dev, QA and production MySQL servers. It is comprised of :

• An agent, written in C, that is installed on each monitored data source, which collects MySQL and OS metrics, SQL code and exec results
• A central server (aka "service manager"), written in Java, that collects, monitors and alerts on the data collected by each agent. The service manager uses MySQL Best Practice Advisors to measure the collected data against user-defined thresholds and to proactively notify DBAs of problems or tuning opportunities. Alerts are sent to a central console or via SMTP or SNMP notifications.
• A repository that holds the data collected by each agent. The service manager monitors the data stored in repository vs maintaining a persistent connection to each MySQL server.

The Enterprise Monitor also provides MySQL specific monitoring features. There is a Replication Monitor that proactively monitors replication topologies for synch and performance issues and a new Query Analyzer that made its debut in release 2.0 in late 2008. The Query Analyzer is designed to save DBAs/Devs time in finding the most expensive queries (by total exec time, exec count, amount of data returned, etc) running across all dev, QA and prod MySQL servers without any dependence on the MySQL logs or things like SHOW PROCESSLIST. You can learn more about the Query Analyzer here.

Based on customer interviews and years spent in the field, we understand the pain associated with finding bottlenecks rooted in poorly written or inefficient SQL code (this is consistently the #1 problem we hear when talking with MySQL DBAs and devs.) This new release helps a DBA/Dev spend less time "finding" and more time "fixing" poorly performing queries. The key new features in the Query Analyzer include:

• Clickable MySQL and OS graphs that visual correlation system and query activity. Mouse-over a spike in any graph and drill into the queries that were running at the same time. Big time saver.
• Drill down capability for query specific executions – drill into any query and see execution specific graphs for exec time, count, data returned. Helps you see the "normal" exec pattern for a query and identify outliers that may occur during specific windows or time or for specific variable combinations.
• Counts for SQL errors/warnings - help you quickly identify queries that may have never finished or that finished in error. These routinely go undetected by the MySQL logs, etc.
• UI support for EXPLAIN generation threshold - gone are the days of hacking the quan.lua script to set this value!

We have also added new Advisor Rules and Graphs around connections, stale table statistics, tables without indexes, PKS and locked and long running processes.

The new release also includes an updated "What's New" page that allows you to optionally subscribe to live feeds for your open support issues (nice, especially for the issues with a status of "waiting on customer") and for MySQL product updates and alerts. Nice time saver, especially for getting updates on new releases of the Enterprise Server and for Monitor Advisors and graphs.

So how do you get it? Glad you asked...if you are an Enterprise subscriber you can grab the new release and all of the updated docs from the Enterprise Customer download page. If you are interested in learning more or want to try the new Monitor and Query Analyzer for yourself, you can register for a 30 day trial subscription which includes a fully functioning version of the Monitor and Query Analyzer. You also learn everything about MySQL Enterprise by visiting the MySQL web site.

Look forward to hearing about your experience with the new release!

Update on MySQL Enterprise Monitor and "Quan"

Just a quick update that the new MySQL Enterprise Monitor has reached RC readiness and is set for official launch in the next few weeks. This is version 2.1 and features:

• Enhanced Query Analyzer with correlation graphs so you can highlight and drill into spikes in key system resources to see the queries that were running at the same time
• Query specific execution graphs so you can track the "normal" behavior of your queries over time
• GUI support for Query Analyzer EXPLAIN generation
  
• Live feeds for MySQL product updates and alerts
• Live feeds for your open support issues
• New Advisor Rules and Graphs
• and some other things...

Here's a screenshot of the Query Analyzer and correlation graphs...


Enterprise subscribers can grab the RC here. If you aren't a subscriber but want to try it for yourself, you can get it here.

Look for the official announcement on GA availability early next week. Another great job by the MySQL Enterprise Tools Engineering team!

MySQL Query Analyzer: Quick Update

As expected, many people are interested in the "what" behind our new Enterprise Monitor 2.0 w/Query Analyzer, and again, as expected more than a fair number are asking "OK, so when can we try it?" With that in mind, here is a quick update on where things stand:

- MySQL Enterprise subscribers can download build 7038 (soon to be 704x) and all of the docs from the MySQL Enterprise customer site. It has been a popular download and we should have a refreshed build very soon.

- We are planning to post a public beta in the next 2-3 weeks, most likely after we (the Enterprise Tools engineering team) all return from our annual Engineering meeting (this year it is in Riga, Latvia). That event wraps up on 9/24.

- I am doing a webcast on Query Analyzer for our friends in EMEA tomorrow. You can learn more and get registered here. I did the same presentation in the US back on 8/20, so if you can't make the live event, the 8/20 recording/demo is here.

As the public beta kicks into gear I am interested in hearing your feedback on the Enterprise Monitor 2.0, especially the new Query Analyzer. Please plan to actively participate by using our public discussion forums (feel free to start posting now!) In advance of the public beta I would love to get your feedback on the Monitor/Advisors and how we can improve things to better fit your needs. The forums are open and your honest feedback is greatly appreciated.

MySQL Query Analyzer: Tracking query executions

From a performance standpoint, sometimes even tightly tuned queries can cause a performance drag. The common problem here is not one of actual query performance, rather it is a function of:

- 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.

MySQL Query Analyzer: DBA Task #1: Finding Bad Queries

One of the biggest problems I faced as a field DBA was defending my production databases against poorly performing SQL code. Talking to folks at the MySQL UC, during customer visits and trolling the MySQL forums/blogs confirms that this is a common problem and even more challenging when a performance problem pops up during times when no code promotions have taken place.

Traditional/popular way of finding bad SQL code
For MySQL DBAs the challenge to identify and isolate resource intensive SQL code is really two-fold; job #1 is to find the offending code (after that the chore becomes fixing it so it performs, but that is a topic for another post). Most MySQL DBAs have used the MySQL Slow Query Log ("SQ Log") at some point to help with job #1. For those not familiar, the SQ Log is enabled by starting MySQL with --log-slow-queries[=file_name] option. Once enabled the SQ Log captures queries that took longer than X seconds (user definable value between 1 and 10) and presents them for parsing and analysis. Beginning in 5.0 it can be configured to also log any queries that do not use indexes. The SQ Log is formatted into entries that look like:

#Time: 08073101 16:25:24

#User@Host: root[root] @ localhost [127.0.0.1]

#Query_time: 8 Lock_time: 0 Rows_sent: 20 Rows_examined: 243661

SELECT part_num FROM `inventory`.`parts` WHERE

(`ven` = "foo") ORDER BY `delivery_datetime` DESC LIMIT 100;

While the SQ Log provides good details on bad code, using it comes at a cost:
- Prior to 5.1, MySQL has to be restarted to enable logging
- The log grows very large, very quickly. Running it in production is not mainstream, but can be managed via scripts and FLUSH LOGS
- Entries must be parsed/sorted for relevance
- Must use long_query_time=0 option to capture all queries, which comes with overhead of its own
- Statements are logged in raw format, there is no aggregation, or roll ups of executions times, occurrences, etc. Basically, the SQ Log is not easy to use, and takes time to navigate to find queries that are suspect and in need of tuning or elimination.

At this point, I have to admit that I *strongly* agree with those that say that MySQL performance related metrics should be part of the MySQL server and readily available for all to consume. In short, things like the SQ Log are good, but such instrumentation belongs in a true performance/wait interface that is designed into the MySQL server product. My buddy Robin Schumacher has been promoting such an Engineering spec for many moons and it appears we will see the first fruits of that work appear in 6.0. So while we are waiting we have a choice; we can continue to pine for a server side solution, or we can be proactive and create an easy to use solution that works now and will be valid for our older MySQL servers. MySQL Proxy to the rescue...

Using MySQL Proxy for Monitoring Queries/Performance
The MySQL Proxy is available under the GPL and provides a chassis for those who want to build advanced MySQL query and performance monitoring solutions. By implementing the Proxy between your apps and MySQL servers you can intercept their communication stream and build your own query and performance related data collections using Lua and then use your own custom apps to aggregate, monitor and view the data as it runs on your systems. Info (and user forums) on using the Proxy is here.


Using MySQL's Query Analyzer to find bad SQL code
MySQL Enterprise provides an out-of-the-box solution for DBAs/Devs who want to use the Proxy to monitor for poorly performing SQL code, but who don't have the time to build a solution on their own. The Enterprise Monitor Service Agent has been extended to include the Proxy chassis and required Lua extensions so that it listens on a user defined port for application queries, collects the SQL code and performance stats and then reports the results back so they can be stored in the Monitor repository for monitoring and analysis. Integrated into the Enterprise Monitor, the Query Analyzer leverages the Proxy reported metrics so DBAs and Devs can visually pinpoint bad SQL code based on aggregated exec times, number of execs, rows/data returned, etc. The cool thing is the Query Analyzer saves development time building a custom monitoring solution. It also eliminates all of the human and system overhead attributed with enabling and parsing the SQ Log. The Query Analyzer:

- minimizes/eliminates the need to enable the SQ Log to find expensive SQL code
- aggregates queries by total exec time, number of executions
- organizes and sorts SQL by relevance across all servers
- filters by query type, content
- shows trending by date range
- tracks queries by database, application
- provides drill downs into normalized and specific execution statistics
- provides full explain of worst offenders (user definable)
- provides summary of all queries in a consolidated view:



After you find a suspect statement, you can then drill into specific occurs across servers:



While enabling the Query Analyzer comes with some Agent overhead expense, it can be quickly enabled/disabled without a server restart and the SQL code and performance data that is collected and returned is stored in the Monitor repository so it can used for later analysis. For the SQ Log purists, we do plan to incorporate a means to pull over the MySQL log files into the Monitor too, but we wanted to implement a solution that helps our pre-5.1 install base (meaning, they can monitor for bad queries without enabling the SQ Log via a server re-start) as part of the first rev. Hey, if we have to wait for a true performance/wait interface, may as well make the best use of our time...

MySQL Enterprise Monitor: Agent = Extensibility

I have gotten a few questions around my 7/29 blog posting on agent vs client-side products and wanted to make it clear that our decision to go with a distributed agent architecture was a strategic decision that has paved the way for us to deliver on our overall "pain point" addressing roadmap. True, building a client-side app would have meant a faster go-to-market delivery, but that path would have imposed serious limitations on our ability to address and alleviate common pain points around the use and scaling of apps on MySQL.

So what does an agent really do for us from a strategic standpoint? Without revealing too many details (well, these things have already been openly discussed with customers and presented in our MySQL UC 2008 Product roadmap session), our agent-based architecture allows us to provide:

- Freedom from a fat client **
- Minimal connections to the backend MySQL database **
- Real-time visibility and monitoring of all MySQL servers in a consolidated view **
- Real-time collection of queries and result sets **
- Independent collection of MySQL, OS, SQL for monitoring, trending, capacity planning **
- Slave aware Load Balancing of reads/writes
- Connection concentration to remove overhead of idle connections from production servers
- Autonomous, remote administration of all MySQL servers
- Server level scheduling of admin tasks, maintenance, back up, recovery, etc.
- Application data sharding across replicated slaves
- Server/App level Security and Data Auditing

Just to name a few. As I said, there are no secrets here, we have discussed all of this openly in our public webinars and at this past Spring's UC. For your day-to-day tasks, we do provide quality, client-side, GUI tools for *free* that will help you manage single servers, dev and test queries, migrate data, etc. For the more advanced things noted above, the work is better left to a distributed management solution.

As a reminder, look for the public beta of the Enterprise Monitor w/Query Analyzer to open up in the next few weeks. I will post links to the beta registration and download when it is ready to roll.

MySQL Query Analyzer: Open Beta Coming!

We are quickly approaching opening up the MySQL Query Analyzer for general beta and I wanted to pass along an open invite to the following related and informational events.

On 8/13, I will be doing a micro level presentation on MySQL Enterprise. Please attend and learn more about the database software, monitoring and advisor services and support solutions that make up a subscription. I plan to do a demo of the Enterprise Monitor and the new Query Analyzer; that alone makes attending worth the price of admission (in this case 45 minutes of your time!). Learn more and register here.

On 8/20, I will be doing a presentation on the new Query Analyzer. This will be a technical discussion around how DBAs monitor for bad queries now and how the Query Analyzer makes the job much easier. This will be a good time to learn about getting in on the beta too. Learn more and register here.