OK, you have waited long enough, here's my take on NoSQL

I have to admit I'm getting old, and I am now scaringly close to being a sad old git, and not only that, I'm a database guy also, and I have worked with SQL based relational databases for more than 20 years now.

So considering my age and heritage, I really should just dispose the NoSQL movement as something for those young kids with earrings and a baseball cap (and to add insult to injury, the cap is worn backwards) and that any serious database dude like myself, with my loads of experience (like the invaluable experience of having run Oracle on a Wang word-processor. Real valuable stuff I tell you). But no, you will not hear me do that. But also, you will not hear me say that NoSQL key-value stores will replace all SQL databases within 5 years (If I worked for an analyst and was paid dearly to say things like that, I might have, though. Any takers?).

My take is actually quite simple. The strength of the relational model is that it is inredibly generic. The lack of a specific order and hierarchy makes it even more generic. I think few people would argue that more or less all applications served by NoSQL could just as well be served by a SQL database, if it wasn't for two things:

• Scalability - The lack of the strict consistency rules of RDBMS heritage in most NoSQL implementations makes them much more scalable. The very nature of most NoSQL stores is distributed, and the lack of strict distributed consistency makes this distribution scalable beyond what is usually possible with an RDBMS, given the same platform etc.
• Performance - This is largely due to the above, i.e. a NoSQL store being more scalable makes it easier to cram more performance out of it.

Now, with all this in mind, am I saying that NoSQL has all the advantages of an RDBMS, but with much better scalability? Nope, no way, José.

The strict consistency requirements of an RDBMS is also an advantage. It's not so that, if I understand them correctly, the propoents for NoSQL stores thinks that consistency is bad, it's just that they don't want to pay the price in terms of performance for it. And to be frank, although in many cases data inconsistency is acceptable, it still has to be controlled, an uncontrolled consistency, i.e. you don't know how inconsistent your store is and in what way or anything, is not something we want. So even a NoSQL store is limited.

So it all comes down to performance then. We sacrifice consistency to gain performance through scalability. Right? If you agree to that, then I think NoSQL is not a long term solution. It's not that I am saying that "NoSQL is for kids, real databases needs SQL", that was the argument against SQL based databases in the 1980's largely, where Hierarchical databases still ruled, and SQL just had a too big overhead, or so it was thought. The differerence here is that SQL had higher functionality than the competing technologies of the 1980's, but not enough performance in many cases. But performance is bound to go up. All the time. And for much less money. At least for a while to come. Look at virtualization. I've been a proponent for that for quite a while, and just a few years back, the argument against it was that "performance sux". Well, compared to raw iron maybe it did, but that wasn't the point. The point was, did I get enough performance? And in many cases you did, with an environment that was a lot easier to manage and at a low cost.

What this means to me is that there is a place for NoSQL stores right now, where the performance and size requiements are really high, and where one is willing to compromize consistency. But a technology that limits functionality, features and ease-of-use at the price of performance will continue to be a niche technology. But that doesn't mean it's useless or anything, quite the opposite, I'm a pragmatist at heart, and whatever works, works. But if I had the choise of storing my data in consistent or in-consistent state, and if both solutions provided enough performance for my needs, I'd go consistent any time.

And then there is one more thing. The scalability of the NoSQL stores is largely due to it's distributed nature. And there are arguments out there that says that you cannot create a consistent, distributed, scalable datastore. I think you can, I'm convinced of it actually. There may be other compromises needed to achieve that, but that it can be done I am sure.

/Karlsson

More fun with the MySQL Audit Plugin API

The Audit API has more uses that you may think! When a statement is executed in the server, the notification function in this API will be called, and we can use that do some interesting things!

Like: Ever wanted to know what the most executed query in your running system is? I mean the information is in there somewhere, right, it's just q question of how to get at it? And frankly, I don't think the queries:
SELECT * FROM user_data WHERE user_id = 57;
and
SELECT * FROM user_data WHERE user_id = 113;
Should count as different queries? I mean, hey, the issue is that the application may be issuing the same query too many times, with different arguments mostly, but still the query:
SELECT * FROM user_data WHERE user_id = <somevalue>

Is executed too often? And if so, how can I figure it out? Well, using MySQL 5.5 with a combination of the Audit Plugin API and the INFORMATION_SCHEMA API, you can do this. The principle is simple:

The logging of queries
This is handled by the notification function in the Audit API. In the plugin, we have a an array of queries, and for every query we see, we "normalize it", in the sense that all literals are replaced by question marks, i.e. the query above would be represented as:
SELECT * FROM user_data WHERE user_id = ?
Then we look for this query among the queries I already know about. If the query is found, I just increment teh execution cound for it, and if it is not found, I add it. I keep track of X number of queries, and I log the time each query is added, so when I add a new query, and there already are X queries logged, I get rid of the one that was executed longest ago. And LRU list in short.
In the sample I code I prove evetually, I also log some other data, but this is the basics. And the list is stored in memory by the way.

