PECL/mysqlnd_ms is now transaction aware, sometimes. A new experimental configuration option trx_stickiness=master disables load balancing if autocommit mode is turned off via API. This makes the mysqlnd library plugin transaction safe. Unfortunately it requires PHP 5.3.99 and, it can be fooled by using SQL to control autocommit. It requires some discipline to use.

Past and today: SQL hints for transactions

By default the PHP replication and load balancing plugin (mysqlnd_ms) is not transaction safe, because it is not transaction aware. The plugin does not know when a transaction starts and when it ends. Thus, it may decide to load balance statements and switch connections in the middle of a transaction. So far, SQL hints have been the only way to prevent …

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, …

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, …

Introducing the best load balancing policy for PECL/mysqlnd_ms: "random_once". A (PHP) web request is short-lived. A web server can handle multiple requests per second. There are many web servers running an application. For each web request choose a random slave to use for the rest of the request. Don’t do load balancing, don’t switch connections during a web request . Result: even load distribution, less connection overhead and less connections overall, cache loves you, less hassle switching stateful slave connections…

Load balancing policies featured by PECL/mysqlnd_ms

As of today, PECL/mysqlnd_ms, our PHP mysqlnd toy plugin for replication load balancing, features four built-in load balancing policies:

1. random: for each read query, choose a random slave
2. round-robin: for each read query, choose the next slaves from the slave …

Introducing the best load balancing policy for PECL/mysqlnd_ms: "random_once". A (PHP) web request is short-lived. A web server can handle multiple requests per second. There are many web servers running an application. For each web request choose a random slave to use for the rest of the request. Don’t do load balancing, don’t switch connections during a web request . Result: even load distribution, less connection overhead and less connections overall, cache loves you, less hassle switching stateful slave connections…

Load balancing policies featured by PECL/mysqlnd_ms

As of today, PECL/mysqlnd_ms, our PHP mysqlnd toy plugin for replication load balancing, features four built-in load balancing policies:

1. random: for each read query, choose a random slave
2. round-robin: for each read query, choose the next slaves from the slave …

Implicit and automatic connection failover done by the driver is broken by design, correct? Should PECL/mysqlnd_ms implement automatic failover? Today Johannes showed me an IRC snippet about TAF (Transparent Application Failover). Maybe, I fail miserably but I don’t see a need for automatic failover in a master slave replication loadbalancing plugin. Let the application do it. In the end, this is also what TAF does - via a callback.

Say you run a query. The master slave load balancing plugin recognizes that it is a read query, a SELECT. The plugin picks a server for query execution, for example, the first of the configured slaves. The plugin sends the query to the slave but the slave is not …

Implicit and automatic connection failover done by the driver is broken by design, correct? Should PECL/mysqlnd_ms implement automatic failover? Today Johannes showed me an IRC snippet about TAF (Transparent Application Failover). Maybe, I fail miserably but I don’t see a need for automatic failover in a master slave replication loadbalancing plugin. Let the application do it. In the end, this is also what TAF does - via a callback.

Say you run a query. The master slave load balancing plugin recognizes that it is a read query, a SELECT. The plugin picks a server for query execution, for example, the first of the configured slaves. The plugin sends the query to the slave but the slave is not …

Imagine PECL/mysqlnd_ms could keep certain reads on certain slaves in a MySQL master slave setup. You would have a good chance to profit from hot database caches. You would not only distribute read load, scale out horizonally and so forth due to using MySQL replication. You would also optimize your reads for hot caches.

PECL/mysqlnd_ms is our master slave replication load balancing proof-of-concept mysqlnd plugin playground (see also previous blog posts). It allows you to define a callback which is responsible for picking the server that shall run a query. That userspace callback could be doing the read redirection optimization for hot caches.

The eye-catcher: hot cache optimization

(pecl_mysqlnd_ms_config.ini)

[myapp]
master[] = …

Imagine PECL/mysqlnd_ms could keep certain reads on certain slaves in a MySQL master slave setup. You would have a good chance to profit from hot database caches. You would not only distribute read load, scale out horizonally and so forth due to using MySQL replication. You would also optimize your reads for hot caches.

PECL/mysqlnd_ms is our master slave replication load balancing proof-of-concept mysqlnd plugin playground (see also previous blog posts). It allows you to define a callback which is responsible for picking the server that shall run a query. That userspace callback could be doing the read redirection optimization for hot caches.

The eye-catcher: hot cache optimization

(pecl_mysqlnd_ms_config.ini)

[myapp]
master[] = …

How transparent does master slave load balancing support for mysqlnd have to be? Transparency, when driven to the extremes, has three limiting factory: power users not needing it, increase of messages send to MySQL, time intensive SQL monitoring and parsing. The PECL/mysqlnd_ms proof-of-concept mysqlnd plugin is almost transparent to make it easy to use. Almost because there are some design limits. Please let us know how you rate the severity of these limits. For example, can we ignore transactions? Your feedback on the previous blog post has been most helpful - thanks!

Give me four paragraphs, one minute, for the …