<!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>Service level and consistency</title>
</head>
<body><div class="manualnavbar" style="text-align: center;">
<div class="prev" style="text-align: left; float: left;"><a href="mysqlnd-ms.quickstart.transactions.html">Transactions</a></div>
<div class="next" style="text-align: right; float: right;"><a href="mysqlnd-ms.quickstart.gtid.html">Global transaction IDs</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.qos-consistency" class="section">
<h2 class="title">Service level and consistency</h2>
<blockquote class="note"><p><strong class="note">Note</strong>:
<strong>Version requirement</strong><br />
<p class="para">
Service levels have been introduced in PECL mysqlnd_ms version 1.2.0-alpha.
<span class="function"><a href="function.mysqlnd-ms-set-qos.html" class="function">mysqlnd_ms_set_qos()</a></span>
is available with PHP 5.4.0 or newer.
</p>
</p></blockquote>
<p class="para">
Different types of MySQL cluster solutions offer different service and
data consistency levels to their users. An asynchronous MySQL replication cluster
offers eventual consistency by default. A read executed on an asynchronous slave
may return current, stale or no data at all, depending on whether the slave
has replayed all changesets from the master or not.
</p>
<p class="para">
Applications using an MySQL replication cluster need to be designed to work
correctly with eventual consistent data. In some cases, however, stale data
is not acceptable. In those cases only certain slaves or even only master accesses are
allowed to achieve the required quality of service from the cluster.
</p>
<p class="para">
As of PECL mysqlnd_ms 1.2.0 the plugin is capable of selecting
MySQL replication nodes automatically that deliver session consistency or
strong consistency. Session consistency means that one client can read its writes.
Other clients may or may not see the clients' write. Strong consistency means
that all clients will see all writes from the client.
</p>
<p class="para">
<div class="example" id="example-1770">
<p><strong>Example #1 Session consistency: read your writes</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-1771">
<p><strong>Example #2 Requesting session consistency</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 /></span><span style="color: #FF8000">/* read-write splitting: master used */<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">"INSERT INTO orders(order_id, item) VALUES (1, 'christmas tree, 1.8m')"</span><span style="color: #007700">)) {<br /> </span><span style="color: #FF8000">/* Please use better error handling in your code */<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</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">/* Request session consistency: read your writes */<br /></span><span style="color: #007700">if (!</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 /></span><span style="color: #FF8000">/* Plugin picks a node which has the changes, here: master */<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 item FROM orders WHERE order_id = 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: #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">/* Back to eventual consistency: stale data allowed */<br /></span><span style="color: #007700">if (!</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">))<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">/* Plugin picks any slave, stale data is allowed */<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 item, price FROM specials"</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">?></span>
</span>
</code></div>
</div>
</div>
</p>
<p class="para">
Service levels can be set in the plugins configuration file and at runtime
using <span class="function"><a href="function.mysqlnd-ms-set-qos.html" class="function">mysqlnd_ms_set_qos()</a></span>.
In the example the function is used to enforce
session consistency (read your writes) for all future statements until further notice.
The <em>SELECT</em> statement on the <em>orders</em> table
is run on the master to ensure the previous write can be seen by the client.
Read-write splitting logic has been adapted to fulfill the service level.
</p>
<p class="para">
After the application has read its changes from the <em>orders</em> table
it returns to the default service level, which is eventual consistency. Eventual
consistency puts no restrictions on choosing a node for statement execution.
Thus, the <em>SELECT</em> statement on the <em>specials</em>
table is executed on a slave.
</p>
<p class="para">
The new functionality supersedes the use of SQL hints and the
<em>master_on_write</em> configuration option. In many cases
<span class="function"><a href="function.mysqlnd-ms-set-qos.html" class="function">mysqlnd_ms_set_qos()</a></span> is easier to use, more powerful
improves portability.
</p>
<p class="para">
<div class="example" id="example-1772">
<p><strong>Example #3 Maximum age/slave lag</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"
}
},
"failover" : "master"
}
}</pre>
</div>
</div>
</div>
</p>
<p class="para">
<div class="example" id="example-1773">
<p><strong>Example #4 Limiting slave lag</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 /></span><span style="color: #FF8000">/* Read from slaves lagging no more than four seconds */<br /></span><span style="color: #0000BB">$ret </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">,<br /> </span><span style="color: #0000BB">MYSQLND_MS_QOS_CONSISTENCY_EVENTUAL</span><span style="color: #007700">,<br /> </span><span style="color: #0000BB">MYSQLND_MS_QOS_OPTION_AGE</span><span style="color: #007700">, </span><span style="color: #0000BB">4</span><span style="color: #007700">);<br /><br />if (!</span><span style="color: #0000BB">$ret</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">/* Plugin picks any slave, which may or may not have the changes */<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 item, price FROM daytrade"</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">/* Back to default: use of all slaves and masters permitted */<br /></span><span style="color: #007700">if (!</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">))<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">?></span>
</span>
</code></div>
</div>
</div>
</p>
<p class="para">
The eventual consistency service level can be used with an optional
parameter to set a maximum slave lag for choosing slaves. If set,
the plugin checks <em>SHOW SLAVE STATUS</em> for all
configured slaves. In case of the example, only slaves
for which <em>Slave_IO_Running=Yes</em>,
<em>Slave_SQL_Running=Yes</em> and
<em>Seconds_Behind_Master <= 4</em>
is true are considered for executing the statement
<em>SELECT item, price FROM daytrade</em>.
</p>
<p class="para">
Checking <em>SHOW SLAVE STATUS</em> is done transparently from
an applications perspective. Errors, if any, are reported as
warnings. No error will be set on the connection handle. Even if all
<em>SHOW SLAVE STATUS</em> SQL statements executed by
the plugin fail, the execution of the users statement is not stopped, given
that master fail over is enabled. Thus, no application changes are required.
</p>
<blockquote class="note"><p><strong class="note">Note</strong>:
<strong>Expensive and slow operation</strong><br />
<p class="para">
Checking <em>SHOW SLAVE STATUS</em> for all slaves adds overhead
to the application. It is an expensive and slow background operation.
Try to minimize the use of it. Unfortunately, a MySQL replication cluster
does not give clients the possibility to request a list of candidates
from a central instance.
Thus, a more efficient way of checking the slaves lag is not available.
</p>
<p class="para">
Please, note the limitations and properties of <em>SHOW SLAVE STATUS</em>
as explained in the MySQL reference manual.
</p>
</p></blockquote>
<p class="para">
To prevent mysqlnd_ms from emitting a warning if no slaves can be found
that lag no more than the defined number of seconds behind the master,
it is necessary to enable master fail over in the plugins configuration file.
If no slaves can be found and fail over is turned on, the plugin
picks a master for executing the statement.
</p>
<p class="para">
If no slave can be found and fail over is turned off, the plugin emits
a warning, it does not execute the statement and it sets an error
on the connection.
</p>
<p class="para">
<div class="example" id="example-1774">
<p><strong>Example #5 Fail over not set</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-1775">
<p><strong>Example #6 No slave within time limit</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 /></span><span style="color: #FF8000">/* Read from slaves lagging no more than four seconds */<br /></span><span style="color: #0000BB">$ret </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">,<br /> </span><span style="color: #0000BB">MYSQLND_MS_QOS_CONSISTENCY_EVENTUAL</span><span style="color: #007700">,<br /> </span><span style="color: #0000BB">MYSQLND_MS_QOS_OPTION_AGE</span><span style="color: #007700">, </span><span style="color: #0000BB">4</span><span style="color: #007700">);<br /><br />if (!</span><span style="color: #0000BB">$ret</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">/* Plugin picks any slave, which may or may not have the changes */<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 item, price FROM daytrade"</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">/* Back to default: use of all slaves and masters permitted */<br /></span><span style="color: #007700">if (!</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">))<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">?></span>
</span>
</code></div>
</div>
<div class="example-contents"><p>The above example will output:</p></div>
<div class="example-contents screen">
<div class="cdata"><pre>
PHP Warning: mysqli::query(): (mysqlnd_ms) Couldn't find the appropriate slave connection. 0 slaves to choose from. Something is wrong in %s on line %d
PHP Warning: mysqli::query(): (mysqlnd_ms) No connection selected by the last filter in %s on line %d
[2000] (mysqlnd_ms) No connection selected by the last filter
</pre></div>
</div>
</div>
</p>
</div><hr /><div class="manualnavbar" style="text-align: center;">
<div class="prev" style="text-align: left; float: left;"><a href="mysqlnd-ms.quickstart.transactions.html">Transactions</a></div>
<div class="next" style="text-align: right; float: right;"><a href="mysqlnd-ms.quickstart.gtid.html">Global transaction IDs</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>
