(This is the first in a series of blogs written while I was trying to get home from Santa Clara. Posting them now as I'm back home and online.)

These MySQL conferences certainly get more interesting every year. Last year we got acquired and I went home thinking I need to start looking for a new job. This year Eyjafjallajõkull erupted and I had trouble getting home at all!

Pre-conference there was also the suspense to see whether the conference will take place at all, but O'Reilly and Colin pulled it together perfectly! It seems most if not all Europeans will eventually find their ways home, so all in all, all is well that ends well.

To recount everything we've learned, I will step backwards in time through the highlights as I saw them.

The extra days we stayed in Santa Clara opened up a lot of opportunities. I eventually skipped DrupalCon in favor of catching up on normal work, but we did visit the Hadoop User Group meeting on Wednesday, which I will tell about in this post. During the weekend it was also nice to relax and take a bike ride over Golden Gate to Sausalito. Both the lunch and the ride was certainly San Fransisco at its finest.

How NoSQL is different and similar to what we do...

Sarah Sproehnle from MySQL training now works at Cloudera on Hadoop. So I picked up on Facebook that she was involved in a Hadoop User Group (HUG) meeting at the Yahoo! Campus in Sunnyvale. Being still stuck here, me and Timour decided to go and embrace the NoSQL movement. In my Sun/MySQL times I had spent a day reading up on Hadoop, HBase and others, so I could almost follow which is which. Timour had to read up when we got home, and today is fully fluent in this too :-)

John Sichi from Facebook reported on his work to make Hive use Hadoop as a backend. From our point of view the most interesting observation was that a lot of what he did addressed exactly the same issues that we work on in MariaDB/MySQL and storage engines on a daily basis. At the end he mentioned "for future work" that one should be able to push filter conditions down to Hadoop - this amused us (up to a LOL level) since it is the very thing Timour (and the team) is also working on in MariaDB. There is a French saying: The more things change, the more they stay the same.

We then had interesting followup discussions with Ted Dunning, CTO at Deepdyve. The discussion was so interesting, we decided to continue it over lunch today. Here I want to write down what we learned in these discussions, and opportunities for cross pollination.

Note: I should at this point apologize for any funny errors regarding Hadoop, Hive or anything else NoSQL related, since my knowledge of these technologies is still mostly based on reading Wikipedia.

Before...

...we came here, I had (ghost)written this text for Monty's keynote. It is on purpose in a controversial "Monty tone", taking jabs at this NoSQL fad. It was eventually thrown out and not used, but reflects well what me, Monty and many others may think:

NoSQL and other NoSENSE trends

When you look at the reasons why people are interested in NoSQL, none of them are actually related to using or not using SQL.

You say that you want an efficient key-value store. Well guess what, MySQL was exactly that before 1995. SQL was only added as a layer on top of the ISAM engine to enable easy access to the data from various tools and also scripting languages like Perl. Later ODBC support was added so my customers could use the most popular Business Intelligence tool on the planet - this is Microsoft Excel - to connect to their database. But none of this has anything to do with how the database works internally. If we need replication that is more efficient or simpler to manage, maybe we need to catch up there. If we need more performance, and we always do, we have to deliver that. But none of this has anything to do with SQL.

In fact, this discussion again goes back to the storage engines, that are unique in the MySQL ecosystem. Most of the things I hear mentioned as reasons to go NoSQL already exist in MySQL, in the form of MySQL Cluster.

Do you want to transparently scale-out by adding nodes and automatically re-partition? It's there...

Ability to access the entire database from any node? It's there...

Do you want to forget SQL and do simple key-value operations? You can...

There was even a memcached compliant API published for MySQL Cluster recently.

But I'm just saying, all of this can be done in MySQL.

And if after this you still want to try NoSQL, then I have only one thing to say to you: You can run, but you can't hide! With the storage engine interface in MySQL, we can make your NoSQL database SQL compliant. Drizzle has already announced they will support using Cassandra as a storage engine. We can do the same for any NoSQL technology, just like we've already done for MySQL Cluster and Berkeley DB a long time ago.

After...

...talking to Ted and others, here's what we've learned:

