What's New in the MySQL Enterprise Fall 2009 Release? - Interview with Mark Matthews and Andy Bang

We talked to Mark Matthews and Andy Bang, two core developers in the MySQL Enterprise Monitor team, to give us some insight into the new features in the latest MySQL Enterprise Release. You can find more about the MySQL Query Analyzer in our previous interview with Mark

[Note: MySQL Enterprise is our commercial subscription offering. And, MySQL Enterprise Monitor and MySQL Query Analyzer are only available with this commercial subscription.]

Q: Mark, could you share with us what's new in the MySQL Enterprise Fall 2009 Release?

There are two new flagship features in the Fall 2009 Release. The largest one is that now you can select the time range on any of the graph in the Enterprise Monitor, and it will take you to the Query Analyzer to show you what queries were running during that time. So if you see something interesting in the graphs, whether it's a sudden change in the CPU usage, an I/O spike, or a surge in the number of queries, you can actually go figure out exactly which queries are causing that issue in a straightforward manner.

Also, when you look at a particular instance of a query that you're interested in, you can see some graphs that show the performance of the query over the time range you selected. In this new graph tab in the Query Analyzer, you can see the execution counts, the number of rows returned if it returns rows or updates rows, as well as the execution time in a single place. It's really handy. It will let you know whether this query has been under-performing forever, or if it's a relatively recent thing.

The other new feature we have is called “What's New” in the Enterprise Dashboard, and it allows the Service Manager console to bring you live feeds of product announcement and the status of your support issues. This is our first step toward integrating the support portion of the MySQL Enterprise subscription with the Enterprise Monitor..

Q: What type of metrics can be correlated with the Query Analyzer?

Any graph you see, anything we collect – that could be anything from operating system metrics, such as CPU usage, memory usage, I/O counters, to MySQL-specific counters. You can look at the query profiles over time, i.e. how many selects and updates and other classes of queries were running at any point of time, connections created, threads created, InnoDB row accesses, buffer pool, locks taken over time -- all of these are implemented in this correlation graph feature.

Q: How do the Query Analyzer graphs benefit customers?

It saves a lot of time. The first release of the Query Analyzer saved a lot of time by removing the need to send query activity to logs, gather all that information, and post-process the data in order to figure out which queries were running, how often and for how long. The logical next step was correlating this information with the metrics the Enterprise Monitor collects and displays the data with graphs. If you see something happening performance-wise in the Enterprise Monitor, using the correlation graphs you can very quickly see what queries were happening at that time, before that time or even after that time from a query perspective in the Query Analyzer. That was possible to do in our earlier release, except that it was a very manual process.

Q: Have the new Query Analyzer graphs helped you solve development problems internally?

We use it about every day for our own products, specifically for finding performance issues in development. For example, one of the things we found in this release was that we had more updates than we expected compared to the previous version. We learned it because we saw a peak in “updates” in the row activity graph in the Enterprise Monitor. We highlighted the peak in the updates, and we figured out exactly which part of our code related to these updates through the Query Analyzer, and finally we fixed the problem. This process would have taken much longer if we did it manually because it was actually a certain set of updates that were increasing at a certain point of time, while other normal updates were happening at the same time. If we just looked at the logs, it wouldn't have been as obvious which one was causing that increase.

In addition, our support team also runs the Query Analyzer on internal servers, and they actually found some problem queries. In some occasions they solve performance issues that they didn't know the root cause; in other situations they see the performance drop and have actually been able to tune some queries that they didn't even know were problematic.

Q: What's the customer feedback been on the correlation graphs in the Query Analyzer?

From the feedback I received from the customers, a lot of them do use the graphs, primarily for navigation. For a long time, people sit there and stare at the Enterprise Monitor graphs thinking “what's causing this behavior?” – they see the symptoms in the graph but they can't figure out the cause. So this new graph feature really cuts down the time that it takes to start finding the cause of a performance issue. It removes a lot of manual steps. It removes a lot more time for you if you use an older version of the Enterprise Monitor and try to do this with different pieces – tailing logs and graphing – by yourself.

Q. How can users start taking a look at the MySQL Query Analyzer and the correlation graphs?

We have included the MySQL Query Analyzer, with the new correlation graphs, as part of the MySQL Enterprise Trial. It’s a 30 day trial, and anyone can try it – you don’t have to be a MySQL Enterprise customer.

