mysqlguy.net

MySQL/Drizzle as a Cloud Storage access layer

Submitted by jay on April 11, 2011 - 6:32pm

 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.

mysql as a cloud access layer.jpg

Trackback URL for this post:

http://mysqlguy.net/trackback/35

AFAIU, the MySQL cluster

AFAIU, the MySQL cluster (ndb) architecture is similar (in rough terms) to what you describe, it is a storage engine on top of a distributed hash table. Along these lines, there also exists a storage engine for AWS S3 (http://fallenpegasus.com/code/mysql-awss3/). Also, incidentally, some time ago I wrote a storage engine on top of a transactional, distributed key-value store. Overall, it works nicely, but there is room for improvement in the SE API. For example, the API for (distributed) push­down joins and BKA will allow quite a few optimizations for network based storage engines. There is also quite a few things that need to be hacked around for distributed engines, such as distributed DDL (each server instance has a FRM on disk), which currently is a limitation imposed by the core MySQL server

Thanks for sharing these

Thanks for sharing these thoughts! Looks like we're on the right track with our two most recent announcements then (albeit these approaches don't use the storage engine API):

NoSQL to InnoDB with memcached

Scalable, persistent, HA NoSQL Memcache storage using MySQL Cluster

 Hi Jay, great post, I wrote

 Hi Jay, great post,

I wrote a blog about NoSQL and how it maps back into SQL, pretty much with similar conclusion you had:

http://blog.xeround.com/2010/12/nosql-the-sequel

 

Hi Jay, I wonder if you had a

Hi Jay, I wonder if you had a chance to visit Xerounds booth or sessions during the conference, because, that's exactly what we do, we use MySQL front ends and a distributed data store as a storage engine implementation, creating a highly scalable and robust database with this approach.

Feel free to try our service

Avi

Warning!

Comment abuse is not tolerated on this site, besides all the comments are moderated, so don't bother posting comments that are not on topic, only for increasing the SEO of your site, or are outright spam.  If you've got something intelligent to contribute, by all means, post a link to your blog.  

About Me

Jay Janssen
Yahoo!, Inc.
jayj at yahoo dash inc dot com
MySQL
High Availability
Global Load Balancing
Failover
View Jay Janssen on Twitter  View Jay Janssen's LinkedIn profile View Jay Janssen's Facebook profile