Ulf Wendel

PHP replication and load balancing plugin: master_on_write, documentation

Hours ago, I have committed the first documentation draft for PECL/mysqlnd_ms. The mirrors should show it on Saturday. PECL/mysqlnd_ms is a replication and load balancing plugin for the mysqlnd library. Its latest feature, master_on_write, helps to work around the issue of replication lag. If master_on_write=1, the plugin stops load balancing statements after the first non read-only statement has been run by a PHP script. Instead of running read-only statements on the slaves, the plugin runs all statements on the master. This is done to ensure that the client will always read the latest data, even the data just written and not yet replicated to the slaves. Thank you, Lukas for the blog comment with the idea!

Thank you, Chris for this presentation!

master_on_write – always use master after first write

MySQL replication is asynchronous. Whenever you perform a write operation on a MySQL master server, it takes some time until it is replicated on all slaves. The write operation is not blocked until all of the slaves have replicated it. The write returns immediately after its been executed on the master. Slaves may fall behind the master and not show the latest data updates.

  Master Slave A Slave B
t1 INSERT    
t2   (replicated) INSERT (stale) SELECT
t3     (replicated) INSERT

For example, you could have a replication setup with one master and two slaves. You use PECL/mysqlnd_ms to direct read-only statements to the slaves and all other statements to the master. Your slaves are a bit behind the master, one a little less (slave A), one a little more (slave B). At the point in time t1, you insert some data. PECL/mysqlnd_ms sends the query to the master. Immediately after the insert operation, in the same script, at time t2, you read from the table you have modified. PECL/mysqlnd_ms picks slave B to execute the SELECT statement. Slave B has not catched up on the latest updates from the master and returns stale results.

/* will be send to the master */
$mysqli->query("INSERT INTO orders(id) VALUES (1)");
/* will be run on a slave - either slave A or slave B */
$mysqli->query("SELECT COUNT(*) FROM orders");

Some applications may either not need to care or have been written with the asynchronous nature of MySQL replication in mind. Other applications may suffer badly from the stale results.

master[] = master_host
slave[] = slave_a_host
slave[] = slave_b_host

Setting master_on_write=1 helps to cure the problem. If set, the plugin stops load balancing for the rest of the script run after the first non read-only query. A read-only query is a query which begins with SELECT or the SQL hint for using a slave server. All other queries are not considered as read-only. INSERT is not considered read-only. Therefore, it is run on the master.

/* will be send to the master */
$mysqli->query("INSERT INTO orders(id) VALUES (1)");
/* run on the master if master_on_write = 1 */
$mysqli->query("SELECT COUNT(*) FROM orders");

If master_on_write=1 and the script has executed a statement on the master, all of the following statements will also be run on the master. This is the case in the example. The SELECT run immediately after the INSERT will be run on the master. This way, it will return current not stale results.

Like any automatic decision, master_on_write can be overruled using SQL hints. If you force using a slave, the plugin will query a slave for you.

There are two downsides of master_on_write. It adds read-load to the master and, it operates on a per request basis. The first is a price you have to pay. The second is something the plugin can hardly help you with. The plugin does not know if the next request may send queries to the slaves or not because the plugin does not know if the slaves are behind. That's a problem left to the application. The application may, for example, use global transaction ids.

Feedback welcome!

As usual, feedback is most welcome.

Next on my TODO is some transaction stickiness mode before doing the first release. As said above, the documentation should appear on the main php.net site and the mirrors by Saturday. Until then, please find it at http://docs.php.net/manual/en/book.mysqlnd-ms.php.

A success story, a hooray on the PHP documentation team!

Credits to the PHP documentation team!

Try edit.php.net - Php  Docbook Online Editor

Documenting a PECL extension in 2011 is pure fun. The PHP documentation team has done an outstanding job over the past decade!

A wiki page explains you how to create a documentation skeleton within minutes. Another wiki page describes editing and rendering on your local box. Creating XHTML from the Docbook XML has become as easy as typing phd -d .manual.xml -f xhtml -t chunkedhtml, or the like. It probably took me as little as 45 minutes to check out the manual, follow instructions from the wiki pages, fail and succeed (AKA learning) until the documentation skeleton appeared in my browser as XHTML.

I'm sorry to say, but the PHP documentation system fails to serve as an excuse for not creating documentation for any of the PECL extensions.

Editing existing documentation is even easier. You don't have to read any wiki pages, you don't need to install anything. Point your browser to edit.php.net. Enjoy using the PHP Docbook Online Editor, please find a screenshot above. Anybody can help improving the documentation of PHP. BTW, does your internet pad or mobile phone have a browser and, do you feel bored sometimes 😉 ?


  1. I follow your plugin since your first post.
    And i think, that it will become very popular.

    It seems very easy to deploy it while being an very effective solution for load balancing managment.
    And the master_on_write option is just great.

    Thx for your work

  2. Thank you!

    I bet there are some solutions out there offering features that we should copy to make the plugin really useful. If you know a solution that we should copy… However, I’m trying to get to an end with adding more and more to the 1.0.

    Meanwhile, I’m not so worried about features but rather about testing. I’m eager to release 1.0 to get field feedback.

  3. Pingback: abcphp.com