What's New in the MySQL Enterprise Spring 2010 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 the previous interviews for Fall 2008 Release and Fall 2009 Release

[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 Spring 2010 Release?

One important new feature is that now we instrument some of the MySQL Connectors to capture the Query Analyzer data from inside the application. Whether you're running Java or .Net applications, using MySQL Connector Plug-ins with the Query Analyzer reduces latency, has fewer moving parts to manage (i.e. no need to have Proxy), and you can capture additional information from the application point of view. For example, you can tell where the troublesome queries actually originated in the source code, and response times are reported as they would be seen by your users. Using the MySQL Connectors we can actually point to the specific spot where the application is causing problems. This has been a cross-product team effort, with the MySQL Enterprise Monitor team implementing the backend and Connectors team implementing the plug-ins that collect and forward the information.

Also, we now support authentication via LDAP so you can integrate the Enterprise Monitor into a directory server – like Oracle Identity Server, Open LDAP, or Microsoft Active Directory. Depending on how the feature is configured, you can have your directory service handle all authentication and roles so that certain roles listed in your directory match the roles in the Enterprise Monitor. Alternatively you can use it just for authentication with user name and password, as well as use mixed modes where there are users local to the monitor, for example agents, and other users defined in your directory server. We're hoping that this feature simplifies user account management for those customers who have standardized on a directory service for their authentication system.

Last but not least, much of the user interface has been reworked to make it easier to use and more flexible. It's now possible to have multiple windows or tabs open against the same monitor instance, which is nice when you want to keep multiple troubleshooting contexts at your fingertips. We've also added or extended filtering and searching to most features. For example, on the "Graphs" tab, you can filter by the graph name; in "Manage Servers", you can search and filter by server attributes such as server name or MySQL version; and in the Query Analyzer, you can filter for queries that are doing table scans, using bad indexes or for your specific requirement by creating expressions against any attribute that you see in the list of collected queries.

Q: Before we get to more details into the new features, can you refresh our memory on what the MySQL Query Analyzer does?

What the MySQL Query Analyzer does is capture every query, times them and reports them in aggregate, back up to the Service Manager repository. You can then use this data to do all kinds of interesting things. You can roll up these statistics across all your servers, or across groups of servers that we have automatically detected in replication topologies. You can sort by query type. You can search through them using regular expressions, you can see the minimum, maximum, average and distributions of execution times, execution counts, row and update counts, result set sizes, and actual amount of data transferred and you can even bring up EXPLAIN plans of queries that have been captured without leaving the application. With the MySQL Query Analyzer, the DBA can take a look at operating system and MySQL performance counters and correlate them with the queries that are flowing through their databases at the same time to diagnose performance issues, all in one place. It's a big time saver.

Q: Now we introduce the Connector Plug-ins in the MySQL Query Analyzer. How do the Plug-ins help DBAs and developers?

Previously, the Query Analyzer required Proxy to capture aggregate metadata about queries flowing through it. Compared to using Proxy, most of the MySQL Connectors have less overhead when using the Connector-based Plug-ins, especially in terms of latency. They are observing the queries that go through the Connectors anyway, and most are able to report this data asynchronously. So they're not really in the middle of a query stream; they just inspect it as it's flowing by. Many of the Connectors are already doing this anyway -- they just don't report the data anywhere -- so there really is no additional cost, performance-wise, to get this data. The other benefit is that we can capture some richer data. For example, now we can identify the location in the source code where the queries were created or executed from, which helps quite a bit if you are trying to debug a performance problem, especially in a complex codebase. Without the Connector Plug-ins, people may still be able to find this location in the source code by putting comments in their queries, which will then be shown in the Query Analyzer. But using the Connectors makes things a lot easier because it's captured, it's transparent and you don't have to annotate your program to issue a query. Sometimes it's not your program that's issuing a query. Or it's not even your code. You might be using some framework and where the query is being executed is buried under many layers, so this is a lot easier to deal with.

For Java and .Net applications, I recommend DBAs and developers at least look at using the Connector Plug-ins because it's one less piece to manage, with way much less latency, and they get richer information to help them debug things quicker.

Q: Did the new MySQL Connector Plug-ins help you solve any problems?

We've used the Java version of the MySQL Connector Plug-in during development of the latest version of MySQL Enterprise Monitor. It's been helpful in finding issues where we do table scans. It's not always obvious by inspection which code might issue a query that causes a table scan. In our application, the hardest ones to find are those queries that are built dynamically. You can only catch that if you do it at run time. Even if we did turn on the slow query log, we wouldn't know where inside the call stack from the application the worst offenders came from. With the MySQL Connector Plug-ins, now we can find the table scans right away because we have the stack trace of where the query came from. When we saw it, we knew exactly what was causing it. So that's been very useful.

Q: Any other exciting enhancement in the MySQL Enterprise Monitor that you want to share?

There are few other things. We have switched to using Partitioning in MySQL 5.1 to manage our collected data. This enabled us to purge all data very quickly and enhance scalability of the Enterprise Monitor. It will speed up the graphs in some cases, but the big thing is that it will allow us to scale further in how many MySQL instances we can support. We've bumped up against the number of 300 or so with our current implementation. That's strictly based on our ability to write and delete out of the repository at the same time, and this bottleneck has been entirely removed.

There's also the ability to generate a report that you can attach to a support issue that captures the configuration over time. For a single server, a group of servers, or all your servers, you can capture anything that has changed, as well as all the performance metrics in the dashboard, the graphs and the raw data. If you happen to have the Query Analyzer turned on during those instances that are being reported, we also report the most time-consuming queries. So basically if you open a support issue and attach this report, you'll allow the Support Team to get a jump-start on resolving your issues more efficiently.

Q: Let's also talk about the Enterprise Advisors. Andy, could you share the new rules in the Spring Release?

We've been focusing our new rules around InnoDB Plug-in this time. We have five rules that aim to help DBAs get up and running with InnoDB Plug-in and also configure it properly. One of the rules I'd like to highlight is regarding the newest file format. The InnoDB Plug-in delivers a number of features and improvements, but it also introduces the new file format for InnoDB, Barracuda. You can get a performance boost under some circumstances if you use that file format. For example, rather than using lots of space, the new format allows you to compress the table to reduce the space required, which speeds up the I/O. The new file format also enables you to store long variable length columns off of the page. You will experience some level of performance improvement by storing certain kinds of information off the page if it's not used on a regular basis. The InnoDB storage engine will continue to work with the old format. Whether you want to use one or the other depends on your performance needs and whether you want to back track to an older version of InnoDB.

Whenever we write the rules, one of the things we've been trying to do is to cover a variety of Advisors so that we can provide best practices in a range of things that DBAs have to handle. For instance, if you want to deploy the InnoDB Plug-in in your system, an essential part of your job, as a database administrator, is to set up InnoDB Plug-in and make sure it performs well with the new file format. Another crucial task for a DBA is to keep the database secure, so we have two new rules focused on security, making sure that you have your system set up in such a way that people can't hack into. Finally, there are also the Upgrade rules to assist you during the upgrades. When we make changes to the MySQL server, such as fixing a nasty bug, the Upgrade rules will notify you if you're using an old version or an old feature that's subject to it. The Upgrade rules will guide you through what you need to do to get up and running with the latest release that doesn't have that bug.

In addition to the new rules, we also improved the integration between the Advisors and the Query Analyzer in the Spring Release. Previously, when a rule triggered query-related advice, we'd just give you an instruction on how to set the filter and to identify that specific query. The rule included a link to the Query Analyzer tab, but didn't set any filters. Now the link takes you to the Query Analyzer tab, sets the filters for the type of query under investigation, and also specifies the time near when the alert was triggered. This saves DBAs a good amount of time and effort on performance tuning.