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.

MySQL Enterprise Monitor: The Secret about Agents

I am often asked why we chose a distributed, web-based architecture for the Enterprise Monitor application. For those not familiar with how the app is deployed, the Enterprise Monitor is typically installed within a customer firewall and is comprised of 3 components:

- a lightweight agent written in C, that is deployed to each monitored server to collect MySQL, OS data and Query content and diagnostics
- a centralized server that monitors the collected data and queries, sends out alerts and serves up the supporting web application
- a MySQL repository that holds the collected data and queries

A typical deployment looks something like this:



Having come to MySQL from Embarcadero Technologies, I understand that many customers are somewhat uneasy with loading an agent on their production database servers. In fact for environments with a small number of developers, applications, and databases I actually prefer a client-centric solution. With this in mind I use the following guidelines when deciding if a new administration/monitoring product or app should be client or web-based (please note that these do NOT apply to development or design tools, which are best rendered in client side apps):

Local, client-based apps are best for:
- Small development shops (3 or less developers). I want the available connections to my production servers to be paying customers not DBAs or Developers!
- Small applications with a small number of MySQL servers. Most client side apps provide single server connections and visibility making navigation a bit kludgy. Some ISVs have the multi-server view thing figured out, but most do not.
- Departmental applications with limited end users. The SLAs tend to be less strict and network bandwidth issues around pulling data to/from dedicated client connections is less of an issue.
- Small, localized corporate campuses. Typically client based solutions require the footprint of the app to reside on a specific desktop, device, etc. This requires the user to be somewhere close to an enabled workstation to well, uh, use the app.

Distributed, web-base apps are best for:
- Large shops with many DBAs, Developers, Sys Admins. In most web-based deployments all app users can be serviced by a single connection and data collected by an agent that is running on the monitored data source. Minimal connections, maximum efficiency.
- Monitoring and managing a large number of backend MySQL servers. This is made easy because typically an agent based architecture scales better than one that relies on adding more clients (and connections) to an environment. Most deployed agents serve as "daemons" that keep track of things MySQL up/down status, connection status, etc, all without the user having to initiate a connection.
- Monitoring MySQL from anywhere. Basically all you need is access to a browser. Enough said.

So, why did we choose a web-based architecture for the Enterprise Monitor? Well, it seems most of our users are split across these guidelines; most have small, nimble MySQL DBA and Dev teams, but an ever growing number of MySQL servers. We opted to provide a tool that would help them scale their resources to monitor/manage more MySQL servers with less time and effort.

MySQL Enterprise Monitor: Competition is a good thing!

As the Product Manager for MySQL Enterprise and the Enterprise Monitor I am constantly being asked questions from our Sales team, prospects, customers, etc. about how our products stack up against competing products. This is tough for a PM because competitive situations change with each new release cycle and ISVs (both free/open and commercial) with agile development practices can deliver new features in very short order. Further, getting into a feature-feature discussion is a no win situation because someone will ALWAYS have more check marks. Also, I tend to be more positive about competing products because a) healthy competition makes us all better and b) my competitors enable more people to use MySQL to build apps that will most likely need MySQL support and c) the best support for MySQL comes under a MySQL Enterprise subscription! With those things in mind you will *never* hear me or the Engineering team I work with bad mouth or otherwise run down a competing MySQL monitoring product.

When asked about competing MySQL Monitoring products I think it is best to focus most on the value the Enterprise Monitor provides by asking some simple questions:

- Does product X allow you to see and monitor all your servers in one consolidated view?
- Is product X agent and web based for scalability?
- Does product X use minimum connections to collect monitoring data from your MySQL servers?
- Does product X store monitoring data so you can analyze it later using any BI app?
- Does product X notify you of problems when you are not using the GUI?
- Does product X describe and help you fix/tune what/where/how when needed?
- Does product X plug into your existing notification/alert/escalation system?
- Does product X show you the top-N query resource hogs for specified periods of time?
- Finally, does product X come as part of solution that includes a version of MySQL fully supported with regular updates, and production support provided by the MySQL Support team for both the MySQL server and the monitoring product?

Not sure about other Monitoring products, but the answers for the Enterprise Monitor are, in no certain order, yes yes, yes, yes, yes, yes, yes, yes and yes.

I am also torn when I am asked to compare how the Enterprise Monitor compares to custom in-house written scripts used to monitor MySQL. Knowing the blood, sweat, tears and pride that goes into each script I usually ask "how will you spend your time now that won't have to collect and store metric data and then write, version, or maintain your own scripts to monitor it?" The former DBA in me recognizes when this particular question strikes a chord...

