Consider these aspects of memcached applications when adapting an existing MySQL schema or application to use the memcached interface:

use test;

CREATE TABLE `multicol` (
  `country` varchar(128) NOT NULL DEFAULT '',
  `population` varchar(10) DEFAULT NULL,
  `area_sq_km` varchar(9) DEFAULT NULL,
  `drive_side` varchar(1) DEFAULT NULL,
  `c3` int(11) DEFAULT NULL,
  `c4` bigint(20) unsigned DEFAULT NULL,
  `c5` int(11) DEFAULT NULL,
  PRIMARY KEY (`country`),
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

insert into innodb_memcache.containers
  (name,db_schema,db_table,key_columns,value_columns,flags,cas_column,
  expire_time_column,unique_idx_name_on_key)
values
  ('bbb','test','multicol','country','population,area_sq_km,drive_side',
  'c3','c4','c5','PRIMARY');

commit;

import sys, os
import memcache

def connect_to_memcached():
  memc = memcache.Client(['127.0.0.1:11211'], debug=0);
  print "Connected to memcached."
  return memc

def banner(message):
  print
  print "=" * len(message)
  print message
  print "=" * len(message)

country_data = [
("Canada","34820000","9984670","R"),
("USA","314242000","9826675","R"),
("Ireland","6399152","84421","L"),
("UK","62262000","243610","L"),
("Mexico","113910608","1972550","R"),
("Denmark","5543453","43094","R"),
("Norway","5002942","385252","R"),
("UAE","8264070","83600","R"),
("India","1210193422","3287263","L"),
("China","1347350000","9640821","R"),
]

def switch_table(memc,table):
  key = "@@" + table
  print "Switching default table to '" + table + "' by issuing GET for '" + key + "'."
  result = memc.get(key)

def insert_country_data(memc):
  banner("Inserting initial data via memcached interface")
  for item in country_data:
    country = item[0]
    population = item[1]
    area = item[2]
    drive_side = item[3]

    key = country
    value = "|".join([population,area,drive_side])
    print "Key = " + key
    print "Value = " + value

    if memc.add(key,value):
      print "Added new key, value pair."
    else:
      print "Updating value for existing key."
      memc.set(key,value)

def query_country_data(memc):
  banner("Retrieving data for all keys (country names)")
  for item in country_data:
    key = item[0]
    result = memc.get(key)
    print "Here is the result retrieved from the database for key " + key + ":"
    print result
    (m_population, m_area, m_drive_side) = result.split("|")
    print "Unpacked population value: " + m_population
    print "Unpacked area value      : " + m_area
    print "Unpacked drive side value: " + m_drive_side

if __name__ == '__main__':

  memc = connect_to_memcached()
  switch_table(memc,"bbb")
  insert_country_data(memc)
  query_country_data(memc)

  sys.exit(0)

mysql: use innodb_memcache;
Database changed

mysql: select * from containers;
+------+-----------+-----------+-------------+----------------------------------+-------+------------+--------------------+------------------------+
| name | db_schema | db_table  | key_columns | value_columns                    | flags | cas_column | expire_time_column | unique_idx_name_on_key |
+------+-----------+-----------+-------------+----------------------------------+-------+------------+--------------------+------------------------+
| aaa  | test      | demo_test | c1          | c2                               | c3    | c4         | c5                 | PRIMARY                |
| bbb  | test      | multicol  | country     | population,area_sq_km,drive_side | c3    | c4         | c5                 | PRIMARY                |
+------+-----------+-----------+-------------+----------------------------------+-------+------------+--------------------+------------------------+
2 rows in set (0.01 sec)

mysql: use test;
Database changed

mysql: select * from multicol;
+---------+------------+------------+------------+------+------+------+
| country | population | area_sq_km | drive_side | c3   | c4   | c5   |
+---------+------------+------------+------------+------+------+------+
| Canada  | 34820000   | 9984670    | R          |    0 |   11 |    0 |
| China   | 1347350000 | 9640821    | R          |    0 |   20 |    0 |
| Denmark | 5543453    | 43094      | R          |    0 |   16 |    0 |
| India   | 1210193422 | 3287263    | L          |    0 |   19 |    0 |
| Ireland | 6399152    | 84421      | L          |    0 |   13 |    0 |
| Mexico  | 113910608  | 1972550    | R          |    0 |   15 |    0 |
| Norway  | 5002942    | 385252     | R          |    0 |   17 |    0 |
| UAE     | 8264070    | 83600      | R          |    0 |   18 |    0 |
| UK      | 62262000   | 243610     | L          |    0 |   14 |    0 |
| USA     | 314242000  | 9826675    | R          |    0 |   12 |    0 |
+---------+------------+------------+------------+------+------+------+
10 rows in set (0.00 sec)

mysql: desc multicol;
+------------+---------------------+------+-----+---------+-------+
| Field      | Type                | Null | Key | Default | Extra |
+------------+---------------------+------+-----+---------+-------+
| country    | varchar(128)        | NO   | PRI |         |       |
| population | varchar(10)         | YES  |     | NULL    |       |
| area_sq_km | varchar(9)          | YES  |     | NULL    |       |
| drive_side | varchar(1)          | YES  |     | NULL    |       |
| c3         | int(11)             | YES  |     | NULL    |       |
| c4         | bigint(20) unsigned | YES  |     | NULL    |       |
| c5         | int(11)             | YES  |     | NULL    |       |
+------------+---------------------+------+-----+---------+-------+
7 rows in set (0.01 sec)

mysql: select avg(population) from multicol where drive_side = 'R';
+-------------------+
| avg(population)   |
+-------------------+
| 261304724.7142857 |
+-------------------+
1 row in set (0.00 sec)

mysql: select sum(area_sq_km) from multicol where country like 'U%';
+-----------------+
| sum(area_sq_km) |
+-----------------+
|        10153885 |
+-----------------+
1 row in set (0.00 sec)

mysql: create view populous_countries as
  select
    country,
    cast(population as unsigned integer) population,
    cast(area_sq_km as unsigned integer) area_sq_km,
    drive_side from multicol
  order by cast(population as unsigned integer) desc
  limit 3;
Query OK, 0 rows affected (0.01 sec)

mysql: select * from populous_countries;
+---------+------------+------------+------------+
| country | population | area_sq_km | drive_side |
+---------+------------+------------+------------+
| China   | 1347350000 |    9640821 | R          |
| India   | 1210193422 |    3287263 | L          |
| USA     |  314242000 |    9826675 | R          |
+---------+------------+------------+------------+
3 rows in set (0.00 sec)

mysql: desc populous_countries;
+------------+---------------------+------+-----+---------+-------+
| Field      | Type                | Null | Key | Default | Extra |
+------------+---------------------+------+-----+---------+-------+
| country    | varchar(128)        | NO   |     |         |       |
| population | bigint(10) unsigned | YES  |     | NULL    |       |
| area_sq_km | int(9) unsigned     | YES  |     | NULL    |       |
| drive_side | varchar(1)          | YES  |     | NULL    |       |
+------------+---------------------+------+-----+---------+-------+
4 rows in set (0.02 sec)

Consider these aspects of MySQL and InnoDB tables when adapting an existing memcached application to use the MySQL integration:

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:

SQL:
select col from tbl where key = 'key_value';

memcached:
GET key_value

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

memcached:
# 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

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

memcached:
# 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.

For other ways to reduce or tune I/O for table operations, see Section 8.5.7, “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 15.6.3.3.6, “libmemcached Command-Line Utilities” for details.

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.

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:

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 "libmemcached.so";
Query OK, 0 rows affected (0.00 sec)

Benchmarks suggest that the InnoDB memcached plugin speeds up DML operations (inserts, updates, and deletes) more than it speeds up queries. You might focus your initial development efforts on write-intensive applications that are I/O-bound, and look for opportunities to use MySQL for new kinds of write-intensive applications.

You can access the InnoDB table (by default, test.demo_test) through the standard SQL interfaces. However, there are some restrictions: