PHP Manual: Setting the TTL

Setting the TTL

The default invalidation strategy of the query cache plugin is Time to Live (TTL). The built-in storage handlers will use the default TTL defined by the PHP configuration value mysqlnd_qc.ttl unless the query string contains a hint for setting a different TTL. The TTL is specified in seconds. By default cache entries expire after 30 seconds

The example sets mysqlnd_qc.ttl=3 to cache statements for three seconds by default. Every second it updates a database table record to hold the current time and executes a SELECT statement to fetch the record from the database. The SELECT statement is cached for three seconds because it is prefixed with the SQL hint enabling caching. The output verifies that the query results are taken from the cache for the duration of three seconds before they are refreshed.

Example #1 Setting the TTL with the mysqlnd_qc.ttl ini setting

mysqlnd_qc.enable_qc=1
mysqlnd_qc.ttl=3


<?php
/* Connect, create and populate test table */
$mysqli = new mysqli("host", "user", "password", "schema", "port", "socket");
$mysqli->query("DROP TABLE IF EXISTS test");
$mysqli->query("CREATE TABLE test(id VARCHAR(255))");

for ($i = 0; $i < 7; $i++) {

    /* update DB row  */
    if (!$mysqli->query("DELETE FROM test") ||
        !$mysqli->query("INSERT INTO test(id) VALUES (NOW())"))
      /* Of course, a real-life script should do better error handling */
      die(sprintf("[%d] %s\n", $mysqli->errno, $mysqli->error));

    /* select latest row but cache results */
    $query  = "/*" . MYSQLND_QC_ENABLE_SWITCH . "*/";
    $query .= "SELECT id AS _time FROM test";
    if (!($res = $mysqli->query($query)) ||
        !($row = $res->fetch_assoc()))
    {
      printf("[%d] %s\n", $mysqli->errno, $mysqli->error);
    }
    $res->free();
    printf("Wall time %s - DB row time %s\n", date("H:i:s"), $row['_time']);

    /* pause one second */
    sleep(1);
}
?>

The above examples will output something similar to:

Wall time 14:55:59 - DB row time 2012-01-11 14:55:59
Wall time 14:56:00 - DB row time 2012-01-11 14:55:59
Wall time 14:56:01 - DB row time 2012-01-11 14:55:59
Wall time 14:56:02 - DB row time 2012-01-11 14:56:02
Wall time 14:56:03 - DB row time 2012-01-11 14:56:02
Wall time 14:56:04 - DB row time 2012-01-11 14:56:02
Wall time 14:56:05 - DB row time 2012-01-11 14:56:05

As can be seen from the example, any TTL based cache can serve stale data. Cache entries are not automatically invalidated, if underlying data changes. Applications using the default TTL invalidation strategy must be able to work correctly with stale data.

A user-defined cache storage handler can implement any invalidation strategy to work around this limitation.

The default TTL can be overruled using the SQL hint /*qc_tt=seconds*/. The SQL hint must be appear immediately after the SQL hint which enables caching. It is recommended to use the PHP constant MYSQLND_QC_TTL_SWITCH instead of using the string value.

Example #2 Setting TTL with SQL hints


<?php
$start = microtime(true);

/* Connect, create and populate test table */
$mysqli = new mysqli("host", "user", "password", "schema", "port", "socket");
$mysqli->query("DROP TABLE IF EXISTS test");
$mysqli->query("CREATE TABLE test(id INT)");
$mysqli->query("INSERT INTO test(id) VALUES (1), (2)");

printf("Default TTL\t: %d seconds\n", ini_get("mysqlnd_qc.ttl"));

/* Will be cached for 2 seconds */
$sql = sprintf("/*%s*//*%s%d*/SELECT id FROM test WHERE id = 1", MYSQLND_QC_ENABLE_SWITCH, MYSQLND_QC_TTL_SWITCH, 2);
$res = $mysqli->query($sql);

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

$mysqli->query("DELETE FROM test WHERE id = 1");
sleep(1);

/* Cache hit - no automatic invalidation and still valid! */
$res = $mysqli->query($sql);
var_dump($res->fetch_assoc());
$res->free();

sleep(2);

/* Cache miss - cache entry has expired */
$res = $mysqli->query($sql);
var_dump($res->fetch_assoc());
$res->free();

printf("Script runtime\t: %d seconds\n", microtime(true) - $start);
?>

The above examples will output something similar to:

Default TTL     : 30 seconds
array(1) {
  ["id"]=>
  string(1) "1"
}
array(1) {
  ["id"]=>
  string(1) "1"
}
NULL
Script runtime  : 3 seconds