Global transaction IDs

Замечание: Version requirement

Global transaction ID injection has been introduced in mysqlnd_ms version 1.2.0-alpha. The feature is not required for synchronous clusters, such as MySQL Cluster. Use it with asynchronous clusters such as classical MySQL replication.

In its most basic form a global transaction ID (GTID) is a counter in a table on the master. The counter is incremented whenever a transaction is comitted on the master. Slaves replicate the table. The counter serves two puposes. In case of a master failure, it helps the database administrator to identify the most recent slave for promoting it to the new master. The most recent slave is the one with the highest counter value. Applications can use the global transaction ID to search for slaves which have replicated a certain write (identified by a global transaction ID) already.

PECL/mysqlnd_ms can inject SQL for every comitted transaction to increment a GTID counter. The so created GTID is accessible by the application to identify an applications write operation. This enables the plugin to deliver session consistency (read your writes) service level by not only quering masters but also slaves which have replicated the change already. Read load is taken away from the master.

Client-side global transaction ID emulation has some limitations. Please, read the concepts section carefully to fully understand the principles and ideas behind it, before using in production environments. The background knowledge is not required to continue with the quickstart.

First, create a counter table on your master server and insert a record into it. The plugin does not assist creating the table. Database administrators must make sure it exists. Depending on the error reporting mode, the plugin will silently ignore the lack of the table or bail out.

CREATE TABLE `trx` (
  `trx_id` int(11) DEFAULT NULL,
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=latin1
INSERT INTO `trx`(`trx_id`) VALUES (1);

In the plugins configuration file set the SQL to update the global transaction ID table using on_commit from the global_transaction_id_injection section. Make sure the table name used for the UPDATE statement is fully qualified. In the example, test.trx is used to refer to table trx in the schema (database) test. Use the table that was created in the previous step. It is important to set the fully qualified table name because the connection on which the injection is done may use a different default database. Make sure the user that opens the connection is allowed to execute the UPDATE.

Enable reporting of errors that may occur when mysqlnd_ms does global transaction ID injection.

{
    "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",
            "report_error":true
        }
    }
}

array(1) {
  ["id"]=>
  string(1) "1"
}

The example runs three statements in auto commit mode on the master, causing three transactions on the master. For every such statement, the plugin will inject the configured UPDATE transparently before executing the users SQL statement. When the script ends the global transaction ID counter on the master has been incremented by three.

The fourth SQL statement executed in the example, a SELECT, does not trigger an increment. Only transactions (writes) executed on a master shall increment the GTID counter.

Замечание: SQL for global transaction ID: efficient solution wanted!

The SQL used for the client-side global transaction ID emulation is inefficient. It is optimized for clearity not for performance. Do not use it for production environments. Please, help finding an efficient solution for inclusion in the manual. We appreciate your input.

{
    "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",
            "report_error":true
        }
    }
}

GTID after transaction 7
GTID after transaction 8

Applications can ask PECL mysqlnd_ms for a global transaction ID which belongs to the last write operation performed by the application. The function mysqlnd_ms_get_last_gtid() returns the GTID obtained when executing the SQL statement from the fetch_last_gtid entry of the global_transaction_id_injection section from the plugins configuration file. The function may be called after the GTID has been incremented.

Applications are adviced not to run the SQL statement themselves as this bares the risk of accidently causing an implicit GTID increment. Also, if the function is used, it is easy to migrate an application from one SQL statement for fetching a transaction ID to another, for example, if any MySQL server ever features built-in global transaction ID support.

The quickstart shows a SQL statement which will return a GTID equal or greater to that created for the previous statement. It is exactly the GTID created for the previous statement if no other clients have incremented the GTID in the time span between the statement execution and the SELECT to fetch the GTID. Otherwise, it is greater.

{
    "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
        }
    }
}

A GTID returned from mysqlnd_ms_get_last_gtid() can be used as an option for the session consistency service level. Session consistency delivers read your writes. Session consistency can be requested by calling mysqlnd_ms_set_qos(). In the example, the plugin will execute the SELECT statement either on the master or on a slave which has replicated the previous INSERT already.

PECL mysqlnd_ms will transparently check every configured slave if it has replicated the INSERT by checking the slaves GTID table. The check is done running the SQL set with the check_for_gtid option from the global_transaction_id_injection section of the plugins configuration file. Please note, that this is a slow and expensive procedure. Applications should try to use it sparsely and only if read load on the master becomes to high otherwise.