Ulf Wendel

Here comes the next open source mysqlnd plugin prototype: PECL/mysqlnd_mux. The PHP mysqlnd connection multiplexing plugin shares a connection to MySQL between multiple user connection handles. Connection multiplexing aims to reduce connect time and lowers the total number of concurrenly opened connections. The first is primarily a benefit for the client, the latter is great for the server. Multiplexing may be most desired in environments with many short-lived connections and weak client-side pooling – such as a PHP web application.

How mysqlnd plugins work in general

The mysqlnd library is part of PHP as of 5.3. Since PHP 5.4 it is the default C client library for all three PHP MySQL APIs (mysqli, PDO_MySQL, mysql).

As a backend C library for the existing APIs, mysqlnd and its plugins can be transparent from a user perspective. They operate inside PHP at a layer beneath the users applications.Thus, the mysqlnd multiplexing plugin can be used with all existing PHP MySQL applications. However, there is a glory detail. The plugin needs the latest version of mysqlnd contained in PHP 5.5.

How the multiplexing is implemented

Whenever a user attempts to open a MySQL connection using any of the PHP MySQL extensions (mysql, mysqli, PDO_MySQL), the corresponding functions of the mysqlnd library are called inside PHP. The plugin hooks those calls. Whenever the user opens a connection, the plugin checks for a cached connection to the requested MySQL server. If none is found, a new connection is opened, cached and returned to the caller.

$handle_1 = mysqli_connect("host", ...)

When doing another connect to the same MySQL server as before, no matter what API used, the plugin checks its cache and links the new user handle to the already opened connection. No second connection needs to be established.

$handle_2 = new PDO("mysql:host=host;...", ...)

Compared to a traditional connect there is now only one connection opened to MySQL. Two user connection handles share the same underlying connection. Less work for the server.

An optimization for some

Multiplexing is a performance optimization with side-effects. Collisions and wait situations can arise. Concurrent access to the shared connection must be synchronized. Imagine two clients attempt to use the shared connection at the same time. One of the clients will have to wait until the other stopped using the connection before it can execute its query.

Sharing a connection means also sharing its state. A connections state consists of assorted settings, transaction status, SQL session variables, temporary tables and many more. Please, see the replication plugin manual for an in-depth discussion. Shared state is one of the biggest criticism of persistent connections. This does not mean shared state is malicious per-se. It needs to be understood and mastered, that’s it.

Connection multiplexing is at an optimization detail level that requires understanding its properties.

A demo of the plugin API

We are making an initial public release: 1.0.0-prototype. We are not announcing a production-ready stable release. The initial implementation supports buffered queries only. API calls that use buffered queries include mysqli_query(), mysql_query() and all of PDO if PDO::ATTR_EMULATE_PREPARES is set. Good enough for an evaluation of the idea.

The initial release is also great to learn about the mysqlnd C plugin API. There is not too much code. This allows expert users to talk to us at any level they want. If anybody feels for C coding and want to speak to us the international developer language, commit a patch. If you feel the next step should be looking at multiplexing from a users perspective, ask for a feature addition. Andrey understands both languages. Andrey Hristov? Yes, he developed the plugin.

Happy hacking!

@Ulf_Wendel 

An INSERT does not delay me much. At least, it does not necessarily block a PHP MySQL script immediately. The asynchronous query feature of the mysqlnd library helps out. A walkthrough what mysqlnd can do today and could do in theory.

Traditional synchronous API

$ret = $handle1->query("INSERT ...");
$ret = $handle1->query("INSERT ...");

By default, mysqli_query() is a blocking API call. It sends the query to MySQL and waits for MySQL to reply. Let’s see what happens in general when executing two INSERT statements. PHP offers no parallel processing language primitives such as threads and thus, the two INSERT statements run in a serial fashion. Execution times add up.

Please note, all times are fictional. They have been choosen to highligh the basic principles.

The non-blocking version

Running statements in parallel using multiple connections makes things faster from a client perspective. Assuming the server can handle the load, the clients wait time is roughly the execution time of the slowest query. As a cutting edge PHP MySQL developer, you know syntax for achieving this already.

