Tuesday, November 30, 2010

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:
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.


What? Me Grumpy? Me?? or MySQL on Windows, what's the deal?

So you have seen my writings on MySQL on Windows. Most recently regarding MySQL 5.5 and before that on Cluster and 5.1. So, what is the deal? Am I a mean person attacking MySQL? Am I a Windows hater trying to show how badly Windows works? Or maybe a Linux / Unix hater who want to keep those bearded 1970's terminal junkies out of my GUI Windows world?

Actually none of those. My ambition is to point out this that should be addressed in MySQL for Windows to make it more Windows friendly, for Windows users and developers. As MySQL on Windows stands right now,, it is OK for the Linux / Unix user user who also wants to run on Windows, and for those with cross-platform knowledge and ambition who want to run MySQL across the range. And I put myself among those. But I also want the Windows hard-core users to find out about, use and love MySQL. And MySQL sure could do much more in that area, but before that happens, we (yes we. We who are cross platform folks, we who who can take the time to check things out a bit and has somewhat of an understanding of what those rough edges are and we who care about MySQL and it's adoption on ALL platforms).

I really do not think that a Windows user, who is not used to Linux / Unix, whould have to learn some Linux stuff just to use MySQL on Windows. Come on, MySQL is a database system, using the SQL standard and that runs fine on Windows. Yes it DOES! And there are some good Windows integration, like the VS plugin, .NET connector and much more. MySQL 5.5 really does do away with many MySQL on Windows limitations also.

I recently reported some bugs on this matter, really minor bugs, but things that could easily be fixed and that would make MySQL on Windows a bit more polished. One issue I reported was that there are still Perl-scripts distributed with MySQL for Windows, even in 5.5. Now, installing Perl just to use MySQL is bad enough. But the thing is that many Windows users / developers don't even know what a .pl file is, and that a Perl runtime is needed! If MySQL on Windows really needs Perl to be fully functional (I don't think this should be the case. If Oracle can do without Perl, then MySQL should be able to do this also, but this is a different issue), then ship a Perl runtime with MySQL!

The reason I am reporting these minor bugs on MySQL on Windows is not that I dislike or hate this or that technology. I am waaay too old to get that emotional about technology in and of itself. The reason I am doing it is that this is low-hanging fruit on the way to make MySQL work, look and behave really good on Windows.


Monday, November 29, 2010

Announcement: MyQuery 3.4.1 Released

MyQuery 4version 3.4.1 is now released on Sourceforge: http://sourceforge.net/projects/myquery/. This is a pretty minor release with just a few fixes, but the first of them pretty significant:
  • Windows 7 User Access Control (UAC) issue fixed - When running on Windows 7, the Windows UAC would prohibit MyQuery from starting, complaining about registry access. For once, this was not yet another UAC annoyance, but an actual bug that I hadn't caught before. Now, this should work OK even on Windows 7.
  • SQL Statement normalization issues - The SQL Statements dialog got statements wrong at times, and could cause a crash. This was due to a bug in the normalization of SQL Statements and this is now fixed, including refining the normalization a bit.
  • Optionally Show SQL SELECT tool output vertically - This will turn the output from the SQL command in a SELECT tool around. This is for running statements that will produce multiple columns instead of multiple rows, such as SHOW SLAVE STATUS and SHOW MASTER STATUS. The output from these commands is weird, the multi-column layout really isn't useful, but this little feature fixes that.
Happy SQL Coding!

Saturday, November 27, 2010

Perl is probably a fine scripting language, but on Windows...

Please do not assume Perl is always installed on all platforms. In particular not on Windows! Just as it is probably a bad idea to assume that Windows DOS commands work on Linux! Come on now!

