Ulf Wendel

PECL/mysqlnd_ms 1.5 + PHP 5.5.0 + MySQL 5.6 = better load balancing

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.

Any PHP MySQL application
|
$pdo->setAttribute(PDO::ATTR_AUTOCOMMIT, false); $mysqli->autocommit(false);
mysqlnd autocommit()
PECL/mysqlnd_ms autocommit():
transaction stickiness set and in transaction?
|  
MySQL Master MySQL Slave

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.

SQL MySQL C API PHP 5.4 MySQL APIs
SET autocommit mysql_autocommit() mysqli_autocommit(), PDO::ATTR_AUTOCOMMIT
START TRANSACTION n/a PDO::beginTransaction()
START TRANSACTION transaction_characteristic (e.g. READ ONLY) n/a n/a
COMMIT mysql_commit() mysqli_commit(), PDO::commit()
COMMIT [WORK] [AND [NO] CHAIN] [[NO] RELEASE] n/a n/a
ROLLBACK mysql_rollback() mysqli_rollback(), PDO::rollBack()
ROLLBACK [WORK] [AND [NO] CHAIN] [[NO] RELEASE] n/a n/a
SAVEPOINT n/a n/a
RELEASE SAVEPOINT n/a n/a
ROLLBACK [WORK] TO [SAVEPOINT] identifier n/a n/a

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.

SQL PHP 5.5 MySQL APIs
SET autocommit mysqli_autocommit(), PDO::ATTR_AUTOCOMMIT
START TRANSACTION mysqli_begin_transaction(), PDO::beginTransaction()
START TRANSACTION transaction_characteristic (e.g. READ ONLY) mysqli_begin_transaction([option [, name]])
COMMIT mysqli_commit(), PDO::commit()
COMMIT [WORK] [AND [NO] CHAIN] [[NO] RELEASE] mysqli_commit([option])
ROLLBACK mysqli_rollback(), PDO::rollBack()
ROLLBACK [WORK] [AND [NO] CHAIN] [[NO] RELEASE] mysqli_rollback([option, [name]])
SAVEPOINT mysqli_savepoint(name)
RELEASE SAVEPOINT mysqli_release_savepoint(name)
ROLLBACK [WORK] TO [SAVEPOINT] identifier mysqli_rollback([option, [name]])

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.

mysqli constant Comment
MYSQLI_TRANS_START_WITH_CONSISTENT_SNAPSHOT, MYSQLI_TRANS_START_READ_WRITE, MYSQLI_TRANS_START_READ_ONLY For mysqli_begin_transaction(). See SQL.
MYSQLI_TRANS_COR_AND_CHAIN, MYSQLI_TRANS_COR_AND_NO_CHAIN, MYSQLI_TRANS_COR_RELEASE, MYSQLI_TRANS_COR_NO_RELEASE Use with mysqli_commit(), mysqli_rollback(). See SQL.

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.

trx_stickiness setting mysqli call version requirements
PECL/mysqlnd_ms load balancing
not set $mysqli->begin_transaction()  
Ignored! Load balancer may switch connections at any time. Not transaction safe!
master $mysqli->autocommit(), PDO::ATTR_AUTOCOMMIT PHP 5.4, PECL/mysqlnd_ms 1.2
If autocommit is turned off, choose master and used it until autocommit is enabled again. Once autocommit is enabled, switching servers may happen at any time.
master $mysqli->begin_transaction() PHP 5.5, PECL/mysqlnd_ms 1.5
Choose a master: if failover is enabled, search a master until you find one based on failover rules. Once a master has been found stop load balancing, stop failover. Use master until the end of the transaction, monitor mysqli_commit(), mysqli_rollback() C API counterparts to learn about the end of the transaction.
on $mysqli->begin_transaction(MYSQLI_TRANS_START_READ_ONLY) PHP 5.5, PECL/mysqlnd_ms 1.5
Try to use a slave to run the transaction on. Try to use slave only if master-on-write and the current quality of service setting allows it. For example, if strong consistency has been requested, slaves will not be considered. If no slaves are configured or all slaves have failed, try using a master (see above).

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 Follow me on Twitter