$handle1->query("INSERT ...", MYSQLI_ASYNC);
$handle2->query("INSERT ...", MYSQLI_ASYNC);
$all_handles = array($handle1, $handle2);
$processed = 0;
do {
  $handles = $errors = $reject = array();
  foreach ($all_handles as $handle) {
    $handles[] = $errors[] = $reject[] = $handle;
  }
  if (!mysqli_poll($handles, $errors, $reject, 1)) {
    continue;
  }
  foreach ($handles as $handle) {
    if ($handle->reap_async_query()) {
      $processed++;
    }
  }
} while ($processed < count($all_handles));

Cool, the client’s wall clock wait time is down from fictional 35ms (5 + 10 + 20) to 25 ms (5 + max(10, 20) ) 30ms (5 + 5 + max(10, 20)) [good find, Thomas!]. If you are really concerned about performance you may want to try further reducing the connection times. Persistent connections come to mind. As an aside: in 2012, we should call them pooled connections because their state is reset before being reused. Internally, the MySQL C API call mysql_change_user() call is used to reset. As this is a rather expensive and slow call, I’ve lowered the connection time only from 5 to 3 in the below example.

Still not good? If you compile PHP yourself, you can disable the call of mysql_change_user() which gives you true persistent connections and gets the connection overhead (in case of reuse) near zero…

How about Multiplexing?

… in theory, given specificaly selected queries, multiplexing may further improve performance. Multiplexing will share a network connection to MySQL among multiple user handles. Please note, I am not talking about anything that exists and is available from MySQL.

Multiplexing is usually done to lower connection overhead and reduce the number of openend connections. The latter is good for the server. Usually the server is the hardest part to scale in a client-server setup. Thus, any technique to offload the server should be welcome.

This looks great but, hey, how is it different from mysqli_query(..., MYSQLI_ASYNC) using a single pooled connection? There is none: the two INSERT end up on the same line being serialized. Likely, the synchronization on the line would even have a negative impact on fire and forget sequences.

To discuss multiplexing advantages, one would have to look at a situation where multiple PHP scripts run in parallel. So, why am I telling? It can be worth looking into the PHP MySQL API at a finer level than the first, random “PHP MySQL tutorial” listed by a search engine suggest.

Happy hacking!

@Ulf_Wendel 

Some people speak incredibly fast, others make you fall asleep listening. If you have a heterogenous MySQL cluster with differently sized machines, some will be able to answer questions faster than others. PECL/mysqlnd_ms 1.4, the PHP MySQL replication plugin, allows you to assign more requests to the faster ones than to the others. Cluster nodes can be given a weight, a priority for load balancing. For example, a node given a weight of two will get twice as many requests in the average as a node assigned the default weight of one.

Load Balancing adaptive to hardware size

The mysqlnd replication plugin features round robin and random load balancing. Random can be combined with a sticky option which means the
plugin picks a random node at the beginning of the web requests and uses it until the end for all requests that the node can fulfill. For example, the plugin may pick one slave and use it for all read requests to come. This is the default strategy. It bares the lowest risk of tapping into any pitfall on connection state changes due to load balancing, as far as they are not handled by the plugin anyway.

Below is a configuration file example that shows the new syntax to set a load balancing weight attribute. The primary use case of the plugin is MySQL Replication, a primary copy based system. Thus, the example configuration lists a master (primary) and three slaves (secondaries). The manual has details how to configure the plugin for use with MySQL Cluster and other write-everywhere solutions.

{ 
 "myapp": { 
   "master": {
     "master1":{
       "host":"localhost",
       "socket":"\/var\/run\/mysql\/mysql.sock"}
   },   
   "slave": { 
     "slave1": {
       "host":"192.168.2.28",
       "port":3306
     },
     "slave2": {
       "host":"192.168.2.29",
       "port":3306
     },
     "slave3": {
       "host":"192.0.43.10",
       "port":3306
     },
   },
   "filters": {
     "random": {
       "weights": {
         "slave1":8, 
         "slave2":4, 
         "slave3":1,
         "master1":1
       }
       "sticky": "1"
     }
   }
 }
}

Continue Reading →

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!

@Ulf_Wendel 

What if MySQL had an HTTP interface and would reply JSON? What if MySQL had server-side JavaScript? What if you could store JSON documents in MySQL? And, what if there was Map and Reduce built-in to MySQL? Make it happen, today. A proof of concept MySQL 5.6 daemon plugin presented at IPC Spring 2012.

