Documentation Home
MySQL 5.7 Reference Manual
Related Documentation Download this Manual Excerpts from this Manual Tuning Performance of the InnoDB memcached Plugin

Because using InnoDB in combination with memcached involves writing all data to disk, whether immediately or sometime later, understand that raw performance is expected to be somewhat lower than using memcached by itself. Focus your tuning goals for the InnoDB memcached plugin on achieving higher performance than equivalent SQL operations.

Benchmarks suggest that both queries and DML operations (inserts, updates, and deletes) are faster going through the memcached interface than with traditional SQL. DML operations typically see a larger speedup. Thus, the types of applications you might adapt to use the memcached interface first are those that are write-intensive. You might also use MySQL as a data store for types of write-intensive applications that formerly used some fast, lightweight mechanism where reliability was not a priority.

Adapting SQL Queries

The types of queries that are most suited to the simple GET request style are those with a single clause, or a set of AND conditions, in the WHERE clause:

select col from tbl where key = 'key_value';

GET key_value

select col from tbl where col1 = val1 and col2 = val2 and col3 = val3;

# Since you must always know these 3 values to look up the key,
# combine them into a unique string and use that as the key
# for all ADD, SET, and GET operations.
key_value = val1 + ":" + val2 + ":" + val3
GET key_value

select 'key exists!' from tbl
  where exists (select col1 from tbl where key = 'key_value') limit 1;

# Test for existence of key by asking for its value and checking if the call succeeds,
# ignoring the value itself. For existence checking, you typically only store a very
# short value such as "1".
GET key_value

Taking Advantage of System Memory

For best performance, deploy the InnoDB memcached plugin on machines that are configured like typical database servers: in particular, with the majority of system RAM devoted to the InnoDB buffer pool through the innodb_buffer_pool_size configuration option. For systems with multi-gigabyte buffer pools, consider raising the value of the innodb_buffer_pool_instances configuration option for maximum throughput when most operations involve data already cached in memory.

Reducing Redundant I/O

InnoDB has a number of settings that let you choose the balance between high reliability in case of a crash, and the amount of I/O overhead during high write workloads. For example, consider setting the configuration options innodb_doublewrite=0 and innodb_flush_log_at_trx_commit=2. Measure the performance with different settings for the innodb_flush_method option. If the binary log is not turned on for the server, use the setting innodb_support_xa=0.


innodb_support_xa is deprecated and will be removed in a future release. As of MySQL 5.7.10, InnoDB support for two-phase commit in XA transactions is always enabled and disabling innodb_support_xa is no longer permitted.

For other ways to reduce or tune I/O for table operations, see Section 8.5.8, “Optimizing InnoDB Disk I/O”.

Reducing Transactional Overhead

The default value of 1 for the configuration options daemon_memcached_r_batch_size and daemon_memcached_w_batch_size is intended for maximum reliability of results and safety of stored or updated data.

Depending on the type of application, you might increase one or both of these settings to reduce the overhead of frequent commit operations. On a busy system, you might increase daemon_memcached_r_batch_size, knowing that changes to the data made through SQL might not become visible to memcached immediately (that is, until N more get operations were processed). When processing data where every write operation must be reliably stored, you would leave daemon_memcached_w_batch_size set to 1. You might increase it when processing large numbers of updates intended to only be used for statistical analysis, where it is not critical if the last N updates are lost in case of a crash.

For example, imagine a system that monitors traffic crossing a busy bridge, recording approximately 100,000 vehicles each day. If the application simply counts different types of vehicles to analyze traffic patterns, it might change daemon_memcached_w_batch_size from 1 to 100, reducing the I/O overhead for commit operations by 99%. In case of an unexpected outage, only a maximum of 100 records could be lost, which might be an acceptable margin of error. If instead the application was doing automated toll collection for each car, it would keep daemon_memcached_w_batch_size set to 1 to ensure that every toll record was immediately saved to disk.

Because of the way InnoDB organizes the memcached key values on disk, if you have a large number of keys to create, it can be faster to sort all the data items by the key value in your application and add them in sorted order, rather than creating them in arbitrary order.

The memslap command, which is part of the regular memcached distribution but not included with the MySQL server, can be useful for benchmarking different configurations. It can also be used to generate sample key/value pairs that you can use in your own benchmarking. See Section, “libmemcached Command-Line Utilities” for details.

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