Featured Database Articles

WEBINAR:On-Demand

Using the InnoDB Memcached Plugin with MySQL 5.6

A lot of work has gone into making MySQL 5.6 faster than its predecessors. In my recent New Query Optimizer Features in MySQL 5.6 article I covered one particular optimization to the processing of subqueries. Another improvement comes in the form of the memcached plugin for InnoDB. It uses a daemon that automatically stores and retrieves data from InnoDB tables, without the overhead of SQL. When used in conjunction with the Query Cache, latency is reduced while throughput is increased. In today's article, we'll be taking a look at some of the uses and benefits offered by the new MySQL 5.6 memcached plugin.

More on Memcached

Although new for MySQL, memcached is not a recent development. It was originally developed by Brad Fitzpatrick for the LiveJournal project back in 2003. His intention was to create a distributed memory object caching system for speeding up dynamic web applications. It alleviates the load on the database by caching both text and serializable object data in memory using a key-value lookup scheme.

Installation

Sorry Windows users, the memcached Daemon Plugin is only supported on Linux, Solaris, and OS X platforms at this time.

You must have libevent installed, since it is required by memcached. The libevent library is not installed for you by the MySQL installer, so you should download and install it before setting up the memcached plugin. Make sure that it's version 1.4.3 or later.

You can build from source or use a MySQL installer. I'll go over the latter here. For instructions on building from source, refer to the MySQL docs.

The memcached installation created by the MySQL installer includes two libraries for memcached and the InnoDB plugin for memcached. They are lib/plugin/libmemcached.so and lib/plugin/innodb_engine.so.

Once the installation is complete, run the configuration script, scripts/innodb_memcached_config.sql, to install the necessary tables used by memcached behind the scenes:

mysql: source MYSQL_HOME/share/innodb_memcached_config.sql

The memcached plugin will reside in the base plugin directory (/usr/lib64/mysql/plugin/libmemcached.so) that can be stopped and started at runtime. To activate the daemon plugin, use the install plugin statement:

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

Testing the Interface

It is possible to connect directly and issue some command using a utility like telnet:

$ telnet localhost 11211
Trying 127.0.0.1...
Connected to localhost.
Escape character is '^]'.
set mykey 0 0 10
Test|Value
STORED
The set command tells memcached that we want to store a value.
"mykey" is the key to store it under.
The first 0 is the flags to use
The second 0 is the expiration TTL
The 10 tells it the length of the string that we're going to store.
"Test|Value" is the value to store.
get a11
VALUE a11 0 10
Test|Value
END
quit

Normally, the memcached data would be lost when you restart the server, so you would have to rely on application logic to load the data back into memory when memcached was restarted. In MySQL this process is automated by the memcached integration. All you have to do is run the install plugin statement to start the daemon_memcached plugin again.

More Storage Commands

Here is a more comprehensive list of storage commands that you'll use the most:

set: Stores the given data.

add: Stores the given data if it does not already exist for the given key.

replace: Stores the given data if it already exists for the given key.

append: Adds the given data after the existing data for the given key.

prepend: Adds the given data before the existing data for the given key.

cas (Check and Set): Stores this data if no other connections have changed it since I last fetched it.

get: Gets the data for the given key.

gets: Gets the data, and includes a unique id for use with cas.

incr: Increments the value for the given key.

decr: Decrements the value for the given key.

delete: Deletes the given key and its data.

Code Samples

Since you'll be issuing memcached commands from your application code it's only fitting to demonstrate how to do that in a couple of different languages. The first two samples are of PHP code, while the last one is Python.

Example #1 - PHP

PHP requires that some configuration options be set to use memcached. These are located in the /etc/php.d/memcache.ini file:

; -- -- - Options to use the memcached session handler
; Use memcached as a session handler
session.save_handler=memcache
; Defines a comma separated of server urls to use for session storage
session.save_path="tcp://localhost:11211"

The following code snippet demonstrates how objects and other non-scalar data types must be serializable. In this case, the object contains a string and a numeric property type. The object is first saved to the cache using the set command and then retrieved using get:

Example #3 - Python

Here's an example of Python code that retrieves favorite albums by number of listens. Python is nice to use because it automatically serializes data using cPickle/pickle. Then, when you load the data back from memcached, you can use the object directly:

Usage Tips

Memcached keys must be unique, so make sure your database schema makes good use of primary keys and unique constraints.

If you are combining multiple char column values into a single memcached item value, be careful that the separator that you use does not appear in the column values! If there is any doubt whatsoever, a common solution is to escape "actual" occurrences of the character and remove the escape character when fetching the data. An example would be adding a second quote to double quotes (""), as done in Visual Basic.

The queries that best lend themselves to memcached lookups are those that feature a single WHERE clause, using an = or IN operator. Memcached doesn't work as well with WHERE clauses that contain the <, >, BETWEEN, or LIKE operators because it can't easily scan through the keys or associated values. For that reason, it's usually better to run those queries on the database every time.

Conclusion

Memcached is a viable option for companies and individuals wishing to speed up execution of their online MySQL-backed applications. The challenge is that it's a solution that overlaps both database and application tiers. Therefore, unless you are multi-talented, you may have to enlist the services of someone who understands both.

Advertiser Disclosure:
Some of the products that appear on this site are from companies from which QuinStreet receives compensation. This compensation may impact how and where products appear on this site including, for example, the order in which they appear. QuinStreet does not include all companies or all types of products available in the marketplace.