Documentation Home
MySQL 8.0 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 42.4Mb
PDF (A4) - 42.5Mb
Man Pages (TGZ) - 272.5Kb
Man Pages (Zip) - 383.8Kb
Info (Gzip) - 4.2Mb
Info (Zip) - 4.2Mb
Excerpts from this Manual

MySQL 8.0 Reference Manual  /  ...  /  InnoDB memcached Architecture

15.20.2 InnoDB memcached Architecture

The 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 through the daemon_memcached plugin, compared with SQL.

Figure 15.4 MySQL Server with Integrated memcached Server

Shows an application accessing data in the InnoDB storage engine using both SQL and the memcached protocol. Using SQL, the application accesses data through the MySQL Server and Handler API. Using the memcached protocol, the application bypasses the MySQL Server, accessing data through the memcached plugin and InnoDB API. The memcached plugin is comprised of the innodb_memcache interface and optional local cache.

Features of the daemon_memcached plugin:

  • 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, including the text-based protocol and the binary protocol. The daemon_memcached plugin 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 InnoDB buffer 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 get requests.

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

  • Control over 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. Batch size options default to a value of 1 for maximum reliability.

  • The ability to specify memcached options through the daemon_memcached_option configuration 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.

  • The innodb_api_trx_level configuration 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_level is set to READ UNCOMMITTED.

  • The innodb_api_enable_mdl option 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 InnoDB storage until memcached or some other user stops using it. MDL stands for metadata locking.

Differences Between InnoDB memcached and Traditional memcached

You may already be familiar with using memcached with MySQL, as described in Using MySQL with memcached. This section describes how features of the integrated InnoDB memcached plugin differ from traditional memcached.

  • Installation: The memcached library comes with the MySQL server, making installation and setup relatively easy. Installation involves running the innodb_memcached_config.sql script to create a demo_test table for memcached to use, issuing an INSTALL PLUGIN statement to enable the daemon_memcached plugin, 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 Installing memcached.

  • Deployment: With traditional memcached, it is typical to run large numbers of low-capacity memcached servers. A typical deployment of the daemon_memcached plugin, 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 InnoDB buffer 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_size configuration 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 memcached Deployment.

  • Expiry: By default (that is, using the innodb_only caching policy), 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 is updated in the underlying table before it expires from the memory cache.

    For comparison with traditional memcached, see 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_memcached plugin lets you use similar naming conventions for keys, with one addition. Key names in 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 get, add, and set functions, but not others such as incr or delete. To designate a default table for subsequent memcached operations within a session, perform a get request using the @@ notation with a table_id, but without the key portion. For example:

    get @@table_id

    Subsequent get, set, incr, delete, and other operations use the table designated by table_id in the column.

    For comparison with traditional memcached, see Using Namespaces.

  • Hashing and distribution: The default configuration, which uses the innodb_only caching policy, is suitable for a traditional deployment configuration where all data is available on all servers, such as a set of replica servers.

    If you physically divide data, as in a sharded configuration, you can split data across several machines running the daemon_memcached plugin, 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 add requests to memcached so that appropriate values are stored in the database on the appropriate server.

    For comparison with traditional memcached, see memcached Hashing/Distribution Types.

  • Memory usage: By default (with the innodb_only caching policy), the memcached protocol passes information back and forth with InnoDB tables, and the InnoDB buffer 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 caching or 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_memcached plugin using the familiar statistics system, accessed through the standard protocol, over a telnet session, for example. Extra utilities are not included with the daemon_memcached plugin. You can use the memcached-tool script to install a full memcached distribution.

    For comparison with traditional memcached, see 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 memcached Thread Support.

  • 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 output to the MySQL error log.

    For comparison with traditional memcached, see memcached Logs.

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

    For comparison with traditional memcached, see Basic memcached Operations.

  • Using memcached as a MySQL front end: This is the primary purpose of the InnoDB memcached plugin. An integrated memcached daemon improves application performance, and having InnoDB handle data transfers between memory and disk simplifies application logic.

    For comparison with traditional memcached, see Using memcached as a MySQL Caching Layer.

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

    For comparison with traditional memcached, see libmemcached Command-Line Utilities.

  • Programming interfaces: You can access the MySQL server through the daemon_memcached plugin using all supported languages: C and C++, Java, Perl, Python, and PHP. Specify the server hostname and port as with a traditional memcached server. By default, the daemon_memcached plugin 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 Developing a memcached Application.

  • Frequently asked questions: MySQL has an extensive FAQ for traditional memcached. The FAQ is mostly applicable, except that using InnoDB tables 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.

    See memcached FAQ.