Service level and consistency

Note:
Version requirement

Service 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.

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.

/* 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 operation

Checking 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.

/* 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