This section describes how the memcached daemon is integrated into the MySQL Server.
When integrated with MySQL Server, memcached is
implemented as a MySQL plugin daemon, accessing the
InnoDB storage engine directly and bypassing
the SQL layer:
Features provided in the current release:
Direct access to
InnoDBtables, bypassing the SQL parser, the optimizer, and even the Handler API layer.
Standard memcached protocols, both the text-based protocol and the binary protocol. The
InnoDB+ memcached combination passes all 55 compatibility tests from 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, you use the memcached protocol to read and write data directly to
InnoDB, and let MySQL manage the in-memory caching through the
InnoDBbuffer pool. The default settings represent the combination of high reliability with the fewest surprises for database applications. For example, the 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 default engine (memory), or use a combination of the “memcached default engine” (memory caching) and the
InnoDBmemcached engine (
InnoDBas backend persistent storage).
You can control how often data is passed back and forth between
InnoDBand memcached operations through the
daemon_memcached_w_batch_sizeconfiguration options. Both the batch size options default to a value of 1 for maximum reliability.
You can specify memcached configuration options through the MySQL configuration variable
daemon_memcached_option. For example, you might 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.
A configuration option
innodb_api_trx_levellets you control the transaction isolation level on queries processed by the memcached interface. Although memcached has no concept of transactions, you might use this property to control how soon memcached sees changes caused by SQL statements, if you issue DML statements on the same table that memcached interfaces with. By default, it is set to
Another configuration option is
innodb_api_enable_mdl. “MDL” stands for “metadata locking”. This basically locks the table from 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 MySQL layer, but will be kept in the InnoDB storage until memcached or any other user stops using it.
MySQL users might already be familiar with using
memcached along with MySQL, as described in
Section 16.3, “Using MySQL with memcached”. This section describes the
similarities and differences between the information in that
section, and when using the
features of the memcached that is built into
MySQL. The link at the start of each item goes to the associated
information about the traditional memcached
Installation: Because the memcached library comes with the MySQL server, installation and setup are straightforward. You run a SQL script to set up a table for memcached to use, issue a one-time
INSTALL PLUGINstatement to enable memcached, and add to the MySQL configuration file or startup script any desired memcached options, for example to use a different port. You might still install the regular memcached distribution to get the additional utilities such as memcp, memcat, and memcapable.
Deployment: It is typical to run large numbers of low-capacity memcached servers. Because the
InnoDB+ memcached combination has a 1:1 ratio between database and memcached servers, the typical deployment involves a smaller number of moderate or high-powered servers, machines that were already running MySQL. The benefit of this server configuration is more for improving the efficiency of each individual database server than in tapping into unused memory or distributing lookups across large numbers of servers. In the default configuration, very little memory is used for memcached, and the in-memory lookups are served from the
InnoDBbuffer pool, which automatically caches the most recently used and most frequently used data. As in 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 of the workload as possible is done in memory.
Expiry: By default (that is, with the caching policy
innodb_only), 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 was updated in the underlying table before it expires from the memory cache.
Namespaces: memcached is like a single giant directory, where to keep files from conflicting with each other you might give them elaborate names with prefixes and suffixes. The integrated
InnoDB/ memcached server lets you use these same naming conventions for keys, with one addition. Key names of 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 the
setfunctions, not the others such as
delete. To designate the default table for all subsequent memcached operations within a session, perform a
getrequest using the
@@notation and a table ID, but without the key portion. For example:
deleteand other operations use the table designated by
Hashing and distribution: The default configuration, with the caching policy
innodb_only, is suitable for the traditional deployment configuration where all data is available on all servers, such as a set of replication slave servers.
If you physically divide the data, as in a sharded configuration, you can split the data across several machines running the
InnoDBand memcached combined server, and use the traditional memcached hashing mechanism to route requests to a particular machine. On the MySQL side, typically you would let all the data be inserted by
addrequests to memcached so the appropriate values were stored in the database on the appropriate server.
These types of deployment best practices are still being codified.
Memory usage: By default (with the caching policy
innodb_only), the memcached protocol passes information back and forth with
InnoDBtables, and the fixed-size
InnoDBbuffer pool handles the in-memory lookups rather than 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 the memcached data values is allocated in terms of “slabs”. You can control the slab size and maximum memory used for memcached.
Either way, you can monitor and troubleshoot the integrated memcached daemon using the familiar statistics system, accessed through the standard protocol, for example over a telnet session. Because extra utilities are not included with the integrated daemon, to use the
memcached-toolscript, install a full memcached distribution.
Thread usage: MySQL threads and memcached threads must co-exist on the same server, so any limits imposed on threads by the operating system apply to this total number.
memcached operations: All the familiar operations such as
deleteare available. Serialization (that is, the exact string format to represent complex data structures) depends on the language interface.
Using memcached as a MySQL front end: That is what the
InnoDBintegration with memcached is all about. Putting these components together improves the performance of your application. Making
InnoDBhandle data transfers between memory and disk simplifies the logic of your application.
Utilities: The MySQL server includes the
libmemcachedlibrary but not the additional command-line utilities. To get the commands such as memcp, memcat, and memcapable commands, install a full memcached distribution. When memrm and memflush remove items from the cache, they are also removed from the underlying
Programming interfaces: You can access the MySQL server through the
InnoDBand memcached combination using the same language as always: C and C++, Java, Perl, Python, PHP, and Ruby. Specify the server hostname and port as with any other memcached server. By default, the integrated memcached server listens on the same port as usual,
11211. You can use both the text and binary protocols. You can customize the behavior of the memcached functions at runtime. Serialization (that is, the exact string format to represent complex data structures) depends on the language interface.
Frequently asked questions: MySQL has had an extensive memcached FAQ for several releases. In MySQL 5.6, the answers are largely the same, except that using
InnoDBtables as a storage medium for memcached data means that you can use this combination for more write-intensive applications than before, rather than as a read-only cache.
For a more detailed look at the workings of this feature, see Section 14.17.7, “Internals of the InnoDB memcached Plugin”.