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:
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.
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 idenentifier 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.