Why am I making a fuzz you ask, do I have a problem with you writing stuff in Perl (personally, I don't even like Perl, but that's another issue)? Nope. But somewhat modern software really should not assume that you have Perl, or DOS, or some other arcane, not terribly standardized piece of software around for a server to do it's job. And MySQL still have a way to go to be reasonably good on Windows. Downloading the lastest RC, 5.5.7, MySQLhas managed to screw up at least two things, at least, in my mind. But maybe that's just me:

  • The optimzied. non debug libraries, are now in /lib under the distribution, not in /lib/opt where they used to be. I see no good reason for this, to be honest (place the libraries where you want, of course, but once a release is out, don't move them around at random in upcoming releases). This has been reported as bug #58532
  • To make it realhard for us in the community to test this release Candidate version of MySQL, to iron out the bugs, MySQL decided not to include dynamic debug client libraries, only the static ones. Why this makes things difficult, you have to do some development on Windows to userstand. For a bunch of reasons (not necessarily good reasons, but if you are Microsoft, you are allowed to do whatever you choose, no reason needed, except that maybe it's the 7th hour on the 7th month and it's afull moon coming up or something), Micosoft compiles stuff so that a static library depends on stuff included by the compiler itself. So if the compiler version changes, the library will not work properly anymore. In short, you need to build your applications with the same version of Visual Studio as the MySQL Client library was built with. Yikes! (For more Yikes, see MySQL bug #58531)
None of this is any fun. And to add insult to injury, in the bin directory of the MySQL distribution on Windows, in 5.5.7, I find the following files: mysql_config.pl, mysqld_multi.pl and 4 more. Yes, Perl programs. No, I do not have Perl install on my Windows boxes. I'm about to test this and see how it worls aout. Above all, I want to test if mysql_config works as expected.

All in all: Please remove Perl depenent client programs from the MySQL distribution! And mysqld_multi seems rather superflous on Windows in general, if you ask me (not that it even works, check bug #58553).


Tuesday, November 23, 2010

Monitoring MySQL SQL statements the way it SHOULD be done!

You may have read a previous post of mine, back in April this year, where I wrote about using the MySQL 5.5 Audit interface to SQL Statement monitoring. There was a bunch of comments and some ideas, but not much happened. Until now that is.

Hereby I release the first version of SQLStats, which is a much enhanced version of what I described in the previous post. This is a MySQL Plugin for MySQL 5.5 that allows you to monitor the most recently executed, and the most frequently executed statements using 2 INFORMATION_SCHEMA tables. The thing is not very complex, to be honest, but it does do the job. So what was the job then? Well, looking at what this plugin does, it goes something like this:
  • Allows you to monitor ALL SQL statements executed by the server.
  • The SQL statements are "normalized", meaning that literals / constants are removed before comparison.
  • Data is saved in memory. No disk access and very little overhead.
  • Data is retrieved from INFORMATION_SCHEMA tables, just a simple SELECT and you know what is going on in the server.
I have done some basic testing of the overhead of this plugin, and it wasn't even noticable. If you have used the general query log for this stuff before, know what overhead I'm talking about. With SQLStats, the overhead is close to 0.

There is no need for MySQL Proxy or anything like that. There is no need to change something in the Client or in the Connector. To be honest, there are a couple of things I want to add to the plugin eventually, but this is a starting point at least. To use it: download it, install the tomcat / mysql monitoring server, install the monitoring agent and ... No wait, that was MySQL Enterprise Monitor, this is how you do it: download, build, install and use it. That's it.

So where can I get it, you ask (or maybe you don't, but I'm gonna tell you anyway). It's on sourceforge, and you can download it from here:

There are two files to download: A simple PDF documents with some basic usage and configuration tips, and a source-code package (which also includes the same PDF).

What would I want from you? Ideas for future development, bug reports and a few beers, that's it, not too much to ask, eh?

Also, did I mention that the overhead is VERY low...

Friday, November 19, 2010

MyMonitor 1.1 released. Finally!

I know some people have used mymonitor on and off over the years, but it was a long ago since I created it, and things has moved on and changed. Among the things that didn't work well was the build process, and the fact that there were a few bugs in the normalization of SQL statements. Now this is fixed.

If you have NOT used MyMonitor, it is a small ncurses based tool for monitoring SQL statements in your server, and work by polling the PROCESSLIST and the normalizing and sorting and grouping the result. What is "normalizing" then? It's the term I use to describe the process where a statement has all the literals replaced with ? in them, so I can see that:
SELECT * FROM mytable WHERE id = 861
is actually the same statement as
SELECT * FROM mytable WHERE id = 387
And by the way, this is also the same statement as
SELECT * FROM mytable WHERE id = 177

MyMonitor can look like this in action:

You get the point I think. MyMonitor has a few more tricks up it's sleeve, like the ability to write out the monitored statements to a file and to do some simple timing on statements. All in all, this is a simple tool that can be real helpful at times. Many times less powerful than, say, MySQL Enterprise Monitor or MONYog, but still useful.

If you are wondering: No GUI needed. What IS needed though is ncurses. And Windows support? Nope, but have a look at my MyQuery project instead, it has many more features, including staement monitoring.

Download mymonitor from Sourceforge here: http://sourceforge.net/projects/mymonitor/


Sunday, November 14, 2010

MySQL configuration management using Chef

Here at Recorded Future we use Chef for Configuration Management, and I am a complete newcomer to this. Chef is, like Puppet, built in Ruby, Ruby sure seems popular for Configuration Management things.

As for the so called "recipes", which is what Chef names a things used for installing some software package, such as MySQL, is really not complete in the case of MySQL at least, but that is really not a big deal. I actually am beginning to like Chef, there are many good points to it, although I can't escape the feeling that I am spending more time writing Ruby than actually installing stuff and getting MySQL running. But I beleive that this most a result of the fact that we are still a startup and do not yet have that many MySQL servers running.

I my opinion, and I can be pretty opinionated as you know, I think the one big thing with Chef that I do not like is Ruby. I have to admit I really have to work hard to get used the idiosycratic means that this language works in. Mind you, I'm no big fan of scriping languages in general, but I have written fair amounts of PHP, for example is most of my website papablues built by myself in PHP as Joomla modules, components, templates and plugins.

In many ways, I think Ruby started of pretty well, the basics of it are promissing. But the way they have complicated Object Orientation seems to me to be not just an overkill, but an overkill without really good uses. There are so many different means and syntaxes for the same thing that you just get confused. And then you do what I do. Learn one tecnique and use that and nuthin' else (which means the code will be hard to read for someone using some other tecnique: Tough sh*t!)

That said, all this may be just because I'm not used to it. I used to hate PHP also, but sort of got used to it (I still don't like it that much, to be frank). But my one and only favourite programming language remains C. Which you can learn by reading the thin "The C programming language", not 1000 pages of "Idiots introduction to the basics of PHP" or some similar utterly useless publication.

All this though, Chef is beginning to be fun, and I will share some more experiences with you regarding the Chef work we are doing here at recorded Future. Also, I was thinking that we should share some experiences with other MySQL Chefs, maybe as a BoF session at the upcoming MySQL UC (It can't be a full blown talk, the deadline for CFP is over).

Cheers for now
Who sticks to his old way of working, even with Chef: Code should be commented! And yes, I am also aware I am old fashioned.... And by the way, Chef documentation leaces a bit to be desired (and that way todays gross understatement).

Wednesday, November 10, 2010

MySQL 5.5.7 - Can we trust it being RC, or?

I just saw that MySQL 5.5.7 RC had been released, and reading the releasenotes made me more than a fair bit suspicious. In some kind of general agreement on what constitutes a "beta" release, this is when the software has reached a level of maturity when no more major features are to be introduced. MySQL (and many others) has broken that rule at times, and the rule is not enforced or something.

What constitutes an RC release though, in my mind, but I really want to know what you think, is software that is really 100% feature complete. There may be, but hopefully there aren't, even any major bugs to iron out. In short, it is "A Candidate to Release", and as close to GA as you can get. I have not seen this rule broken much, really.

With MySQL 5.5.7, this is an rc, as was the previous release, 5.5.6, and this time there is a really major feature introduced between these two release, pluggable authentication. And before I go on, let me stress that this feature per se is not what I am questioning here, quite the opposite, this is a very useful feature.

What I am questioning though is:
  • Why is MySQL introducing new major features in an RC release, even in between 2 RC releases? This means, if I am not mistaken, that this very important feature (authentication deal with security, mind you), that it might go live (the C is for Candiate) without having been beta tested?
  • What made MySQL 5.5.6 a Release Candidate? What I mean here is that if we assume that this major new features was conceived, written and performed in just a few weeks between 5.5.6 and 5.5.7, MySQL knew that 5.5.6 wasn't feature complete, and hence in no way a release candidate (C is for Candidate, if you had forgotten that little fact).
  • Why does MySQL insist on having major important changes to the security setup be tested the least in the server before GA? Fact is, what MySQL is telling us here is that there may not be any testing at all (as 5.7.7 is RC (where C is for Candidate) which means is could possibly be GA.
My conclusion is that MySQL 5.5 is not to be treated as GA (is MySQL even considering a GA release as feature complete, or are they about to introduce more features again in that line of releases) just not yet. Which is a problem for me personally I I just recommended us to go with 5.5.6, hey, it's RC (you know what the C in RC is for now, right?), and that it would be as close to solid for production use as you can get.

To be clear, I will stick with 5.5.6 for now. Not 5.7.7 or even 5.5 GA for a while, until I have tested that pluggable authentication is secure and solid for production use. I really want 5.5, and I am not alone, so I do not understand why MySQL had to screw around with this. I do understand why pluggable authentication should go into MySQL, for sure, but not in 5.5 or at least not in the midst of a RC cycle.

Who is not saying he will now change to Postgres. Nope, I will not not act stupid. And watch me run with 5.5.6 for a while yet, I will not be alpha testing MySQL security in a live production site, no way, José.