14.2.9.7. Internals of the InnoDB memcached Plugin

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

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

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

The tables are:

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)

db_schema and db_table columns specify the database and table name for storing the memcached value.
key_columns specifies the single column name used as the lookup key for memcached operations.
value_columns describes the columns (one or more) used as values for memcached operations. To specify multiple columns, separate them with pipe characters (such as col1|col2|col3 and so on).
unique_idx_name_on_key is the name of the index on the key column. It must be a unique index. It can be the primary key or a secondary index. Preferably, make the key column the primary key of the InnoDB table. Doing so saves a lookup step over using a secondary index for this column. You cannot make a covering index for memcached lookups; InnoDB returns an error if you try to define a composite secondary index over both the key and value columns.

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 needed for full compliance with the memcached protocol:

flags specifies the columns used as flags (a user-defined numeric value that is stored and retrieved along with the main value) for memcached. It is also used as the column specifier for some operations (such as incr, prepend) if memcached value is mapped to multiple columns. So the operation would be done on the specified column. For example, if you have mapped a 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 (compare-and-swap) and exp (expiry) value of memcached. Those values are related to the way memcached hashes requests to different servers and caches data in memory. Because the InnoDB memcached plugin is so tightly integrated with a single memcached daemon, and the in-memory caching mechanism is handled by MySQL and the buffer pool, these columns are rarely needed in this type of deployment.

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.

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 19valuecolx|valuecoly

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.

Example Tables

Finally, the configuration script 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:

If a row has a name value of default, the corresponding table is used by the memcached plugin. Thus, when you make your first entry in innodb_memcache.containers to move beyond the demo_test table, use a name value of default.
If there is no innodb_memcache.containers.name value of default, the row with the first name value in alphabetical order is used.