Monday August 28, 2006

The MySQL query cache has rarely been of much use to me since it's a pretty much just an optimization for read-heavy data. Furthermore, if you have a pool of query hosts (e.g. you're using MySQL replication to provide a pool of slaves to select from), each with its own query cache in a local silo, there's no "network effect" of benefitting from a shared cache. MySQL's heap tables are a neat trick for keeping tabular data in RAM but they don't work well for large data sets and suffer from the same siloization as the query cache. The standard solution for this case is to use memcached as an object cache. The elevator pitch for memcached: it's a thin distributed hash table in local RAM stores accessible by a very lightweight network protocol and bereft of the featuritus that might make it slow; response times for reads ands writes to memcached data stores typical clock in at single digits of milliseconds.

RDBMS-based caches are often a glorified hash table; a primary key'd column and value column. Using an RDBMS as a cache works but it's kinda overkill; you're not using the "R" in RDBMS. Anyway, transacting with a disk based storage engine that's concerned with ACID bookkeeping isn't an efficient cache. MySQL has the peculiar property of supporting pluggable storage backends. MyISAM, InnoDB and HEAP backends are the most commonly used ones. Today, Brian Aker (of Slashdot and MySQL AB fame) announced his first cut release of his memcache_engine backend.

Here's Brian's example usage:

mysql>  INSTALL PLUGIN memcache SONAME 'libmemcache_engine.so' ; create table foo1 (k varchar(128) NOT NULL, val blob, primary key(k)) ENGINE=memcache CONNECTION='localhost:6666';

mysql> insert into foo1 VALUES ("mine", "This is my dog");
Query OK, 1 row affected (0.01 sec)

mysql> select * from foo1 WHERE k="mine";
+------+----------------+
| k    | val            |
+------+----------------+
| mine | This is my dog |
+------+----------------+
1 row in set (0.01 sec)

mysql> delete  from foo1 WHERE k="mine";
Query OK, 1 row affected (0.00 sec)

mysql> select * from foo1 WHERE k="mine";
Empty set (0.01 sec)

Brian's release is labelled a pre-alpha, some limitations apply, your milage my vary, prices do not include taxes, customs or agriculture inspection fees.

What works

• SELECT, UPDATE, DELETE, INSERT
• INSERT into foo SELECT ...

What doesn't work

• Probably ORDER BY operations
• REPLACE (I think)
• IN ()
• NULL
• multiple memcache servers (this would be cake though to add)
• table namespace, right now it treats the entire server as one big namespace

mysql   memcached   postgresql