PhpRiot
News Archive
PhpRiot Newsletter
Your Email Address:

More information

PECL/mysqlnd_ms 1.4: A failover standby using weighted/prioritized load balancing

Note: This article was originally published at Planet PHP on 28 June 2012.
Planet PHP

Some people speak incredibly fast, others make you fall asleep listening. If you have a heterogenous MySQL cluster with differently sized machines, some will be able to answer questions faster than others. PECL/mysqlnd_ms 1.4, the PHP MySQL replication plugin, allows you to assign more requests to the faster ones than to the others. Cluster nodes can be given a weight, a priority for load balancing. For example, a node given a weight of two will get twice as many requests in the average as a node assigned the default weight of one.

Load Balancing adaptive to hardware size

The mysqlnd replication plugin features round robin and random load balancing. Random can be combined with a sticky option which means the
plugin picks a random node at the beginning of the web requests and uses it until the end for all requests that the node can fulfill. For example, the plugin may pick one slave and use it for all read requests to come. This is the default strategy. It bares the lowest risk of tapping into any pitfall on connection state changes due to load balancing, as far as they are not handled by the plugin anyway.

Below is a configuration file example that shows the new syntax to set a load balancing weight attribute. The primary use case of the plugin is MySQL Replication, a primary copy based system. Thus, the example configuration lists a master (primary) and three slaves (secondaries). The manual has details how to configure the plugin for use with MySQL Cluster and other write-everywhere solutions.

{ "myapp": { "master": { "master1":{ "host":"localhost", "socket":"\/var\/run\/mysql\/mysql.sock"} }, "slave": { "slave1": { "host":"192.168.2.28", "port":3306 }, "slave2": { "host":"192.168.2.29", "port":3306 }, "slave3": { "host":"192.0.43.10", "port":3306 }, }, "filters": { "random": { "weights": { "slave1":8, "slave2":4, "slave3":1, "master1":1 } "sticky": "1" } } } }

Latency considerations

The slave on 192.168.2.28 is given a weight of 8, the one on 192.168.2.29 is given 4. That means, 192.168.2.28 will get twice as many requests as 192.168.2.29 in the average. A third slave slave3 runs on 192.0.43.10. Assume, it is not co-located to the other two slaves. Thus, latency is higher.

EU data center A US data center Rack 1 Rack 2 Rack 3 A Rack 4 Slave 1 Slave 2 Master PHP A Slave 3 weight = 8 weight = 4 A A weight = 1 ~61% of all reads ~31% of all reads A A ~8% of all reads

Let slave3 be located in a different data center or even a different continent than your PHP server. Thus, network latency between the client and the node is higher. For performance reasons you want PHP

Truncated by Planet PHP, read more at the original (another 4878 bytes)