Eye-opening community contributions

Mycached (2009, Cybozu) was a ground breaking achievement. A MySQL server deamon plugin was used to add a new client interface (Memcache protocol) to MySQL. Together with the new protocol came new access methods, an additional data model (key – value) and a new output format (JSON). The work was later superseeded by HandlerSocket (Dena/Yoshinori Matsunobu, 2010) which in turn was abandoned in favour of InnoDB Memcached respectively MySQL Cluster/NDB Memcached by Oracle. Stewart Smith presented a HTTP JSON plugin for Drizzle in 2011. The value of MySQL as storage – not only for SQL – was exposed..

Motivation

The steadily growing popularity of JavaScript deserves a proof of concept. Client-side JavaScript has to use proxying to access MySQL, for example, through PHP/Apache or node.js. Client-side JavaScript is limited to HTTP/Websocket but MySQL speaks neither one. A direct wire between MySQL and client-side JavaScript would help.

Proxing means deploying an extra set of software (e.g. PHP, Apache). Extra layers add delays. Extra layers require extra resource – less computing power is left for MySQL.

HTTP for MySQL

MySQL Server daemon plugins are libraries loaded into the MySQL process. A deamon plugin can do anything it likes, for example, start a multi-threaded HTTP webserver . Embedding a webserver can easily be done using the proven BSD libraries libevent and libevht. The embedded webserver shall handle GET /?sql=<mysql>, execute <mysql> and return the result as JSON. JSON is a lightweight data format and it is the natural one for JavaScript.

JSON for MySQL

Running a SQL statement from within a deamon plugin requires setting up and tearing down a THD (thread descriptor object). The event scheduler source has pointers how this is to be done. Running the statement is no problem, but where is the result? The result is gone by the wind. MySQL has streamed it into the Protocol class member of THD for sending out during result generation. Create your own Protocol_json class, wrap all calls for sending data to a function that appends the data to a string, set the protocol class of THD to Protocol_json and fetch the JSON buffered into the string after query execution. Return using the embedded webserver. Done.

Server side JavaScript

Another BSD software is used for adding server-side JavaScript: Google V8. Using the function developed to run SQL, we can store the source code of server-side JavaScript scripts in a table, fetch it upon request, cache the source code and execute it. He’s the result for Hello World.

mysql> select * from js_applications where name='internetsuperhero'\G
*************************** 1. row ***************************
  name: internetsuperhero
source: function main() { return "Hello world"; } main(); 

Server side JavaScript gets access to MySQL data

It get more interesting as soon as we link the function for running SQL and returning results as JSON strings to the users’ script. We export the function to JavaScript. Server-side JavaScript has now access to all data inside MySQL. Below are the results for SELECT 1. Please recall, this is a proof of concept. No less. No more.

mysql> select * from js_applications where name='select'\G
*************************** 1. row ***************************
  name: select
source: function main() { return ulf("SELECT 1"); } main();
1 row in set (0,00 sec)

Server side JavaScript manipulates JSON documents

What if a we store JSON documents in BLOBs? Server-side JavaScript can fetch the contents of the BLOBs. It gets the data as JSON. JSON can be converted into JavaScript objects. The object can then be manipulated, converted back to JSON and send out via HTTP to the user. Of course, it would also be possible to store the results in a table, a "view"

function filter_names() { 
  var s = ulf("SELECT document FROM test_documents"); 
  var docs = JSON.parse(s);
  var res = []; 
  for (i = 0; i < docs.length; i++) { 
    var doc = JSON.parse(docs[i]); 
    if (doc.firstname !== undefined) { 
      res[i] = "Hi " + doc.firstname; 
    } 
  } 
  return JSON.stringify(res); 
}

That’s cool. We have now MySQL speaking HTTP, replying JSON, server-side JavaScript has access to MySQL data and we can keep "documents" inside MySQL, MySQL can do map and reduce. All goals of the proof of concept have been reached. Whoever can do map, can do reduce as well. Reduce is just another iteration in the result generation loop.

Map and Reduce using SQL vs. handler interface

The handler interface is the MySQL internal low level interface to fetch data. One key motivator behind the Memcached protocol additions to MySQL was to bypass the SQL layer for better performance. Maybe we can use the handler interface to make the map function look more like a map function and improve performace? Most often the map function is called for every row.

