Examples
Service level and consistency
Service level and consistency
Service level and consistency
Note: Version
requirementService levels have been introduced in PECL
mysqlnd_ms version 1.2.0-alpha. mysqlnd_ms_set_qos() is available with PHP
5.4.0 or newer.
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.
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.
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.
Example #1 Session consistency: read your
writes
{
"myapp": {
"master": {
"master_0": {
"host": "localhost",
"socket": "\/tmp\/mysql.sock"
}
},
"slave": {
"slave_0": {
"host": "127.0.0.1",
"port": "3306"
}
}
}
}
Example #2 Requesting session consistency
<?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()));
}
/* read-write splitting: master used */
if (!$mysqli->query("INSERT INTO orders(order_id, item) VALUES (1, 'christmas tree, 1.8m')")) {
/* Please use better error handling in your code */
die(sprintf("[%d] %s\n", $mysqli->errno, $mysqli->error));
}
/* 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));
}
/* Plugin picks a node which has the changes, here: master */
if (!$res = $mysqli->query("SELECT item FROM orders WHERE order_id = 1")) {
die(sprintf("[%d] %s\n", $mysqli->errno, $mysqli->error));
}
var_dump($res->fetch_assoc());
/* Back to eventual consistency: stale data allowed */
if (!mysqlnd_ms_set_qos($mysqli, MYSQLND_MS_QOS_CONSISTENCY_EVENTUAL)) {
die(sprintf("[%d] %s\n", $mysqli->errno, $mysqli->error));
}
/* Plugin picks any slave, stale data is allowed */
if (!$res = $mysqli->query("SELECT item, price FROM specials")) {
die(sprintf("[%d] %s\n", $mysqli->errno, $mysqli->error));
}
?>Service levels can be set in the plugins
configuration file and at runtime using mysqlnd_ms_set_qos(). In the example the
function is used to enforce session consistency (read your writes)
for all future statements until further notice. The SELECT
statement on the orders 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.
After the application has read its changes from the
orders 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
SELECT statement on the specials table is
executed on a slave.
The new functionality supersedes the use of SQL
hints and the master_on_write configuration option. In
many cases mysqlnd_ms_set_qos() is easier to use, more
powerful improves portability.
Example #3 Maximum age/slave lag
{
"myapp": {
"master": {
"master_0": {
"host": "localhost",
"socket": "\/tmp\/mysql.sock"
}
},
"slave": {
"slave_0": {
"host": "127.0.0.1",
"port": "3306"
}
},
"failover" : "master"
}
}
Example #4 Limiting slave lag
<?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()));
}
/* Read from slaves lagging no more than four seconds */
$ret = mysqlnd_ms_set_qos(
$mysqli,
MYSQLND_MS_QOS_CONSISTENCY_EVENTUAL,
MYSQLND_MS_QOS_OPTION_AGE,
4
);
if (!
$ret) {
die(sprintf("[%d] %s\n", $mysqli->errno, $mysqli->error));
}
/* Plugin picks any slave, which may or may not have the changes */
if (!$res = $mysqli->query("SELECT item, price FROM daytrade")) {
die(sprintf("[%d] %s\n", $mysqli->errno, $mysqli->error));
}
/* Back to default: use of all slaves and masters permitted */
if (!mysqlnd_ms_set_qos($mysqli, MYSQLND_MS_QOS_CONSISTENCY_EVENTUAL)) {
die(sprintf("[%d] %s\n", $mysqli->errno, $mysqli->error));
}
?>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 SHOW SLAVE STATUS for
all configured slaves. In case of the example, only slaves for
which Slave_IO_Running=Yes, Slave_SQL_Running=Yes
and Seconds_Behind_Master <= 4 is true are considered
for executing the statement SELECT item, price FROM
daytrade.
Checking SHOW SLAVE STATUS 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 SHOW SLAVE STATUS 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.
Note: Expensive and slow
operationChecking SHOW SLAVE STATUS 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.Please, note the limitations and properties of
SHOW SLAVE STATUS as explained in the MySQL reference
manual.
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.
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.
Example #5 Fail over not set
{
"myapp": {
"master": {
"master_0": {
"host": "localhost",
"socket": "\/tmp\/mysql.sock"
}
},
"slave": {
"slave_0": {
"host": "127.0.0.1",
"port": "3306"
}
}
}
}
Example #6 No slave within time limit
<?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()));
}
/* Read from slaves lagging no more than four seconds */
$ret = mysqlnd_ms_set_qos(
$mysqli,
MYSQLND_MS_QOS_CONSISTENCY_EVENTUAL,
MYSQLND_MS_QOS_OPTION_AGE,
4
);
if (!
$ret) {
die(sprintf("[%d] %s\n", $mysqli->errno, $mysqli->error));
}
/* Plugin picks any slave, which may or may not have the changes */
if (!$res = $mysqli->query("SELECT item, price FROM daytrade")) {
die(sprintf("[%d] %s\n", $mysqli->errno, $mysqli->error));
}
/* Back to default: use of all slaves and masters permitted */
if (!mysqlnd_ms_set_qos($mysqli, MYSQLND_MS_QOS_CONSISTENCY_EVENTUAL)) {
die(sprintf("[%d] %s\n", $mysqli->errno, $mysqli->error));
}
?>The above example will output:
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