14.2.9.3. Getting Started with InnoDB Memcached Plugin

This section describes the steps to activate the InnoDB / memcached integration 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 memcached interface for any data, consult Section 14.2.9.4, "Security Considerations for the InnoDB memcached Plugin" to understand the security procedures needed to prevent any unauthorized access.

14.2.9.3.1. Prerequisites for the InnoDB memcached Plugin

Before you set up the plugin itself and the internal tables is uses, verify that your server has the required prerequisite software.

Platform Support

Currently, the memcached Daemon Plugin is only supported on Linux, Solaris, and OS X platforms.

Software Prerequisites

You must have libevent installed, since it is required by memcached. The way to get this library is different if you use the MySQL installer or build from source, as described in the following sections.

Using a MySQL Installation Package

When you use a MySQL installer, the libevent library is not included. Use the particular method for your operating system to download and install libevent 1.4.3 or later: for example, depending on the operating system, you might use the command apt-get, yum, or port install.

The libraries for memcached and the InnoDB plugin for memcached are put into the right place by the MySQL installer. For typical operation, the files lib/plugin/libmemcached.so and lib/plugin/innodb_engine.so are used.

Building from Source

For a brief introduction on the setup steps, see the file README-innodb_memcached in the source distribution. This is a more detailed explanation of that procedure.

If you have the source code release, then there is a libevent 1.4.3 included in the package.

Go to the directory plugin/innodb_memcached/libevent.
Issue the following commands to build and install the libevent library:
```
autoconf./configuremakemake install
```

After libevent is installed, build the MySQL server with -DWITH_INNODB_MEMCACHED=ON.

The source code for the InnoDB-memcached plugin is in the plugin/innodb_memcached directory. Building MySQL Server with -DWITH_INNODB_MEMCACHED=ON generates two shared libraries in this directory.

libmemcached.so: the memcached daemon plugin to MySQL.
innodb_engine.so: an InnoDB API plugin to memcached.

Put these two shared libraries in the MySQL plugin directory. To find the MySQL plugin directory, issue the following command:

mysql> select @@plugin_dir;+-----------------------------------------------+| @@plugin_dir                                  |+-----------------------------------------------+| /Users/cirrus/sandbox-setup/5.6.10/lib/plugin/ |+-----------------------------------------------+1 row in set (0.00 sec)

Setting Operating System Limits

The memcached daemon can sometimes cause the MySQL server to exceed the OS limit on the number of open files. You might need to run the ulimit command to increase the limit, and then start the MySQL server from that same shell. See Section 14.2.9.8, "Troubleshooting the InnoDB memcached Plugin" for the steps to resolve this issue.

14.2.9.3.2. Installing and Configuring the InnoDB memcached Plugin

Setting Up Required Tables

To configure the memcached plugin so it can interact with InnoDB tables, run the configuration script scripts/innodb_memcached_config.sql to install the necessary tables used behind the scenes:

mysql: source MYSQL_HOME/share/innodb_memcached_config.sql

This is a one-time operation. The tables remain in place if you later disable and re-enable the memcached support. For information about the layout and purpose of these tables, see Section 14.2.9.7, "Internals of the InnoDB memcached Plugin".

Installing the Daemon Plugin

To activate the daemon plugin, use the install plugin statement, just as when installing any other MySQL plugin:

mysql> install plugin daemon_memcached soname "libmemcached.so";

Once the plugin is installed this way, it is automatically activated each time the MySQL server is booted or restarted.

Disabling the Daemon Plugin

When making major changes to the plugin configuration, you might need to turn off the plugin. To do so, issue the following statement:

mysql> uninstall plugin daemon_memcached;

To re-enable it, issue the preceding install plugin statement again. All the previous configuration settings, internal tables, and data are preserved when the plugin is bounced this way.

Specifying memcached Configuration Options

If you have any memcached specific configuration parameters, specify them on the mysqld command line or enter them in the MySQL configuration file, encoded in the argument to the daemon_memcached_option MySQL configuration option. The memcached configuration options take effect when the plugin is installed, which you do each time the MySQL server is started.

For example, to make memcached listen on port 11222 instead of the default port 11211, add -p11222 to the MySQL configuration option daemon_memcached_option:

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

You can add other memcached command line options to the daemon_memcached_option string. The other configuration options are:

daemon_memcached_engine_lib_name (default innodb_engine.so)
daemon_memcached_engine_lib_path (default NULL, representing the plugin directory).
daemon_memcached_r_batch_size, batch commit size for read operations (get). It specifies after how many memcached read operations the system automatically does a commit. By default, this is set to 1 so that every get request can access the very latest committed data in the InnoDB table, whether the data was updated through memcached or by SQL. When its value is greater than 1, the counter for read operations is incremented once for every get call. The flush_all call resets both the read and write counters.
daemon_memcached_w_batch_size, batch commit for any write operations (set, replace, append, prepend, incr, decr, and so on) By default, this is set as 1, so that no uncommitted data is lost in case of an outage, and any SQL queries on the underlying table can access the very latest data. When its value is greater than 1, the counter for write operations is incremented once for every add, set, incr, decr, and delete call. The flush_all call resets both the read and write counters.

By default, you do not need to change anything with the first two configuration options. Those options allow you to load any other storage engine for memcached (such as the NDB memcached engine).

Again, please note that you will have these configuration parameters in your MySQL configuration file or MySQL boot command line. They take effect when you load the memcached plugin.

Summary

Now you have everything set up. You can directly interact with InnoDB tables through the memcached interface. To verify that the feature is working properly, see Section 14.2.9.3.3, "Verifying the InnoDB and memcached Setup".

14.2.9.3.3. Verifying the InnoDB and memcached Setup

Now that everything is set up, you can experiment with the InnoDB and memcached combination:

Here is an example using the Unix, Linux, or OS X command shell:

# Point memcached-related commands at the memcached attached to the mysqld process.export MEMCACHED_SERVERS=127.0.0.1:11211# Store the contents of a modestly sized text file in memcached, with the data passed# to MySQL and stored in a table. The key is the basename of the file, 'mime.types'.memcp /etc/apache2/mime.types# Retrieve the data we just stored, from the memory cache.memcat mime.types

Here is an example using telnet to send memcached commands and receive results through the ASCII protocol:

telnet 127.0.0.1 11211set a11 10 0 9123456789STOREDget
            a11VALUE a11 0 9123456789ENDquit

To prove that all the same data has been stored in MySQL, connect to the MySQL server and issue:

mysql> select * from test.demo_test;

Now, shut down the MySQL server, which also shuts off the integrated memcached server. Further attempts to access the memcached data now fail with a connection error. Normally, the memcached data would disappear at this point, and you would write application logic to load the data back into memory when memcached was restarted. But the MySQL / memcached integration automates this process:

Restart the MySQL server.
Run the install plugin statement to start the daemon_memcached plugin again.
Now any memcat commands or 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, it is automatically queried from the MySQL table, by default test.demo_test.