Recently I was asked if PECL/mysqlnd_ms should be used to add MySQL replication support to a yet to be developed PHP application. The mysqlnd plugin, which supports all PHP MySQL extensions (PDO, mysqli, mysql), stood up against a classical, simple, proven and fast approach: one connection for reads, one connection for writes. Let’s compare. This is a bit of an unfair challenge, because PECL/mysqlnd_ms was designed as a drop-in for existing applications, not optimized for those starting from scratch, *yell*… The plugin stands up quite well, anyway!

The classical pattern

If starting from scratch you can manually direct all reads to the slaves and all writes to the masters. Use a factory/singleton to create database objects. For database read requests, ask the factory for connection object to a slave. For writes, ask the factory for a connection object to a master. Once the application has requested a connection to a master, always return that master connection, even for reads. It is possible that the application has just written data and wants to read it now. Because MySQL replication slaves can lag behind the master, your application should execute all reads for the rest of the request on the master.

class DBCreator {
  static private $connection;
  static private $what;

  static public function getConnection($what) { 
   if ("read" == $what) {
    if (is_null(self::$connection)) {
     self::$connection = new mysqli("slave", "user", "pass");
     self::$what = "read";
    }
   } else {
    if (is_null(self::$connection) || ("read" == self::$what)) {
     self::$connection = new mysqli("master", "user", "pass");
     self::$what = "write";
    } 
   }
   return self::$connection;
  }
}

Using is straight forward.

$mysqli = DBCreator::getConnection("read");
$mysqli->query("SELECT id, title, short_desc FROM latest_products ORDER BY id DESC");
/* ... */
$mysqli = DBCreator::getConnection("write");
$mysqli->query("INSERT INTO something (id, value) VALUES (1, 'silly example')");
/* goes to the master */
$mysqli = DBCreator::getConnection("read");
$mysqli->query("SELECT id, value FROM something ORDER BY id DESC");

The plugin

Now, the mysqlnd plugin. Instead of creating and deploying a DBCreator class, you install the PECL/mysqlnd_ms extension. Very different, but if you start from scratch it makes no real difference. At the end of the day it may not make much of a difference to either deploy an extension or some library code, because you have automated your deployment anyway. Also, C versus PHP didn’t matter much – at least to the person who asked.

Classic pattern vs. plugin, deployment – 1:1

Fast forward to the code you have to write to do the same as above, if using the plugin.

$mysqli = new mysqli("reply_cluster_config_name", "user", "password", "db");
$mysqli->query("SELECT id, title, short_desc FROM latest_products ORDER BY id DESC");
/* ... */
$mysqli->query("INSERT INTO something (id, value) VALUES (1, 'silly example')");
/* goes to the master if master_on_write configured */
$mysqli->query("SELECT id, value FROM something ORDER BY id DESC");

Obviously the code is a little shorter, a little easier. And, if not starting from scratch but looking for a drop-in solution requiring no code changes, hard to beat. The plugin integrated read-write splitter is basic but could handle this case. All statements which start with SELECT will be executed on a slave. If enabling master_on_write, all read statements will go to the master after the first write. Lazy connections are a plugin default and make sure that no connection is opened before query execution.

Classic pattern vs. plugin, most basic example – 1:2

A complex example

The shorter-easier-better code argument can be flipped around, if using a more complex example. The complex example calls a stored procedures and uses the good, old session variable trick to obtains the value of its OUT parameter. Classic solution first.

$mysqli = DBCreator::getConnection("read");
$mysqli->query("CALL p(@myvar)");
$mysqli->query("SELECT @myvar AS _outparam");

Plugin next. Plugin seems to loose this time. The CALL statement does not begin with SELECT and thus the plugin will execute it on a master, unless you enforce use of a slave with a SQL hint. The SELECT goes to the slave. If not using a SQL hint for CALL, the session variable @myvar will be available on the master and the SELECT fails. Strictly speaking the example lacks another SQL hint which would make it even more ugly. Imagine you would have more than one slave and you had misconfigured the plugins build-in load balancing to allow server switches between CALL and SELECT. In that case you must use the MYSQLND_MS_LAST_USED_SWITCH.

