Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MySQL

Posted August 5, 2013

Using the InnoDB Memcached Plugin with MySQL 5.6

By Rob Gravelle

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:

 <?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[0], e.args[1])
 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[0], row[1])

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

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.

See all articles by Rob Gravelle



MySQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MySQL Forum
Topic By Replies Updated
MySQL in high availability and transction secure banking application klamor 1 August 28th, 10:24 AM
MySQL rollback UAL225 1 August 28th, 10:15 AM
Browsing a DB file that uses MySql finleytech 1 July 26th, 10:51 AM
php cookie won't pass variable to next page ITdevGirl 0 June 22nd, 12:13 PM


















Thanks for your registration, follow us on our social networks to keep up-to-date