14.21.3 Setting Up the InnoDB memcached Plugin

This section describes how to set up the daemon_memcached plugin on a MySQL server. Because the memcached daemon is tightly integrated with the MySQL server to avoid network traffic and minimize latency, you perform this process on each MySQL instance that uses this feature.

Note

Before setting up the daemon_memcached plugin, consult Section 14.21.4, “Security Considerations for the InnoDB memcached Plugin” to understand the security procedures required to prevent unauthorized access.

Prerequisites

The daemon_memcached plugin is only supported on Linux, Solaris, and macOS platforms. Other operating systems are not supported.
When building MySQL from source, you must build with -DWITH_INNODB_MEMCACHED=ON. This build option generates two shared libraries in the MySQL plugin directory (plugin_dir) that are required to run the daemon_memcached plugin:
- libmemcached.so: the memcached daemon plugin to MySQL.
- innodb_engine.so: an InnoDB API plugin to memcached.
libevent must be installed.
- If you did not build MySQL from source, the libevent library is not included in your installation. Use the installation method for your operating system to install libevent 1.4.12 or later. For example, depending on the operating system, you might use apt-get, yum, or port install. For example, on Ubuntu Linux, use:
```
sudo apt-get install libevent-dev
```
- If you installed MySQL from a source code release, libevent 1.4.12 is bundled with the package and is located at the top level of the MySQL source code directory. If you use the bundled version of libevent, no action is required. If you want to use a local system version of libevent, you must build MySQL with the -DWITH_LIBEVENT build option set to system or yes.

Installing and Configuring the InnoDB memcached Plugin

Configure the daemon_memcached plugin so it can interact with InnoDB tables by running the innodb_memcached_config.sql configuration script, which is located in MYSQL_HOME/share. This script installs the innodb_memcache database with three required tables (cache_policies, config_options, and containers). It also installs the demo_test sample table in the test database.
```
mysql> source MYSQL_HOME/share/innodb_memcached_config.sql
```
Running the innodb_memcached_config.sql script is a one-time operation. The tables remain in place if you later uninstall and re-install the daemon_memcached plugin.
```
mysql> USE innodb_memcache;
mysql> SHOW TABLES;
+---------------------------+
| Tables_in_innodb_memcache |
+---------------------------+
| cache_policies            |
| config_options            |
| containers                |
+---------------------------+

mysql> USE test;
mysql> SHOW TABLES;
+----------------+
| Tables_in_test |
+----------------+
| demo_test      |
+----------------+
```
Of these tables, the innodb_memcache.containers table is the most important. Entries in the containers table provide a mapping to InnoDB table columns. Each InnoDB table used with the daemon_memcached plugin requires an entry in the containers table.

The innodb_memcached_config.sql script inserts a single entry in the containers table that provides a mapping for the demo_test table. It also inserts a single row of data into the demo_test table. This data allows you to immediately verify the installation after the setup is completed.
```
mysql> SELECT * FROM innodb_memcache.containers\G
*************************** 1. row ***************************
                  name: aaa
             db_schema: test
              db_table: demo_test
           key_columns: c1
         value_columns: c2
                 flags: c3
            cas_column: c4
    expire_time_column: c5
unique_idx_name_on_key: PRIMARY

mysql> SELECT * FROM test.demo_test;
+----+------------------+------+------+------+
| c1 | c2               | c3   | c4   | c5   |
+----+------------------+------+------+------+
| AA | HELLO, HELLO     |    8 |    0 |    0 |
+----+------------------+------+------+------+
```
For more information about innodb_memcache tables and the demo_test sample table, see Section 14.21.7, “InnoDB memcached Plugin Internals”.
Activate the daemon_memcached plugin by running the INSTALL PLUGIN statement:
```
mysql> INSTALL PLUGIN daemon_memcached soname "libmemcached.so";
```
Once the plugin is installed, it is automatically activated each time the MySQL server is restarted.

Verifying the InnoDB and memcached Setup

To verify the daemon_memcached plugin setup, use a telnet session to issue memcached commands. By default, the memcached daemon listens on port 11211.

