5.1 + new InnoDB Plugin

If you're wondering what MySQL 5.1 with the new InnoDB Plugin is all about you'll want to tune into the webinar I am hosting next Tuesday. I'll cover the why, what and how behind the immediate performance and scale improvements that can be had, especially on modern (> 4 CPU) servers, by enabling the new plugin in MySQL 5.1. Learn more and get registered, it should be good time!

You can also take it to a deeper level by joining Brian Miezejewski from our PS team on 3/9 for a practical guide to using and tuning the new plugin features to improve performance and scale. Hope you can join us!

One reason a no indexes approach is nice

Like a lot of you, I’ve been following with interest Percona’s testing of the open source column databases. One thing I think is pretty cool about some column databases that work with MySQL is that they don’t require you to create indexes. The reason is, in general, the column is the index. Not having to create indexes is nice because lots of indexes can really bog down a database if you’ve got a lot of load or DML activities because the indexes have to be maintained for all data input and alterations.

In Percona’s test, they showed the load time for all the different databases, but I noticed that the times didn’t include the index creation for LucidDB or MonetDB. I decided to follow Vadim’s link on the LucidDB index creation and totaled up the time it took to create the indexes. For the index and statistics times, it was 384,314 seconds and when you add that to the 140,736 seconds for the table load, you get 6 days just to create the database. That’s quite a difference from the 6 hours for InfiniDB and 14 hours for InfoBright, both of which don’t need or use indexes.

I’m sure indexes supply a benefit for some column DB’s in various use cases, but if the database was real dynamic and required a lot of new objects with indexes be added, continuous heavy loads, or DML, it would seem that indexes could really put a ding in things. In that case, it would seem column DB’s thatdon’t require indexes could have an edge there.

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