Q: Are there other interesting facts about the Query Analyzer that you want to share with us?

The correlation graphs in the Query Analyzer are the first feature in the Enterprise Monitor where we really start to use AJAX to provide a more interactive user experience. While looking at any of the graphs in the Enterprise Monitor, you can click and drag to select a time range, and then it will take you to that point of time in the Query Analyzer with that graph showing, so you can start looking at the queries that were executing at that point of time. At that point, you can either drill down, by narrowing the time range, or choose a graph that shows different metrics for the same time range. This level of interactivity is enabled by AJAX, where we let the users select regions on the graph, select different graphs and dynamically re-draw them without re-drawing entire parts of the page, and without requiring browser-side plug-ins, such as Flash.

We also have overhauled the graph performance quite a bit because the graphs have become so central in this release. The performance of graphs is much better in the Enterprise Monitor Fall release in terms of the amount of data and the number of servers we can show. It's also much more scalable than it was in previous versions. You'll be able to look at the data for a longer time with a higher server count. If you have a lot of servers you can have it all aggregated in one graph, which has been there since the day one of the Enterprise Monitor, but we keep raising that bar on how many servers you can monitor and the length of timespan of data we can show.

Q: We've discussed a lot about the Query Analyzer, so let's switch gears and talk about the Enterprise Advisors. Andy, what are the new Advisors in the Fall Release and how do they benefit our customers?

We're releasing 12 new Enterprise Advisor rules and 6 new Advisor graphs in the Fall Release, including new rules for the following Advisors: Administration, Heat Chart, Performance, Replication, Schema and Upgrade. Among these new rules, I think the following two are the most interesting and worth mentioning.

The first one is called “Tables Found with No Primary or Unique Keys” in the Schema Advisor. A primary or unique key of a relational table uniquely identifies each record in the table. Except in very unusual circumstances, every database table should have one or more columns designated as the primary key or as a unique key, and it is common practice to declare one. However, in some cases a table is put into production without an appropriate primary or unique key, probably because the developer is not an expert in database table design or because the table is designed without being reviewed properly. This new rule investigates why the customer's database has tables without primary or unique keys, and suggests adding such keys where appropriate.

The second one I want to mention is “Agent Host Time Out of Sync Relative to Dashboard” in the Heat Chart Advisor. To maintain data consistency and to facilitate day-to-day system management operations, which often require comparing logs, files, and timestamps across servers, it is important that the server clocks across all your systems and data centers are synchronized with respect to each other. If the clock across servers is not synchronized, it would be more complicated when you try to diagnose a problem by comparing timestamps across systems. Another drawback of unsynchronized clocks is that the data and graphs shown in the MySQL Enterprise Monitor Dashboard will be skewed by the difference in time between the machine hosting the Service Manager and the machines running the Agents. This new advisor rule looks into the time difference between each server and the server hosting the MySQL Enterprise Service Manager, and then suggests ways to have the clocks synchronized if they're not. Our support team has seen problems caused by this issue a number of times, and this will be a very useful rule for our customers to keep their systems in sync -- an important, yet easy to forget task.

Q: To wrap up our interview, Mark could you talk about the future plan for the MySQL Enterprise Monitor?

Some of the things on the roadmap that are of interest to customers will be more data sources for the Query Analyzer. We're looking at ways to provide other options than MySQL Proxy to cover some common MySQL use cases, so that they can remove the Proxy variable in the equation and feel more comfortable using the Query Analyzer in production. For example, not just using the Agent-Proxy combination, but using things like Connectors or logs from the server, and opening-up the APIs so that people can put the information in their repository in the way they want.

Another feature that I mentioned earlier, not related to the Query Analyzer, is the deeper integration with our professional support. Our plan is to add the ability to take the information in the Enterprise Monitor as you're looking at it, whether it's the Query Analyzer data or the performance data, open a support ticket right from there, and get the support team the data from the Enterprise Monitor that will help resolve the problem more efficiently.

The support team has identified key problem areas and they know which information is useful to them for diagnosing a particular kind of problem. Information such as immediate performance data, performance data over time, and configuration settings are some of the data that we collect today, and we would like to be able to send those data to the support team to help them tackle customer issues. Instead of having the support team ask for these critical data after an issue is opened, the information will be right there and ready to go.