Get started with InnoDB Memcached Daemon plugin
By Calvin Sun on Apr 11, 2011
Note: this article was originally published on http://blogs.innodb.com on April 11, 2011 by Jimmy Yang.
As Calvin mentioned in “NoSQL to InnoDB with Memcached“, we just released a “technology preview” of the feature that makes memcached a MySQL Daemon Plugin. And this “technology preview” release demonstrates how user can go around SQL Optimizer and Query Processing and directly interact with InnoDB Storage Engine through InnoDB APIs. Here, we would like to walk with you step by step to see how to get the memcached Daemon Plugin set up and get it running.
If you would just like to get a brief introduction on the setup steps, there is a “README-innodb_memcached” in the mysql-5.6.2-labs-innodb-memcached package. This is a more elaborated description on these steps.
Currently, the Memcached Daemon Plugin prototype is only supported on Linux platform. And as a prerequisite, you must have libevent installed, since it is required by memcached.
If you have the source code release, then there is a libevent 1.4.3 included in the package (plugin/innodb_memcached/libevent). You can go to the directory and do autoconf, ./configure, make and make install to make the libevent installed.
2) Build the server
Assuming you would like to build the server yourself (otherwise, you can just skip this section), once libevent is installed, you can just build the MySQL server as usual.
Our source code is in the “plugin/innodb_memcached” directory. As part of server build, it will generate two shared libraries:
1) libmemcached.so: this is the memcached daemon plugin to MySQL
2) innodb_engine.so: this is an InnoDB API plugin to memcached
Make sure above two shared libraries are put in the MySQL plugin directory. You can find MySQL plugin directory by doing “select @@plugin_dir”:
mysql> select @@plugin_dir;
| @@plugin_dir |
| /home/jy/work2/mysql-5.6-memcached-labs/lib/plugin |
1 row in set (0.00 sec)
3) Install configuration tables:
Next, the memcached plugin needs a bit configuration to know how to interact with InnoDB table. We have a configuration script in “scripts/innodb_memcached_config.sql”. You can just install the necessary configure tables by running “mysql < scripts/innodb_memcached_config.sql”. If you do not like to know the detail of these configuration tables, you can just skip this section.
This configure script installs 3 tables needed by the InnoDB Memcached. These tables are created in a dedicated database “innodb_memcache”. We will go over these three tables in a bit more detail:
1) “containers” – This table is the most important table for “Memcached – InnoDB mapping”. It describes the table used to store the memcached values. Currently, we can only map memcached to one table at a time. So essentially, there will be only one row in the table. In the future, we would consider making this configuration more flexible and dynamic, or user can map memcached operations to multiple tables.
The mapping is done through specifying corresponding column values in the table:
- “db_schema” and “db_table” columns describe the database and table name for storing the memcached value.
- “key_columns” describes the column (single column) name for the column being used as “key” for the memcached operation
- “value_columns” describes the columns (can be multiple) used as “values” for the memcached operation. User can specify multiple columns by separating them by comma (such as “col1, col2″ etc.)
- “unique_idx_name_on_key” is the name of the index on the “key” column. It must be a unique index. It can be primary or secondary.
Above 5 column values (table name, key column, value column and index) must be supplied. Otherwise, the setup will fail.
Following are optional values, however, to fully comply with memcached protocol, you will need these column values supplied too.
- “flags” describes the columns used as “flag” for memcached. It also used as “column specifier” for some operations (such as incr, prepend) if memcached “value” is mapped to multiple columns. So the operation would be done on specified column. For example, if you have mapped value to 3 columns, and only want the “increment” operation performed on one of these columns, you can use flags to specify which column will be used for these operations.
- “cas_column” and “exp_column” are used specifically to store the “cas” and “exp” value of memcached.
2. Table “cache_policies” specifies whether we’ll use InnoDB as the data store of memcached (innodb_only) or use memcached’s “default engine” as the backstore (cache-only) or both (caching). In the last case, only if the default engine operation fails, the operation will be forwarded to InnoDB (for example, we cannot find a key in the memory, then it will search InnoDB).
3) Table “config_options”, currently, we only support one config option through this table. It is the “separator” used to separate values of a long string into smaller values for multiple columns values. For example, if you defined “col1, col2″ as value columns. And you define “|” as separate, you could issue following command in memcached to insert values into col1 and col2 respectively:
set keyx 10 0 19
So “valuecol1x” will send to col1 and valuecoly will send to col2.
4) Example tables
Finally, as part of the configuration script, we created a “demo_test” in the “test” database as an example. It also allows the Daemon Memcached to work out of box, and no need to for any additional configurations.
As you would notice, this “demo_test” table has more columns than needed, so it would need the entries in the “container” table to tell which column is used for what purpose as described above.
4) Install the Daemon Plugin
The final step would be installing the daemon plugin. It is the same as installing any other MySQL plugin:
mysql> install plugin daemon_memcached soname “libmemcached.so”;
If you have any memcached specific configure parameters, although it takes effect when the plugin is installed, you would need to specify it during MySQL server boot time or enter them in the MySQL configure files.
For example, if you would like the memcached to listen on port “11222″ instead of the default port “11211″, then you would need to add “-p11222″ to MySQL system configure variable “daemon_memcached_option”:
mysqld …. –loose-daemon_memcached_option=”-p11222″.
Of course, you can add other memcached command line options to “daemon_memcached_option” string.
The other configure options are:
1) daemon_memcached_engine_lib_name (default “innodb_engine.so”)
2) daemon_memcached_engine_lib_path (default NULL, the plugin directory).
By default, you do not need to set/change anything with these two configure option. We have above two configure options because they allow you to load any other storage engine for memcached (such as NDB memcached engine). This opens door for more interesting exploration.
3) daemon_memcached_r_batch_size, batch commit size for read operation (get operations. It specifies after how many ops we will do a commit. By default, this is set as a very large number, 1048576.
4) daemon_memcached_w_batch_size, batch commit for any write operations (set, replace, append, prepend, incr, decr etc.) By default, this is set as 32.
Again, please note that you will have these configuration parameter in your MySQL configure file or MySQL boot command line. And they will take effect when you load the memcached plugin.
5) Start to play:
Now you have everything set up, you can start to play:
telnet 127.0.0.1 11211
set a11 10 0 9
VALUE a11 0 9
You can access the InnoDB table (“demo_test”) through the standard SQL interfaces. However, there are some catches:
1) If you would like to take a look at what’s in the “demo_test” table, please remember we had batched the commits (32 ops by default) by default. So you will need to do “read uncommitted” select to find the just inserted rows:
mysql> set session TRANSACTION ISOLATION LEVEL
-> read uncommitted;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from demo_test;
| cx | cy | c1 | cz | c2 | ca | CB | c3 | cu | c4 | C5 |
| NULL | NULL | a11 | NULL | 123456789 | NULL | NULL | 10 | NULL | 3 | NULL |
1 row in set (0.00 sec)
2) The InnoDB table would be IS (shared intention) or IX (exclusive intentional) locked for all operations in a transaction. So unless you change “daemon_memcached_r_batch_size” and “daemon_memcached_w_batch_size” to small number (like 1), the table is most likely intentionally locked between each operations. So you cannot do any DDL on the table.
Now you have everything setup. And you can directly interact with InnoDB storage engine through Memcached interfaces. In addition, as you might notice while going through this extended “README”, we still have a lot interesting options open for exploration and enhancement. This is the beginning of opening InnoDB to the outside world, and NoSQL is a perfect place for it to play.