Query Caching with PHP & MySQLnd

While MySQL has a query cache built in, it has several big problems:

We can solve these issues by using application-level caching, but how can we get the simplicity and ease of use of the query cache without the problems?

This is where the mysqlnd_qc plugin comes in.

Installation

The mysqlnd_qc plugin is the least stable of those we are exploring in this series, and requires you to use the alpha package for newer versions of PHP (at least 5.5+):

$ pecl install mysqlnd_qc-alpha

Storage Backends

The plugin supports multiple backends for cache storage known as storage handlers. It includes two built-in storage handlers, and three that you must compile in:

• default: per-process in-memory storage (built-in)
• user: user-defined custom storage backend (built-in)
• memcache: use memcached for storage
• sqlite: use sqlite for storage
• apc: use APC for storage — requires both mysqlnd_qc and APC be compiled statically, and may not work with apcu (for PHP 5.5+)

When you install with the PECL command, it will ask you if you wish to include APC, Memcache, or SQLite (in that order). Simply type in “yes” at the prompt to include them.

Of all the storage options, memcache is the one that I would recommend — or as we’ll explore later, you can build your own using the user handler.

To change the storage handler, you must call mysqlnd_qc_set_storage_handler(), and pass in one of the storage handlers above. For example, to use memcache (or the MySQL innodb memcache interface):

mysqlnd_qc_set_storage_handler('memcache');

You can configure the memcache connection settings using the INI configuration options mysqlnd_qc.memc_server, and mysqlnd_qc.memc_port. This can be done either in your INI files, or using ini_set():

mysqlnd_qc.memc_server = 'localhost'
mysqlnd_qc.memc_port = '11211'

or

ini_set('mysqlnd_qc.memc_server', 'localhost');
ini_set('mysqlnd_qc.memc_port', '11211');

The default is localhost, port 11211. Unfortunately, you can only specify a single memcache server, not a list of them. If you want to spread it across multiple servers then you will need to create a custom user storage handler.

Basic Usage

The mysqlnd_qc plugin allows you to transparently cache all SELECT queries that do not include dynamic columns (e.g. NOW(), or LAST_INSERT_ID()). This is done by setting the INI option mysqlnd_qc.cache_by_default to 1.

With this option enabled it acts very similar to the MySQL query cache, in that all possible SELECTs are cached, however, because it is part of the PHP process, it does not have the contention issues that the single threaded MySQL query cache has.

Cache Invalidation

Out of the box, the cache is invalided using a simple time-based mechanism. Each cache item (query) is given what is known as a TTL (Time To Live) which defaults to 30 seconds.

You can change the TTL in two ways. First, by changing the INI setting mysqlnd_qc.ttl to whatever you desire. The higher you can set this without negatively impacting your application the larger the benefit you will see from this plugin.

The second way is using a SQL hint. This is represented by the constant MYSQLND_QC_TTL_SWITCH, as you can see in the example below:

$sql = sprintf('/*%s%d*/SELECT * FROM table', MYSQLND_QC_TTL_SWITCH, 10);

This will be expanded to:

/*qc_tt=10*/SELECT *FROM table

Which will set the TTL to 10 seconds.

Opting Out of the Cache

If you do choose to enable caching by default, you can opt-out on a query-by-query basis by using another SQL hint, MYSQLND_QC_DISABLE_SWITCH:

$sql = sprintf('/*%s*/SELECT * FROM table', MYSQLND_QC_DISABLE_SWITCH);

This will be expanded to:

/*qc=off*/SELECT * FROM table

Which will turn the query cache off.

Conditional Caching

In addition to the basic options of caching everything or nothing, and using SQL hints to change this behavior, mysqlnd_qc also supports the ability to automatically include queries made against a specific database and table.

This is done using the mysqlnd_qc_set_cache_condition() function. This function accepts three arguments, the first of which is currently always MYSQLND_QC_CONDITION_META_SCHEMA_PATTERN. The second is a pattern to match against, and the last is an optional TTL. If you do not provide a TTL, the value set by the mysqlnd_qc.ttl INI option is used.

The pattern uses the same syntax as MySQL’s LIKE operator: % matches one or more characters, and _ matches a single character.

For example, we could automatically cache session data for 5 minutes:

mysqlnd_qc_set_cache_condition(MYSQLND_QC_CONDITION_META_SCHEMA_PATTERN, 'myapp.session', 5*60);

Or cache all user data for 15 seconds:

mysqlnd_qc_set_cache_condition(MYSQLND_QC_CONDITION_META_SCHEMA_PATTERN, 'myapp.user_%', 15);

Pattern Based Caching

The mysqlnd_qc plugin gets really interesting when you introduce the pattern based caching feature. This feature allows you to set a callback that will evaluate each query to determine if it can be cached, and optionally, for how long.

The callback should return one of three values:

• false: The query should not be cached
• true: The query should be cached for the default TTL as set in the INI by mysqlnd_qc.ttl
• An integer: The query should be cached for N seconds

It should be noted that returning true, or an integer, does not guarantee that the plugin will be able to cache the result set due to other factors such as non-deterministic SQL.

