Why read stale data from an asynchronous MySQL replica (slave)? Fetch it from a local cache instead! Good for the clusters overall load, good for your applications performance. And, possible with PECL/mysqlnd_ms 1.3, the replication and load balancing plugin for PHP MySQL users.

The idea is simple

Any application using asynchronous MySQL replication must be capable of handling stale results read from a slave (replica) that is lagging behind the master. The quality of service that the application needs from the database cluster is low. If an application explicitly states the minimum service quality it needs, the underlying systems can adopt to it. That’s a cool thing, because the underlying systems don’t need to do more work than necessary. In the case of a PHP MySQL user, the first underlying system is the database driver library, which is mysqlnd.

Tell mysqlnd that you can deal with data that is as old as five seconds. Then, mysqlnd can search a matching slave lagging no more than five seconds or even replace the slave access with a TTL-based cache access. Cache? Sure, PECL/mysqlnd_qc, various storage backends including main memory, user-defined, APC and Memcache … have a look at the quickstart.

It exists…

The first step is done. The development trees of PECL/mysqlnd_qc 1.1 and PECL/mysqlnd_ms 1.3 have been de-stabilized. A first, crude approach to make the query cache and replication plugin work transparently together exists.


./configure --enable-mysqlnd-ms --enable-mysqlnd-ms-cache-support


PECL/mysqlnd_ms 1.2 introduced the mysqlnd_ms_set_qos() function for setting the service level required from the cluster.

 mysqlnd_ms_set_qos($connection, 
  MYSQLND_MS_QOS_CONSISTENCY_EVENTUAL, 
  MYSQLND_MS_OPTION_CACHE, 
  5);

If you set the service level as shown above, PECL/mysqlnd_ms does never read from a slave that reports itself to be lagging more than 5 seconds behind the master. If no matching slave is found, PECL/mysqlnd_ms picks the master. Additionally, PECL/mysqlnd_ms tries to cache all slave queries for up to 5 seconds. The cache logic is so, that you never get data older than 5 seconds.

The initial cache logic

First, PECL/mysqlnd_ms asks PECL/mysqlnd_qc if the query is already in the cache. If so, PECL/mysqlnd_ms stops searching slaves, continues working and tries to fetch the results from the cache in the following. If fetching from cache fails, which should happen rarely, it reads the result from a master.

In case the query is not cached yet, PECL/mysqlnd_ms searches for all slaves that lag no more than 5 seconds. The cache TTL is reduced by the highest lag found. If, for example, there are two slaves, lagging 2 and 3 seconds behind, the cache TTL is set to 5 - max(2, 3) = 5 - 3 = 2 seconds.

Then, slave selection continues, for example, load balancing is done. At the end of the chain eventually one slave has been selected. The query is run on the slave and put into the cache for 2 seconds.

How the plugins work together

PECL/mysqlnd_ms does control the cache, PECL/mysqlnd_qc, through SQL hints. PECL/mysqlnd_ms sets exactly the same SQL hints that are also available to the user, as described in the PECL/mysqlnd_qc manual.

/*qc=on*//*qc_ttl=2*/SELECT id FROM test

… and tomorrow: a sneak preview of the new built-in pattern based caching for PECL/mysqlnd_qc 1.1.

Happy hacking!

@Ulf_Wendel