function map(){ 
   var res; 
   var row = JSON.parse(doc.before); 
   if (row.firstname !== undefined) 
     res = "Hi " + row.firstname; 
   doc.after = JSON.stringify(res); 
} 
map();

Performance decreases. Once cause could be the increased number of C++ to JavaScript context switches.

Proof given!

The original goals of the proof of concept have been achieved. It is possible to expose the value of MySQL as a storage, to follow out motto not only SQL and to give client-side JavaScript a direct wire to MySQL.

Out of curiosity I benchmarked how many rows (documents) can be read per second. A single thread managed to process 12,700 documents per second. Integrating V8 into the read loop but neither compiling nor running a script let the performance drop to 11,500 documents per second. Compiling and running an empty script resulted in 8,700 documents being scanned per second. And, its been 8,300 documents per second that could be mapped.

Will you make it happen, will you create an HTTP interface for MySQL, today?

Happy hacking!

@Ulf_Wendel 

Users of MySQL Replication sometimes throttle client requests to give slaves time to catch up to the master. PECL/mysqlnd_ms 1.4, the current development version, features some throttling through the quality-of-service filter and global transaction identifier (GTID). Both the plugins client-side GTID emulation and the MySQL 5.6 built-in GTID feature can be used to slow down PHP MySQL requests, if wanted.

How its done

The replication plugin has a neat feature called quality-of-service filter. If, for example, the quality of service you need from a MySQL Replication cluster is "read your writes", you call mysqlnd_ms_set_qos($connection, MYSQLND_MS_QOS_CONSISTENCY_SESSION). This instructs the plugin to either use a master or a slave, that has replicated your writes already, for all further reads. The plugin takes care of picking an appropriate cluster node. Once you are done with "read your writes" you can relax the service quality to make node selection faster.

By default, MYSQLND_MS_QOS_CONSISTENCY_SESSION will enforce reading from the master. This is undesired as it increases the load on the master. However, before the introduction of global transaction identifiers, there was no safe way of knowing whether a slave had replicated a certain update already or not.

Using either the plugins GTID emulation or the MySQL 5.6 build-in GTID feature, one can reliably check the up-to-date status of a slave using a SQL SELECT statement. GTIDs are some kind of unique transaction sequence numbers. If you know the transaction sequence number of a write operation, you can check whether it has been replicated using a statement like, for example, SELECT GTID_SUBSET('gtid_of_write', @@GLOBAL.GTID_DONE) AS trx_id FROM DUAL. Please, check my previous posts for a more precise description of the GTID feature. This statement will check the replication status and return immediately.

SQL_THREAD_WAIT_AFTER_GTIDS(string gtids [, timeout])

Alternatively, a MySQL 5.6 user can issue SELECT SQL_THREAD_WAIT_AFTER_GTIDS('gtid_of_write') which will block until either the slave has replicated the write in question or the statement times out. This is great to throttle clients and prevent them to send new updates before the slaves have caught up. This is what some throttling is about. You can control which logic PECL/mysqlnd_ms shall use when searching for an up-to-date slave.

Continue Reading →

Tweaking is the motto – what an easy release PECL/mysqlnd_ms 1.4 will be! The first tweak for the next stable version of the mysqlnd replication and load balancing plugin solves pitfalls around charsets. String escaping now works on lazy connection handles (default) prior to establishing a connection to MySQL. A new server_charset setting has been introduced for this. The way it works also prevents you from the risk of using a different charset for escaping than used later on for your connection.

Lazy connections and server_charset

PECL/mysqlnd_ms is a load balancer. A users connection handle can point to different nodes of a replication cluster over time. For example, if using MySQL Replication, the connection handle may point to the master for running writes and, later on, to one of the slaves for reads. At the very moment a user opens a connection handle, the load balancer does not yet know which cluster node needs to be queried first.

/* Load balanced following "myapp" section rules from the plugins config file */
$mysqli = new mysqli("myapp", "username", "password", "database");
$pdo = new PDO('mysql:host=myapp;dbname=database', 'username', 'password');
$mysql = mysql_connect("myapp", "username", "password");

