MySQL 5.6 – InnoDB Memcached Plugin as a caching layer

http://www.mysqlperformanceblog.com/2013/03/29/mysql-5-6-innodb-memcached-plugin-as-a-caching-layer/

Friday, March 29, 2013 6:20 AMMySQL 5.6 – InnoDB Memcached Plugin as a caching layerMySQL Performance BlogMike Benshoof

A common practice to offload traffic from MySQL 5.6 is to use a caching layer to store expensive result sets or objects.  Some typical use cases include:

• Complicated query result set (search results, recent users, recent posts, etc)
• Full page output (relatively static pages)
• Full objects (user or cart object built from several queries)
• Infrequently changing data (configurations, etc)

In pseudo-code, here is the basic approach:

data = fetchCache(key)  if (data) {    return data  }  data = callExpensiveFunction(params)  storeCache(data, key)  return data

Memcached is a very popular (and proven) option used in production as a caching layer.  While very fast, one major potential shortcoming of memcached is that it is not persistent.  While a common design consideration when using a cache layer is that "data in cache may go away at any point", this can result in painful warmup time and/or costly cache stampedes.

Cache stampedes can be mitigated through application approaches (semaphores, pre-expiring and populating, etc), but those approaches are more geared towards single key expiration or eviction.  However, they can't help overall warmup time when the entire cache is cleared (think restarting a memcache node).  This is where a persistent cache can be invaluable.

Enter MySQL 5.6 with the memcached plugin…

As part of the standard MySQL 5.6 GA distribution, there is a memcached plugin included in the base plugin directory (/usr/lib64/mysql/plugin/libmemcached.so) that can be stopped and started at runtime.  In a nutshell, here is how one would start the memcached plugin:

mysql> install plugin daemon_memcached soname 'libmemcached.so';

In an effort to not re-invent the wheel, here is a link to the full documentation for setting up the plugin:

http://dev.mysql.com/doc/refman/5.6/en/innodb-memcached-setup.html

As a quick benchmark, I ran some batches of fetch and store against both a standard memcached instance and a minimally tuned MySQL 5.6 instance running the memcached plugin.  Here are some details about the test:

• Minimal hardware (vBox instances on MacBook Pro)
  • Centos 6.4
  • Single core VM
  • 528M RAM
  • Host-Only network
  • 1 Box with http/php, 1 box with memcache or mysql started
• PHP script
  • Zend framework
  • libmemcached PECL module
  • Zend_Cache_Backend_Libmemcached

Here is the rough code for this benchmark:

// Identical config/code for memcached vs InnoDB  $frontendOpts = array(    'caching' => true,    'lifetime' => 3600,    'automatic_serialization' => true  );  $memcacheOpts = array(    'servers' =>array(      array(        'host'   => '192.168.57.51',        'port'   => 11211,        'weight' => 1,      )    ),    'client' => array(      'compression' => true,    ),  );  $cache = Zend_Cache::factory('Core', 'Libmemcached', $frontendOpts, $memcacheOpts);  $timer->start();  for ($i = 0; $i < 100000; $i++) {    $cache->save(new Object(), "key_$i");  }  $totalTimeStore = $timer->stop();  $avgTimeStore = $totalTimeStore / 100000;  $timer->start();  for ($i = 0; $i < 10; $i++) {    for ($i = 0; $i < 100000; $i++) {      $obj = $cache->load("key_$i");    }  }  $totalTimeFetch = $timer->stop();  $avgTimeFetch = $totalTimeFetch / 1000000;

While this benchmark doesn't show any multi-threading or other advanced operation, it is using identical code to eliminate variation due to client libraries.  The only change between runs is on the remote server (stop/start memcached, stop/start plugin).

As expected, there is a slowdown for write operations when using the InnoDB version.  But there is also a slight increase in the average fetch time.  Here are the raw results from this test run (100,000 store operations, 1,000,000 fetch operations):

Standard Memcache:

Storing [100,000] items:

60486 ms total
0.60486 ms per/cmd
0.586 ms min per/cmd
0.805 ms max per/cmd
0.219 ms range per/cmd

Fetching [1,000,000] items:
288257 ms total
0.288257 ms per/cmd
0.2843 ms min per/cmd
0.3026 ms max per/cmd
0.0183 ms range per/cmd

InnoDB Memcache:

Storing [100,000] items:

233863 ms total
2.33863 ms per/cmd
1.449 ms min per/cmd
7.324 ms max per/cmd
5.875 ms range per/cmd

Fetching [1,000,000] items:
347181 ms total
0.347181 ms per/cmd
0.3208 ms min per/cmd
0.4159 ms max per/cmd
0.0951 ms range per/cmd

InnoDB MySQL Select (same table):

Fetching [1,000,000] items:

441573 ms total
0.441573 ms per/cmd
0.4327 ms min per/cmd
0.5129 ms max per/cmd
0.0802 ms range per/cmd

Keep in mind that the entire data set fits into the buffer pool, so there are no reads from disk.  However, there is write activity stemming from the fact that this is using InnoDB under the hood (redo logs, etc).

Based on the above numbers, here are the relative differences:

• InnoDB store operation was 280% higher (~1.73 ms/op)
• InnoDB fetch operation was 20% higher (~.06 ms/op)
• MySQL Select showed 27% increase over InnoDB fetch (~.09 ms/op)
  • This replaced $cache->load() with $db->query("SELECT * FROM memcached.container WHERE id='key_id'");
  • id is PK of the container table

While there are increases in both operations, there are some tradeoffs to consider:

• Cost of additional memcached hardware
• Cost of operations time to maintain an additional system
• Impact of warmup time to application
• Cost of disk space on database server

Now, there are definitely other NoSQL options for persistent cache out there(Redis, Couchbase, etc), but they are outside the scope of this investigation and would require different client libraries and benchmark methodology.

My goal here was to compare a transparent switch (in terms of code) and experiment with the memcache plugin.  Even the use of HandlerSocket would require coding changes (which is why it was also left out of the discussion).

The post MySQL 5.6 – InnoDB Memcached Plugin as a caching layerappeared first on MySQL Performance Blog.