PECL/mysqlnd_ms is a client-side load balancer for PHP that supports any MySQL cluster. It does read-write splitting, failover, introduces a quality of service concept, supports partitioning and, of course, load balancing. New mysqli API (begin, *savepoint) calls in PHP 5.5.0 help to improve transaction awareness. New read only in MySQL 5.6 promise major performance gains (think 2x) and an option to reduce the load on a MySQL Replication master. Read how the features go together in PECL/mysqlnd_ms 1.5.

Load balancing – transaction aware?

A load balancer must not switch connections in the middle of a transaction. A load balancer must send all queries to the server a transaction has been started on until the transaction ends. Unfortunately, it is very hard to develop a transparent load balancer for MySQL. In general there are four approaches:

• forget about transparency and require applications to hint the load balancer about transaction boundaries (buuuh!)
• have the MySQL server announce transactions to clients on the wirte protocol (buggy 🙁)
• monitor SQL queries controlling transactions
• monitor API calls controlling transactions

PECL/mysqlnd_ms supports the basic hinting and the API monitoring approach. Using SQL hints to control load balancing during transactions is possible but very uncomfortable.

$mysqli = new mysqli(...);
$mysqli->query("BEGIN");
/* stop load balancing, force use of last server */
$mysqli->query(sprintf("/*%s*/INSERT INTO test(id) VALUES (1)", MYSQLND_MS_LAST_USED_SWITCH));
sprintf("/*%s*/COMMIT", MYSQLND_MS_LAST_USED_SWITCH));

API monitoring is a step forward. If transaction stickiness has been configured, PECL/mysqlnd_ms stops load balancing once autocommit is turned off. Given you set trx_stickiness=master, the load balancer will run all transactions on the master.

$mysqli->autocommit(false);
/* autocommit is off, must not switch connections if transaction_stickiness is set */
$mysqli->query("INSERT INTO test(id) VALUES (1)");

$pdo->setAttribute(PDO::ATTR_AUTOCOMMIT, false);
/* if trx_stickiness is set, no connection switch allowed */
$stmt = $pdo->prepare("SELECT @myrole AS _role");
$stmt->execute();
$result = $stmt->fetchAll(PDO::FETCH_ASSOC);

Internally, PECL/mysqlnd_ms hooks the autocommit() C API function of mysqlnd. PDO_MySQL and mysqli call it and thus, PECL/mysqlnd_ms recognizes the change.

However, remember that if you used SQL to control the autocommit mode, PECL/mysqlnd_ms would not recognize the change and transaction stickiness would not work.

MySQL C API vs. PHP API

As a PHP user, you may be surprised to hear that autocommit() is the only call monitored in PECL/mysqlnd_ms 1.4. That’s because its pretty much all the MySQL C API had to offer and thus, all the plugin could hook and use to detect transaction boundaries. For example, PECL/mysqlnd_ms 1.4 cannot be made aware of a call to PDO::beginTransaction() because PDO::beginTransaction() does not map to any MySQL C API call that the plugin could monitor.

A close look unveils that SQL offers way more options to control transactions than the MySQL C API.

The feature gap between SQL and PHP (mysqli) API is closed in PHP 5.5. The mysqlnd C library has been extended to offer C calls for all SQL features. Those C calls can be monitored by PECL/mysqlnd_ms 1.5. And, those calls are exported to the mysqli API. The transaction aware load balancing of PECL/mysqlnd_ms 1.5 is no longer limited to autocommit() but covers all of the below mysqli_*-functions.

PDO_MySQL has not been modified yet to use the new mysqlnd API calls. Work in progress… even the mysqli API additions have not been documented yet.

Better load balancing in PECL/mysqlnd_ms 1.5

MySQL 5.6 introduces read only transactions. If you tell InnoDB in advance that a transaction will perform read operations only, it can be executed faster than a transaction that may perform a write. Early MySQL 5.6 Release Candidate benchmarks hinted that read only transactions could run twice as fast as normal transactions. You can use the SQL statement
START TRANSACTION READ ONLY to begin a read only transaction, or you use the new mysqli API features of PHP 5.5.0.

$mysqli->begin_transaction(MYSQLI_TRANS_START_READ_ONLY);
$mysqli->query(...);
$mysqli->query(...);
$mysqli->commit();

Using the API has the advantage that PECL/mysqlnd_ms 1.5 can do transaction aware load balancing: the plugin picks a server to run the transaction and continues using it until the transaction ends. If MYSQLI_TRANS_START_READ_ONLY is set, the plugin may try to run the transaction on a slave in order to reduce the load on the master. Whether the transaction will end up on a slave depends on a number of additional factors.

Beside all the improvements, it would be so much easier for clients to do proper transaction aware load balancing if the server would announce the begin and end of a transaction on the wire protocol…

Happy hacking!

@Ulf_Wendel