Thus, the plugin delays opening a MySQL connection to any configured cluster node (master or slave) until a node has been selected for statement execution. The plugin calls this lazy connections. Instead of openening a connection to a default node or even opening connections to all nodes, it waits and sees.

/* Nothing but a handle, no MySQL connection opened so far*/
$mysqli = new mysqli("myapp", "username", "password", "database");
/* Escaping on a lazy connection is not possible - will emit a warning */
$mysqli->real_escape("What charset to use?");
/* A node is picked and a connection will be openend */
$mysqli->query("SELECT 'connection will be opened now' AS _msg FROM DUAL");

While lazy connections potentially help to keep the number of connections opened as low as possible, there is a problem. Which charset to use for string escaping prior to opening a connection to MySQL? PECL/mysqlnd_ms 1.4 will search for a server_charset setting in its configuration file and use it. If it is not there, it will bark at you, pretty much like all previous stable releases. A warning will be thrown that reads like (mysqlnd_ms) string escaping doesn't work without established connection in the current series and is a bit more verbose in the 1.4 series, like (mysqlnd_ms) string escaping doesn't work without established connection. Possible solution is to add server_charset to your configuration

Setting server_charset removes the warning. PECL/mysqlnd_ms 1.4 will use the server_charset to do the string escaping. At any time thereafter the user can change the charset using an API call for string escaping with a different charset. SQL statements shall not be used to change charsets as they are not monitored by the plugin. However, upon establishing a connection to any MySQL server, the connection will be set to server_charset again.

No need to take care of the server configuration

Enforcing the configured server_charset whenever a connection is opened free’s the administrator from the need to set the same default charset on all servers. When using version 1.3 or older you should make sure that servers use the same charset. This prevents tapping into the pitfall of escaping a string using the charset of the first server contacted, then switching the connection to another server/connection with a different charset and accidently using a wrongly escaped string.

More tweaks and improvements considered for version 1.4 are listed at http://wiki.php.net/pecl/mysqlnd_ms. Please, do not understand the list as a firm promise that everything will be implemented. Feel free to add ideas or tasks to the wiki page.

Happy hacking!

@Ulf_Wendel 

Why do we have to bother about built-in GTID support in MySQL 5.6 at all? Sure, it is a tremendous step forward for a lazy primary copy system like MySQL Replication. Period. GTIDs make server-side failover easier (slides). And, load balancer, including PECL/mysqlnd_ms as an example of a driver integrated load balancer, can use them to provide session consistency. Please, see the slides. But…

… the primary remains a single point of failure. GTIDs can be described as cluster-wide transaction counters generated on the master. In case of a master failure, the slave that has replicated the highest transaction counter shall be promoted to become the master. Its the most current slave. Failover made easy – no doubt! Adequately deployed, you should reach very reasonable availability.

Know the limits of replicated systems

A multi-master (update anywhere) design does not have a single point of failure. But among the biggest is scaling a multi-master solution. Jim Gray and Pat Helland concluded 1996 in "The Dangers of Replication and a Solution": Update anywhere-anytime-anyway transactional replication has unstable behavior as the workload scales up: a ten-fold increase in nodes and traffic gives a thousand fold increase in deadlocks or reconciliations.. N^3 – buuuhhhh, anything worse than linear scale is not appreciated. Guess what: Microsoft SQL Azure is using primary copy combined with partitioning.

Continue Reading →

The long lasting MySQL replication failover issue is cured. MySQL 5.6 makes master failover easy, PECL/mysqlnd_ms assists with the client/connection failover. Compared to the past this is a significant step towards improving MySQL replication cluster availability, eleminating the need to use 3rd party tools in many cases. The slides illustrate the basic idea, as blogged about before.

There is not much to say about the feature as such. Slave to master promotion works without hassles, finally. Regardless if you do failover because of an error of the current master or switchover because you want to change the master server, its easy now. Congratulations to the replication team!

Limitations of the current server implementation

The global transaction identifier implementation in MySQL 5.6 has a couple of limitations, though. Its not hard to guess that mixing transactional and non-transactional updates in one transaction can cause problems. Its pretty much the first pitfall I ran into when trying to setup a MySQL 5.6.5-m8 (not 5.6.4-m8…) slave using a mysqldump generated SQL dump. MySQL bailed at me and stopped me from failing.

Continue Reading →

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