I have now released version 1.4 of my sqlstats plugin for MySQL and MariaDB. As of this version I focus on test with MariaDB, but it should work with MySQL also. In addition, I have now made the code a bit more portable by removing some features that depended on the THD struct.
So, what is sqlstats then, you ask? Well, the idea is to keep track of executed SQL statements and gather up statistics on how often they are used. The interesting thing is that sqlstats, before counting the number of executions of a statement, "normalizes" it, by which I mean that it regards
SELECT prod_name FROM products WHERE prod_id = 90;
as the same statement as
SELECT prod_name FROM products WHERE prod_id = 212;
This is useful to track those statements that takes very little time to execute, so probably they don't show up that often in SHOW PROCESSLIST, but are executed to so often to the performance effect can be real bad.
Also, this is useful to track those fast statements that are executed often, but that actually doesn't need to be executed that often as the data is cached or the result is already known or something.
So how does all this work then?
Well, the plugin is an AUDIT and an INFORMATION_SCHEMA plugin in one. The AUDIT plugin part is used to track the statements, every statement that is execute in the server passes this. When a statement is processed by this plugin, the statement is normalized and then it is checked for existence in a list of least recently used statements. This is to ensure that I don't have to keep track of all statements executed to keep a top list. Then if the statements is executed so often that it should be on the top list, then it is placed there. The reason I can't do with the top list only is that if a "new" statement that is frequently executed gets in, and the top list is already full, it will never get on the top list at all (as it is not on the list, it's executing count is 1 and it doesn't reach the list, then the same thing happens next time etc).
These lists of statements are kept in memory inside the plugin itself.
Now, to see what the top SQL statements are, this is done by the plugin also being an INFORMATION_SCHEMA plugin, so the data is available by a simple select:
SELECT * FROM information_schema.sqlstats_topsql;
And that's it!
There is also an INFORMATION_SCHEMA table to show the last executed SQL statements:
SELECT * FROM information_schema.sqlstats_lastsql;
Now, you may ask what the performance overhead is of all this, and fact is that it's rather small, mostly hardly noticeable. In addition, you can switch it off using the sqlstats_enable global variable.
And the size of the top list and lru lists can also be adjusted by in the sqlstats_top_stmts and sqlstats_lru_stmts global variables respectively.
The plugin is downloadable from sourceforge which contains the sourcecode which uses GNU autotools to build it. Also, there is a full documentation pdf there.
/Karlsson
