Examples
Cache integration
Cache integration
Cache integration
Note: Version requirement,
dependencies and statusPlease, 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.