More on the sqlstats SQL Statement monitor plugin

There was some questions on how sqlstats really works, so I'll provide some more details here. Before I begin though, if you want to play with it, it can be downloaded from Sourcefore here: http://sourceforge.net/projects/sqlstats/

OK. So lets start then. SQLStats is a plugin for MySQL 5.5 and up, it will not work at all with previous versions. What it does is reasonable simple, the reason it hasn't been done before is that MySQL just wasn't instrumented to support it, but the effect is really usuful anyway. The 4 things it does, in short, are:

• Pick up all SQL Statements executing in MySQL. This is done using the MySQL Audit plugin interface.
• The SQL Statement is then normalized, which in this case means that all literals in the statements are taken away, so that SELECT * FROM orders WHERE orderid = 57 becomes SELECT * FROM orders WHERE orderid = ?. Then we place this statement in a list of most recently executed statements, if the normalized version is already in the list, the execute counter is incremented and the statement is popped to the top (as it is most recently executed), else it is inserted into the list (and popped to the top), in which case, if the list of full, the least recently executed statement leaves the list.
• Then we check a second list, the toplist of most executed statements. If the execute count of the normalized statement will make it appear on this list, it is added there in the same way.
  As a sidenote, both the recently executed list as well as the top executed list are simple in-memory datastructures. They are not stored on disk or anything, they are just lists of C-structs allocated dynamically.
• The last thing the Plugin do is to expose two INFORMATION_SCHEMA plugins, so that the recently executed list and the toplist can be examined as normal MySQL tables.

All three plugins (the Audit plugin that will pick up and process the statements, and the two INFORMATION_SCHEMA tables) are in the same plugin library, so that they can share the same data: the lists of SQL statements. The lists are protected by an internal mutex, so that multiple threads can use the plugin simultaneously without breaking anything.

One magic aspect here is the Audit plugin API? What is that you ask? It is actually pretty well documented these days (see http://dev.mysql.com/doc/refman/5.5/en/audit-plugins.html). Basically how I use it in this instance is to pick up the statements every time they are written to, or would be written to, the general log. Note that the general log does NOT have to be enabled anywhere for the plugin to work! That's a releif, right?

So, once the plugin is installed, if you follow the documentation, you can see the most recently executed statements in the SQLSTATS_LASTSQL tables in the INFORMATION_SCHEMA schema, like this for example:
SELECT * FROM INFORMATION_SCHEMA.SQLSTATS_LASTSQL;
The columns are described in the documentation, but the most important ones are:

• STATEMENT - The normalized statement.
• SAMPLE_STATEMENT - The first real statement that was turned into the normalized statement in the STATEMENT column.
• NUM_EXECUTES - The number of times the statement has been executed.
• NUM_ROWS_TOTAL - The total number of rows retrieved by all NUM_EXECUTES executions of the statement.

The SQLSTATS_TOPSQL table looks the same, but has different rows in it of course.

You can turn collection of statements on and off by setting the sqlstats_enable global variable:
SET GLOBAL sqlstats_enable=1; -- Turn SQLSTATS on
The installation script will turn it on. You may also adjust the size of the lists using the global variables sqlstats_lru_stmts and sqlstats_top_stmts global variables. The way these work now, they will not really be adjusted down below the current size of the list.

To install the plugin, just follow the documentation. As you have to link with the MySQL sourcecode, the build process is slightly iffy, but once built, the plugin can be installed and deinstalled and recinfigured, without shuting down the server or anything.

I think that the Audit API is good enough reason to go with MySQL 5.5, if you ask me. My testing, and some semi-production testing here at RF, has shown that the overhead of the plugin is hardly noticable.

/Karlsson