Clustering databases is a mainstream technology. MySQL PHP users can take many approaches to load balancing, pooling and redirection. PECL/mysqlnd_ms is one solution. How does it handle transactions? Transactions are a challenge for every MySQL load balancer.
Load Balancer and transactions
A load balancer must not switch from one cluster node to another in the middle of a transactions. Unfortunately, the MySQL Protocol does not announce the transaction status. Thus, load balancers either need to be hinted by the application, attempt to monitor SQL or hook API calls.
PECL/mysqlnd_ms understands SQL hints and can monitor API calls that control transactions, if using PHP 5.4 or newer. It does not try to parse and understand SQL. For example, with the code below PECL/mysqlnd_ms will stop load balancing after autocommit(FALSE). The plugin assumes that you want to run a transaction.
$mysqli = new mysqli("myapp", "username", "password", "database");
/* Disable autocommit, master used, no server switch allowed */
$mysqli->autocommit(FALSE);
/* ... */
if (!$mysqli->commit()) {
die(sprintf("[%d] %s\n", $mysqli->errno, $mysqli->error));
}
/* Transaction has ended, load balancing begins again */
$mysqli->autocommit(TRUE);
Load Balancer cannot know in advance whether a transaction will do writes
Depending on the MySQL clustering solution you may want to make sure the transaction is run on selected cluster nodes. Think of MySQL Replication. All transactions should go to the master. At the beginning of a transaction the load balancer does not know whether it will contain a write or not. The only safe and reasonable choice is to execute the transaction on the master. Set trx_stickiness=master in the PECL/mysqlnd_ms configuration and you are done.
If you ever migrate from MySQL Replication (lazy primary copy) to MySQL Cluster (eager update anywhere), the transaction can go to any node. MySQL Cluster is a synchronous multi-master system. Remove trx_stickiness=master from the config and you are done.
PECL/mysqlnd_ms has many sophisticated features – hidden behind simple config settings or comprehensive API calls. Please, have a look at the slides presented at the International PHP Conference Spring Edition 2012 (#ipc12).
Happy hacking!
