14.2.9.2. Architecture of InnoDB and memcached Integration

memcached as a daemon plugin of mysqld: both mysqld and memcached run in the same process space, with very low latency access to data.

Direct access to InnoDB tables, 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 InnoDB buffer 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 get requests.

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 InnoDB memcached engine (InnoDB as backend persistent storage).

You can control how often data is passed back and forth between InnoDB and memcached operations through the innodb_api_bk_commit_interval, daemon_memcached_r_batch_size, and daemon_memcached_w_batch_size configuration options. Both the batch size options default to a value of 1 for maximum reliability.

You can specify any 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_level lets you control the transaction isolation level on queries processed by the memcached interface. Although memcached has 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 READ UNCOMMITTED.

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.

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 plugin statement 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 InnoDB buffer 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_size configuration 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 InnoDB table is always returned, so the expiry options have no practical effect. If you change the caching policy to caching or 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_id.key.table_id are decoded to reference a specific a table, using mapping data from the innodb_memcache.containers table. The key is looked up in or written to the specified table.

The @@ notation only works for individual calls to the get, add, and set functions, not the others such as incr or delete. To designate the default table for all subsequent memcached operations within a session, perform a get request using the @@ notation and a table ID, but without the key portion. For example:

get @@table_x

Subsequent get, set, incr, delete and other operations use the table designated by table_x in the innodb_memcache.containers.name column.

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 InnoDB and 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 add requests 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 InnoDB tables, and the fixed-size InnoDB buffer 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 caching or 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-tool script, 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.

Log usage: Because the memcached daemon is run alongside the MySQL server and writes to stderr, the -v, -vv, and -vvv options for logging write their output to the MySQL error log.

memcached operations: All the familiar operations such as get, set, add, and delete are available. Serialization (that is, the exact string format to represent complex data structures) depends on the language interface.

delete(key, [, time]): Deletes the key and its associated item from the cache.  If you supply a time, then adding another item with the specified key is blocked  for the specified period.flush_all: Invalidates (or expires) all the current items in the cache.  Technically they still exist (they are not deleted), but they are silently destroyed  the next time you try to access them.

Using memcached as a MySQL front end: That is what the InnoDB integration with memcached is all about. Putting these components together improves the performance of your application. Making InnoDB handle data transfers between memory and disk simplifies the logic of your application.

Utilities: The MySQL server includes the libmemcached library 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 InnoDB table.

Programming interfaces: You can access the MySQL server through the InnoDB and 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 InnoDB tables 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.