Char’s Blogophone

MySQL-centric memcached warming

Situation:

You’re using memcached to, among other things, cache information from a MySQL database.

Problem:

There are many clients who read and write to this database but only some of them use memcached. Therefore, not everyone can keep the cache sync’d when updating database values. Those that do use memcached need a current view of certain data, that is, they will react poorly to stale cache. Anndddd…. setting the cache record expirations low enough to solve the “staleness problem” defeats the benefit of caching in the first place (we’re back to lots of DB chatter).

Solution:

Install Patrick Galbraith and Brian Aker’s memcached UDFs for MySQL and set up exploitive triggers to keep the cache both warm and current… downright hot hot hot! *ksssss*

Quick HOW-TO:

What you’ll need:

The latest libmemcached.
The latest memcached functions for MySQL.
Actually, that’s it since I’m assuming you already have MySQL, memcached, some memcached clients, and the basic knowledge of how they work… otherwise you wouldn’t have my problem.

Build and install libmemcached:

This is just standard operating procedure for installing a dynamically linked library.

Build and install the memcached MySQL UDF:

If you’ve never installed a MySQL UDF before, this could require some reading. The README in the package is good but if you want an excerpt just to get it installed quickly as to get on with this how-to:

To build:
./configure \
  --with-mysql=/usr/local/mysql/bin/mysql_config \
  --libdir=/usr/local/mysql/lib/mysql/
make
make install
Please keep in mind that for your UDF to be loaded, it must be in the
library path for your server (and yes, we should fix this). On Linux you can
set this by exporting the correct path in LD_LIBRARY_PATH for your mysql
server.

Set up the basic UDFs memc_servers_set(), memc_set(), memc_get(), and memc_delete() in MySQL:

CREATE FUNCTION memc_servers_set RETURNS INT SONAME "libmemcached_functions_mysql.so";
CREATE FUNCTION memc_set RETURNS INT SONAME "libmemcached_functions_mysql.so";
CREATE FUNCTION memc_get RETURNS STRING SONAME "libmemcached_functions_mysql.so";
CREATE FUNCTION memc_delete RETURNS INT SONAME "libmemcached_functions_mysql.so";

These are the only functions you’ll need for this how-to but you might want to load them all (see the README).

Create triggers to update your cache when particular tables are updated:

So far we’ve only been doing prep. work. Now for the fun part…

For this example, I’ve set up five memcached servers on my local machine on TCP and UDP ports 11411 - 11415. Now, let’s pretend that I want to access important_data for username in the cache_test table by the memcached key “imp:<username>”. I want the cache to be refreshed any time a row is inserted or updated and I want the key removed when the related row is deleted from the database.

To create the table we’re going to play with:

CREATE TABLE cache_test (
  username VARCHAR( 32 ) NOT NULL,
  important_data VARCHAR( 255 ),
  PRIMARY KEY ( username )
) ENGINE=MyISAM;

To set the memcached servers we want to use (this only needs to be set once):

SELECT memc_servers_set( '127.0.0.1:11411,127.0.0.1:11412,127.0.0.1:11413,127.0.0.1:11414,127.0.0.1:11415' );

To create the related triggers, your user will need the TRIGGER and/or the SUPER privileges depending on the version of MySQL you’re running. You can’t create a trigger that returns a result, but there’s nothing stopping you from just storing the result somewhere else. Here are the statements to create the triggers:

DELIMITER |

CREATE TRIGGER cache_test_warmer_insert
  AFTER INSERT ON cache_test
  FOR EACH ROW BEGIN
    SET @memc_rc:= memc_set( CONCAT( "imp:", NEW.username ), NEW.important_data );
  END; |

CREATE TRIGGER cache_test_warmer_update
  AFTER UPDATE ON cache_test
  FOR EACH ROW BEGIN
    SET @memc_rc:= memc_set( CONCAT( "imp:", NEW.username ), NEW.important_data );
  END; |

CREATE TRIGGER cache_test_cooler
  AFTER DELETE ON cache_test
  FOR EACH ROW BEGIN
    SET @memc_rc:= memc_delete( CONCAT( "imp:", OLD.username ) );
  END; |

DELIMITER ;

That’s it. Let’s test it out! Let’s insert some records into the table:

mysql>
mysql> INSERT INTO cache_test ( username, important_data )
    -> VALUES
    -> ( 'zaphod', 'no ego' ),
    -> ( 'charles', 'hides money under his bed' ),
    -> ( 'zilton', 'drinks kool-aid with no sugar' );
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql>

… and then check the cache:

(caylward@barghest):~/local/libmemcached/bin$ ./memcat imp:zilton
drinks kool-aid with no sugar
(caylward@barghest):~/local/libmemcached/bin$ ./memcat imp:charles
hides money under his bed
(caylward@barghest):~/local/libmemcached/bin$

