News Archive
PhpRiot Newsletter
Your Email Address:

More information

PECL/mysqlnd_ms: MySQL 5.6.4-m8+ global transaction identifier feature supported

Note: This article was originally published at Planet PHP on 6 March 2012.
Planet PHP

MySQL Replication is sometimes critizied for being asynchronous and having slaves that lag behind. True! However, sometimes slaves can be used safely and reliably for read-your-writes. Its easy for PHP MySQL users. All the magic is in the driver. As of yesterday, the development version of PECL/mysqlnd_ms 1.3.0-alpha supports not only a client-side global transaction ID emulation but also the global transaction identifier feature of MySQL 5.6.4-m8.

Read-your-writes (session consistency) with MySQL Replication

A global transaction identifier can be understood as a sequence number for a transaction. The sequence number is incremented whenever a write transaction is performed on a MySQL replication master. Slaves replicate the transaction ID. After a client has executed a write on the master he can obtain a global transaction identifier created for his write set. Then, the client can use the ID to find a slave which has replicated the writes already.

$link-query("INSERT INTO test(id) VALUES (123)"); $gtid = mysqlnd_ms_get_last_gtid($link);

| A Master A A GTID = 27263 A Slave 1 Slave 2 Slave 3 GTID = 27263 GTID = 27251 GTID = 27263

Without global transaction identifiers there is no safe and water-proof way of telling whether a slave has replicated the latest changes or not. Thus, PHP clients in need for session consistency had to query the master only after their first write for the rest of their request. This approach has two downsides: the master has to handle read load at all and it potentially has to handle reads although slaves have caught up.

Client-side emulation and server-side feature

PECL/mysqlnd_ms 1.2.0 has introduced a client-side global transaction ID emulation to solve this. Details of the server selection and the global transaction ID emulation are greatly hidden from the user. Set the consistency level you need calling mysqlnd_ms_set_qos() and the plugin takes care. More than 75 pages full of examples, a quickstart and reference materials in the PHP manual give the details. $link can be a MySQL connection from mysql, mysqli or PDO_MySQL, if those have been compiled to use the mysqlnd library, which is a default on all platforms as of PHP 5.4.0.

$link-query("INSERT INTO test(id) VALUES (123)"); $gtid = mysqlnd_ms_get_last_gtid($link); /* requesting read-your-writes */ if (false == mysqlnd_ms_set_qos($link, MYSQLND_MS_QOS_CONSISTENCY_SESSION, MYSQLND_MS_QOS_OPTION_GTID, $gtid)) { printf(" [%d] %s\n", $link-errno, $link-error); } /* do your reads */ /* return to relaxed eventual consistency */ if (false == mysqlnd_ms_set_qos($link, MYSQLND_MS_QOS_CONSISTENCY_EVENTUAL)) { printf(" [%d] %s\n", $link-errno, $link-error); }

Maintaining a global transaction ID using a client-side emulation has some limitations. Unless one is faced with an heterogenous environment with MySQL servers of many different versions, this may not be the best option.

  • DBAs must deploy global transaction identifier sequence tables on all nodes
  • Clients must be able to detect transaction boundaries for proper sequence numbering
  • If not all clients update the sequence number gaps are likely. This can easily be the case when not only PHP clients access the master

MySQL 5.6.4-m8 or later add a choice by introducing built-in global transaction identifiers. The server-side approach has non of the listed limitations but may have others that you hopefully will find described in the MySQL Reference Manual soon. PECL/mysqlnd_ms 1.3.0-alpha can either use its own client-side emulation or the server-side feature. From the perspective of a client only after the read-your-writes the only difference is in the SQL that is needed to access and compare global transaction IDs.

Accessing and comparing GTIDs in MySQL 5.6.4-m8+

The MySQL server does not use a simple sequence number as a global transaction identifier. Instead it uses a combin

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