-

MySQL Query Analyzer: Finds good code, gone bad

In my 14 years in development I learned that outside of poor schema design, nothing drains the performance of an application more than poorly performing SQL code. Even code that ran well on day one of production would sometimes come back to bite at the worst possible times. Even worse, as a DBA I was consistently asked to bail out a development team that was either tuning their code before the rush to production or that was trying to finger code that had fallen victim to a dropped or changed index. Never fun.

As a Product Manager with MySQL I have learned from meeting with friends/customers that this experience hasn't really changed much since I left the field. I hear things like:

- MySQL is not well instrumented for tracking code level performance metrics
- Logs are OK, but not centralized and too low-level for easy navigation
- We need help identifying "good code gone bad" and "bad code gone worse"

To these friends I say we have listened (and will continue to) and help is on the way! My team has been busy working on the new MySQL Query Analyzer that will ship as part of the Enterprise Monitor a little later this year. Just a few bullets on what it does and what you can do with it.

Cool things it does:
- Proactive collection and identification of problematic SQL code.
- Historical browsing/analysis of queries across all servers.
- Aggregated roll ups of all queries in canonical form (no variables).
- Fully qualified views of worst performing statements.
- Drill downs into query details, number of executions, execution stats

These things are baked into the Monitor to help:
- Tune your SQL code before it is promoted to production
- Quickly identify queries that are sapping performance
-- by query type, content, server or application user
- Once problematic code is surfaced you can:
-- view an EXPLAIN plan to see where you need indexes

Here's a screenshot of the Monitor with Quan enabled:



We are currently working with a few "friendlies" on the first beta, but plan to open things for general beta soon. Stay tuned (no pun intended...), more details to follow on how you can help with this.

To be completely open and honest...

I remember the day I got "the" call. It was in January, 2001 and I was working as a part of an engineering team developing an online, web-based customer billing system for Louisville Gas & Electric. The app was being developed using VB.net on a Windows DNA (distributed network app) architecture that would eventually be served up under IIS. Cutting edge stuff for the day, ancient by today's standards. The call came from my current boss, Robin Schumacher, who wanted me to join him in something call "Product Management" with Embarcadero Technologies. Overall the move has been a good one, but to be completely honest, I miss getting elbows deep in technical details. Along the way, I have had the opportunity to work with some of the best database management tools on the market. I was fortunate to manage Embarcadero's flagship Rapid SQL and DBArtisan products during my 4+ year tenure and had the great privilege of working with one of the highest performing engineering teams in the ISV business.

Robin left Embarcadero to start the Product Management group at MySQL in June, 2005. He recruited me again and I joined him in December of 2005 to manage what was then call MySQL Network. Having worked primarily for corporate IT shops and commercial software companies, it was quite an adjustment coming to work for MySQL. Front and center, the primary product is open source and free to use; hard to swallow for the capitalist in me, but hey I quickly adapted. Actually, I prefer the MySQL model to the old school, licensed, proprietary model. Basically, you use our product to build great apps and when those apps become critical to your business, you pay us to help you make sure they are up and running great. Win-win!

At MySQL I work with some of the best and brightest Engineering and business minds in the industry. My primary partnership is with the Enterprise Tools Engineering team led by Gary Whizin and Mark Matthews. Our primary work is focused on the MySQL Enterprise Monitor which comes as part of a MySQL Enterprise subscription. MySQL Enterprise is our subscription based solution that includes the Enterprise Server and regular bug fixes, the Enterprise Monitor and Expert Advisors and our stellar Production Support Services. You can learn more here. We (PM and the Enterprise Tools team) release new versions of the Monitor 2-3 times a year to ensure that the product continues to evolve. Along these lines we are currently working on Enterprise Monitor 2.0 that will ship with advanced Query collection and analysis capabilities. Of all the product releases I have been involved with since jumping from development to Product Management, this one has me the most excited. It might be because we are addressing a common DBA/Developer pain point with a very real, easy to use solution. It might be because every single customer we have spoken to echoes this pain. It might be those things, but I think I am most excited because some of the proprietary tool vendors in the MySQL monitoring space have creatively adopted our very public facing roadmap and announced similar features in their products. I think this is great! We want them to be successful and to enable more people to adopt and deploy MySQL as part of their move to innovative Web 2.0 technologies. IMHO this is a win all the way around!

I will post more details on the new beta release of the Enterprise Monitor with the new Query Analyzer in my upcoming posts. It is mighty cool!