Documentation Home
MySQL 5.7 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 33.8Mb
PDF (A4) - 34.0Mb
PDF (RPM) - 33.2Mb
EPUB - 8.5Mb
HTML Download (TGZ) - 8.2Mb
HTML Download (Zip) - 8.2Mb
HTML Download (RPM) - 7.1Mb
Eclipse Doc Plugin (TGZ) - 9.0Mb
Eclipse Doc Plugin (Zip) - 11.1Mb
Man Pages (TGZ) - 219.4Kb
Man Pages (Zip) - 322.3Kb
Info (Gzip) - 3.2Mb
Info (Zip) - 3.2Mb
Excerpts from this Manual

MySQL 5.7 Reference Manual  /  ...  /  Architecture of InnoDB and memcached Integration

14.17.2 Architecture of InnoDB and memcached Integration

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:

Architecture Diagram for MySQL Server with Integrated memcached Server

Features provided in the current release:

  • 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 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 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 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.

Differences Between Using memcached Standalone or with InnoDB

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 InnoDB integration 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 server.

  • 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 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.

  • 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.7, 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.

For a more detailed look at the workings of this feature, see Section 14.17.7, “Internals of the InnoDB memcached Plugin”.

User Comments
Sign Up Login You must be logged in to post a comment.