Documentation Home
MySQL 5.7 Reference Manual
Related Documentation Download this Manual Excerpts from this Manual

MySQL 5.7 Reference Manual  /  ...  /  Controlling Transactional Behavior of the InnoDB memcached Plugin Controlling Transactional Behavior of the InnoDB memcached Plugin

Unlike with the traditional memcached, with the InnoDB + memcached combination you can control how durable are the data values produced through calls to add, set, incr, and so on. Because MySQL places a high priority on durability and consistency of data, by default all data written through the memcached interface is always stored to disk, and calls to get always return the most recent value from disk. Although this default setting does not give the highest possible raw performance, it is still very fast compared to the traditional SQL interface for InnoDB tables.

As you gain experience with this feature, you can make the decision to relax the durability settings for non-critical classes of data, at the risk of possibly losing some updated values in case of an outage, or returning data that is slightly out-of-date.

Frequency of Commits

One tradeoff between durability and raw performance is how frequently new and changed data is committed. If the data is critical, you want it to be committed immediately so that it is safe in case of any crash or outage. If the data is less critical, such as counters that would be reset after a crash, or debugging or logging data where you could afford to lose a few seconds worth, you might prefer the higher raw throughput that comes with less frequent commits.

When a memcached operation causes an insert, update, or delete in the underlying InnoDB table, that change might be committed to the underlying table instantly (if daemon_memcached_w_batch_size=1) or some time later (if that configuration option 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=1 to avoid high I/O overhead during busy times, commits could become very 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, and by default is 5 seconds.

When a memcached operation causes an insert or update 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 committed yet on the MySQL side.

Transaction Isolation

When a memcached operation such as get or incr causes a query or DML operation in the underlying InnoDB table, you can control whether it sees the very latest data written to the table, only data that has been committed, or other variations of transaction isolation level. You control this feature through the innodb_api_trx_level configuration option. The numeric values specified with this option correspond to the familiar isolation level names such as REPEATABLE READ. See the description of the innodb_api_trx_level option for the full list.

The stricter the isolation level, the more certain you can be that the data you retrieve will not be rolled back or changed suddenly so that a subsequent query sees a different value. But that strictness comes with greater locking overhead that can cause waits. For a NoSQL-style application that does not use long-running transactions, you can typically stay with the default isolation level or switch to a less strict one.

Disabling Row Locks for memcached DML Operations

The innodb_api_disable_rowlock option can be used to disable row locks when InnoDB memcached performs DML operations. By default, innodb_api_disable_rowlock is set to OFF which means that memcached requests row locks for get and set operations. When innodb_api_disable_rowlock is set to ON, memcached requests a table lock instead of row locks.

The innodb_api_disable_rowlock option is not dynamic. It must be specified at startup on the mysqld command line or entered in the MySQL configuration file.

Allowing or Disallowing DDL

By default, you can perform DDL operations such as ALTER TABLE on the tables being used by the InnoDB memcached plugin. To avoid potential slowdowns when these tables are being used for high-throughput applications, you can disable DDL operations on these tables by turning on the innodb_api_enable_mdl configuration option at startup. This option is less appropriate when you are accessing the same underlying tables through both the memcached interface and SQL, because it blocks CREATE INDEX statements on the tables, which could be important for configuring the system to run reporting queries.

Data Stored on Disk, in Memory, or Both

Table innodb_memcache.cache_policies specifies whether to store data written through the memcached on disk (innodb_only, the default); to store the data in memory only, as in the traditional memcached (cache-only); or both (caching).

With the 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 they were updated on disk in the InnoDB table but not yet expired from the memory cache.

The caching policy can be set independently for get, set (including incr and decr), delete, and flush operations. For example:

  • You might allow get and set operations to query or update a table and the memcached memory cache at the same time (through the caching setting), while making delete, flush, or both operate only on the in-memory copy (through the cache_only setting). That way, deleting or flushing an item just expires it from the cache, and the latest value is returned from the InnoDB table the next time the item is requested.

mysql> desc innodb_memcache.cache_policies;
| Field         | Type                                                  | Null | Key | Default | Extra |
| policy_name   | varchar(40)                                           | NO   | PRI | NULL    |       |
| get_policy    | enum('innodb_only','cache_only','caching','disabled') | NO   |     | NULL    |       |
| set_policy    | enum('innodb_only','cache_only','caching','disabled') | NO   |     | NULL    |       |
| delete_policy | enum('innodb_only','cache_only','caching','disabled') | NO   |     | NULL    |       |
| flush_policy  | enum('innodb_only','cache_only','caching','disabled') | NO   |     | NULL    |       |

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';

The cache_policies values are only read at startup, and are tightly integrated with the operation of the memcached plugin. After changing any of the values in this table, uninstall the plugin and reinstall it:

mysql> uninstall plugin daemon_memcached;
Query OK, 0 rows affected (2.00 sec)
mysql> install plugin daemon_memcached soname "";
Query OK, 0 rows affected (0.00 sec)

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