Retrieve data from the test.demo_test table. The single row of data in the demo_test table has a key value of AA.

telnet localhost 11211
Trying 127.0.0.1...
Connected to localhost.
Escape character is '^]'.
get AA
VALUE AA 8 12
HELLO, HELLO
END

Insert data using a set command.
```
set BB 10 0 16
GOODBYE, GOODBYE
STORED
```
where:
- set is the command to store a value
- BB is the key
- 10 is a flag for the operation; ignored by memcached but may be used by the client to indicate any type of information; specify 0 if unused
- 0 is the expiration time (TTL); specify 0 if unused
- 16 is the length of the supplied value block in bytes
- GOODBYE, GOODBYE is the value that is stored

Verify that the data inserted is stored in MySQL by connecting to the MySQL server and querying the test.demo_test table.

mysql> SELECT * FROM test.demo_test;
+----+------------------+------+------+------+
| c1 | c2               | c3   | c4   | c5   |
+----+------------------+------+------+------+
| AA | HELLO, HELLO     |    8 |    0 |    0 |
| BB | GOODBYE, GOODBYE |   10 |    1 |    0 |
+----+------------------+------+------+------+

Return to the telnet session and retrieve the data that you inserted earlier using key BB.
```
get BB
VALUE BB 10 16
GOODBYE, GOODBYE
END
quit
```

If you shut down the MySQL server, which also shuts off the integrated memcached server, further attempts to access the memcached data fail with a connection error. Normally, the memcached data also disappears at this point, and you would require application logic to load the data back into memory when memcached is restarted. However, the InnoDB memcached plugin automates this process for you.

When you restart MySQL, get operations once again return the key-value pairs you stored in the earlier memcached session. When a key is requested and the associated value is not already in the memory cache, the value is automatically queried from the MySQL test.demo_test table.

Creating a New Table and Column Mapping

This example shows how to setup your own InnoDB table with the daemon_memcached plugin.

Create an InnoDB table. The table must have a key column with a unique index. The key column of the city table is city_id, which is defined as the primary key. The table must also include columns for flags, cas, and expiry values. There may be one or more value columns. The city table has three value columns (name, state, country).

Note

There is no special requirement with respect to column names as along as a valid mapping is added to the innodb_memcache.containers table.
```
mysql> CREATE TABLE city (
       city_id VARCHAR(32),
       name VARCHAR(1024),
       state VARCHAR(1024),
       country VARCHAR(1024),
       flags INT,
       cas BIGINT UNSIGNED, 
       expiry INT,
       primary key(city_id)
       ) ENGINE=InnoDB;
```

Add an entry to the innodb_memcache.containers table so that the daemon_memcached plugin knows how to access the InnoDB table. The entry must satisfy the innodb_memcache.containers table definition. For a description of each field, see Section 14.21.7, “InnoDB memcached Plugin Internals”.

mysql> DESCRIBE innodb_memcache.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    |       |
+------------------------+--------------+------+-----+---------+-------+

The innodb_memcache.containers table entry for the city table is defined as:

mysql> INSERT INTO `innodb_memcache`.`containers` (
       `name`, `db_schema`, `db_table`, `key_columns`, `value_columns`,
       `flags`, `cas_column`, `expire_time_column`, `unique_idx_name_on_key`)
       VALUES ('default', 'test', 'city', 'city_id', 'name|state|country', 
       'flags','cas','expiry','PRIMARY');

default is specified for the containers.name column to configure the city table as the default InnoDB table to be used with the daemon_memcached plugin.
Multiple InnoDB table columns (name, state, country) are mapped to containers.value_columns using a “|” delimiter.
The flags, cas_column, and expire_time_column fields of the innodb_memcache.containers table are typically not significant in applications using the daemon_memcached plugin. However, a designated InnoDB table column is required for each. When inserting data, specify 0 for these columns if they are unused.

After updating the innodb_memcache.containers table, restart the daemon_memcache plugin to apply the changes.

mysql> UNINSTALL PLUGIN daemon_memcached;

mysql> INSTALL PLUGIN daemon_memcached soname "libmemcached.so";

Using telnet, insert data into the city table using a memcached set command.

