Call for action: Geo-spatial in MySQL needs your support

At the MySQL UC, I went to a talk on MySQL for Geospatial applications, as for those of who know me and have read my blog before, you know that this is a hot topic for me! I will try to drive this a bit more, more specifically, getting the "real" MySQL functions for Geospatial applications, that currently only exists in a MySQL Preview release, and have them firmly in the MySQL GA version. This will happen eventually, and we need to make sure sure it happens. The current preview release is maintained and updated, so it's not that far behind the main server, and the GIS functions themselves are stable enough.

If you have an interest in this, let me know. I will try to organize some kind of community around this somehow, at least that is my plan, but first, I want to know who you are. If you have a commercial project, then that is even better, as a commercial project might help get even more drive here. But I am interested in any project. And if you have tools, ideas, code or requirements, let me know and I'll try to organize.

Drop me an email at anders@mysql.com or just comment here and watch this space for more info.

/Karlsson

A User Conference non-announcement

WARNING! Certain amount of irony to follow!Do not read while driving or operating heavy machinery

Just so you know, those at and those not at the MySQL User Conference, no, MySQL / Sun / Oracle hasn't been acquired. It feels real strange to have been not-acquired for such a long time, about a year now, but I guess I have to learn to live with it. And the User Conference isn't over yet, so who knows what will happen. I mean, not MySQL has taken control of Oracle, Sun, InnoDB, TimesTen and many other application, but we still have a few more companies to get control over.

/Karlsson
Part of the MySQL team conquering the world

SQL Monitoring in the server

Some of you might have seen my post on the MySQL Audit API that is included with MySQL 5.5 m3 and some of you might not have. Well, the code that is part of that is real simple, it's more to show a concept than something you would want to run in your production servers. But it was an attempt to check if there was any interest in something like this. And there was.

So I will develop this further into something much more useful. I have had several comments and suggestions already, and keep those coming, and I am listening. What I will implement for sure in an upcoming first release is four things:

• More configurability and status.
• Means of turning the plugin on and off.
• More data provided through the INFORMATION_SCHEMA table.
• Some means of limiting and controlling what gets monitored, i.e. # of rows inspected, type of query executed etc.

I'm pretty excited about this, there are some cool things you can do with this. Also, I think this would be a great example of a plugin in general, as it will use quite a bit of the plug-in features. And before you ask: Yes, I will test performance and provide some numbers of the overhead of using this puppy.

So watch this space for more info.

Cheers
/Karlsson

NoSQL or not? Database history... Come and discuss with us at the UC...

At the upcoming MySQL UC, I have a BoF on Tuesday at 7 PM on the history of databases. One things that would be interesting to discuss there is NoSQL. Is this the next great wave? If you have read my Blog before, you know that I think this is not the case, although I do see the need and use for NoSQL technologies. But my main argument against it is that sacrificing functionality and general purposeness (if that is a word), is not how progress works. Performance increases in general, without the need to compromise functionality. But I may be wrong, it has happened before (at least I think I have been wrong before, but I'm not really sure).
But in addition to this, I'd like a general debate on database history. SQL has been around for a long time, but there are other technologies out there, most of them still around in some shape or form, at least on mainframes. Network and Hierarchical databases were the first to be somewhat standardized, in the sense that they were not specific to a particular application.

And the object databases, ORDBMS and other things, where are they now and where are they going?

See you at the UC!
/Karlsson

MySQL Audit plugin. Now documented

When I originally wrote the post on the MySQL Audit Plugin API, the API still wasn't documented, instead I got my information from the source and the samples. Just now, the documentation is available here, with some more specific information here.

I have a bug to report on the Plugin API though, which is that a user with root privileges can possibly uninstall the plugin, which will void any auditing. I'm not 100% how this works yet, but I am certain that for any serious use of auditing, you would possibly want the ability to not being able to unload a plugin. I'll get back to you on this.

/Karlsson

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