InnoDB memcached plugin
implements memcached as a MySQL plugin daemon
that accesses the
InnoDB storage engine
directly, bypassing the MySQL SQL layer.
The following diagram illustrates how an application accesses data
daemon_memcached plugin, compared
Features of the
Direct access to
InnoDBtables, bypassing the SQL parser, the optimizer, and even the Handler API layer.
Standard memcached protocols, including the text-based protocol and the binary protocol. The
daemon_memcachedplugin passes all 55 compatibility tests of the memcapable command.
Multi-column support. You can map multiple columns into the “value” part of the key-value store, with column values delimited by a user-specified separator character.
By default, the memcached protocol is used to read and write data directly to
InnoDB, letting MySQL manage in-memory caching using the
InnoDBbuffer pool. The default settings represent a combination of high reliability and the fewest surprises for database applications. For example, default settings avoid uncommitted data on the database side, or stale data returned for memcached
Advanced users can configure the system as a traditional memcached server, with all data cached only in the memcached engine (memory caching), or use a combination of the “memcached engine” (memory caching) and the
InnoDBmemcached engine (
InnoDBas back-end persistent storage).
Control over how often data is passed back and forth between
InnoDBand memcached operations through the
daemon_memcached_w_batch_sizeconfiguration options. Batch size options default to a value of 1 for maximum reliability.
The ability to specify memcached options through the
daemon_memcached_optionconfiguration parameter. For example, you can change the port that memcached listens on, 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.
innodb_api_trx_levelconfiguration option controls the transaction isolation level on queries processed by memcached. Although memcached has no concept of transactions, you can use this option to control how soon memcached sees changes caused by SQL statements issued on the table used by the daemon_memcached plugin. By default,
innodb_api_trx_levelis set to
innodb_api_enable_mdloption can be used to lock the table at the MySQL level, so that the mapped table cannot be dropped or altered by DDL through the SQL interface. Without the lock, the table can be dropped from the MySQL layer, but kept in
InnoDBstorage until memcached or some other user stops using it. “MDL” stands for “metadata locking”.
You may already be familiar with using
memcached with MySQL, as described in
Section 16.2, “Using MySQL with memcached”. This section describes how
features of the integrated
memcached plugin differ from traditional
Installation: The memcached library comes with the MySQL server, making installation and setup relatively easy. Installation involves running the
innodb_memcached_config.sqlscript to create a
demo_testtable for memcached to use, issuing an
INSTALL PLUGINstatement to enable the
daemon_memcachedplugin, and adding desired memcached options to a MySQL configuration file or startup script. You might still install the traditional memcached distribution for additional utilities such as memcp, memcat, and memcapable.
For comparison with traditional memcached, see Section 16.2.1, “Installing memcached”.
Deployment: With traditional memcached, it is typical to run large numbers of low-capacity memcached servers. A typical deployment of the
daemon_memcachedplugin, however, involves a smaller number of moderate or high-powered servers that are already running MySQL. The benefit of this configuration is in improving efficiency of individual database servers rather than exploiting unused memory or distributing lookups across large numbers of servers. In the default configuration, very little memory is used for memcached, and in-memory lookups are served from the
InnoDBbuffer pool, which automatically caches the most recently and frequently used data. As with a traditional MySQL server instance, keep the value of the
innodb_buffer_pool_sizeconfiguration option as high as practical (without causing paging at the OS level), so that as much work as possible is performed in memory.
For comparison with traditional memcached, see Section 18.104.22.168, “memcached Deployment”.
Expiry: By default (that is, using the
innodb_onlycaching policy), the latest data from the
InnoDBtable is always returned, so the expiry options have no practical effect. If you change the caching policy to
cache_only, the expiry options work as usual, but requested data might be stale if it is updated in the underlying table before it expires from the memory cache.
For comparison with traditional memcached, see Section 22.214.171.124, “Data Expiry”.
Namespaces: memcached is like a large directory where you give files elaborate names with prefixes and suffixes to keep the files from conflicting. The
daemon_memcachedplugin lets you use similar naming conventions for keys, with one addition. Key names in the format
table_idare decoded to reference a specific a table, using mapping data from the
keyis looked up in or written to the specified table.
@@notation only works for individual calls to
setfunctions, but not others such as
delete. To designate a default table for subsequent memcached operations within a session, perform a
getrequest using the
@@notation with a
, but without the key portion. For example:
delete, and other operations use the table designated by
For comparison with traditional memcached, see Section 126.96.36.199, “Using Namespaces”.
Hashing and distribution: The default configuration, which uses the
innodb_onlycaching policy, is suitable for a traditional deployment configuration where all data is available on all servers, such as a set of replication slave servers.
If you physically divide data, as in a sharded configuration, you can split data across several machines running the
daemon_memcachedplugin, and use the traditional memcached hashing mechanism to route requests to a particular machine. On the MySQL side, you would typically let all data be inserted by
addrequests to memcached so that appropriate values are stored in the database on the appropriate server.
For comparison with traditional memcached, see Section 188.8.131.52, “memcached Hashing/Distribution Types”.
Memory usage: By default (with the
innodb_onlycaching policy), the memcached protocol passes information back and forth with
InnoDBtables, and the
InnoDBbuffer pool handles in-memory lookups instead of memcached memory usage growing and shrinking. Relatively little memory is used on the memcached side.
If you switch the caching policy to
cache_only, the normal rules of memcached memory usage apply. Memory for memcached data values is allocated in terms of “slabs”. You can control slab size and maximum memory used for memcached.
Either way, you can monitor and troubleshoot the
daemon_memcachedplugin using the familiar statistics system, accessed through the standard protocol, over a telnet session, for example. Extra utilities are not included with the
daemon_memcachedplugin. You can use the
memcached-toolscript to install a full memcached distribution.
For comparison with traditional memcached, see Section 184.108.40.206, “Memory Allocation within memcached”.
Thread usage: MySQL threads and memcached threads co-exist on the same server. Limits imposed on threads by the operating system apply to the total number of threads.
For comparison with traditional memcached, see Section 220.127.116.11, “memcached Thread Support”.
Log usage: Because the memcached daemon is run alongside the MySQL server and writes to
-vvvoptions for logging write output to the MySQL error log.
For comparison with traditional memcached, see Section 18.104.22.168, “memcached Logs”.
memcached operations: Familiar memcached operations such as
deleteare available. Serialization (that is, the exact string format representing complex data structures) depends on the language interface.
For comparison with traditional memcached, see Section 22.214.171.124, “Basic memcached Operations”.
Using memcached as a MySQL front end: This is the primary purpose of the
InnoDBmemcached plugin. An integrated memcached daemon improves application performance, and having
InnoDBhandle data transfers between memory and disk simplifies application logic.
For comparison with traditional memcached, see Section 126.96.36.199, “Using memcached as a MySQL Caching Layer”.
Utilities: The MySQL server includes the
libmemcachedlibrary but not additional command-line utilities. To use commands such as memcp, memcat, and memcapable commands, install a full memcached distribution. When memrm and memflush remove items from the cache, the items are also removed from the underlying
For comparison with traditional memcached, see Section 188.8.131.52.6, “libmemcached Command-Line Utilities”.
Programming interfaces: You can access the MySQL server through the
daemon_memcachedplugin using all supported languages: C and C++, Java, Perl, Python, PHP, and Ruby. Specify the server hostname and port as with a traditional memcached server. By default, the
daemon_memcachedplugin listens on port
11211. You can use both the text and binary protocols. You can customize the behavior of memcached functions at runtime. Serialization (that is, the exact string format representing complex data structures) depends on the language interface.
For comparison with traditional memcached, see Section 16.2.3, “Developing a memcached Application”.
Frequently asked questions: MySQL has an extensive FAQ for traditional memcached. The FAQ is mostly applicable, except that using
InnoDBtables as a storage medium for memcached data means that you can use memcached for more write-intensive applications than before, rather than as a read-only cache.