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($mysqli, MYSQLND_MS_QOS_CONSISTENCY_EVENTUAL, MYSQLND_MS_QOS_OPTION_CACHE, 60)) {
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($mysqli, MYSQLND_MS_QOS_CONSISTENCY_EVENTUAL, MYSQLND_MS_QOS_OPTION_CACHE, 60)) {
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($mysqli, MYSQLND_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.