We can use this feature to replicate the behavior above programmatically:

function is_cacheable($sql)
{
    if (preg_match('/SELECT (.*?) FROM session (.*)/ism', $sql) === 1) {
        return 5*60;
    }

    if (preg_match('/SELECT (.*?) FROM user_(.*?) (.*)/ism', $sql) === 1) {
        return 15;
    }

    return false;
}

We then set the callback:

mysqlnd_qc_set_is_select('is_cacheable');

This becomes much more powerful, however, once we start to introduce more application logic. For example, we might choose to never cache when editing, or for admin users. Authenticated users may get a short cache TTL while unauthenticated users might get a longer one.

Custom Cache Handlers

The final feature provided by this plugin is the ability to write completely custom cache handlers. This allows you to create your own backend for managing cache storage.

For example, if you choose to use your framework’s default cache library – often this allows you to then easily switch out adapters to allow different storage backends, such as Cassandra or Riak.

To implement a user cache handler, you must define seven callback functions, or methods. While no interface is provided by PHP, it might look something like this:

namespace MySQLnd;

interface CacheHandlerInterface {
	// Get a unique hash for the query, this is the cache-key
    public function get_hash($host_info, $port, $user, $db, $query);

	// Retrieve the data from the cache
    public function find_query_in_cache($key);

	// Called each time the data is pulled from the cache
    public function return_to_cache($key);

	// Add the query to the cache
    public function add_query_to_cache_if_not_exists($key, $data, $ttl, $run_time, $store_time, $row_count);

	// Called after the query executes to help maintain stats
    public function update_query_run_time_stats($key, $run_time, $store_time);

	// Get the stats
    public function get_stats($key = null);

	// Clear the cache completely
    public function clear_cache();
}

If we were to implement this using Zend\Cache from Zend Framework 2, we would end up with something like this:

namespace MyApp\Db\MySQLnd;

use MySQLnd\CacheHandlerInterface;
class CacheHandler implements CacheHandlerInterface
{

    protected $cache;

    public function __construct(\Zend\Cache\Storage\StorageInterface $cache)
    {
        $this->cache = $cache;
    }

    public function get_hash($host_info, $port, $user, $db, $query)
    {
        return md5(sprintf('%s%s%s%s%s', $host_info, $port, $user, $db, $query));
    }

    public function find_query_in_cache($key)
    {
        if ($this->cache->hasItem($key)) {
            return $this->cache->getItem($key);
        }
        return null;
    }

    public function return_to_cache($key)
    {
        $this->cache->touch($key);
    }

    public function add_query_to_cache_if_not_exists($key, $data, $ttl, $run_time, $store_time, $row_count)
    {
        $data = array(
            'data' => $data,
            'row_count' => $row_count,
            'valid_until' => time() + $ttl,
            'hits' => 0,
            'run_time' => $run_time,
            'store_time' => $store_time,
            'cached_run_times' => array(),
            'cached_store_times' => array(),
        );

        if ($this->cache->hasData($key)) {
            return null;
        }

        $this->cache->getOptions()->setTtl($ttl);
        $this->cache->setItem($key, $data);

        return true;
    }

    public function update_query_run_time_stats($key, $run_time, $store_time)
    {
        if ($this->cache->hasKey($key . '_stats')) {
            $stats = $this->cache->getKey($key);
        } else {
            $stats = [
                'hits' => 0,
                'run_times' => [],
                'store_times' => []
            ];
        }

        $stats['hits']++;
        $stats['run_times'][] = $run_time;
        $stats['store_times'][] = $store_time;

        $this->cache->setItem($key . '_stats', $stats);

    }

    public function get_stats($key = null)
    {
        if ($key !== null && $this->cache->hasKey($key . '_stats')) {
            return $this->cache->getItem($key . '_stats');
        }

        return [];
    }

    public function clear_cache()
    {
        if ($this->cache instanceof \Zend\Cache\Storage\FlushableInterface) {
            $this->cache->flush();
            return true;
        }

        return false;
    }
}

We can then use this like so:

use MyApp\Db\MySQLnd\CacheHandler;

// Get the cache service from the service locator
$cache = $serviceLocator->getServiceLocator()->get('cache');

// Create an instance of our CacheHandler, passing in the Cache instance
$ch = new CacheHandler($cache);

// Setup the user handler
\mysqlnd_qc_set_user_handlers([$ch, 'get_hash'], [$ch, 'find_query_in_cache'], [$ch, 'return_to_cache'], [$ch, 'add_query_to_cache_if_not_exists'], [$ch, 'update_query_run_time_stats'], [$ch, 'get_stats'], [$ch, 'clear_cache']);

Once you’ve registered your user handler it will use called to perform all caching actions automatically.

Conclusion

The cache handler allows you to solve a number of problems with the MySQL query cache, as well as providing an interface for easily, and transparently, implementing caching within your application. It provides the ability to automate query caching, and supportmultiple storage backends for storing your cache, including memcached, and custom user storage.

P.S. Have you tried any of the MySQLnd plugins yet? What’s your favorite? We’d love to hear about your experience with MySQLnd and it’s plugins.