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.

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 to prefer using slave1, slave2. Consequently, slave3 is given a low weight of 1. In the average only every 13th read will be directed to slave3 by the driver plugin.

Worst-case hot standby failover slave

Why list slave3 at all in your configuration? slave3 may be a great failover option if both slave1 and slave2 fail because the rack (or data center) they belong to fails. slave3 is a worst-case scenario hot standby that is rarely used during normal operations. But if you need it, its there.

Ease of use unchanged – for the basics!

PECL/mysqlnd_ms 1.4 is about tweaking glory details for those who really need it. Rest assured if you don’t need it. The tweaking does not get into your way. The plugin continues to make using any kind of MySQL cluster easier. Below is the classical, minimalistic example of using MySQL Replication with PHP and the mysqlnd plugin. All PHP MySQL APIs are supported. Simple cases require no code changes at all. It just works.

/* Load balanced following "myapp" section rules from the plugins config file */
$mysqli = new mysqli("myapp", "username", "password", "database");
$pdo = new PDO('mysql:host=myapp;dbname=database', 'username', 'password');
$mysql = mysql_connect("myapp", "username", "password");

/* Load balanced following "myapp" section rules from the plugins config file */
$mysqli = new mysqli("myapp", "username", "password", "database");
if (mysqli_connect_errno())
  /* Of course, your error handling is nicer... */
  die(sprintf("[%d] %s\n", mysqli_connect_errno(), mysqli_connect_error()));

/* Statements will be run on the master */
if (!$mysqli->query("DROP TABLE IF EXISTS test")) {
 printf("[%d] %s\n", $mysqli->errno, $mysqli->error);
}
/*...*/
/* read-only: statement will be run on a slave */
if (!($res = $mysqli->query("SELECT id FROM test")) {
 printf("[%d] %s\n", $mysqli->errno, $mysqli->error);
} else {
 $row = $res->fetch_assoc();
 $res->close();
 printf("Slave returns id = '%s'\n", $row['id'];
}
$mysqli->close();

No thought on locating nodes/servers needed. It can wait until you demand it. The upcoming 1.4 is a "we can, just in case you need…" release.

Happy hacking!

@Ulf_Wendel