telnet localhost 11211
Trying 127.0.0.1...
Connected to localhost.
Escape character is '^]'.
set B 0 0 22
BANGALORE|BANGALORE|IN
STORED

Using MySQL, query the test.city table to verify that the data you inserted was stored.

mysql> SELECT * FROM test.city;
+---------+-----------+-----------+---------+-------+------+--------+
| city_id | name      | state     | country | flags | cas  | expiry |
+---------+-----------+-----------+---------+-------+------+--------+
| B       | BANGALORE | BANGALORE | IN      |     0 |    3 |      0 |
+---------+-----------+-----------+---------+-------+------+--------+

Using MySQL, insert additional data into the test.city table.

mysql> INSERT INTO city VALUES ('C','CHENNAI','TAMIL NADU','IN', 0, 0 ,0);
mysql> INSERT INTO city VALUES ('D','DELHI','DELHI','IN', 0, 0, 0);
mysql> INSERT INTO city VALUES ('H','HYDERABAD','TELANGANA','IN', 0, 0, 0);
mysql> INSERT INTO city VALUES ('M','MUMBAI','MAHARASHTRA','IN', 0, 0, 0);

Note

It is recommended that you specify a value of 0 for the flags, cas_column, and expire_time_column fields if they are unused.

Using telnet, issue a memcached get command to retrieve data you inserted using MySQL.
```
get H
VALUE H 0 22
HYDERABAD|TELANGANA|IN
END
```

Configuring the InnoDB memcached Plugin

Traditional memcached configuration options may be specified in a MySQL configuration file or a mysqld startup string, encoded in the argument of the daemon_memcached_option configuration parameter. memcached configuration options take effect when the plugin is loaded, which occurs each time the MySQL server is started.

For example, to make memcached listen on port 11222 instead of the default port 11211, specify -p11222 as an argument of the daemon_memcached_option configuration option:

mysqld .... --daemon_memcached_option="-p11222"

Other memcached options can be encoded in the daemon_memcached_option string. For example, you can specify options to reduce the maximum number of simultaneous connections, change the maximum memory size for a key-value pair, or enable debugging messages for the error log, and so on.

There are also configuration options specific to the daemon_memcached plugin. These include:

daemon_memcached_engine_lib_name: Specifies the shared library that implements the InnoDB memcached plugin. The default setting is innodb_engine.so.
daemon_memcached_engine_lib_path: The path of the directory containing the shared library that implements the InnoDB memcached plugin. The default is NULL, representing the plugin directory.
daemon_memcached_r_batch_size: Defines the batch commit size for read operations (get). It specifies the number of memcached read operations after which a occurs. daemon_memcached_r_batch_size is set to 1 by default so that every get request accesses the most recently committed data in the InnoDB table, whether the data was updated through memcached or by SQL. When the value is greater than 1, the counter for read operations is incremented with each get call. A flush_all call resets both read and write counters.
daemon_memcached_w_batch_size: Defines the batch commit size for write operations (set, replace, append, prepend, incr, decr, and so on). daemon_memcached_w_batch_size is set to 1 by default so that no uncommitted data is lost in case of an outage, and so that SQL queries on the underlying table access the most recent data. When the value is greater than 1, the counter for write operations is incremented for each add, set, incr, decr, and delete call. A flush_all call resets both read and write counters.

By default, you do not need to modify daemon_memcached_engine_lib_name or daemon_memcached_engine_lib_path. You might configure these options if, for example, you want to use a different storage engine for memcached (such as the NDB memcached engine).

daemon_memcached plugin configuration parameters may be specified in the MySQL configuration file or in a mysqld startup string. They take effect when you load the daemon_memcached plugin.

When making changes to daemon_memcached plugin configuration, reload the plugin to apply the changes. To do so, issue the following statements:

mysql> UNINSTALL PLUGIN daemon_memcached;

mysql> INSTALL PLUGIN daemon_memcached soname "libmemcached.so";

Configuration settings, required tables, and data are preserved when the plugin is restarted.

For additional information about enabling and disabling plugins, see Section 5.5.1, “Installing and Uninstalling Plugins”.