<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="content-type" content="text/html; charset=UTF-8">
<title>Cache integration</title>
</head>
<body><div class="manualnavbar" style="text-align: center;">
<div class="prev" style="text-align: left; float: left;"><a href="mysqlnd-ms.quickstart.gtid.html">Global transaction IDs</a></div>
<div class="next" style="text-align: right; float: right;"><a href="mysqlnd-ms.quickstart.failover.html">Failover</a></div>
<div class="up"><a href="mysqlnd-ms.quickstart.html">Quickstart and Examples</a></div>
<div class="home"><a href="index.html">PHP Manual</a></div>
</div><hr /><div id="mysqlnd-ms.quickstart.cache" class="section">
<h2 class="title">Cache integration</h2>
<blockquote class="note"><p><strong class="note">Note</strong>:
<strong>Version requirement, dependencies and status</strong><br />
<p class="para">
Please, find more about version requirements, extension load order dependencies and the current status
in the <a href="mysqlnd-ms.concept_cache.html" class="link">concepts section</a>!
</p>
</p></blockquote>
<p class="para">
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 <span class="function"><strong>mysqlnd_ms_set_quos()</strong></span>. Please, see also the
<a href="mysqlnd-ms.quickstart.qos-consistency.html" class="link">service level and consistency</a>
introduction.
</p>
<p class="para">
<div class="example" id="example-1784">
<p><strong>Example #1 Recap: quality of service to request read your writes</strong></p>
<div class="example-contents"><div class="phpcode"><code><span style="color: #000000">
/* Request session consistency: read your writes */<br />if (!mysqlnd_ms_set_qos($mysqli, MYSQLND_MS_QOS_CONSISTENCY_SESSION))<br /> die(sprintf("[%d] %s\n", $mysqli->errno, $mysqli->error));</span>
</code></div></div>
</div>
</p>
<p class="para">
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.
</p>
<p class="para">
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.
</p>
<p class="para">
<div class="example" id="example-1785">
<p><strong>Example #2 Plugin config: no special entries for caching</strong></p>
<div class="example-contents">
<div class="inicode"><pre class="inicode">{
"myapp": {
"master": {
"master_0": {
"host": "localhost",
"socket": "\/tmp\/mysql.sock"
}
},
"slave": {
"slave_0": {
"host": "127.0.0.1",
"port": "3306"
}
},
}
}</pre>
</div>
</div>
</div>
</p>
<p class="para">
<div class="example" id="example-1786">
<p><strong>Example #3 Caching a slave request</strong></p>
<div class="example-contents">
<div class="phpcode"><code><span style="color: #000000">
<span style="color: #0000BB"><?php<br />$mysqli </span><span style="color: #007700">= new </span><span style="color: #0000BB">mysqli</span><span style="color: #007700">(</span><span style="color: #DD0000">"myapp"</span><span style="color: #007700">, </span><span style="color: #DD0000">"username"</span><span style="color: #007700">, </span><span style="color: #DD0000">"password"</span><span style="color: #007700">, </span><span style="color: #DD0000">"database"</span><span style="color: #007700">);<br />if (!</span><span style="color: #0000BB">$mysqli</span><span style="color: #007700">)<br /> </span><span style="color: #FF8000">/* Of course, your error handling is nicer... */<br /> </span><span style="color: #007700">die(</span><span style="color: #0000BB">sprintf</span><span style="color: #007700">(</span><span style="color: #DD0000">"[%d] %s\n"</span><span style="color: #007700">, </span><span style="color: #0000BB">mysqli_connect_errno</span><span style="color: #007700">(), </span><span style="color: #0000BB">mysqli_connect_error</span><span style="color: #007700">()));<br /><br />if (!</span><span style="color: #0000BB">$mysqli</span><span style="color: #007700">-></span><span style="color: #0000BB">query</span><span style="color: #007700">(</span><span style="color: #DD0000">"DROP TABLE IF EXISTS test"</span><span style="color: #007700">) ||<br /> !</span><span style="color: #0000BB">$mysqli</span><span style="color: #007700">-></span><span style="color: #0000BB">query</span><span style="color: #007700">(</span><span style="color: #DD0000">"CREATE TABLE test(id INT)"</span><span style="color: #007700">) ||<br /> !</span><span style="color: #0000BB">$mysqli</span><span style="color: #007700">-></span><span style="color: #0000BB">query</span><span style="color: #007700">(</span><span style="color: #DD0000">"INSERT INTO test(id) VALUES (1)"</span><span style="color: #007700">))<br /> die(</span><span style="color: #0000BB">sprintf</span><span style="color: #007700">(</span><span style="color: #DD0000">"[%d] %s\n"</span><span style="color: #007700">, </span><span style="color: #0000BB">$mysqli</span><span style="color: #007700">-></span><span style="color: #0000BB">errno</span><span style="color: #007700">, </span><span style="color: #0000BB">$mysqli</span><span style="color: #007700">-></span><span style="color: #0000BB">error</span><span style="color: #007700">));<br /><br /></span><span style="color: #FF8000">/* Explicitly allow eventual consistency and caching (TTL <= 60 seconds) */<br /></span><span style="color: #007700">if (</span><span style="color: #0000BB">false </span><span style="color: #007700">== </span><span style="color: #0000BB">mysqlnd_ms_set_qos</span><span style="color: #007700">(</span><span style="color: #0000BB">$mysqli</span><span style="color: #007700">, </span><span style="color: #0000BB">MYSQLND_MS_QOS_CONSISTENCY_EVENTUAL</span><span style="color: #007700">, </span><span style="color: #0000BB">MYSQLND_MS_QOS_OPTION_CACHE</span><span style="color: #007700">, </span><span style="color: #0000BB">60</span><span style="color: #007700">)) {<br /> die(</span><span style="color: #0000BB">sprintf</span><span style="color: #007700">(</span><span style="color: #DD0000">"[%d] %s\n"</span><span style="color: #007700">, </span><span style="color: #0000BB">$mysqli</span><span style="color: #007700">-></span><span style="color: #0000BB">errno</span><span style="color: #007700">, </span><span style="color: #0000BB">$mysqli</span><span style="color: #007700">-></span><span style="color: #0000BB">error</span><span style="color: #007700">));<br />}<br /><br /></span><span style="color: #FF8000">/* To make this example work, we must wait for a slave to catch up. Brute force style. */<br /></span><span style="color: #0000BB">$attempts </span><span style="color: #007700">= </span><span style="color: #0000BB">0</span><span style="color: #007700">;<br />do {<br /> </span><span style="color: #FF8000">/* check if slave has the table */<br /> </span><span style="color: #007700">if (</span><span style="color: #0000BB">$res </span><span style="color: #007700">= </span><span style="color: #0000BB">$mysqli</span><span style="color: #007700">-></span><span style="color: #0000BB">query</span><span style="color: #007700">(</span><span style="color: #DD0000">"SELECT id FROM test"</span><span style="color: #007700">)) {<br /> break;<br /> } else if (</span><span style="color: #0000BB">$mysqli</span><span style="color: #007700">-></span><span style="color: #0000BB">errno</span><span style="color: #007700">) {<br /> die(</span><span style="color: #0000BB">sprintf</span><span style="color: #007700">(</span><span style="color: #DD0000">"[%d] %s\n"</span><span style="color: #007700">, </span><span style="color: #0000BB">$mysqli</span><span style="color: #007700">-></span><span style="color: #0000BB">errno</span><span style="color: #007700">, </span><span style="color: #0000BB">$mysqli</span><span style="color: #007700">-></span><span style="color: #0000BB">error</span><span style="color: #007700">));<br /> }<br /> </span><span style="color: #FF8000">/* wait for slave to catch up */<br /> </span><span style="color: #0000BB">usleep</span><span style="color: #007700">(</span><span style="color: #0000BB">200000</span><span style="color: #007700">);<br />} while (</span><span style="color: #0000BB">$attempts</span><span style="color: #007700">++ < </span><span style="color: #0000BB">10</span><span style="color: #007700">);<br /><br /></span><span style="color: #FF8000">/* Query has been run on a slave, result is in the cache */<br /></span><span style="color: #0000BB">assert</span><span style="color: #007700">(</span><span style="color: #0000BB">$res</span><span style="color: #007700">);<br /></span><span style="color: #0000BB">var_dump</span><span style="color: #007700">(</span><span style="color: #0000BB">$res</span><span style="color: #007700">-></span><span style="color: #0000BB">fetch_assoc</span><span style="color: #007700">());<br /><br /></span><span style="color: #FF8000">/* Served from cache */<br /></span><span style="color: #0000BB">$res </span><span style="color: #007700">= </span><span style="color: #0000BB">$mysqli</span><span style="color: #007700">-></span><span style="color: #0000BB">query</span><span style="color: #007700">(</span><span style="color: #DD0000">"SELECT id FROM test"</span><span style="color: #007700">);<br /></span><span style="color: #0000BB">?></span>
</span>
</code></div>
</div>
</div>
</p>
<p class="para">
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 <span class="function"><a href="function.mysqlnd-ms-set-qos.html" class="function">mysqlnd_ms_set_qos()</a></span>.
Then, the result set of every read-only statement is cached for upto that
many seconds as allowed with <span class="function"><a href="function.mysqlnd-ms-set-qos.html" class="function">mysqlnd_ms_set_qos()</a></span>.
</p>
<p class="para">
The actual TTL is lower or equal to the value set
with <span class="function"><a href="function.mysqlnd-ms-set-qos.html" class="function">mysqlnd_ms_set_qos()</a></span>. 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.
</p>
<p class="para">
<div class="example" id="example-1787">
<p><strong>Example #4 Read your writes and caching combined</strong></p>
<div class="example-contents">
<div class="phpcode"><code><span style="color: #000000">
<span style="color: #0000BB"><?php<br />$mysqli </span><span style="color: #007700">= new </span><span style="color: #0000BB">mysqli</span><span style="color: #007700">(</span><span style="color: #DD0000">"myapp"</span><span style="color: #007700">, </span><span style="color: #DD0000">"username"</span><span style="color: #007700">, </span><span style="color: #DD0000">"password"</span><span style="color: #007700">, </span><span style="color: #DD0000">"database"</span><span style="color: #007700">);<br />if (!</span><span style="color: #0000BB">$mysqli</span><span style="color: #007700">)<br /> </span><span style="color: #FF8000">/* Of course, your error handling is nicer... */<br /> </span><span style="color: #007700">die(</span><span style="color: #0000BB">sprintf</span><span style="color: #007700">(</span><span style="color: #DD0000">"[%d] %s\n"</span><span style="color: #007700">, </span><span style="color: #0000BB">mysqli_connect_errno</span><span style="color: #007700">(), </span><span style="color: #0000BB">mysqli_connect_error</span><span style="color: #007700">()));<br /><br />if (!</span><span style="color: #0000BB">$mysqli</span><span style="color: #007700">-></span><span style="color: #0000BB">query</span><span style="color: #007700">(</span><span style="color: #DD0000">"DROP TABLE IF EXISTS test"</span><span style="color: #007700">) ||<br /> !</span><span style="color: #0000BB">$mysqli</span><span style="color: #007700">-></span><span style="color: #0000BB">query</span><span style="color: #007700">(</span><span style="color: #DD0000">"CREATE TABLE test(id INT)"</span><span style="color: #007700">) ||<br /> !</span><span style="color: #0000BB">$mysqli</span><span style="color: #007700">-></span><span style="color: #0000BB">query</span><span style="color: #007700">(</span><span style="color: #DD0000">"INSERT INTO test(id) VALUES (1)"</span><span style="color: #007700">))<br /> die(</span><span style="color: #0000BB">sprintf</span><span style="color: #007700">(</span><span style="color: #DD0000">"[%d] %s\n"</span><span style="color: #007700">, </span><span style="color: #0000BB">$mysqli</span><span style="color: #007700">-></span><span style="color: #0000BB">errno</span><span style="color: #007700">, </span><span style="color: #0000BB">$mysqli</span><span style="color: #007700">-></span><span style="color: #0000BB">error</span><span style="color: #007700">));<br /><br /></span><span style="color: #FF8000">/* Explicitly allow eventual consistency and caching (TTL <= 60 seconds) */<br /></span><span style="color: #007700">if (</span><span style="color: #0000BB">false </span><span style="color: #007700">== </span><span style="color: #0000BB">mysqlnd_ms_set_qos</span><span style="color: #007700">(</span><span style="color: #0000BB">$mysqli</span><span style="color: #007700">, </span><span style="color: #0000BB">MYSQLND_MS_QOS_CONSISTENCY_EVENTUAL</span><span style="color: #007700">, </span><span style="color: #0000BB">MYSQLND_MS_QOS_OPTION_CACHE</span><span style="color: #007700">, </span><span style="color: #0000BB">60</span><span style="color: #007700">)) {<br /> die(</span><span style="color: #0000BB">sprintf</span><span style="color: #007700">(</span><span style="color: #DD0000">"[%d] %s\n"</span><span style="color: #007700">, </span><span style="color: #0000BB">$mysqli</span><span style="color: #007700">-></span><span style="color: #0000BB">errno</span><span style="color: #007700">, </span><span style="color: #0000BB">$mysqli</span><span style="color: #007700">-></span><span style="color: #0000BB">error</span><span style="color: #007700">));<br />}<br /><br /></span><span style="color: #FF8000">/* To make this example work, we must wait for a slave to catch up. Brute force style. */<br /></span><span style="color: #0000BB">$attempts </span><span style="color: #007700">= </span><span style="color: #0000BB">0</span><span style="color: #007700">;<br />do {<br /> </span><span style="color: #FF8000">/* check if slave has the table */<br /> </span><span style="color: #007700">if (</span><span style="color: #0000BB">$res </span><span style="color: #007700">= </span><span style="color: #0000BB">$mysqli</span><span style="color: #007700">-></span><span style="color: #0000BB">query</span><span style="color: #007700">(</span><span style="color: #DD0000">"SELECT id FROM test"</span><span style="color: #007700">)) {<br /> break;<br /> } else if (</span><span style="color: #0000BB">$mysqli</span><span style="color: #007700">-></span><span style="color: #0000BB">errno</span><span style="color: #007700">) {<br /> die(</span><span style="color: #0000BB">sprintf</span><span style="color: #007700">(</span><span style="color: #DD0000">"[%d] %s\n"</span><span style="color: #007700">, </span><span style="color: #0000BB">$mysqli</span><span style="color: #007700">-></span><span style="color: #0000BB">errno</span><span style="color: #007700">, </span><span style="color: #0000BB">$mysqli</span><span style="color: #007700">-></span><span style="color: #0000BB">error</span><span style="color: #007700">));<br /> }<br /> </span><span style="color: #FF8000">/* wait for slave to catch up */<br /> </span><span style="color: #0000BB">usleep</span><span style="color: #007700">(</span><span style="color: #0000BB">200000</span><span style="color: #007700">);<br />} while (</span><span style="color: #0000BB">$attempts</span><span style="color: #007700">++ < </span><span style="color: #0000BB">10</span><span style="color: #007700">);<br /><br /></span><span style="color: #0000BB">assert</span><span style="color: #007700">(</span><span style="color: #0000BB">$res</span><span style="color: #007700">);<br /><br /></span><span style="color: #FF8000">/* Query has been run on a slave, result is in the cache */<br /></span><span style="color: #0000BB">var_dump</span><span style="color: #007700">(</span><span style="color: #0000BB">$res</span><span style="color: #007700">-></span><span style="color: #0000BB">fetch_assoc</span><span style="color: #007700">());<br /><br /></span><span style="color: #FF8000">/* Served from cache */<br /></span><span style="color: #007700">if (!(</span><span style="color: #0000BB">$res </span><span style="color: #007700">= </span><span style="color: #0000BB">$mysqli</span><span style="color: #007700">-></span><span style="color: #0000BB">query</span><span style="color: #007700">(</span><span style="color: #DD0000">"SELECT id FROM test"</span><span style="color: #007700">)))<br /> die(</span><span style="color: #0000BB">sprintf</span><span style="color: #007700">(</span><span style="color: #DD0000">"[%d] %s\n"</span><span style="color: #007700">, </span><span style="color: #0000BB">$mysqli</span><span style="color: #007700">-></span><span style="color: #0000BB">errno</span><span style="color: #007700">, </span><span style="color: #0000BB">$mysqli</span><span style="color: #007700">-></span><span style="color: #0000BB">error</span><span style="color: #007700">));<br /></span><span style="color: #0000BB">var_dump</span><span style="color: #007700">(</span><span style="color: #0000BB">$res</span><span style="color: #007700">-></span><span style="color: #0000BB">fetch_assoc</span><span style="color: #007700">());<br /><br /></span><span style="color: #FF8000">/* Update on master */<br /></span><span style="color: #007700">if (!</span><span style="color: #0000BB">$mysqli</span><span style="color: #007700">-></span><span style="color: #0000BB">query</span><span style="color: #007700">(</span><span style="color: #DD0000">"UPDATE test SET id = 2"</span><span style="color: #007700">))<br /> die(</span><span style="color: #0000BB">sprintf</span><span style="color: #007700">(</span><span style="color: #DD0000">"[%d] %s\n"</span><span style="color: #007700">, </span><span style="color: #0000BB">$mysqli</span><span style="color: #007700">-></span><span style="color: #0000BB">errno</span><span style="color: #007700">, </span><span style="color: #0000BB">$mysqli</span><span style="color: #007700">-></span><span style="color: #0000BB">error</span><span style="color: #007700">));<br /><br /></span><span style="color: #FF8000">/* Read your writes */<br /></span><span style="color: #007700">if (</span><span style="color: #0000BB">false </span><span style="color: #007700">== </span><span style="color: #0000BB">mysqlnd_ms_set_qos</span><span style="color: #007700">(</span><span style="color: #0000BB">$mysqli</span><span style="color: #007700">, </span><span style="color: #0000BB">MYSQLND_MS_QOS_CONSISTENCY_SESSION</span><span style="color: #007700">)) {<br /> die(</span><span style="color: #0000BB">sprintf</span><span style="color: #007700">(</span><span style="color: #DD0000">"[%d] %s\n"</span><span style="color: #007700">, </span><span style="color: #0000BB">$mysqli</span><span style="color: #007700">-></span><span style="color: #0000BB">errno</span><span style="color: #007700">, </span><span style="color: #0000BB">$mysqli</span><span style="color: #007700">-></span><span style="color: #0000BB">error</span><span style="color: #007700">));<br />}<br /><br /></span><span style="color: #FF8000">/* Fetch latest data */<br /></span><span style="color: #007700">if (!(</span><span style="color: #0000BB">$res </span><span style="color: #007700">= </span><span style="color: #0000BB">$mysqli</span><span style="color: #007700">-></span><span style="color: #0000BB">query</span><span style="color: #007700">(</span><span style="color: #DD0000">"SELECT id FROM test"</span><span style="color: #007700">)))<br /> die(</span><span style="color: #0000BB">sprintf</span><span style="color: #007700">(</span><span style="color: #DD0000">"[%d] %s\n"</span><span style="color: #007700">, </span><span style="color: #0000BB">$mysqli</span><span style="color: #007700">-></span><span style="color: #0000BB">errno</span><span style="color: #007700">, </span><span style="color: #0000BB">$mysqli</span><span style="color: #007700">-></span><span style="color: #0000BB">error</span><span style="color: #007700">));<br /></span><span style="color: #0000BB">var_dump</span><span style="color: #007700">(</span><span style="color: #0000BB">$res</span><span style="color: #007700">-></span><span style="color: #0000BB">fetch_assoc</span><span style="color: #007700">());<br /></span><span style="color: #0000BB">?></span>
</span>
</code></div>
</div>
</div>
</p>
<p class="para">
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.
</p>
</div><hr /><div class="manualnavbar" style="text-align: center;">
<div class="prev" style="text-align: left; float: left;"><a href="mysqlnd-ms.quickstart.gtid.html">Global transaction IDs</a></div>
<div class="next" style="text-align: right; float: right;"><a href="mysqlnd-ms.quickstart.failover.html">Failover</a></div>
<div class="up"><a href="mysqlnd-ms.quickstart.html">Quickstart and Examples</a></div>
<div class="home"><a href="index.html">PHP Manual</a></div>
</div></body></html>
