wormhole Storage Engine

October 1st, 2007

MySQL has a quite unique feature: the pluggable storage engine interface. Thanks to it MySQL supports different Storage Engines for different needs: MyISAM is perfect for heavy read, InnoDB for transational data and blackhole .... for sending data to /dev/null.

Thanks to some advance science we now have a wormhole Storage Engine. While the blackhole can only be written to but nothing ever comes back, the wormhole is the inverse of it. You get data from another galaxy, but all writes might have no effect on your side. Sounds useful ?

What's the deal ? The wormhole SE is a lua-based storage engine. The data is "stored" in a script-language. ... Ok, this explaination doesn't help very much. Let's take a look at an example:

The CREATE TABLE for a wormhole table is pretty simple. No magic:

root@127.0.0.1:test> show create table t1\G
CREATE TABLE `t1` (
  `id` int(11) DEFAULT NULL
) ENGINE=WORMHOLE DEFAULT CHARSET=latin1

You can select from it as usual:

root@127.0.0.1:test> select * from t1;
+------+
| id   |
+------+
|    5 | 
|    7 | 
|    3 | 
|   92 | 
+------+
4 rows in set (0.13 sec)

root@127.0.0.1:test> select * from t1;
+------+
| id   |
+------+
|    5 | 
|    7 | 
|    3 | 
|   20 | 
+------+
4 rows in set (0.11 sec)

But hey, we never wrote to it, but the data is changing. Let's take a look at the storage:

$ cd test/
$ cat t1.lua
return { { 5 }, { 7 }, { 3 }, { math.random(1, 100)} }

A lua-script, the last field is generated at run-time. Now that this works, what is it good for ?

Coming up

For me this is more an experiment on how to write a storage engine. So, we will see some more stuff getting implemented other than just table-scans. Next is table-discovery to generate the .frm at runtime from the lua-script.

Posted by jan Filed in mysql

8 Responses to “wormhole Storage Engine”

  1. Giuseppe Maxia Says:
    October 1st, 2007 at 09:24 PM I saw this coming ... :)
  2. Roland Bouman Says:
    October 1st, 2007 at 09:31 PM Useful? well, - sequences - date dimensions - custom uuid schemes ....
  3. Monty Taylor Says:
    October 2nd, 2007 at 02:40 AM a lua storage engine. I love it. I'm going to have to write a Python storage engine now.
  4. Xaprb Says:
    October 2nd, 2007 at 03:03 AM I keep thinking about writing a Hitchhiker storage engine. No matter what query you issue against it, it would just tell you "42." Not sure how useful it would be, though :)
  5. Ivan Garavito Says:
    October 2nd, 2007 at 01:56 PM This is really great, because this can be used as a wrapper (I think that's the idea of all this). I mean these cases: * Read JSON data from a local/remote website. * Read game stats from some(s) server(s). * Implement true real time replication or synchronization between 2, 3, ...n servers by propagating changes (like making a broadcast). * Implement a high available data server, just by having a cluster of data nodes, obiously using the wormhole storage engine. I guess these are useful, but not limited to. This SE could be implemented for AI AND distributed processing AND distributed storage AND etc purposes. Just think about the posibilities =D! Sorry for my bad english =P
  6. Dossy Shiobara Says:
    October 2nd, 2007 at 08:58 PM As cute as "wormhole" is for a name, can this please be renamed the "lua" storage engine instead? Cute and confusing vs. intuitive ... I'd hope intuitive wins out every time.
  7. Donal Lynch Says:
    October 3rd, 2007 at 12:14 AM Is it possible to declare a table where instead of the full result set being generated, each row is lazily evaluated when the query executor asks the storage engine for it? If the problem of infinite loops can be worked around, this would allow problems that would otherwise require programmatic loops to be solved declaratively. For example, to print out the first N primes: "select * from primes limit 100", or to crack a password: "select string from alphanumeric_strings where password(string)='...'"?
  8. Paul M Says:
    October 7th, 2007 at 06:01 AM Very nice, like Monty I immediately thought of all the other scripting languages out there which could use the same principle. Nice ideas, Ivan, your english is fine. If you take the functional, bottom-up approach to this, you could have these scripting engines stacked on each other, so one feeds into another. With my DBA hat on, it is going need some metrics around performance, even maybe some smarts to retry if the connection(if you are going down the REST/web path). Have Fun Paul

Sorry, comments are closed for this article.