MySQL 5.6 Reference Manual Including MySQL NDB Cluster 7.3-7.4 Reference Guide
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.
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”.
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 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 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_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 Section 16.2.2.2, “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 Section 16.2.2.4, “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
, but
without the key portion. For example:
table_id
get @@table_id
Subsequent get
, set
,
incr
, delete
, and
other operations use the table designated by
in
the table_id
innodb_memcache.containers.name
column.
For comparison with traditional memcached, see Section 16.2.2.3, “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 Section 16.2.2.5, “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 Section 16.2.2.7, “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 16.2.2.8, “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 Section 16.2.2.9, “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 Section 16.2.3.1, “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 Section 16.2.3.2, “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 Section 16.2.3.3.6, “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,
PHP, and
Ruby.
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 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 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.