Write-read sequences can be tricky for users of MySQL replication, if what has been written needs to be available for reading immediately. Slaves could be behind. You have to run the sequence on the master. If MySQL semisynchronous replication would tell the client which slave is in sync, a client library could select that slave for load balancing.

The more we look into load balancing for PECL/mysqlnd_ms, the PHP mysqlnd master-slave replication load balancing proof-of-concept plugin, the more restrictions and issues come up. An obvious restriction is that connections have a state.

No solution: binlog monitoring

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

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 …

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 …

Are you interested in a master slave load balancing plugin for mysqlnd? It could have two operational modes. The automatic mode would operate transparent from an applications point of view. The manual mode would give you full control over all decisions. Automatic mode is for install and forget when you start with MySQL replication. Manual mode is for growing as your application evolves.

Start small…

Let’s assume you have to run a forum for a customer. You have picked, more or less randomly, the second-best PHP forum application. The customer’s website becomes popular. The forum is read-mostly and qualifies for scaling through MySQL replication. You add two MySQL slaves to your web farm. Now, how do you get the second-best PHP forum application to direct all writes to the MySQL master? Of course, the second-best application was not designed to support MySQL replication and …

Are you interested in a master slave load balancing plugin for mysqlnd? It could have two operational modes. The automatic mode would operate transparent from an applications point of view. The manual mode would give you full control over all decisions. Automatic mode is for install and forget when you start with MySQL replication. Manual mode is for growing as your application evolves.

Start small…

Let’s assume you have to run a forum for a customer. You have picked, more or less randomly, the second-best PHP forum application. The customer’s website becomes popular. The forum is read-mostly and qualifies for scaling through MySQL replication. You add two MySQL slaves to your web farm. Now, how do you get the second-best PHP forum application to direct all writes to the MySQL master? Of course, the second-best application was not designed to support MySQL replication and …

Psst, you want some client-side MySQL load balancing with and without sharding for your PHP application? PHP 5.3 has something to offer for you. It is free. It requires no to very little changes to your applications. It is open source but works with closed source applications as well. You won’t find this information in any book, because some book authors still do not cover "mysqlnd". Read on for 30+ lines of PHP to add round-robin connection load balancing and, 70+ lines of PHP to add MySQL master slave replication or sharding support to your application without changing the application. Black magic from Manmuckiel.

The goal