mysqlnd-ms-php-quickstart-php-gtid-3

  • Quickstart and
    Examples

    • Global transaction IDs

  • Global transaction IDs

    • Global transaction IDs

    Global transaction IDs

    Note: Version
    requirement

    A client-side 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.

    As of MySQL 5.6.5-m8 release candidate the MySQL
    server features built-in global transaction identifiers. The MySQL
    built-in global transaction ID feature is supported by
    PECL/mysqlnd_ms 1.3.0-alpha or later. However, the final
    feature set found in MySQL 5.6 production releases to date is not
    sufficient to support the ideas discussed below in all cases.
    Please, see also the concepts section.

    PECL/mysqlnd_ms can either use its own
    global transaction ID emulation or the global transaction ID
    feature built-in to MySQL 5.6.5-m8 or later. From a developer
    perspective the client-side and server-side approach offer the same
    features with regards to service levels provided by
    PECL/mysqlnd_ms. Their differences are discussed in the concepts section.

    The quickstart first demonstrates the use of the
    client-side global transaction ID emulation built-in to
    PECL/mysqlnd_ms before its show how to use the server-side
    counterpart. The order ensures that the underlying idea is
    discussed first.

    Idea and client-side
    emulation

    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 committed on the master.
    Slaves replicate the table. The counter serves two purposes. 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
    committed 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 querying
    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.

    Example #1 Create counter table on master

    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.

    Example #2 Plugin config: SQL for client-side GTID
    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
        }
    }
}

    Example #3 Transparent global transaction ID
    injection

    <?php
    $mysqli     = new mysqli("myapp", "username", "password", "database");
    if (!    $mysqli) {
            /* Of course, your error handling is nicer... */
            die(sprintf("[%d] %s\n", mysqli_connect_errno(), mysqli_connect_error()));
    }

    /* auto commit mode, transaction on master, GTID must be incremented */
    if (!$mysqli->query("DROP TABLE IF EXISTS test")) {
        die(    sprintf("[%d] %s\n", $mysqli->errno, $mysqli->error));
    }

    /* auto commit mode, transaction on master, GTID must be incremented */
    if (!$mysqli->query("CREATE TABLE test(id INT)")) {
        die(    sprintf("[%d] %s\n", $mysqli->errno, $mysqli->error));
    }

    /* auto commit mode, transaction on master, GTID must be incremented */
    if (!$mysqli->query("INSERT INTO test(id) VALUES (1)")) {
        die(    sprintf("[%d] %s\n", $mysqli->errno, $mysqli->error));
    }

    /* auto commit mode, read on slave, no increment */
    if (!($res = $mysqli->query("SELECT id FROM test"))) {
        die(    sprintf("[%d] %s\n", $mysqli->errno, $mysqli->error));
    }

    var_dump($res->fetch_assoc());
    ?>

    The above example will output:

    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.

    Note: 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.

    Example #4 Plugin config: SQL for fetching
    GTID

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

    Example #5 Obtaining GTID after injection

    <?php
    $mysqli     = new mysqli("myapp", "username", "password", "database");
    if (!    $mysqli) {
            /* Of course, your error handling is nicer... */
            die(sprintf("[%d] %s\n", mysqli_connect_errno(), mysqli_connect_error()));
    }

    /* auto commit mode, transaction on master, GTID must be incremented */
    if (!$mysqli->query("DROP TABLE IF EXISTS test")) {
        die(    sprintf("[%d] %s\n", $mysqli->errno, $mysqli->error));
    }

    printf("GTID after transaction %s\n", mysqlnd_ms_get_last_gtid($mysqli));

    /* auto commit mode, transaction on master, GTID must be incremented */
    if (!$mysqli->query("CREATE TABLE test(id INT)")) {
        die(    sprintf("[%d] %s\n", $mysqli->errno, $mysqli->error));
    }

    printf("GTID after transaction %s\n", mysqlnd_ms_get_last_gtid($mysqli));
    ?>

    The above example will output:

    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 accidentally 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.

    Example #6 Plugin config: Checking for a certain
    GTID

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

    Example #7 Session consistency service level and GTID
    combined

    <?php
    $mysqli     = new mysqli("myapp", "username", "password", "database");
    if (!    $mysqli) {
            /* Of course, your error handling is nicer... */
            die(sprintf("[%d] %s\n", mysqli_connect_errno(), mysqli_connect_error()));
    }

    /* auto commit mode, transaction on master, GTID must be incremented */
    if (   !$mysqli->query("DROP TABLE IF EXISTS test")
        || !    $mysqli->query("CREATE TABLE test(id INT)")
        || !    $mysqli->query("INSERT INTO test(id) VALUES (1)")
    ) {
        die(    sprintf("[%d] %s\n", $mysqli->errno, $mysqli->error));
    }

    /* GTID as an identifier for the last write */
    $gtid = mysqlnd_ms_get_last_gtid($mysqli);

    /* Session consistency (read your writes): try to read from slaves not only master */
    if (false == mysqlnd_ms_set_qos($mysqli, MYSQLND_MS_QOS_CONSISTENCY_SESSION, MYSQLND_MS_QOS_OPTION_GTID, $gtid)) {
        die(    sprintf("[006] [%d] %s\n", $mysqli->errno, $mysqli->error));
    }

    /* Either run on master or a slave which has replicated the INSERT */
    if (!($res = $mysqli->query("SELECT id FROM test"))) {
        die(    sprintf("[%d] %s\n", $mysqli->errno, $mysqli->error));
    }

    var_dump($res->fetch_assoc());
    ?>

    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.

    Use of the server-side global
    transaction ID feature

    Note: Insufficient server
    support in MySQL 5.6

    The plugin has been developed against a
    pre-production version of MySQL 5.6. It turns out that all released
    production versions of MySQL 5.6 do not provide clients with enough
    information to enforce session consistency based on GTIDs. Please,
    read the concepts
    section     for details.

    Starting with MySQL 5.6.5-m8 the MySQL Replication
    system features server-side global transaction IDs. Transaction
    identifiers are automatically generated and maintained by the
    server. Users do not need to take care of maintaining them. There
    is no need to setup any tables in advance, or for setting
    on_commit. A client-side emulation is no longer
    needed.

    Clients can continue to use global transaction
    identifier to achieve session consistency when reading from MySQL
    Replication slaves in some cases but not all! The algorithm works
    as described above. Different SQL statements must be configured for
    fetch_last_gtid and check_for_gtid. The
    statements are given below. Please note, MySQL 5.6.5-m8 is a
    development version. Details of the server implementation may
    change in the future and require adoption of the SQL statements
    shown.

    Using the following configuration any of the above
    described functionality can be used together with the server-side
    global transaction ID feature. mysqlnd_ms_get_last_gtid() and mysqlnd_ms_set_qos() continue to work as
    described above. The only difference is that the server does not
    use a simple sequence number but a string containing of a server
    identifier and a sequence number. Thus, users cannot easily derive
    an order from GTIDs returned by mysqlnd_ms_get_last_gtid().

    Example #8 Plugin config: using MySQL 5.6.5-m8 built-in
    GTID feature

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