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
Before setting up the memcached interface for any data, consult Section 188.8.131.52, “Security Considerations for the InnoDB memcached Plugin” to understand the security procedures needed to prevent any unauthorized access.
Before you set up the plugin itself and the internal tables is uses, verify that your server has the required prerequisite software.
Currently, the memcached Daemon Plugin is only supported on Linux, Solaris, and OS X platforms.
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.
When you use a MySQL installer, the
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
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/innodb_engine.so are used.
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
If you have the source code release, then there is a
libevent 1.4.3 included in the package.
Go to the directory
Issue the following commands to build and install the
autoconf ./configure make make install
When building the MySQL Server from source, once
libevent is installed, build the MySQL server
as usual. No special build steps are required.
The source code for the
InnoDB-memcached plugin is
As part of the server build, it generates two shared libraries:
memcached daemon plugin to MySQL.
InnoDB API plugin to
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)
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 184.108.40.206, “Troubleshooting the InnoDB memcached Plugin” for the steps to resolve this issue.
To configure the memcached plugin so it can
InnoDB tables, run the
install the necessary tables used behind the scenes:
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 220.127.116.11, “Internals of the InnoDB memcached Plugin”.
To activate the daemon plugin, use the
plugin statement, just as when installing any other
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.
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
plugin statement again. All the previous configuration
settings, internal tables, and data are preserved when the
plugin is bounced this way.
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
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
mysqld .... --loose-daemon_memcached_option="-p11222"
You can add other memcached command line options to the
The other configuration options are:
(default NULL, representing the plugin directory).
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
flush_all call resets both the read
and write counters.
batch commit for any write operations
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
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 parameter in your MySQL configure file or MySQL boot command line. They take effect when you load the memcached plugin.
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 18.104.22.168.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 11211
set a11 10 0 9
get a11VALUE a11 0 9 123456789 END
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.
install plugin statement to start
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