Seeing the latest queries
For viewing the queries, I use an INFORMATION_SCHEMA plugin, that is part of the same plugin library as the one above. although this is a different type of plugin. This is again pretty simple. The plugin will expose an INFORMATION_SCHEMA table that contains the list of the queries above, and when I select from that table, I materialize the list as a table. As this will then look like any normal table, you can use just about any SQL operation on it, like ordering, filtering specific queries etc. And all this with a simple SELECT!

The issues
Will this affect performance? Yes, sure, but I don't really know to what extent right now, I havent gotten around to test that. And then there is one more issue: You need MySQL 5.5 m3 or above, which currently means you need to get it from Launchpad, there are still (March 29) only m2 binaries to be found at http://dev.mysql.com.

Example code
Now it's time for some sample code. Lets first have a look at how it works. There is an information_schema table introduced by the plugin called my_is_proclist, and this is the one we are interested in. I have a table in the test database that I issue queries on. And it looks like this then:
MySQL [test]> select last_executed, num_executes, statement from information_schema.my_is_proclist where statement like '%from t1%';
+---------------------+--------------+-------------------------------+
| last_executed | num_executes | statement |
+---------------------+--------------+-------------------------------+
| 2010-02-28 15:12:26 | 4 | select * from t1 |
| 2010-02-28 15:34:07 | 6 | select * from t1 where c1 < ? |
+---------------------+--------------+-------------------------------+
2 rows in set (0.00 sec)

MySQL [test]> select * from t1 where c1 <>
+------+------+
| c1 | c2 |
+------+------+
| 1 | 16 |
| 2 | 16 |
| 3 | 16 |
+------+------+
3 rows in set (0.00 sec)

MySQL [test]> select last_executed, num_executes, statement from information_schema.my_is_proclist where statement like '%from t1%';
+---------------------+--------------+-------------------------------+
| last_executed | num_executes | statement |
+---------------------+--------------+-------------------------------+
| 2010-02-28 15:12:26 | 4 | select * from t1 |
| 2010-02-28 15:34:12 | 7 | select * from t1 where c1 < ? |
+---------------------+--------------+-------------------------------+
2 rows in set (0.00 sec)

As you can see, the counter goes of what I execute the query in question. Nothing complicated, and as you can see, I can do all sorts of filtering on the INFORMATION_SCHEMA table.

Installing the plugin
The plugins are contained within one library. I have the sourcecode and a simple makefile downloadable here: http://www.papablues.com/src/my_proclist.tar.gz

Modify the supplied Makefile appropriately and run:
make
make install
Note that you must have rights to write to the MySQL Server plugin directory for the install to work! Once this is done, you must tell the MySQL server about the plugins:
install plugin my_proclist soname 'my_proclist.so';
install plugin my_is_proclist soname 'my_proclist.so';
And that's it! Note that there is also a status variable that shows how many statement I currently keep track of. Currently, the max number of statement I track is 30, but there is a simple
#define NUM_STMTS 30
in my_proclist.cc that you may edit to fix that.

/Karlsson

More on the MySQL Audit Plugin interface

