Oracle 5.0, Reference Manual

Page: 2613 / 3232

Mysqlnd replication and load balancing plugin (
mysqlnd_ms
)
2593
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.