It is true to a degree NoSQL systems simply tackle a set of requirements that MySQL could have solved, but didn't. Flexible cloud-style scale-out, more reliable and performant replication, rolling restarts, online repartitioning... Basically, MySQL Cluster does all of these things, except that it doesn't target the kind of batch workloads that these guys are looking for, but it could have been done in MySQL with another engine similar to NDB Cluster.

JOINs are so out...

Previously, I had thought that the lack of SQL in the NoSQL systems was mostly just due to the fact that they just didn't have time to implement SQL yet. Ted made a good argument that this is not the case. He made two points:

1) A language that is not so high level as SQL can be considered an advantage. For a long running job in a large cluster, it can actually be seen as an advantage that the developer or dba is slightly more aware of what the query will do, rather than having to second guess what an optimizer might do. More importantly, with SQL RDBMS systems it often happens that the optimizer may suddenly decide to optimize the exact same query from yesterday, in a completely different way - for example if the table has grown over night. If the original job was running 5 hours, the new query plan may run for days. What's worse it would take you 5 hours even to notice that something is wrong. So a lower abstraction level is considered a win. Adn by the way, PIG is easier to learn than SQL (says he).

2) Users of NoSQL systems also often do operations that are not in your standard relational algebra. As an example, the co-group operation in Hive. The co-group is almost like a join, in that you can ask to get a set of data related by key (primary key, but really they just say key since there is no secondary key), but you don't actually join the rows together, you just return "bags". In other words, the application will get a list of items from "table1" and "table2" that can be found with a certain key (or list of keys), but there is no need to actually join those 2 different bags (or lists) together.

After thinking about this for a while, I feel that I sympatize with the first argument, but don't yet buy the second argument. So what Ted says is unnecessary is this:

SELECT * FROM t1 
 LEFT OUTER JOIN t2 ON t1.key=t2.key 
 LEFT OUTER JOIN t3 ON t1.key=t3.key 
 WHERE t1.key=?;

...because the effort to actually join t1,t2,t3 row by row is unneccessary, he just wants the result set "in a bag" (essentially 3 separate lists, or hashmaps, if I got it right?).

It seems to me, what Ted wants can still be expressed in SQL as a UNION ALL:

SELECT key, blob, "bag1" as bag_id FROM t1 WHERE t1.key=?
UNION ALL
SELECT key, blob, "bag2" as bag_id FROM t2 WHERE t2.key=?
UNION ALL
SELECT key, blob, "bag3" as bag_id FROM t3 WHERE t3.key=?;

This would be more efficient as no joining occurs, and the application can find the "co-groups" by looking at the value of the artificial "bag_id" value. (Note that MySQL will actually collect the above result set into a temporary table anyway, even if this is completely unnecessary. A MariaDB worklog task exists to eliminate this to really make this as performant as Ted would want.)

Now, I agree that this looks overly complex and maybe even hacky. My main purpose was just to illustrate to myself "so what would this mean in good ole' SQL?" and it seems what Ted wants can still be expressed in SQL.

So maybe PIG (as the "Almost SQL" query language is called") has a nice new syntax to do this in a nicer way and more importantly, some convenient way to return the results. It is true that SQL is very much geared towards selecting 2 dimensional tables, and returning 2 dimensional results, rather than bags or other structures.

MySQL inside NoSQL!

Until now I had been mostly preoccupied with the idea of how to put SQL back in front of NoSQL. However, Ted told about someone who had done the opposite: Run MySQL on each of the local Hadoop nodes.

I often think of Hadoop as a clustered database, with Map Reduce somewhere in there. It turns out, you can think of Hadoop also as a way to run distributed jobs. The jobs just happen to be about manipulating data. (So from this point of view Hadoop is more like Gearman than like MySQL.) Usually the data is stored in HDFS (Hadoop Filesystem) or just local disk, but it turns out you may just as well put the data in MySQL running on each node. The results from this excercise was that the jobs performed better, since data retrieval from MySQL is efficient, and the jobs can ask MySQL to manipulate data in more complex ways than what you can do with basically a flat file.

I noted that this seems to be also what Nokia has done in using MySQL as the persistance engine in their Voldemort cluster, as presented last week at the MySQL conference. One immediate benefit at least for Nokia was that you can let Voldemort handle all of your replication, repartitioning, rejoining cluster and other issues, and forget about MySQL replication (and in theory, even backups).