I will write some more on this interface eventually, following up my previous MySQL Audit API post, and will show some ideas and hopefully push some interesting code (I have ideas!). But note that the API so far isn't well documented (only source so far), but there is work underway to fix this by the friendly MySQL docs team.
Already I have realized that Audit events are different than I thought. The source of the event is currentlyt either from inside the parser code or from the general log code. The events I got looked like general log events, so I just imaginged this was the source of what I saw, and I never relaized that there was another possible source, the parser. Actually, when the general log is not on, the parser events is all you get, but as I have shown, this is usually good enough. For the log events to be received, you still have to have the general log on. In practice, this doesn't seem to be much of a difference, but I'll keep an eye on it once the documentation is in place, and if there is a use for having Audit general log events, without having the general log per-se on, then I will create a worklog for that (along the lines of having the general_log variables have 3 values (ON, AUDIT_ONLY and OFF) or something like that.

/Karlsson

The MySQL Audit plugin API

MySQl 5.5 is getting more and more solid, and one thing recently (at least I think so) pushed is the Audit plugin. Currently, this is only in the source repository (I tried from 5.5.4-m3). This prugin is actually quite useful, more useful than you might expect. Any event that would cause a write to the general query log will send a notification to a function in this API, and note that this will happen even if the general query log is enabled or not.

The stuff that gets passed to the notification function is a struct with similar data that gets written to an entry of the general query log, but don't let this limit you, also passed is a pointer to the THD class for the thread that caused the notification. And if you haven't looked a lot at the MySQL sourcecode, note that the THD class has MANY members, and contains all sorts of information.

If you wanted to create your own general log, that would allow filtering on, say, specific nodes, statements, users or something, this is where to hook into for that. Another advantage over using the general query log, is that you can log a lot more information, by virtue of the THD being passed.

Currently, there is a lack of documentation for the AUDIT plugin API, but you can figure it out from the null_audit plugin that is part of the 5.5.4-m3 source. The API is pretty simple, and only three functions are usually needed, one called when initializing the plugin, one to handle the event notification, and one that is called after when deinitalizing the plugin. A complete plugin that does this, that is based on the null_audit one, but which is written to support C++, as I want to use the THD which is a C++ class, this is a requirement if you want to get at the THD (unless you are cheating that is).

To begin with, we need some headers and stuff:
#define MYSQL_SERVER
#include
#include <../sql/mysql_priv.h>
#include
#include
#include

#if !defined(__attribute__) && (defined(__cplusplus) || !defined(__GNUC__) || __GNUC__ == 2 && __GNUC_MINOR__ <>
#define __attribute__(A)
#endif

static FILE *log_fp;
static volatile int localhost_commands;

Then we need to init, deinit and notification functions:
static int my_general_log_plugin_init(void *arg __attribute__((unused)))
{
log_fp = NULL;
localhost_commands = 0;
return(0);
}


static int my_general_log_plugin_deinit(void *arg __attribute__((unused)))
{
fclose(log_fp);
return(0);
}


static void my_general_log_notify(MYSQL_THD thd, const struct mysql_event *event)
{
mysql_event_general *pEvent;

/* Open the log file, if not already done. */
if(log_fp == NULL)
log_fp = fopen("/tmp/my_general_log.log", "a");

/* Only log if on localhost. */
if(thd->net.vio->localhost && event->event_class == MYSQL_AUDIT_GENERAL_CLASS
&& log_fp != NULL)
{
pEvent = (mysql_event_general *) event;

localhost_commands++;
if(pEvent->general_query != NULL && *(pEvent->general_query) != '\0')
{
fprintf(log_fp, "%s;\n\n", pEvent->general_query);
fflush(log_fp);
}
}
}

As you see, this is kept real simple, so as not to confuse things. But it is rather powerful, as I have both the event and the THD data available. Before I compile the above, I also need to add the structs that control the plugin itself:
static struct st_mysql_audit my_general_log_descriptor=
{
MYSQL_AUDIT_INTERFACE_VERSION,
NULL,
my_general_log_notify,
{(unsigned long) -1}
};


static struct st_mysql_show_var my_general_log_status[]=
{
{"my_general_log_commands", (char *) &localhost_commands, (enum_mysql_show_type) SHOW_INT},
{0,0,(enum_mysql_show_type) 0}
};


mysql_declare_plugin(my_general_log)
{
MYSQL_AUDIT_PLUGIN,
&my_general_log_descriptor,
"my_general_log",
"Anders Karlsson",
"A general log that will log localhost access only",
PLUGIN_LICENSE_GPL,
my_general_log_plugin_init,
my_general_log_plugin_deinit,
0x0001,
my_general_log_status,
NULL,
NULL
}
mysql_declare_plugin_end;

The st_mysql_show_var struct provides a simple status value, that is displayed when you issue a SHOW STATUS command.

Beyond the above, you need a Makefile. I wrote mine myself, to keep things simple, and I also use no fancy macros, just the simple stuff:
MYSQL_BASE=../..
MYSQL_INST=/usr/local/mysql5.5/lib/mysql/plugin
CFLAGS=-D_HAVE_CONFIG_H -DMYSQL_DYNAMIC_PLUGIN -fPIC
all: my_general_log.so

my_general_log.o: my_general_log.cc
g++ $(CFLAGS) -I$(MYSQL_BASE)/include -I$(MYSQL_BASE)/regex -I$(MYSQL_BASE)/sql -c my_general_log.cc

my_general_log.so: my_general_log.o
g++ -shared my_general_log.o -o my_general_log.so

install: my_general_log.so
cp my_general_log.so $(MYSQL_INST)/my_general_log.so

If you are to try this yourself, you will need to modify the names and the paths and stuff, but I think you get the point and then run make and make install. The last thing to do is to enable the plugin. Just enter the mysql commandline client as root and run:
install plugin my_general_log soname 'my_general_log.so';

As foir the MySQL base source for this, either get 5.5.4-m3 from launchpad, or wait a few days and get it from http://dev.mysql.com when it is available there.

Hey, even being audited can be fun!

/Karlsson