mysqlnd-ms-php-quickstart-php-qos-consistency-1

  • Quickstart and
    Examples

    • Service level and consistency

  • Service level and consistency

    • Service level and consistency

    Service level and consistency

    Note: Version
    requirement

    Service levels have been introduced in PECL
    mysqlnd_ms version 1.2.0-alpha. mysqlnd_ms_set_qos() is available with PHP
    5.4.0 or newer.

    Different types of MySQL cluster solutions offer
    different service and data consistency levels to their users. An
    asynchronous MySQL replication cluster offers eventual consistency
    by default. A read executed on an asynchronous slave may return
    current, stale or no data at all, depending on whether the slave
    has replayed all changesets from the master or not.

    Applications using an MySQL replication cluster
    need to be designed to work correctly with eventual consistent
    data. In some cases, however, stale data is not acceptable. In
    those cases only certain slaves or even only master accesses are
    allowed to achieve the required quality of service from the
    cluster.

    As of PECL mysqlnd_ms 1.2.0 the plugin is capable
    of selecting MySQL replication nodes automatically that deliver
    session consistency or strong consistency. Session consistency
    means that one client can read its writes. Other clients may or may
    not see the clients’ write. Strong consistency means that all
    clients will see all writes from the client.

    Example #1 Session consistency: read your
    writes

    {
    "myapp": {
        "master": {
            "master_0": {
                "host": "localhost",
                "socket": "\/tmp\/mysql.sock"
            }
        },
        "slave": {
            "slave_0": {
                "host": "127.0.0.1",
                "port": "3306"
            }
        }
    }
}

    Example #2 Requesting session consistency

    <?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()));
    }

    /* read-write splitting: master used */
    if (!$mysqli->query("INSERT INTO orders(order_id, item) VALUES (1, 'christmas tree, 1.8m')")) {
            /* Please use better error handling in your code */
            die(sprintf("[%d] %s\n", $mysqli->errno, $mysqli->error));
    }

    /* Request session consistency: read your writes */
    if (!mysqlnd_ms_set_qos($mysqli, MYSQLND_MS_QOS_CONSISTENCY_SESSION)) {
        die(    sprintf("[%d] %s\n", $mysqli->errno, $mysqli->error));
    }

    /* Plugin picks a node which has the changes, here: master */
    if (!$res = $mysqli->query("SELECT item FROM orders WHERE order_id = 1")) {
        die(    sprintf("[%d] %s\n", $mysqli->errno, $mysqli->error));
    }

    var_dump($res->fetch_assoc());

    /* Back to eventual consistency: stale data allowed */
    if (!mysqlnd_ms_set_qos($mysqli, MYSQLND_MS_QOS_CONSISTENCY_EVENTUAL)) {
        die(    sprintf("[%d] %s\n", $mysqli->errno, $mysqli->error));
    }

    /* Plugin picks any slave, stale data is allowed */
    if (!$res = $mysqli->query("SELECT item, price FROM specials")) {
        die(    sprintf("[%d] %s\n", $mysqli->errno, $mysqli->error));
    }
    ?>

    Service levels can be set in the plugins
    configuration file and at runtime using mysqlnd_ms_set_qos(). In the example the
    function is used to enforce session consistency (read your writes)
    for all future statements until further notice. The SELECT
    statement on the orders table is run on the master to
    ensure the previous write can be seen by the client. Read-write
    splitting logic has been adapted to fulfill the service level.

    After the application has read its changes from the
    orders table it returns to the default service level,
    which is eventual consistency. Eventual consistency puts no
    restrictions on choosing a node for statement execution. Thus, the
    SELECT statement on the specials table is
    executed on a slave.

    The new functionality supersedes the use of SQL
    hints and the master_on_write configuration option. In
    many cases mysqlnd_ms_set_qos() is easier to use, more
    powerful improves portability.

    Example #3 Maximum age/slave lag

    {
    "myapp": {
        "master": {
            "master_0": {
                "host": "localhost",
                "socket": "\/tmp\/mysql.sock"
            }
        },
        "slave": {
            "slave_0": {
                "host": "127.0.0.1",
                "port": "3306"
            }
        },
        "failover" : "master"
    }
}

    Example #4 Limiting slave lag

    <?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()));
    }

    /* Read from slaves lagging no more than four seconds */
    $ret = mysqlnd_ms_set_qos(
            $mysqli,
            MYSQLND_MS_QOS_CONSISTENCY_EVENTUAL,
            MYSQLND_MS_QOS_OPTION_AGE,
            4
    );

    if (!$ret) {
        die(    sprintf("[%d] %s\n", $mysqli->errno, $mysqli->error));
    }

    /* Plugin picks any slave, which may or may not have the changes */
    if (!$res = $mysqli->query("SELECT item, price FROM daytrade")) {
        die(    sprintf("[%d] %s\n", $mysqli->errno, $mysqli->error));
    }

    /* Back to default: use of all slaves and masters permitted */
    if (!mysqlnd_ms_set_qos($mysqli, MYSQLND_MS_QOS_CONSISTENCY_EVENTUAL)) {
        die(    sprintf("[%d] %s\n", $mysqli->errno, $mysqli->error));
    }
    ?>

    The eventual consistency service level can be used
    with an optional parameter to set a maximum slave lag for choosing
    slaves. If set, the plugin checks SHOW SLAVE STATUS for
    all configured slaves. In case of the example, only slaves for
    which Slave_IO_Running=Yes, Slave_SQL_Running=Yes
    and Seconds_Behind_Master <= 4 is true are considered
    for executing the statement SELECT item, price FROM
    daytrade    .

    Checking SHOW SLAVE STATUS is done
    transparently from an applications perspective. Errors, if any, are
    reported as warnings. No error will be set on the connection
    handle. Even if all SHOW SLAVE STATUS SQL statements
    executed by the plugin fail, the execution of the users statement
    is not stopped, given that master fail over is enabled. Thus, no
    application changes are required.

    Note: Expensive and slow
    operation

    Checking SHOW SLAVE STATUS for all slaves
    adds overhead to the application. It is an expensive and slow
    background operation. Try to minimize the use of it. Unfortunately,
    a MySQL replication cluster does not give clients the possibility
    to request a list of candidates from a central instance. Thus, a
    more efficient way of checking the slaves lag is not available.

    Please, note the limitations and properties of
    SHOW SLAVE STATUS as explained in the MySQL reference
    manual.

    To prevent mysqlnd_ms from emitting a warning if no
    slaves can be found that lag no more than the defined number of
    seconds behind the master, it is necessary to enable master fail
    over in the plugins configuration file. If no slaves can be found
    and fail over is turned on, the plugin picks a master for executing
    the statement.

    If no slave can be found and fail over is turned
    off, the plugin emits a warning, it does not execute the statement
    and it sets an error on the connection.

    Example #5 Fail over not set

    {
    "myapp": {
        "master": {
            "master_0": {
                "host": "localhost",
                "socket": "\/tmp\/mysql.sock"
            }
        },
        "slave": {
            "slave_0": {
                "host": "127.0.0.1",
                "port": "3306"
            }
        }
    }
}

    Example #6 No slave within time limit

    <?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()));
    }

    /* Read from slaves lagging no more than four seconds */
    $ret = mysqlnd_ms_set_qos(
            $mysqli,
            MYSQLND_MS_QOS_CONSISTENCY_EVENTUAL,
            MYSQLND_MS_QOS_OPTION_AGE,
            4
    );

    if (!$ret) {
        die(    sprintf("[%d] %s\n", $mysqli->errno, $mysqli->error));
    }

    /* Plugin picks any slave, which may or may not have the changes */
    if (!$res = $mysqli->query("SELECT item, price FROM daytrade")) {
        die(    sprintf("[%d] %s\n", $mysqli->errno, $mysqli->error));
    }

    /* Back to default: use of all slaves and masters permitted */
    if (!mysqlnd_ms_set_qos($mysqli, MYSQLND_MS_QOS_CONSISTENCY_EVENTUAL)) {
        die(    sprintf("[%d] %s\n", $mysqli->errno, $mysqli->error));
    }
    ?>

    The above example will output:

    PHP Warning:  mysqli::query(): (mysqlnd_ms) Couldn't find the appropriate slave connection. 0 slaves to choose from. Something is wrong in %s on line %d
PHP Warning:  mysqli::query(): (mysqlnd_ms) No connection selected by the last filter in %s on line %d
[2000] (mysqlnd_ms) No connection selected by the last filter