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.

The post Query Caching with PHP & MySQLnd appeared first on EngineYard.

MySQL has always been the default go-to database server for pairing with PHP—it has been the go-to database since almost the inception of the language. Sure, some people use PostgreSQL, or SQL Server, or Oracle, but for the web workload, MySQL is usually the relational database of choice.

This was due mostly in part because it was so easy to get going. Libmysqlclient was bundled with PHP itself until it was re-licensed under the GPL. This change meant it was no longer possible to bundle with PHP and it was removed.

This made the compilation process for PHP slightly more difficult, requiring that libmysqlclient be available on the host system.

Given the widespread nature of PHP, and the fact it was the most popular single language to use MySQL, this wasn’t ideal for Oracle (then Sun) and so they came to an agreement: they’d build the MySQL Native Driver—a PHP licensed contribution to PHP that would allow access to MySQL without libmysqlclient.

MySQL Native Drive (mysqlnd) was added in PHP 5.3 and has been the default since PHP 5.4 (though you can still compile against libmysqlclient). It brings extra features, better performance, and better memory usage than libmysqlclient.

From the MySQL manual:

The mysqlnd library is using PHP internal C infrastructure for seamless integration into PHP. In addition, it is using PHP memory management, PHP Streams (I/O abstraction) and PHP string handling routines. The use of PHP memory management by mysqlnd allows, for example, memory savings by using read-only variables (copy on write) and makes mysqlnd apply to PHP memory limits.

Additionally it has a plugin architecture, and a number of plugins are available.

Installation

To install, just compile one of the three MySQL extensions. In each instance, do not explicitly specify the path to the libmysqlclient library.

The three libraries are:

• ext/pdo_mysql (since PHP 5.1)
• ext/mysqli (since PHP 5.0)
• ext/mysql (since PHP 2.0, deprecated since PHP 5.6)

Note: If you install ext/mysql or ext/mysqli, ext/pdo_mysql is enabled automatically.

You can select an extension by choosingone or more of the following configure flags:

• --with-mysql
• --with-mysqli
• --with-pdo-mysql

If you are using Debian, or Ubuntu, you can easily install the php5-mysqlnd package:

$ sudo apt-get install php5-mysqlnd

This will remove the libmysqlclient-based php5-mysql package, and includes all three extensions.

MySQL Native Driver Plugins

Aside from the performance benefits, the biggest benefit to mysqlnd are it’s plugins. These plugins are available via PECL, and can be installed easily using:

$ pecl install mysqlnd_<name>

The available (stable) plugins are:

• mysqlnd_memcache: Transparently translate SQL to use the MySQL 5.6 memcache-protocol compatible NoSQL daemon
• mysqlnd_ms: Easily perform read/write splitting between master and slave (ms) servers, with simple load balancing
• mysqlnd_qc: Adds a simple query cache to PHP
• mysqlnd_uh: Allows writing mysqlnd plugins in PHP

Because these plugins are for mysqlnd itself, they apply to all three extensions.

Read/Write Splitting

The most useful plugin is mysqlnd_ms, or master/slave. This plugin allows you to transparently—albeit somewhat naively—split reads and writes between different servers.

Configuration

Once you have installed using pecl, you need to configure both the php.ini, and the mysqlnd_ms configuration file.

In php.ini (or mysqlnd_ms.ini on Debian-like systems):

extension=mysqlnd_ms.so
mysqlnd_ms.enable=1
mysqlnd_ms.config_file=/path/to/mysqlnd_ms.json

Then you need to create the mysqlnd_ms.json file. This file defines the master and slave servers, as well as the read/write splitting and load balancing strategies.

How you setup the configuration is going to depend on your replication topology.

The simplest configuration file includes one master, and one slave:

{
	'appname': {
		'master': {
			'master_0': {
				'host': 'master.mysql.host',
				'port': '3306',
				'user': 'dbuser',
				'password': 'dbpassword',
				'db': 'dbname'
			}
		},
		'slave': {
			'slave_0': {
				'host': 'slave.mysql.host',
				'port': '3306'
				'user': 'dbuser',
				'password': 'dbpassword',
				'db': 'dbname'
			},
		}
	}
}

The only required setting is the host. All others are optional.

Load Balancing

Additionally, mysqlnd_ms can do simple load balancing in one of several strategies:

• random—a random slave is picked for each read query
• random once—a random slave is picked for the first read query and re-used for the remainder of the request
• round robin—a new slave is picked for *each *read query, in the order they are defined
• user—a user-specified callback determines which slave will be called for each query

It is important to understand that unless you use the last strategy and maintain state yourself, every single request will execute the load balancing strategy in isolation. So, round-robin applies to each query within the same request, and it isn’t that one server is picked per request in sequential order as you might expect of an actual hardware or software load balancer.

With that in mind, I would recommend that youdo not use the load balancing aspect of this plugin, and instead use an actual load balancer for your slaves—such as haproxy—and simply point the configuration to the load balancer as the only slave.

Routing Queries

By default mysqlnd_ms will transparently route all queries starting with SELECT to the slave servers, and anything else to the master.

This is both good and bad. Being transparent, it means zero changes to your code. But it is also simplistic, and does not analyze the query to ensure it is actually a read-only query.

Not only will it not send a query that starts with (SELECT to the master, it will also send a write query using SELECT … INTO to the slave, which could be a disaster.

Luckily, the plugin includes the ability to hint which server the query should be sent to. This is done by placing one of three SQL hint constants in the query:

• MYSQLND_MS_MASTER_SWITCH—Run the statement on the master
• MYSQLND_MS_SLAVE_SWITCH—Run the statement on the slave
• MYSQLND_MS_LAST_USED_SWITCH—Run the statement on whichever server was last used

These three constants are simple placeholders for strings, ms=master, ms=slave, and ms=last_used respectively. However these strings may change in the future and therefore the constants should be used.

To use a SQL hint, add a comment before the query with whichever one you wish to use. The easiest way to do this is to use sprintf() which will replace placeholders (in this case %s,the string placeholder) for the given arguments.

For example, to send a SELECT to the master:

$sql = sprintf('/*%s*/ SELECT * FROM table_name;', MYSQLND_MS_MASTER_SWITCH);

Or, to send a non-SELECT to a slave:

$sql = sprintf('/*%s*/ CREATE TEMPORARY TABLE `temp_table_name` SELECT * FROM table_name;', MYSQLND_MS_SLAVE_SWITCH);

The last hint will let you ensure that the same connection is used as for the previous query. This is particularly useful for ensuring that you switch to reading from the master after data has been modified but potentially not yet replicated, or when performing transactions that include both read and write statements.

if ($request->isPost() && $form->isValid()) {
	$user>setValues($form->getValues());
	$user->save();
}

$sql = sprintf('/*%s*/ SELECT * FROM user_session WHERE user_id = :user_id', MYSQLND_LAST_USED_SWITCH);

This will use the master for the query if—and only if—the master was previously used. In this case the master is used if the user data is updated (with $user->save()).

Conclusion

The mysqlnd_ms plugin is incredibly useful, especially when you want to move large legacy applications to using distributed read/write. While it’s not perfect, it should get you 80-90% of the way there for most applications without changing a single line of code.

In the second installment in this series, we’ll look at more advanced usage of the mysqlnd_ms plugin.

P.S. Have you been working with “vanilla” MySQL in your PHP app and want to try some of these methods? Share your experiences and thoughts.

The post Easy Read/Write Splitting with PHP’s MySQLnd appeared first on EngineYard.