Tuesday, April 29, 2014

SQLStats plugin version 1.4 released

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.



Mark Leith said...

Or just use statement digests in performance schema, which are on by default in MySQL 5.6. :)

Anders Karlsson said...

I haven't checked that particular P_S table, but it seems useful. I looked at the docs for it and realize this also uses the term "normalize", which is the term I started using when I first wrote sqlstats (which doesn't mean someone stole it, all it means is that I started using a term that was already in use, but me not knowing it). Anyway, I'll check it up, it sure looks useful and not having to have a plugin helps of course.


Mark Leith said...

It was modelled on MEM Query Analysis, which also used the normalize term, for quite some number of years now (http://dev.mysql.com/doc/mysql-monitor/2.3/en/glossary.html#glos_normalized_query).

mysqldumpslow did similar before that too, but in a far less aggressive form (only replacing literals, and only using the term "abstracting").

Thomas pedro said...

Dwindle Taylor is a senior budgetary investigator at LoansX with a keenness for fund and protection. Lately he has taken up to give autonomous money related counsel through his useful articles. His articles are generally perused as a result of the clear way of composing and altogether examined information. auto title loans near me chicago