News Archive
PhpRiot Newsletter
Your Email Address:

More information

Not only SQL - memcache and MySQL 5.6

Note: This article was originally published at Planet PHP on 14 April 2011.
Planet PHP

This week there are two big events for the MySQL community: The O'Reilly MySQL Conference and Oracle Collaborate run by the IOUG. At these events our Engineering VP, Tomas Ulin, announced the latest milestone releases for our main products. MySQL 5.6 and MySQL Cluster 7.1 as well as our new Windows Installer. There's lots of cool stuff in there but one feature really excited me: MySQL 5.6 contains a memcache interface for accessing InnoDB tables. This means you can access data stored in MySQL not only using SQL statements but also by using a well established and known noSQL protocol.

This works by having the memcache daemon running as plugin as part of the MySQL server. This daemon can then be configured in three ways: Either

  • to do what memcached always did - use an in memory hash table to store its data - or
  • to access an InnoDB table to store and read data from or
  • to use its own hash table in memory and fall back to InnoDB if data is not found directly in memcache.

This combines the power of MySQL and InnoDB's persistent storage with the lightweight protocol memcache uses, which has faster connecting times (no authorization handshake etc.) and faster data access (no SQL parsing, optimization etc.) while you're still able to query the data using SQL when you're doing more complex operations.

Of course I had to give it a run with PHP.

First step for using this is fetching the MySQL preview release and configuring it accordingly. My colleague Jimmy Yang from the InnoDB team has a nice blog posting showing these first steps. After that we have to configure PHP where we have two choices: We can use the a bit older memcache module or the newer memcached module. I've chosen the first one as that was already configured on my system. On most systems the installation should be as easy as querying your package manager or using PECL:

# pecl install memcache or # pecl install memcached

And then adding the corresponding entry (extension=memcache[d].so) to your php.ini file.

So let's do a first test from command line:

$ php -r '$m = memcache_connect("localhost", 11211); ' \ AAAA A A '$m-add("key", "value"); var_dump($m-get("key"));' string(5) "value"

So we store a value in memcache and then load it again to see if it was stored properly. Now we verify the results directly in MySQL:

mysql SELECT * FROM demo_test WHERE c1 = 'key'; Empty set (0.00 sec)

Uh, what's wrong? - O simple: We didn't read Jimmy's article properly:

If you would like to take a look at what's in the aodemo_testa table, please remember we had batched the commits (32 ops by default) by default. So you will need to do aoread uncommitteda select to find the just inserted rows

So we can apply that knowledge and query again:

mysql set session TRANSACTION ISOLATION LEVEL read uncommitted; Query OK, 0 rows affected (0.00 sec) mysql SELECT * FROM demo_test WHERE c1 = 'key'; +------+------+------+------+-------+------+------+------+------+------+------+ | cx | cy | c1 | cz | c2 | ca | CB | c3 | cu | c4 | C5 | +------+------+------+------+-------+------+------+------+------+------+------+ | NULL | NULL | key | NULL | value | NULL | NULL | 0 | NULL | 1 | NULL | +------+------+------+------+-------+------+------+------+------+------+------+ 1 row in set (0.00 sec)

And yay! - We see our value in between the other columns for meta-data and other things.

Both PHP modules provide a session handler so you can store your session data easily in memcacheInnoDB. For configuring this we first need to add two entries to our php.ini file:

; when using the "memcache" extension: session.save_handler=memcache ; when using the "memcached" extension: ; session.save_handler=memcached session.save_path="tcp://localhost:11211"

After restarting the web server, so it reads the new configuration we can test it with a simple script:

Session ID: ".session_id()."\n"; var_dump($_SESSION); $_SESSION['foo'] = 'bar'; ?

When first requesting this we will receive an output like

m1h4iqmp6hc7e4l85qlld0gtd array(0) { }

Then we reload the page and se

Truncated by Planet PHP, read more at the original (another 1255 bytes)