I am currently playing a lot with the new memcached interface to MySQL. Making MySQL a "NoSQL" solution.
Why should I access the same data via SQL and noSQL protocol?
A simple noSQL protocol like memcached only has lightweight access methods like "set" or "get". No complex parsing, no optimizing, no execution of expressions. And this additional overhead in SQL can be tremendous: I did a set of SELECT statements only based on primary key. Buffer cache is 50% of the table size. With ENGINE=InnoDB it takes 7.6 seconds to read the data. If I switch to BLACKHOLE engine it takes 6.4 seconds! BLACKHOLE has no code in the storage engine. So queries on BLACKHOLE engine create only load on parser and optimizer but nothing in the storage engine. But if I run on InnoDB it adds only 1 second or 15% runtime. Obviously the main part of execution time is outside the storage engine. Erkan found the same behaviour
here. See page 12. The memcached interface accesses the storage engine directly. So it bypasses all the computing in parser, optimizer, expression evaluation and so on. Of course with INSERT statements the part of InnoDB gets bigger as there is more I/O work to do. But nevertheless there can be huge performance gains in using a simpler protocol.
If you only want a glass of milk, don't buy a cow.
Why not use memcached directly? Why that plugin to MySQL?
Memcache is a memory caching but not a persistent datastore. The memcached plugin for MySQL stores the data in an InnoDB table. So you get persistence, nearly unlimited table size and still you can access your data through SQL if you want more complex stuff like COUNT(*) WHERE pkey LIKE ="page:%"; This would not be possible in memcached. But with the memcached plugin you can store data with memcached SET and report on your data with SQL queries.
How can I test it?
Download the labs release from http://labs.mysql.com, do the usual install and then follow the README-innodb_memcached file. It is very simple. Only executing a small sql script and you are ready to test memcached. BUT: If you SET data via telnet to memcached you can retrieve it via GET but you will probably not see the data in the table via SQL. This was a bit confusing to me at least. The secret is the variable daemon_memcached_w_batch_size which is set to 32 by default. The memcached plugin will commit data changes only after 32 SET commands (or INCR, DECR, ...). This batching is good for performance. In fact currently you cannot set daemon_memcached_w_batch_size to values lower than 32. Only bigger is possible. One exception is replication: If you enable replication for the plugin, daemon_memcached_w_batch_size is set to 1. See below.
If you want to see memcached data changes immediately even before a commit, you can set your session to SET TRANSACTION ISOLATION TO READ-UNCOMMITTED;
What about performance?
This is a more complex issue. I will write a separate blog post with some performance discussions. But the summary:
The main parameter is the daemon_memcached_w_batch_size. That will batch 32 statements in memcache protocol into one transaction on InnoDB side.
My first tests showed that (as expected) access via memcached protocoll offers nearly twice the throughput of SQL for writing. This is especially useful if you have only few user connections. If there are many simultaneous connections IO becomes the bottleneck and not the SQL processing.
What about replication and memcached interface?
This works seamlessly. You only have to specify innodb_direct_access_enable_binlog=1. Currently this variable is not (yet?) dynamic. So best to put it into my.cnf/my.ini. The aforementioned daemon_memcached_w_batch_size is set to 1 in this case which means each SET operation is committed separately. As binlog group commit is not implemented in this labs release, this affects performance very badly. But binlog group commit is already in another labs release and this would probably solve a lot of this performance issue.
What if I want more than one value column?
Memcached only knows one value column. That's it. But the plugin to MySQL can help. Look at the configuration table innodb_memcache.config_options:
mysql> SELECT * FROM innodb_memcache.config_options;
+-----------+-------+
| name | value |
+-----------+-------+
| separator | | |
+-----------+-------+
1 row in set (0.00 sec)
This is the magic character to separate different columns. Take another look at my innodb_memcache.containers table:
mysql> SELECT * FROM innodb_memcache.containers;
+------+-----------+----------+-------------+-----------------+-------+------------+--------------------+------------------------+
| name | db_schema | db_table | key_columns | value_columns | flags | cas_column | expire_time_column | unique_idx_name_on_key |
+------+-----------+----------+-------------+-----------------+-------+------------+--------------------+------------------------+
| bbb | test | kvstore | key | value,val2,val3 | flags | cas | expires | PRIMARY |
+------+-----------+----------+-------------+-----------------+-------+------------+--------------------+------------------------+
What is the value to memcached protocol is split into three different columns on the MySQL side. And the separator is the "|" pipe character. Let's try:
[root@olga ~]# telnet localhost 11211
Trying ::1...
Connected to localhost.
Escape character is '^]'.
set mykey 0 0 14
abcd|1234|WXYZ
STORED
get mykey
VALUE mykey 0 14
abcd|1234|WXYZ
END
And in MySQL we find the following data:
mysql> SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT * FROM kvstore WHERE `key`="mykey";
+-------+-------+-------+------+---------+------+------+
| key | value | flags | cas | expires | val2 | val3 |
+-------+-------+-------+------+---------+------+------+
| mykey | abcd | 0 | 1 | 0 | 1234 | WXYZ |
+-------+-------+-------+------+---------+------+------+
1 row in set (0.00 sec)
Don't forget to set transaction isolation level to read-uncommitted. Due to write batching the last SET statements may not be visible otherwise.
What happens with Foreign Keys?
NoSQL usually means that data is unstructured and the knowledge about data is no longer in the database but in the application. So I am not sure if foreign keys are relevant to traditional key value store applications. But if you distribute your value into different columns in the innodb table, it might be interesting. So let's test it:
CREATE TABLE `kvstore` (
`key` varchar(32) NOT NULL DEFAULT '',
`value` varchar(1024) DEFAULT NULL,
`flags` int(11) DEFAULT NULL,
`cas` bigint(20) unsigned DEFAULT NULL,
`expires` int(11) DEFAULT NULL,
`val2` varchar(32) DEFAULT NULL,
`val3` varchar(32) NOT NULL,
PRIMARY KEY (`key`),
KEY `val3` (`val3`),
CONSTRAINT `kvstore_ibfk_1` FOREIGN KEY (`val3`) REFERENCES `refdata` (`val3`)
) ENGINE=InnoDB;
mysql> SELECT * FROM refdata;
+------+---------------------+
| val3 | somefield |
+------+---------------------+
| ABCD | Another good val3 |
| WXYZ | This entry is valid |
+------+---------------------+
2 rows in set (0.00 sec)
So we can add ABCD or WXYZ as the last field via memcached. Let's see what happens:
set mykey3 0 0 14
abcd|1234|ABCD
STORED
get mykey3
VALUE mykey3 0 14
abcd|1234|ABCD
END
set mykey4 0 0 14
efgh|5678|EFGH
NOT_STORED <--- That is cool! Memcached plugin appreciates foreign keys!
get efgh
END
get mykey4
END
So foreign key constraints are enforced with the memcached plugin.
How can I access multiple tables via memcached?
Memcached does not know anything about tables. There is only one data store. Usually memcached programmers use a trick to simulate different data stores: They include the table name into the key. So the key is something like "user:4711" or "page:/shop/home" or "session:fh5hjk543bjk". But still all data is in a single table in MySQL. If you want to report via SQL on only one type of data like "session:" you can add "...WHERE pkey LIKE "session:%" to your query. To make it comfortable you can also define different views:
mysql> SELECT * FROM kvstore;
+----------------------+-------------------------------+-------+------+---------+
| key | value | flags | cas | expires |
+----------------------+-------------------------------+-------+------+---------+
| session:grcn34r834cn | 2012-01-12 08:32|4711 | 0 | 0 | 0 |
| session:k35jnjkj56ff | 2012-01-14 23:11|4713 | 0 | 0 | 0 |
| user:4711 | dummy|secret|Berlin | 0 | 0 | 0 |
| user:4712 | superman|unkown|London | 0 | 0 | 0 |
| user:4713 | wonderwoman|dontknow|New York | 0 | 0 | 0 |
+----------------------+-------------------------------+-------+------+---------+
5 rows in set (0.00 sec)
mysql> CREATE VIEW user AS SELECT RIGHT(`key`,4) AS userID, value FROM kvstore WHERE `key` LIKE "user%";
Query OK, 0 rows affected (0.37 sec)
mysql> SELECT * FROM user;
+--------+-------------------------------+
| userID | value |
+--------+-------------------------------+
| 4711 | dummy|secret|Berlin |
| 4712 | superman|unkown|London |
| 4713 | wonderwoman|dontknow|New York |
+--------+-------------------------------+
3 rows in set (0.00 sec)
Summary
Memcached plugin is easy to enable and offers very lightweight access protocoll to InnoDB data.
You can store values into different columns. Foreign key relationships are enforced.
You can also replicate data that is stored via memcached plugin to slave servers.
The most important tuning parameter is daemon_memcached_w_batch_size, which is 32 by default, 1 if replicating.
I will add a more enhanced use case of the memcached configuration in another post that should show the benefits of using the same data via memcached protocoll and SQL at the same time.
