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.
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.
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:
<?php $memcache = new Memcache; $memcache->connect('localhost', 11211) or die ("Could not connect"); $version = $memcache->getVersion(); echo "Server's version: ".$version."<br/>\n"; $tmp_object = new stdClass; $tmp_object->str_attr = 'This is a test'; $tmp_object->num_attr = 2112; $memcache->set('testkey', $tmp_object, false, 10) or die ("Failed to save data at the server"); echo "Store data in the cache (data will expire in 10 seconds)<br/>\n"; $get_result = $memcache->get('testkey'); echo "Data from the cache:<br/>\n"; var_dump($get_result); ?>
Example #2 - PHP Session Data
Memcached data can be saved for the duration of the session as follows:
<?php $session_save_path = "tcp://$host:$port?persistent=1&weight=2&timeout=2&retry_interval=10, ,tcp://$host:$port "; ini_set('session.save_handler', 'memcache'); ini_set('session.save_path', $session_save_path); ?>
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:
import sys import MySQLdb import memcache memc = memcache.Client(['127.0.0.1:11211'], debug=1); try: conn = MySQLdb.connect (host = "localhost", user = "robg", passwd = "password01", db = "myalbums") except MySQLdb.Error, e: print "Error %d: %s" % (e.args, e.args) sys.exit (1) favoritealbums = memc.get('top5films') if not favoritealbums: cursor = conn.cursor() cursor.execute('select album_id, artist, title from album order by no_of_listens desc limit 5') rows = cursor.fetchall() memc.set('top5albums',rows,60) print "Updated memcached with MySQL data" else: print "Loaded data from memcached" for row in favoritealbums: print "%s, %s" % (row, row)
Running the program would yield something like this:
shell> python memc_python.py Loaded data from memcached 34, Iron Maiden Powerslave 22, Rush Moving Pictures 7, Abba Abba Gold 109, Allen Lande Showdown 56, Ivory Knight Unconscience
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.
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.