MySQL server now includes embedded memcached for performance

Written by David Mytton

The latest MySQL release (5.6.6) integrates the memcached daemon into the core server which allows you to use the memcached protocol to query InnoDB tables. This is interesting because it means you can store all your data in one database – MySQL – and structure the data for analysis using SQL, but configure options to allow queries to get the performance benefits of having data stored in memcached. Queries against memcached avoid the query parsing and planning overheads of SQL but still gives you the crash and corruption durability guarantees that InnoDB offers.

Perhaps the biggest benefit is the ability to do fast key lookups in some situations but use the same data store to do more advanced SQL based analysis. With many new NoSQL products, there is usually a tradeoff between these – key/value stores are not designed for large scale analysis and equally, big data products are more designed towards long running map/reduce style jobs rather than fast key lookups.

This hasn’t been released as a stable package as yet but there is already fairly detailed documentation on the plugin. Some interesting excerpts include:

Running memcached in the same process space as the MySQL server avoids the network overhead of passing requests back and forth (source).
The transfer between memory and disk is handled automatically, simplifying application logic (source).
Multi-column support: you can map multiple columns into the “value” part of the key/value store, with column values delimited by a user-specified separator character (source).
You can configure the system as a traditional memcached server, with all data cached only in memory, or use a combination of memcached caching and InnoDB persistent storage (source).

The Percona distribution of MySQL has had similar functionality for a while through the HandlerSocket plugin but now this is in core MySQL, it will mean that many benefits of NoSQL key/value stores are moot, at least where they exist as caching systems designed to be a layer in front of MySQL.

Of course, this just reduces the need for one particular use case of key/value stores. In many cases, they are used where the durability of the likes of InnoDB isn’t necessary but the ability to link traditional SQL tables directly into a caching layer is compelling, particularly for legacy applications already using MySQL.

Having read this far, why not subscribe to our RSS feed or follow us on Twitter?

Enjoy this post? You may also like MongoDB Benchmarks

http://twitter.com/bvansomeren Barry van Someren (@bvansomeren)

While not forgetting all the work that is being done by the community, I must say that Oracle kept their word on improving MySQL; It’s gotten a lot better over the years.
G Dixon

I think this is going to be a phenomenal combination. Imagine all the operational and hardware expenses it will save if we don’t have to spend extra $$ on unnecessary cache technologies just to make a badly written application run faster.
http://www.wearemotive.com Israel Shirk

That’s pretty awesome. Is it just a drop-in replacement for MySQL’s older query caching implementation, or do they run side-by-side?
- http://blog.serverdensity.com David Mytton
  
  It’s not a replacement for the query caching – it runs alongside it so you choose whether to query using Memcached or SQL. It’s a different use case for caching since SQL isn’t used at all.
http://twitter.com/scalabl3 Scalabl3 (@scalabl3)

I don’t think that this plugin would avoid the typical scalability issues of MySQL implementations which involve vertical scaling and sharding data. SQL Replication isn’t fast enough to keep up with HA applications and maintain consistency across all slaves in a timely fashion. It certainly doesn’t invalidate NoSQL by providing K/V storage. That is only one aspect of NoSQL, the other is scalability [partionability] (and not all NoSQL are the same in this regard).

Couchbase (NoSQL) is unique in that it is a drop-in replacement for memcached that scales horizontally and the upcoming 2.0 release provides map/reduction with incremental indexing. Couchbase was an evolution of Membase Server (memcached) and CouchDB, but is a different new product made by the founders of both. It it much more than a memcache replacement, but of course that’s an easy start for many developers. For offline BigData analytics you can use the Hadoop Connector, and in the near future with 2.0 includes full text search via Elastic Search.