Installing memcached functions for MySQL

December 10, 2008 provide a memcached client for MySQL, in the form of UDFs. The provided set of functions allow for connecting to a memcached server, putting values in the cache, getting values, invalidating, utilizing increments etc.

The code is not (yet?) available in binary format, so the libraries need to be compiled and installed manually. Following is a quick installation HOWTO for Linux users.

I have installed on Ubuntu 8.04, running on an old IBM ThinkPad (600X) which has ~490MB and 10GB of disk space, with a 500MHz who-knows-which Intel processor. Well, that's the machine on which I experiment... The setup I've tested is a single machine setup, with a single memcached instance.


To start with, you need to have the memcached daemon installed. Easy enough:

sudo apt-get install memcached

That should install memcached and start the daemon, on the default 11211 port (see /etc/memcached.conf)


Next, memcached functions for MySQL depend on libmemcached. This one comes with RPM and SRPM builds, but I'm running on Ubuntu/Debian, which invites trouble: I've tried installing the RPM, but got into dependency hell. I thought I may as well just compile the sources. And so I've downloaded libmemcached-0.25.tar.gz, and went throught the usualy steps:

tar xzfv libmemcached-0.25.tar.gz
cd libmemcached-0.25/
sudo make install

The configure script did give me some trouble, claiming something about invalid struct padding. Running configure with

bash -x configure

has shown that I was simply missing the g++ compiler. Once installed, all went well.


We do need to have MySQL up and running, of course. Required version is 5.0 and above. But we also need to have mysql_config. This tool does not come with the standard apt-get package for debian/ubuntu. It is available in the develop package, though:

sudo apt-get install libmysqlclient-dev

RedHat and derived users can use the mysql-devel RPM. I have MySQL installed from binary tarball, so mysql_config is already there.

More dependencies

The README states you need to have the latest autoconf tools. pkg-config was required.

memcached funtions for MySQL

Finally, we get to business. Download sources for memcached functions to MySQL.

tar xzfv memcached_functions_mysql-0.7.tar.gz
cd memcached_functions_mysql-0.7/
./configure --with-mysql=/usr/local/mysql/bin/mysql_config --libdir=/usr/local/mysql/lib/mysql
sudo make install

The parameters to configure are the location of mysql_config, and the destination into which the libraries are written. Since I've installed my MySQL tarball on /usr/local/mysql, my destination is /usr/local/mysql/lib/mysql. You may wish to set this one up differently. Once the process is done, see that the libraries have indeed been created there. In particular, you're looking for

These libraries need to be found in the library search path. One way of doing so is to add the path to /etc/

sudo echo /usr/local/mysql/lib/mysql/ >> /etc/

And then update the search path

sudo ldconfig

Once this is done, we can install the functions in MySQL. Go to the memcached_functions_mysql-0.7/ path, and execute:

mysql -u root -p < sql/install_functions.sql

This file simply contains the CREATE FUNCTION statements for all supplied memcached API.


To put our installation to the test, let's try setting a value to memcached, then getting it back:

mysql> SELECT memc_set('mykey', 'Getting this with SELECT means all works well');
| memc_set('mykey', 'Getting this with SELECT means all works well') |
|                                                                  0 |
1 row in set (0.03 sec)

mysql> SELECT memc_get('mykey');
| memc_get('mykey')                             |
| Getting this with SELECT means all works well |
1 row in set (0.00 sec)

The README file contains examples for all supplied functions. Take a look at the MySQL docs, as well.


It is also nice to see that Java or Python clients are also able to read the value stored with the "mykey" key. Well, that's the nice thing about memcached: its diversity and compatibility of clients.

In a future post, I will write about if, why and how I think memcahed functions for MySQL should be used.

Please share below any insights about installing on other Linux flavours, BSD, Solaris or other operating systems.

posted in MySQL by shlomi

« | »