$mysqli = new mysqli("reply_cluster_config_name", "user", "password", "db");
$mysqli->query(sprintf("/*%s*/CALL p(@myvar)", MYSQLND_MS_SLAVE_SWITCH));
$mysqli->query("SELECT @myvar AS _outparam");

Classic pattern vs. plugin, complex example – 2:2? Wait! The nasty SQL hints would be history, if there was a function to mark a connection as read-only. Similar to MySQL Connector/J.

$mysqli = new mysqli("reply_cluster_config_name", "user", "password", "db");
mysqlnd_ms_connection_set_readonly($mysqli, true);
$mysqli->query("CALL p(@myvar)");
$mysqli->query("SELECT @myvar AS _outparam");

We will implement such a function after the release of PECL/mysqlnd_ms 1.1.0-beta scheduled for Thursday. But as of today, it does not exist. Classic gets the point.

Classic pattern vs. plugin, complex example – 2:2

Anybody using the plugin?

At this point the person who asked what to use had only time for one more question before he had to leave. Is there anybody using the plugin in production? When asked I couldn’t point to any references. Andrey just shared that there is a US sports news site running 1.1.0-svn. I had not heard back from them after fixing a bug in 1.0.1-alpha and 1.1.0-svn quite some time ago… will ask them if, we can share more details.

At this point the conversation ended. Two versus two, proven versus new technology. Easy choice for the person who had asked. Maybe it was a bit early to leave the discussion. Let’s look at an edge case: a developer making a mistake.

The human factor

The developers mistake is to forget about the state of the read- and the write connection. The read connection is set to use the database otherdb and charset latin1. The write connection is using the default database and the default charset, whatever they may be. The SQL statement SELECT id, title, short_desc FROM latest_products ORDER BY id DESC is once executed against otherdb on the read connection and once against the default database on the write connection. This may or may not give an error. In any case, it is likely not what the developer had in mind.

$mysqli = DBCreator::getConnection("read");
$mysqli->select_db("otherdb");
$mysqli->set_charset("latin1");
$mysqli->query("SELECT id, title, short_desc FROM latest_products ORDER BY id DESC");
/* ... */
$mysqli = DBCreator::getConnection("write");
$mysqli->query("INSERT INTO something (id, value) VALUES (1, 'silly example')");
/* goes to the master */
$mysqli = DBCreator::getConnection("read");
$mysqli->query("SELECT id, value FROM something ORDER BY id DESC");
$mysqli->query("SELECT id, title, short_desc FROM latest_products ORDER BY id DESC");

The plugin documentation has an in-depth discussion of what makes the state of a connection and which pitfalls exist. The documentation is not so long because the plugin does so bad and is so hard to use. Rather the opposite…

Some state changing API calls, such as select_db() and set_charset() are monitored by the plugin and broadcasted on all open connections. When the mysqlnd library call select_db() is invoked through the user API call mysqli_select_db() no connection has been opened by the plugin yet, because lazy connections are configured (see above). Lazy connections delay openinig until statement execution. As of 1.1.0-beta the plugin "remembers" some of the state changing API calls and replays them whenever a connection is opened. As a result the read- and the write connections opened by the plugin will use otherdb and latin1. Pitfall removed, the PHP documentation has the details.

$mysqli = new mysqli("reply_cluster_config_name", "user", "password", "db");
/* state changes broadcasted on all connections */
$mysqli->select_db("otherdb");
$mysqli->set_charset("latin1");
$mysqli->query("SELECT id, title, short_desc FROM latest_products ORDER BY id DESC");
/* ... */
$mysqli->query("INSERT INTO something (id, value) VALUES (1, 'silly example')");
/* goes to the master if master_on_write configured */
$mysqli->query("SELECT id, value FROM something ORDER BY id DESC");
$mysqli->query("SELECT id, title, short_desc FROM latest_products ORDER BY id DESC");

Classic pattern vs. plugin, human factor – 2:3

Load Balancing and other goodies

Imagine you have more than one slave server and need to do load balancing. If using the plugin, load balancing can be configured in a few lines… and, there is more to discover. The 1.1. series changelog does not read very exiting. The versions motto/theme is "cover replication basics with production quality". Many foundations have been laid in 1.1. The freshly updated and not yet reviewed documentation has pointers about the future here and there. More to come… classic pattern vs. plugin 1.3 – a different league, hopefully.