Ulf Wendel

PECL/mysqlnd_qc: table pattern based query caching

Cache all queries which match a schema pattern is one of the few visible feature additions to PECL/mysqlnd_qc 1.1, the client-side query cache plugin for the mysqlnd library. As usual, this client-side cache is mostly transparent, works with all PHP MySQL APIs (mysql, mysqli, PDO_MySQL) and, of course, supports various storage backends including Memcache, process memory, SQLite, user-defined and more. Please, find details in the quickstart.

Setting a cache condition

Caching only selected queries is something that could be done in 1.0 already, for example, using a callback. What’s new is that filtering is built-in to 1.1. The new feature is straigth forward to use.


/* cached, TTL = 1s */
$link->query("SELECT id, title FROM oldnews");

/* uncached */
$link->query("SELECT id, title FROM hotnews");

Internally, at the C level, we take an optimistic approach to caching. If cache conditions have been set, we start the internal cache logic for every query. Regardless if it contains a SQL hint to enable caching or not, regardless whether it is a SELECT statement or not. After the query has been processed by the MySQL server, the server send the result set to the client. The result set contains of the actual data and meta data. Then, we compare the database and table names from the meta data with the list of cache conditions set. If they match we cache the query. If they don’t match we drop the recorded wire protocol data and do not put it into the cache.

Other enhancements

The proposed API allows for potential future additions. For example, we may decide to support conditions which define a run time criteria. If a statement exceeds a certain run time, we cache it. Or, a size criteria. Let us know what you need.

However, this is not the main focus of the 1.1 release. The primary goal is to make PECL/mysqlnd_ms and PECL/mysqlnd_qc work together in a way that MySQL Replication slave reads can be transparently replaced with cache accesses, if the application allows for it (more on the idea). I got it working on my computer but that’s another story…

Happy hacking!

@Ulf_Wendel Follow me on Twitter

Comments are closed.