Now that hopefully you believe that this is actually working, the rest of the example will just use the MySQL client:

mysql> UPDATE cache_test SET important_data = "can't write for beans"
    -> WHERE username = 'charles';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql>
mysql> SELECT memc_get( 'imp:charles' );
+---------------------------+
| memc_get( 'imp:charles' ) |
+---------------------------+
| can't write for beans     |
+---------------------------+
1 row in set (0.00 sec)

mysql>
mysql> DELETE FROM cache_test WHERE username = 'charles';
Query OK, 1 row affected (0.00 sec)

mysql>
mysql> SELECT memc_get( 'imp:charles' );
+---------------------------+
| memc_get( 'imp:charles' ) |
+---------------------------+
| NULL                      |
+---------------------------+
1 row in set (0.00 sec)

There ya go. Piping hot memcached. :)

There are however a few caveats to all of this:

This is all basically using alpha software so normal paranoia applies here.
The list of memcached servers to use needs to be set any time the database is restarted.
Trying to change the list of memcached servers more than once should be possible but does not behave correctly (possible bug I’m looking into).

Good luck!

End of transmission.

Published by Charles Aylward on March 8th, 2008 | Filed under Databases, Performance

2 Responses to “MySQL-centric memcached warming”

rupurt Says:
September 23rd, 2008 at 19:47
How do you deal with complex keys? i.e objects that are made up of items from multiple tables? In this case where the item in cache is just from one row in a table it is easy.

Charles Aylward Says:
September 26th, 2008 at 0:36

@Rupurt,

The principle is the same. One can stick an arbitrary number of arbitrary SQL statements into a trigger so long as they don’t return rows. If one has a cached object derived from multiple tables, one can simply add a trigger (or into an existing trigger) SQL to update cache objects invalidated by the table update (example below).

I would argue however that it is generally better to handle caching as a unified strategy across applications on your network, that is, built into some shared persistence layer. If that layer/service/package/class cannot be shared, then at least the methodology is well-defined and consistent amongst applications. While one can certainly put the onus of this work on the database, I use it only when I do not have control over all of the applications accessing the database (3rd party proprietary apps, etc).

Excuse the arbitrary nature of the tables and their columns in this example, just winging it here:

Two tables, documents which have a category to which they best fit.

CREATE TABLE category (
  name VARCHAR(32) NOT NULL,
  short_description VARCHAR(100) DEFAULT NULL,
  long_description VARCHAR(100) DEFAULT NULL,
  PRIMARY KEY (name)
) ENGINE=InnoDB;

CREATE TABLE document (
  name VARCHAR(32) NOT NULL,
  category_name VARCHAR(32) NOT NULL,
  url VARCHAR(255) NOT NULL,
  PRIMARY KEY (name),
  KEY (category_name),
  CONSTRAINT document_category_name
    FOREIGN KEY (category_name) REFERENCES category (name)
    ON UPDATE CASCADE ON DELETE RESTRICT
) ENGINE=InnoDB;

And some phony entries:

INSERT INTO category VALUES
( 'Scientific', 'Scientific Publications',
  'Well founded scientific publications and white papers.  No blogs.' );
INSERT INTO category VALUES
( 'Blog', 'Web Logs',
  'Web logs of gossip, opinion pieces, and hearsay.' );
INSERT INTO document VALUES
( 'Prion Protein Gene Mutation', 'Scientific', 'http://fakeurl/sciencelib/102' );
INSERT INTO document VALUES
( 'Silicon Valley Slides Into Ocean', 'Blog', 'http://fakeurl/blog/hysteria' );

If one has some object cached that was generated from a joined query like:

SELECT d.name as document_name, c.name as document_category,
  c.long_description as category_description, d.url as document_url
  FROM document d, category c
  WHERE d.name = 'Prion Protein Gene Mutation'
    AND c.name = d.category_name

which obviously becomes invalid if say the url of the document changes, one could add to the trigger triggered when a document changes:

DELIMITER |
CREATE TRIGGER document_update
  AFTER UPDATE ON document
  FOR EACH ROW BEGIN
    SET @memc_rc:= memc_set( CONCAT( "doc_details:", NEW.name ),
      (SELECT CONCAT_WS( '|', d.name, c.name, c.long_description, d.url )
       FROM document d, category c
       WHERE d.name = NEW.name
         AND c.name = d.category_name)
    );
END; |
DELIMITER ;

The example stores the object as a tokenized string delimited by pipes but the concept is the same for other possibly saner formats. :)

You must be logged in to post a comment.

No-duh on software

MySQL-centric memcached warming

Situation:

Problem:

Solution:

Quick HOW-TO:

2 Responses to “MySQL-centric memcached warming”

Leave a Comment

Navigation Menu

Categories:

Archives:

Search:

Meta: