Service level and consistency

Note: Version
requirement

Service levels have been introduced in mysqlnd_ms
version 1.2.0-alpha. mysqlnd_ms_set_qos() requires PHP 5.4.0 or
newer.

The plugin can be used with different kinds of
MySQL database clusters. Different clusters can deliver different
levels of service to applications. The service levels can be
grouped by the data consistency levels that can be achieved. The
plugin knows about:

• eventual
  consistency
• session
  consistency
• strong
  consistency

Depending how a cluster is used it may be possible
to achieve higher service levels than the default one. For example,
a read from an asynchronous MySQL replication slave is eventual
consistent. Thus, one may say the default consistency level of a
MySQL replication cluster is eventual consistency. However, if the
master only is used by a client for reading and writing during a
session, session consistency (read your writes) is given. PECL
mysqlnd 1.2.0 abstracts the details of choosing an appropriate node
for any of the above service levels from the user.

Service levels can be set through the
qualify-of-service filter in the plugins
configuration file and at runtime using the function
mysqlnd_ms_set_qos().

The plugin defines the different service levels as
follows.

Eventual consistency is the default service
provided by an asynchronous cluster, such as classical MySQL
replication. A read operation executed on an arbitrary node may or
may not return stale data. The applications view of the data is
eventual consistent.

Session consistency is given if a client can always
read its own writes. An asynchronous MySQL replication cluster can
deliver session consistency if clients always use the master after
the first write or never query a slave which has not yet replicated
the clients write operation.

The plugins understanding of strong consistency is
that all clients always see the committed writes of all other
clients. This is the default when using MySQL Cluster or any other
cluster offering synchronous data distribution.

Service level
parameters

Eventual consistency and session consistency
service level accept parameters.

Eventual consistency is the service provided by
classical MySQL replication. By default, all nodes qualify for read
requests. An optional age parameter can be given to filter
out nodes which lag more than a certain number of seconds behind
the master. The plugin is using SHOW SLAVE STATUS to
measure the lag. Please, see the MySQL reference manual to learn
about accuracy and reliability of the SHOW SLAVE STATUS
command.

Session consistency (read your writes) accepts an
optional GTID parameter to consider reading not only from
the master but also from slaves which already have replicated a
certain write described by its transaction identifier. This way,
when using asynchronous MySQL replication, read requests may be
load balanced over slaves while still ensuring session
consistency.

The latter requires the use of client-side global transaction
id injection.

Advantages of the new
approach

The new approach supersedes the use of SQL hints
and the configuration option master_on_write in some
respects. If an application running on top of an asynchronous MySQL
replication cluster cannot accept stale data for certain reads, it
is easier to tell the plugin to choose appropriate nodes than
prefixing all read statements in question with the SQL hint to
enforce the use of the master. Furthermore, the plugin may be able
to use selected slaves for reading.

The master_on_write configuration option
makes the plugin use the master after the first write (session
consistency, read your writes). In some cases, session consistency
may not be needed for the rest of the session but only for some,
few read operations. Thus, master_on_write may result in
more read load on the master than necessary. In those cases it is
better to request a higher than default service level only for
those reads that actually need it. Once the reads are done, the
application can return to default service level. Switching between
service levels is only possible using mysqlnd_ms_set_qos().

Performance
considerations

A MySQL replication cluster cannot tell clients
which slaves are capable of delivering which level of service.
Thus, in some cases, clients need to query the slaves to check
their status. PECL mysqlnd_ms transparently runs the necessary SQL
in the background. However, this is an expensive and slow
operation. SQL statements are run if eventual consistency is
combined with an age (slave lag) limit and if session consistency
is combined with a global transaction ID.

If eventual consistency is combined with an maximum
age (slave lag), the plugin selects candidates for statement
execution and load balancing for each statement as follows. If the
statement is a write all masters are considered as candidates.
Slaves are not checked and not considered as candidates. If the
statement is a read, the plugin transparently executes SHOW
SLAVE STATUS on every slaves connection. It will loop over all
connections, send the statement and then start checking for
results. Usually, this is slightly faster than a loop over all
connections in which for every connection a query is send and the
plugin waits for its results. A slave is considered a candidate if
SHOW SLAVE STATUS reports Slave_IO_Running=Yes,
Slave_SQL_Running=Yes and Seconds_Behind_Master
is less or equal than the allowed maximum age. In case of an SQL
error, the plugin emits a warning but does not set an error on the
connection. The error is not set to make it possible to use the
plugin as a drop-in.

If session consistency is combined with a global
transaction ID, the plugin executes the SQL statement set with the
fetch_last_gtid entry of the
global_transaction_id_injection section from the plugins
configuration file. Further details are identical to those
described above.

In version 1.2.0 no additional optimizations are
done for executing background queries. Future versions may contain
optimizations, depending on user demand.

If no parameters and options are set, no SQL is
needed. In that case, the plugin consider all nodes of the type
shown below.

• eventual consistency, no
  further options set: all masters, all slaves
• session consistency, no
  further options set: all masters
• strong consistency (no
  options allowed): all masters

Throttling

The quality of service filter can be combined with
Global transaction
IDs to throttle clients. Throttling does reduce the write load
on the master by slowing down clients. If session consistency is
requested and global transactions identifier are used to check the
status of a slave, the check can be done in two ways. By default a
slave is checked and skipped immediately if it does not match the
criteria for session consistency. Alternatively, the plugin can
wait for a slave to catch up to the master until session
consistency is possible. To enable the throttling, you have to set
wait_for_gtid_timeout configuration option.