mysqlnd-ms-php-quickstart-php-cache-8

  • Quickstart and
    Examples
  • Cache integration

  • Cache integration
  • Cache integration

    Cache integration

    Note: Version requirement,
    dependencies and status

    Please, find more about version requirements,
    extension load order dependencies and the current status in the
    concepts
    section
    !

    Databases clusters can deliver different levels of
    consistency. As of PECL/mysqlnd_ms 1.2.0 it is possible to
    advice the plugin to consider only cluster nodes that can deliver
    the consistency level requested. For example, if using asynchronous
    MySQL Replication with its cluster-wide eventual consistency, it is
    possible to request session consistency (read your writes) at any
    time using mysqlnd_ms_set_quos(). Please,
    see also the service level and consistency introduction.

    Example #1 Recap: quality of service to request read
    your writes

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

    Assuming PECL/mysqlnd has been explicitly told to
    deliver no consistency level higher than eventual consistency, it
    is possible to replace a database node read access with a
    client-side cache using time-to-live (TTL) as its invalidation
    strategy. Both the database node and the cache may or may not serve
    current data as this is what eventual consistency defines.

    Replacing a database node read access with a local
    cache access can improve overall performance and lower the database
    load. If the cache entry is every reused by other clients than the
    one creating the cache entry, a database access is saved and thus
    database load is lowered. Furthermore, system performance can
    become better if computation and delivery of a database query is
    slower than a local cache access.

    Example #2 Plugin config: no special entries for
    caching

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

    Example #3 Caching a slave request

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

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

    /* Explicitly allow eventual consistency and caching (TTL <= 60 seconds) */
    if (false == mysqlnd_ms_set_qos($mysqliMYSQLND_MS_QOS_CONSISTENCY_EVENTUALMYSQLND_MS_QOS_OPTION_CACHE60)) {
        die(
    sprintf("[%d] %s\n"$mysqli->errno$mysqli->error));
    }

    /* To make this example work, we must wait for a slave to catch up. Brute force style. */
    $attempts 0;
    do {
        
    /* check if slave has the table */
        
    if ($res $mysqli->query("SELECT id FROM test")) {
            break;
        } else if (
    $mysqli->errno) {
            die(
    sprintf("[%d] %s\n"$mysqli->errno$mysqli->error));
        }
        
    /* wait for slave to catch up */
        
    usleep(200000);
    } while (
    $attempts++ < 10);

    /* Query has been run on a slave, result is in the cache */
    assert($res);
    var_dump($res->fetch_assoc());

    /* Served from cache */
    $res $mysqli->query("SELECT id FROM test");
    ?>

    The example shows how to use the cache feature.
    First, you have to set the quality of service to eventual
    consistency and explicitly allow for caching. This is done by
    calling mysqlnd_ms_set_qos(). Then, the result set of
    every read-only statement is cached for upto that many seconds as
    allowed with mysqlnd_ms_set_qos().

    The actual TTL is lower or equal to the value set
    with mysqlnd_ms_set_qos(). The value passed to the
    function sets the maximum age (seconds) of the data delivered. To
    calculate the actual TTL value the replication lag on a slave is
    checked and subtracted from the given value. If, for example, the
    maximum age is set to 60 seconds and the slave reports a lag of 10
    seconds the resulting TTL is 50 seconds. The TTL is calculated
    individually for every cached query.

    Example #4 Read your writes and caching
    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()));
    }

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

    /* Explicitly allow eventual consistency and caching (TTL <= 60 seconds) */
    if (false == mysqlnd_ms_set_qos($mysqliMYSQLND_MS_QOS_CONSISTENCY_EVENTUALMYSQLND_MS_QOS_OPTION_CACHE60)) {
        die(
    sprintf("[%d] %s\n"$mysqli->errno$mysqli->error));
    }

    /* To make this example work, we must wait for a slave to catch up. Brute force style. */
    $attempts 0;
    do {
        
    /* check if slave has the table */
        
    if ($res $mysqli->query("SELECT id FROM test")) {
            break;
        } else if (
    $mysqli->errno) {
            die(
    sprintf("[%d] %s\n"$mysqli->errno$mysqli->error));
        }
        
    /* wait for slave to catch up */
        
    usleep(200000);
    } while (
    $attempts++ < 10);

    assert($res);

    /* Query has been run on a slave, result is in the cache */
    var_dump($res->fetch_assoc());

    /* Served from cache */
    if (!($res $mysqli->query("SELECT id FROM test"))) {
        die(
    sprintf("[%d] %s\n"$mysqli->errno$mysqli->error));
    }
    var_dump($res->fetch_assoc());

    /* Update on master */
    if (!$mysqli->query("UPDATE test SET id = 2")) {
        die(
    sprintf("[%d] %s\n"$mysqli->errno$mysqli->error));
    }

    /* Read your writes */
    if (false == mysqlnd_ms_set_qos($mysqliMYSQLND_MS_QOS_CONSISTENCY_SESSION)) {
        die(
    sprintf("[%d] %s\n"$mysqli->errno$mysqli->error));
    }

    /* Fetch latest data */
    if (!($res $mysqli->query("SELECT id FROM test"))) {
        die(
    sprintf("[%d] %s\n"$mysqli->errno$mysqli->error));
    }
    var_dump($res->fetch_assoc());
    ?>

    The quality of service can be changed at any time
    to avoid further cache usage. If needed, you can switch to read
    your writes (session consistency). In that case, the cache will not
    be used and fresh data is read.