188.8.131.52 Controlling Transactional Behavior of the InnoDB memcached Plugin
Unlike traditional memcached, the
daemon_memcached plugin allows you to control durability of data values produced through calls to
incr, and so on. By default, data written through the memcached interface is stored to disk, and calls to
get return the most recent value from disk. Although the default behavior does not offer the best possible raw performance, it is still fast compared to the SQL interface for
As you gain experience using the
daemon_memcached plugin, you can consider relaxing durability settings for non-critical classes of data, at the risk of losing some updated values in the event of an outage, or returning data that is slightly out-of-date.
One tradeoff between durability and raw performance is how frequently new and changed data is committed. If data is critical, is should be committed immediately so that it is safe in case of a crash or outage. If data is less critical, such as counters that are reset after a crash or logging data that you can afford to lose, you might prefer higher raw throughput that is available with less frequent commits.
When a memcached operation inserts, updates, or deletes data in the underlying
InnoDB table, the change might be committed to the
InnoDB table instantly (if
daemon_memcached_w_batch_size=1) or some time later (if the
daemon_memcached_w_batch_size value is greater than 1). In either case, the change cannot be rolled back. If you increase the value of
daemon_memcached_w_batch_size to avoid high I/O overhead during busy times, commits could become infrequent when the workload decreases. As a safety measure, a background thread automatically commits changes made through the memcached API at regular intervals. The interval is controlled by the
innodb_api_bk_commit_interval configuration option, which has a default setting of
When a memcached operation inserts or updates data in the underlying
InnoDB table, the changed data is immediately visible to other memcached requests because the new value remains in the memory cache, even if it is not yet committed on the MySQL side.
When a memcached operation such as
incr causes a query or DML operation on the underlying
InnoDB table, you can control whether the operation sees the very latest data written to the table, only data that has been committed, or other variations of transaction isolation level. Use the
innodb_api_trx_level configuration option to control this feature. The numeric values specified for this option correspond to isolation levels such as
REPEATABLE READ. See the description of the
innodb_api_trx_level option for information about other settings.
A strict isolation level ensures that data you retrieve is not rolled back or changed suddenly causing subsequent queries to return different values. However, strict isolation levels require greater locking overhead, which can cause waits. For a NoSQL-style application that does not use long-running transactions, you can typically use the default isolation level or switch to a less strict isolation level.
innodb_api_disable_rowlock option can be used to disable row locks when memcached requests through the
daemon_memcached plugin cause DML operations. By default,
innodb_api_disable_rowlock is set to
OFF which means that memcached requests row locks for
set operations. When
innodb_api_disable_rowlock is set to
ON, memcached requests a table lock instead of row locks.
innodb_api_disable_rowlock option is not dynamic. It must be specified at startup on the mysqld command line or entered in a MySQL configuration file.
By default, you can perform DDL operations such as
ALTER TABLE on tables used by the
daemon_memcached plugin. To avoid potential slowdowns when these tables are used for high-throughput applications, disable DDL operations on these tables by enabling
innodb_api_enable_mdl at startup. This option is less appropriate when accessing the same tables through both memcached and SQL, because it blocks
CREATE INDEX statements on the tables, which could be important for running reporting queries.
innodb_memcache.cache_policies table specifies whether to store data written through the memcached interface to disk (
innodb_only, the default); in memory only, as with traditional memcached (
cache_only); or both (
caching setting, if memcached cannot find a key in memory, it searches for the value in an
InnoDB table. Values returned from
get calls under the
caching setting could be out-of-date if the values were updated on disk in the
InnoDB table but are not yet expired from the memory cache.
The caching policy can be set independently for
For example, you might allow
set operations to query or update a table and the memcached memory cache at the same time (using the
caching setting), while making
flush, or both operate only on the in-memory copy (using the
cache_only setting). That way, deleting or flushing an item only expires the item from the cache, and the latest value is returned from the
InnoDB table the next time the item is requested.
mysql> SELECT * FROM innodb_memcache.cache_policies; +--------------+-------------+-------------+---------------+--------------+ | policy_name | get_policy | set_policy | delete_policy | flush_policy | +--------------+-------------+-------------+---------------+--------------+ | cache_policy | innodb_only | innodb_only | innodb_only | innodb_only | +--------------+-------------+-------------+---------------+--------------+ mysql> UPDATE innodb_memcache.cache_policies SET set_policy = 'caching' WHERE policy_name = 'cache_policy';
innodb_memcache.cache_policies values are only read at startup. After changing values in this table, uninstall and reinstall the
daemon_memcached plugin to ensure that changes take effect.
mysql> UNINSTALL PLUGIN daemon_memcached; mysql> INSTALL PLUGIN daemon_memcached soname "libmemcached.so";