Just got done with day 1 from the MySQL conf 2011... tutorial day. I decided to attend a session a bit outside of my comfort zone, so I chose MySQL Plugin Development. I haven't written any serious C/C++ for over 10 years, but lately it's been more appealing to me.

Anyway, the session helped me get a sense of what is necessary to write a mysql plugin, especially storage engines. The example used in the class was writing a storage engine that used Tokyo Cabinet to store the data. The more I saw of the example storage engine, the more I thought about how it was really just writing a set of functions that allowed MySQL's SQL parser to do all of its magic against the dataset. The functions by themselves aren't all that complicated (though definitely it seemed like some of the indexing functions get a bit tricky).

I suppose this shouldn't be a surprise. It's not like the storage engine API is a closely guarded secret or anything. However, the thing that struck me (which maybe also isn't much of a surprise) is how easy it would be to write storage engines that interface with some massively scalable No-SQL type key-value store.

The negative to using NoSQL, IMO, has always been that once you need to do more complicated queries against your dataset, you're mostly stuck implementing your own join functions and moving a lot of that complexity up into the application layer.

But, if you could take the best of a NoSQL solution (i.e., just throwing boxes at the scalability problem without much other hassle), and easily add the power of SQL to it, that'd sort of be the holy grail, wouldn't it? Imagine an architecture where instead of using Webservices for everything between the frontends and the datastore, you used MySQL or Drizzle nodes instead? But those MySQL/Drizzle daemons wouldn't be actually storing anything locally, they would simply be exposing the NoSQL services as SQL tables. It, then, should be easy to combine any number of "tables" from any series of datasources in sort of a federated way. A local MongoDB cluster, for example, could be easily joined with an Amazon SimpleDB setup (*why* you might want to do that is another question).

Now, I'm not laboring under the illusion that this will magically solve every problem. All the storage engines that I've ever seen fall into two categories: Innodb, and the basically useless. Ok, ok, that's not fair. NDB is slick, MyISAM had its day, and there definitely exists other storage engines that are notable. However, it seems like if this was an easy problem and something that really solved some problems with NoSQL, people would be all over this.

One of my hunches going into today was that the Storage Engine API is and has been, insufficient. I know that the MySQL cluster guys basically had to hack the base MySQL code to get NDB to work right. Some of the proprietary storage engine sellers had to do the same thing. That tells me that the API isn't sufficient for people, at least not those who are really trying to knock performance out of the park. To the best of my knowledge, it appears as if the API has been updated in 5.5, but it's not really clear to me what state it's in (i.e., nearly perfect, perfectly insufficient, etc.).

There has to be other issues with using a non-local backing store for data storage: namely that the possibility for it to disappear, have network issues, and generally be flakey must be handled well by MySQL itself. That means good code in whatever Storage Engine code used, but also good error handling within the SQL layer of MySQL itself. How good is that today? No idea.

My basic theory of NoSQL is that nobody would use it if there existed an SQL interface to the data. That's why the invention of technologies like Hadoop immediately were followed with the invention of technologies like HiveDB. However, maybe I'm being naive.

Whatever the case, web services seem somewhat insufficient for a data layer API. I mean, that's what SQL was invented for, wasn't it?

Anyway, this is sort of rambly. Hopefully there's some intelligence in there somewhere.