Documentation Home
MySQL 5.7 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 33.9Mb
PDF (A4) - 34.0Mb
PDF (RPM) - 33.2Mb
EPUB - 8.5Mb
HTML Download (TGZ) - 8.2Mb
HTML Download (Zip) - 8.2Mb
HTML Download (RPM) - 7.1Mb
Eclipse Doc Plugin (TGZ) - 9.0Mb
Eclipse Doc Plugin (Zip) - 11.1Mb
Man Pages (TGZ) - 219.4Kb
Man Pages (Zip) - 322.3Kb
Info (Gzip) - 3.2Mb
Info (Zip) - 3.2Mb
Excerpts from this Manual

MySQL 5.7 Reference Manual  /  ...  /  Internals of the InnoDB memcached Plugin

14.17.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.13 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 describes the underlying tables used by the InnoDB memcached plugin.

The innodb_memcached_config.sql configuration script installs three tables required by the InnoDB memcached plugin. The tables are created in a dedicated innodb_memcache database:

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

The containers table is the most important of the three tables. Entries in this table are containers for InnoDB tables that are used to store memcached values. The containers map the columns of the InnoDB table to the values outlined in the table below. This mapping is necessary for memcached to work with InnoDB tables.

The containers table has a default entry for the test.demo_test table. To use the InnoDB memcached plugin with your own InnoDB table, you must add an entry for your table to the containers table.

Table 14.14 containers Columns

ColumnDescription
nameThe name given to the container.
db_schemaThe name of the database in which the InnoDB table resides. This is a required value.
db_tableThe name of the InnoDB table that stores memcached values. This is a required value.
key_columnsThe column in the InnoDB table that contains lookup key values for memcached operations. This is a required value.
value_columnsThe columns (one or more) in the InnoDB table that store memcached data. Multiple columns can be specified using the separator character that is specified in the innodb_memcached.config_options table. By default, the separator is a pipe character (|). To specify multiple columns, separate them with the defined separator character. For example: col1|col2|col3. This is a required value.
flagsSpecifies the columns in the InnoDB table that are used as flags (a user-defined numeric value that is stored and retrieved along with the main value) for memcached. A flag value can be used as a column specifier for some operations (such as incr, prepend) if memcached value is mapped to multiple columns, so that an operation is performed on a 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. If you do not use the flags column, set its value to 0 to indicate that it is unused.
cas_columnThe column in the InnoDB table that stores compare-and-swap (cas) values. The cas_column value and expire_time_column value are related to the way memcached hashes requests to different servers and caches data in memory. Because the InnoDB memcached plugin is 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. If you do not use these columns, set their value to 0 to indicate that the columns are unused.
expire_time_columnThe column in the InnoDB table that stores expiration values. The cas_column value and expire_time_column value are related to the way memcached hashes requests to different servers and caches data in memory. Because the InnoDB memcached plugin is 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. If you do not use these columns, set their value to 0 to indicate that the columns are unused. As of MySQL 5.7.8, maximum expire time is defined as INT_MAX32 or 2147483647 seconds (approximately 68 years).
unique_idx_name_on_keyThe 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.

containers Table Column Constraints

  • You must supply a value for db_schema, db_name, key_columns, value_columns and unique_idx_name_on_key. Otherwise, the setup will fail. Specify 0 for flags, cas_column, and expire_time_column if they are unused. Failing to do so could cause your setup to fail.

  • key_columns: The maximum limit for a memcached key is 250 characters, which is enforced by memcached. If a mapped key longer than the maximum limit is used, the operation will fail. The mapped key must be a non-Null CHAR or VARCHAR type.

  • value_columns: Must be mapped to a CHAR, VARCHAR, or BLOB column. There is no length restriction and the value can be NULL.

  • cas_column: The cas value is a 64 bit integer. It must be mapped to a BIGINT of at least 8 bytes. If you do not use this column, set its value to 0 to indicate that it is unused.

  • expiration_time_column: Must mapped to an INTEGER of at least 4 bytes. Expiration time is defined as a 32-bit integer for Unix time (the number of seconds since January 1, 1970, as a 32-bit value), or the number of seconds starting from the current time. For the latter, the number of seconds may not exceed 60*60*24*30 (the number of seconds in 30 days). If the number sent by a client is larger, the server will consider it to be a real Unix time value rather than an offset from the current time. If you do not use this column, set its value to 0 to indicate that it is unused.

  • flags: Must be mapped to an INTEGER of at least 32-bits and can be NULL. If you do not use this column, set its value to 0 to indicate that it is unused.

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

The cache_policies table defines a cache policy for your InnoDB memcached setup. You can specify individual policies for get, set, delete, and flush operations within a single cache policy. The default setting for all operations is innodb_only.

  • innodb_only: Use InnoDB as the data store of memcached.

  • cache-only: Use the traditional memcached engine as the data store.

  • caching: Use both InnoDB and the traditional memcached engine as data stores. In this case, if memcached cannot find a key in memory, it searches for the value in an InnoDB table.

  • disable: Disable caching.

Table 14.15 cache_policies Columns

ColumnDescription
policy_nameName of the cache policy. The default cache policy name is cache_policy.
get_policyThe cache policy for get operations. Valid values are innodb_only, cache-only, caching, or disabled. The default setting is innodb_only.
set_policyThe cache policy for set operations. Valid values are innodb_only, cache-only, caching, or disabled. The default setting is innodb_only.
delete_policyThe cache policy for delete operations. Valid values are innodb_only, cache-only, caching, or disabled. The default setting is innodb_only.
flush_policyThe cache policy for flush operations. Valid values are innodb_only, cache-only, caching, or disabled. The default setting is innodb_only.

config_options Table

The config_options table stores memcached-related settings that can be changed at runtime, using SQL. Supported configuration options are separator and table_map_delimiter:

Table 14.16 config_options Columns

ColumnDescription
NameName of the memcached-related configuration option. The following configuration options are supported through the config_options table:
  • separator: Used to separate values of a long string into separate values when there are multiple value_columns defined. By default, the separator is a | character. For example, if you defined col1, col2 as value columns, and you define | as the separator, you can issue the following command in memcached to insert values into col1 and col2 respectively:

    set keyx 10 0 19
    valuecolx|valuecoly

    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.

ValueThe value assigned to the memcached-related configuration option.


Multiple-column Mapping

  • During plugin initialization, when InnoDB memcached is configured with information defined in the containers table, each mapped column that is parsed from value_columns is verified against the mapped table. If multiple columns are mapped, there is a check to ensure that each column exists and is the right type.

  • At run-time, for memcached insert operations, if there are more delimiters in the value than the number of mapped columns, only the number of mapped values are taken. For example, if there are 6 mapped columns and 7 delimited values are provided, only the first 6 delimited values are taken. The 7th delimited value is ignored.

  • If there are fewer delimited values than mapped columns, unfilled columns are set to NULL. If an unfilled column cannot be NULL, the insert will fail.

  • If a table has more columns than mapped values, the extra columns do not affect output results.

Example Tables

The innodb_memcached_config.sql configuration script creates a table demo_test in the test database as an example. It also allows the InnoDB memcached plugin 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.


User Comments
Sign Up Login You must be logged in to post a comment.