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.
- 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.
- If the cache is not full but the query has not been updated in some time then the query should also be thrown out.
- 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
- 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.
- 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
- Include an optional filter to filter queries by the database being changed.
- Include an optional filter to filter queries by the database user making the queries
- A command to clear the cache data
- 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.