Service levels have been introduced in mysqlnd_ms version
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.
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
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
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
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.
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
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
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
SHOW SLAVE STATUS reports
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
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
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.