Query analysis plugin for 5.5!

A colleague pointed me at More fun with the MySQL Audit Plugin API which looks very interesting.  Analysis of the queries going on inside a msyqld has been something that has been wanted for some time.  Until now it’s only been possible with external addons such as MySQL Enterprise Monitor which do a good job. However, really the place for this functionality is inside the db server itself. If 5.5 m3 provides the hooks to do this that’s great news and while Anders’ first implementation may be simple, this can surely be extended in many ways.

The things I would like to see added to this plugin are the following many of which are safeguards to ensure you can use functionality on a system like this in production without bringing the server down.

  1. The size of the normalised query cache should be configurable, so that memory size is under control. Once full older queries should be thrown out.
  2. If the cache is not full but the query has not been updated in some time then the query should also be thrown out.
  3. Include columns such as time_first_seen, total_execution_time, shortest_execution_time, longest_execution_time, total_rows_returned, minimum_rows_returned, maximum_rows_returned, statements returning warnings, statements returning errors
  4. A big on/off switch that perhaps can be triggered by excessive load, thus saving us making the db server do a nose dive. Perhaps something as simple as a maximum number of queries to analyze in a second, when exceeded the analyse functionality is just skippped. This should be easy to implement though may produce a bottleneck around this counter.
  5. Include an optional filter so large queries may be ignored. I have seen multi-megabyte queries and if you analyze hundreds of these you need a lot of memory
  6. Include an optional filter to filter queries by the database being changed.
  7. Include an optional filter to filter queries by the database user making the queries
  8. A command to clear the cache data
  9. Some extra global counters so that we can see if we are dropping data: time cache last cleared, number of queries dropped for some of the reasons indicated above.

This sort of information would allow software such as Merlin to simply collect the data directly from the server and pass it back for longer term storage, so good for enterprise users, whilst the adhoc users would have a way to check their servers pretty quickly and see what they are doing and which are the heaviest queries running on their systems.

So thanks MySQL for adding the hooks, thanks Anders for starting this plugin and I hope that it will grow and perhaps do some of the things I outline above.

I’m looking forward to seeing a GA version of 5.5.

Is there a MySQL New feature request list anywhere?

Since the time that I’ve been using MySQL I have filed quite a few bug reports. Some of these have been fixed and many of the bug reports are actually new feature requests. While working with MySQL Enterprise Monitor I’ve probably filed more feature requests than bug reports.

That’s fine of course and my opinion of what is needed in MySQL or Merlin is one thing,  yours or the MySQL developers is something else. We all have our own needs and find things missing which would solve our specific problems.

If I have ten feature requests open and only one could be added to the software I’d also like to be able to say: this feature is the most important one for me.

However, it seems to me that there is no easy way in the mysql bug tracker at the moment to group together different types of new feature requests into groups of related features and then see the different types of requested features. I imagine many feature requests may be quite similar, but as I do not have a lot of time to look at all bugs it is easy to lose track of the things that people are asking for. It’s also likely that others who might be interested in my feature request are not aware of the request or able to say “I’d like this too”.

Having a clearer list of requested new features, especially if you have a clearer idea of how many people are interested in these new features (whether paying customers or not) would surely be a good way of guiding the product’s development in the way which would be useful to a wider audience. Is there any way this can be done with MySQL, and how is this done with other products which also are complex and have “insufficient resources” to be able to satisfy everyone’s wish?

Currently I do not feel that I can see where MySQL is going or work out if features that I need might actually be implemented in a reasonable time span (or at all) and that is rather frustrating. Some of the “Enterprise” type features that I think are important such as better partition management (variables such as innodb_file_per_table really suck, but the alternatives of X ibdata files which you can’t manage properly are even worse), better replication (taking out the replication process and putting into a separate daemon which would allow you to do N:1 replication, currently impossible in the current MySQL implementation but actually very useful if you want to have multiple sets of replicated databases each handling their own dataset, but with one or more central servers which see the whole combined dataset) are just larger more complex examples but many simpler changes are also important and some I get told will happen after MySQL 7. For me that’s never never land….

So is there a way that this can all be done more transparentlly?

Bacula 5.0.1 binary rpms for CentOS 5 on http://postfix.wl0.org/ftp/bacula/

I’ve been using Bacula for a while to do backups and it seems to work quite well. I was looking for pre-built Cent OS 5 x86_64 packages and couldn’t find them. In case you don’t want to build your own mine can be found here.

Hope they are useful to you.

The innodb_plugin – a pleasant surprise!

I’ve heard about the innodb_plugin but not had time to put it to the test.

Recently though due to some problems I’ve been having with the MySQL Enterprise Monitor (Merlin) I’ve had to try a few changes and had the opportunity to try out the innodb plugin.

I have been using Merlin for some time and like it a lot. It is not perfect but does a good job for me.  However, since upgrading to version 2.1 I have been having some database load problems. I long ago split the merlin server into a front- and back-end server with the backend running a standard MySQL 5.1 Advanced package. That has been working fine.

I have been monitoring more and more mysqld servers and recently the database backend could not cope. Basically the writes of data collected from the agents and the deletes of old date (purging) caused too much I/O and that is on a box with 6 disks in RAID-10 with a battery backed write-cache.

So I upgraded the db server to a new box with lots of memory and a 300 GB Fusion IO card. I expected all problems to go away. Well not quite. In spite of the solid state drive which was not I/O bound, and the CPU which was not CPU bound, mysqld could not keep up with the load. This was running the MySQL 5.1.44 Advanced rpm. Looking more deeply it seems that mysqld itself was the bottleneck and there was too much contention on the PK by the different INSERTing and DELETing threads.

The Merlin team suggested trying the innodb_plugin (1.0.6) and all of a sudden the bottleneck seems to have gone away.

This is the iostat output taken before switching to the innodb plugin:

This is the iostat output taken after switching to the innodb plugin:

Note: the first set of figures was taken using CentOS 4, and the second using CentOS 5. The IO statistics aren’t exactly identical and on CentOS 5 for some reason the driver appears not to be providing all the stats. However the wsec/s value clearly shows a significant performance improvement and the original problem mysqld was having of not being able to purge as fast as it was inserting data seems to have been solved. At least initial signs seem to indicate this. The only configuration change made to the server was the following:


  • If you are having performance problems on your MySQL server and perhaps the hardware is not the bottleneck then try using the plugin. It may make a big difference.
  • Throwing hardware at a problem does not always solve it.

Also a big thanks to the Merlin team for helping me out with this problem and getting things up and running.