Follow comments via the RSS Feed | Leave a comment | Trackback URL

  • Michael

    Just a brief note on some findings using the MySQL memcached UDF's and hashing:

    If you are using MySQL as a key invalidator (e.g. a key was put into memcached from PHP / some other app and using MySQL to invalidate it on insert, etc.) and you have multiple memcached servers, the hashing algorithms are important.

    Take the [possibly old] default PHP hashing mechanism (CRC32) as an example... libmemcached's default hashing function is not CRC32 -- and the CRC32 implementation only uses the high 16 bits, as opposed to PHP's which uses all 32. If you put a key into a memcached pool using PHP, calling memc_delete() with the key within MySQL on a pool of servers, libmemcached may not find/delete the key in the target server(s) because it is hashing it differently.

    The same applies applied if using PHP's (or any other) consistent hashing. Keep in mind the use of server distribution algorithms as well....

    Point I'm trying to make -- ensure that you are using the same key hashing and server distribution algorithms across the board (if using 2+ memcached servers) or you will get [seemingly] inconsistent results between the MySQL UDF's and your app's memcached API's.

  • Michael, thanks for the info.
    Is there a best practice to choose a hashing algorithm?

  • Michael

    I don't believe there are "best practices" per sé. memcached purposely decouples the hashing options and leaves the client responsible for choosing where to store and how to distribute the data. There are some "standards" in the sense that there are known libraries/implementations which have been adopted to be the leader in consistent hashing algorithms (e.g. ketama). However, ultimately its up to you on how to distribute and hash your keys.

    As an FYI, the whole "consistent distribution" issue came up when there was a large distributed memcached installation where if you had [initially] distributed your keys using a simple modulus algorithm, when/if you took one server out of the pool the entire cache would be invalidated as the modulus algorithm now returned different results. To address that, the consistent distribution concept came along so that in the event you add or remove memcached instances to your pool you will at worst invalidate cached objects on a single instance within the pool. This is because the consistent distribution algorithm says that key X is consistently assigned to server Y. When server Y is removed from the pool, the consistent hashing algorithm reassigns it to another server. When you add a server to the pool, key X will still [consistently] hash to server Y.

  • Pingback: » Blog Archive » Using memcached functions for MySQL; an automated alternative to Query Cache()

  • Pete

    I am trying to install the memcache functions againt the packaged mysql-server but no matter what I do it always ends up saying it cannot load the library file when I try to install the functions in the database. I think I must have libdir wrong any ideas what the value should be?

  • @Pete
    Try running
    ldconfig --verbose
    to see the current search path. Either you put the libraries in one of the listed paths, or you create a sym link, or just add you path to /etc/


  • SailorCTO

    Nice post. I ran through all the install steps using YUM on my Fedora 10 machine and everything went smoothly, including the loading of UDFs.

    I can
    SELECT mem_setc('mykey', 'Some data here');

    ...however...when I try to retrieve it with:

    SELECT mem_getc('mysql');

    it finds the key, but null instead of 'Some data here'.

    Fedora 10 - kernel
    MySQL 5.0.84

    Did I miss something?

  • @SailorCTO

    Thanks for posting. I'm unable at this moment to check this on my current setup, sorry 🙁
    Best if you could ping the appropriate forums, or even poke Patrick Galbraith 🙂


  • That's pretty slick. I like it. I made a UDF too that enables JavaScript to evaluate on MySQL. It could be helpful if you are into UDFs:

  • Martin

    Thx for this great tutorial, I managed finally to compile and install everything on a debian lenny system.

    Just the last step doesn't work, when I try

    mysql -u root -p < sql/install_functions.sql

    mysql replies that the database is missing and when I add a --database=xxx the mysql replies 'ERROR 1126 (HY000) at line 1: Can't open shared library '' (errno: 22 /usr/lib/mysql/ undefined symbol: memcached_string_append)'

    Maybe I will find a way, already checked the rights etc.

    Some day it will work 🙂

  • @Martin,
    Joy of open source: a *lot* of compilations and complications. I'll be happy if you post another comments once you find the solution.


  • Pingback: Memcached Functions || libmemcached « Yref2's Blog()

  • Paul Thexton

    Mysql is looking for the .so file in it's specific plugin_dir, instead of relying on ld to be able to find the correct .so file.

    In a mysql cli, run the command
    show variables like 'plugin_dir'

    Use the dir in the query result as your --libdir

Powered by Wordpress and MySQL. Theme by