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.

/Karlsson 

6 comments:

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.

/Karlsson

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").

jack sha said...

Establish and maintain an online page listing "Military Best Practices" and providing info on: locating or contacting CFSA member companies; addressing queries or considerations check cashing for you concerning member company practices; monetary help on the market to military personnel; and military credit subject matter alternatives to be used by the overall public

md sukria said...

Take the pain out of tax time by lease North American country prepare your returns. begin with a free tax estimate that needs solely your most current paystub; we'll then gather all the data necessary to organize associate correct come fast cash advances richmond. we are able to give you with copies to mail yourself, or we are able to e-file your returns for you.

Sohidul Islam said...

I simply have a glimpse here and seem pleasant to seek out this journal. Made content writing hand and extremely cooperative website. I wish most of we are inclined to United Nations bureau locality unit to search out these methods of things, here we will observe everything payday loan in mobile. I’m with the content tribute and do esteem him as a decent supplier. Thanks for your labor and you too.