Why Should I Be Reading This?

To better understand how the MySQL Server functions, how to monitor the relevant server events, and find out what’s new in MySQL 5.7.8.

What’s Special About the Audit Plugin API?

Picking the right API for your new plugin is probably the most important design decision a plugin author will need to make. Each of the plugin types exposed by the MySQL server have interesting and unique events that plugins can consume. But in addition to that, some of them provide very important additional characteristics that make these APIs stand out and become much more convenient to use.

One API in particular—the Audit Plugin API—is full of very interesting traits:

• It can support multiple active plugins receiving the same event(s).
• It stores plugin references in the execution context so that it doesn’t have to repeatedly search for it in the global plugin list.
• It supports grouping of events in a number of event classes or categories and allows plugins to subscribe to only those event classes that they are interested in.

Let’s go into more detail on why each of these are important.

Multiple Active Plugin Support

Take for example the Authentication Plugin API. Each user account uses one and only one authentication plugin. This makes a lot of sense because user accounts can only authenticate in one certain way. And there’s a lot of good authentication plugins out there. But what if I want to take some extra action when users connect?

Yes, in theory I can tweak the code of the authentication plugin(s) I’m using. But maintaining my custom changes on top of the ever changing upstream(s) can quickly become tedious, especially if I’m using multiple authentication methods within MySQL.

This is where the Audit API’s ability to support multiple active plugins all receiving the same event can be extremely handy. It allows me to write my own little plugin, then subscribe to the MYSQL_AUDIT_CONNECTION_CLASS and react to the events I need. For example, MYSQL_AUDIT_CONNECTION_CONNECT and MYSQL_AUDIT_CONNECTION_CHANGE_USER. And I can do all this without even modifying the existing plugins.

Caching the Plugin References in the Execution Context

Plugins are dynamically loadable and unloadable.  Loading and unloading is done through (eventually concurrent) SQL commands. And plugins loaded from one thread need to become visible to all of the other threads too. To implement all this the MySQL server keeps the plugins in a global list and employs some good old fashioned multi-threaded programing techniques to protect that list from concurrent access. Namely it uses a mutex called LOCK_plugin.

Now let’s look at what it takes to be able to safely call a plugin method. First of all we need to ensure that the plugin will not be unloaded for as long as we need it. Obviously it’s not practical to be holding LOCK_plugin for the entire duration that we’re using the plugin, as no other plugin operation can occur while we are holding the lock.

The server solves this by reference counting the loaded plugins. When we need to call a plugin we can lock LOCK_plugin, take a plugin reference (increasing the reference count as we do), then release LOCK_plugin and go on and use the plugin.

Consequently, when we are done with our plugin usage, we can lock LOCK_plugin, release the plugin reference (decreasing the reference count in the process), and then release LOCK_plugin.

Now imagine we need to do this millions of of time to execute a single query (e.g. call a function that calls a plugin from inside a subquery). The LOCK_plugin usage can cause a significant performance hit.

This is where caching the plugin references in the execution context comes in very handy. What it does is instead of releasing the reference right after usage it will store it into the session’s context, so that the next time we need the plugin we won’t have to take any mutexes or look at any global structures. We can just reuse the reference we’ve already taken. Of course there’s a cost for that. And it comes from the fact that one can’t UNINSTALL a plugin that has active references to it. But most of the time this is a very small cost to pay for much better performance.

Event Pre-Filtering

The Audit API generates an event for every query that’s executed and for every connection/disconnection. In the typical scenario the latter is much rarer than the former. Why would we need to bother calling all plugins that need to react on connect/disconnect for each query? Not only is there no real benefit, but there’s a cost to that (see the explanations in the previous section). This is where the event pre-filtering comes in handy. Check out the Audit Plugin data structure:

More specifically the class_mask property. This defines a bit-mask of all possible event classes the plugin is interested in receiving. The server aggregates this mask at INSTALL PLUGIN time so that it can consult it when it’s about to dispatch an event to the plugins, dispatching it solely to the plugins that are interested in receiving it and avoiding the dispatch altogether if there are no plugins interested in it.

Changes in 5.7.8

I hope you’ve seen the Query Rewrite API in the previous 5.7 DMRs (see Martin’s two part blog series for an introduction: part1, part2). It’s a useful API allowing one to install pre and post-parse plugins that can alter the query being executed. That API was being called several times during each query execution but without having some of the traits described above. Instead of re-inventing the wheel for it we’ve just transformed that separate plugin API into an audit API event class. And thus the Query Rewrite Plugins are now actually Audit plugins. This is described further in worklog 8505.

We’re also working on strengthening the good parts of the Audit API even further, so stay tuned!

That’s it for now. I hope that this has helped to shine a light on how useful the Audit Plugin API is! THANK YOU for using MySQL!