14.18.7 Internals of the InnoDB memcached Plugin

InnoDB API for the InnoDB memcached Plugin

The InnoDB memcached engine accesses InnoDB through InnoDB APIs. Most of the APIs are directly adopted from embedded InnoDB. InnoDB API functions are passed to InnoDB memcached as callback functions. InnoDB API functions access the InnoDB table directly, and are mostly DML operations except for the TRUNCATE TABLE operation.

All memcached commands, listed below, are implemented through the InnoDB memcached API. The following table outlines how each memcached command is mapped to a DML operation.

Table 14.10 memcached Commands and Associated DML Operation

memcached CommandDML Operation
geta read/fetch command
seta search followed by an insertion or update (depending on whether or not a key exists)
adda search followed by an insertion or update
replacea search followed by an update
appenda search followed by an update (appends data to the result before update)
prependa search followed by an update (prepends data to the result before update)
incra search followed by an update
decra search followed by an update
deletea search followed by a deletion
flush_alltruncate table

Underlying Tables Used by the InnoDB memcached Plugin

This section explains the details of the underlying tables used by the InnoDB / memcached plugin.

The configuration script, scripts/innodb_memcached_config.sql, installs 3 tables needed by the InnoDB memcached. These tables are created in a dedicated database innodb_memcache:

mysql> use innodb_memcache;
Database changed
mysql> show tables;
| Tables_in_innodb_memcache |
| cache_policies            |
| config_options            |
| containers                |
3 rows in set (0.01 sec)

containers Table

containers - This table is the most important table for the memcached daemon. It describes the table or tables used to store the memcached values. You must make changes to this table to start using the memcached interface with one or more of your own tables, rather than just experimenting with the test.demo_test table.

The mapping is done through specifying corresponding column values in the table:

mysql> desc containers;
| Field                  | Type         | Null | Key | Default | Extra |
| name                   | varchar(50)  | NO   | PRI | NULL    |       |
| db_schema              | varchar(250) | NO   |     | NULL    |       |
| db_table               | varchar(250) | NO   |     | NULL    |       |
| key_columns            | varchar(250) | NO   |     | NULL    |       |
| value_columns          | varchar(250) | YES  |     | NULL    |       |
| flags                  | varchar(250) | NO   |     | 0       |       |
| cas_column             | varchar(250) | YES  |     | NULL    |       |
| expire_time_column     | varchar(250) | YES  |     | NULL    |       |
| unique_idx_name_on_key | varchar(250) | NO   |     | NULL    |       |
9 rows in set (0.02 sec)

The above 5 column values (table name, key column, value column and index) must be supplied. Otherwise, the setup will fail.

Although the following values are optional, they are required for full compliance with the memcached protocol. If you do not use flags, cas_column, or expiration_time_column, set their value to 0 to indicate that they are unused. Failing to do so will result in an error when you attempt to load the plugin.

containers Table Column Constraints

A pre-check is performed at plugin load time to enforce column constraints. If any mismatches are found, the plugin will not load.

cache_policies Table

Table cache_policies specifies whether to use InnoDB as the data store of memcached (innodb_only), or to use the traditional memcached engine as the backstore (cache-only), or both (caching). In the last case, if memcached cannot find a key in memory, it searches for the value in an InnoDB table.

config_options Table

Table config_options stores memcached-related settings that are appropriate to change at runtime, through SQL. Currently, MySQL supports the following configuration options through this table:

separator: The separator used to separate values of a long string into smaller values for multiple columns values. By default, this is the | character. For example, if you defined col1, col2 as value columns, And you define | as separator, you could issue the following command in memcached to insert values into col1 and col2 respectively:

set keyx 10 0 19

So valuecol1x is stored in col1 and valuecoly is stored in col2.

table_map_delimiter: The character separating the schema name and the table name when you use the @@ notation in a key name to access a key in a specific table. For example, @@t1.some_key and @@t2.some_key have the same key value, but are stored in different tables and so do not conflict.

Multiple-column Mapping

Example Tables

The configuration script, scripts/innodb_memcached_config.sql, creates a table demo_test in the test database as an example. It also allows the Daemon Memcached to work immediately, without creating any additional tables.

The entries in the container table define which column is used for what purpose as described above:

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 |
| aaa  | test      | demo_test | c1          | c2            | c3    | c4         | c5                 | PRIMARY                |
1 row in set (0.00 sec)

mysql> desc test.demo_test;
| Field | Type                | Null | Key | Default | Extra |
| c1    | varchar(32)         | NO   | PRI |         |       |
| c2    | varchar(1024)       | YES  |     | NULL    |       |
| c3    | int(11)             | YES  |     | NULL    |       |
| c4    | bigint(20) unsigned | YES  |     | NULL    |       |
| c5    | int(11)             | YES  |     | NULL    |       |
5 rows in set (0.01 sec)

When no table ID is requested through the @@ notation in the key name: