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);

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 none 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 combination of a server identifier and a sequence number, such as 123-some-server-uuid:n-m. The global transaction identifiers can be accessed through the new global server variable GTID_DONE and be compared with the new server SQL function GTID_SUBSET.

"myapp": {
     "master": {
         "master_0": {
             "host": "localhost",
             "socket": "/tmp/mysql.sock"
         }
      },
     "slave": {
         "slave_0": {
             "host": "127.0.0.1",
             "port": "3306"
       }
      },
     "global_transaction_id_injection":{
         "fetch_last_gtid" : "SELECT @@GLOBAL.GTID_DONE AS trx_id FROM DUAL",
          "check_for_gtid" : "SELECT GTID_SUBSET('#GTID', @@GLOBAL.GTID_DONE) AS trx_id FROM DUAL",
          "report_error":true
      }
  }
} 

The above is an example PECL/mysqlnd_ms 1.3.0-alpha plugin configuration to use the global transaction identifier feature of MySQL 5.6. Don’t let the section name global_transaction_id_injection confuse you. The section global_transaction_id_injection is used for configuring the SQL to fetch the latest GTID and to check if a server has replicated a certain GTID no matter if you want to use the client-side emulation or the server-side feature.

Client-side GTID emulation continues to be supported

To hint PECL/mysqlnd_ms that you want to use a client-side emulation you must additionally provide a SQL statement for incrementing a GTID at the end of a transaction as shown in the example below.

{
    "myapp": {
        "master": {
            "master_0": {
                "host": "localhost",
                "socket": "/tmp/mysql.sock"
            }
        },
        "slave": {
            "slave_0": {
                "host": "127.0.0.1",
                "port": "3306"
            }
        },
        "global_transaction_id_injection":{
            "on_commit":"UPDATE test.trx SET trx_id = trx_id + 1",
            "fetch_last_gtid" : "SELECT MAX(trx_id) FROM test.trx",
            "check_for_gtid" : "SELECT trx_id FROM test.trx WHERE trx_id >= #GTID",
            "report_error":true
        }
    }
}

Comparing the two example configuration you may get the impression MySQL 5.6 could make your life easier… True? Stay tuned. I didn’t like everything.

PHP documentation updates related to GTID support by PECL/mysqlnd_ms 1.3.0-alpha (development version, trunk) have been been pushed today. It should take a day or two until they appear on all mirrors.

Happy hacking!

@Ulf_Wendel