Easy Read/Write Splitting with PHP’s MySQLnd

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.