Copyright 1997-2012 the PHP Documentation Group.

The mysqlnd replication and load balancing plugin is implemented as a PHP extension. It is written in C and operates under the hood of PHP. During the startup of the PHP interpreter, in the module init phase of the PHP engine, it gets registered as a mysqlnd plugin to replace selected mysqlnd C methods.

At PHP runtime, it inspects queries sent from mysqlnd (PHP) to the MySQL server. If a query is recognized as read-only, it will be sent to one of the configured slave servers. Statements are considered read-only if they either start with SELECT, the SQL hint /*ms=slave*/ or a slave had been chosen for running the previous query, and the query started with the SQL hint /*ms=last_used*/. In all other cases, the query will be sent to the MySQL replication master server.

For better portability, applications should use the MYSQLND_MS_MASTER_SWITCH , MYSQLND_MS_SLAVE_SWITCH , and MYSQLND_MS_LAST_USED_SWITCH predefined mysqlnd_ms constants, instead of their literal values, such as /*ms=slave*/.

The plugin handles the opening and closing of database connections to both master and slave servers. From an application point of view, there continues to be only one connection handle. However, internally, this one public connection handle represents a pool of network connections that are managed by the plugin. The plugin proxies queries to the master server, and to the slaves using multiple connections.

Database connections have a state consisting of, for example, transaction status, transaction settings, character set settings, and temporary tables. The plugin will try to maintain the same state among all internal connections, whenever this can be done in an automatic and transparent way. In cases where it is not easily possible to maintain state among all connections, such as when using BEGIN TRANSACTION, the plugin leaves it to the user to handle.

Copyright 1997-2012 the PHP Documentation Group.

The replication and load balancing plugin changes the semantics of a PHP MySQL connection handle. The existing API of the PHP MySQL extensions (mysqli, mysql, and PDO_MYSQL) are not changed in a way that functions are added or removed. But their behaviour changes when using the plugin. Existing applications do not need to be adapted to a new API, but they may need to be modified because of the behaviour changes.

The plugin breaks the one-by-one relationship between a mysqli, mysql, and PDO_MYSQL connection handle and a MySQL network connection. And a mysqli, mysql, and PDO_MYSQL connection handle represents a local pool of connections to the configured MySQL replication master and MySQL replication slave servers. The plugin redirects queries to the master and slave servers. At some point in time one and the same PHP connection handle may point to the MySQL master server. Later on, it may point to one of the slave servers or still the master. Manipulating and replacing the network connection referenced by a PHP MySQL connection handle is not a transparent operation.

Every MySQL connection has a state. The state of the connections in the connection pool of the plugin can differ. Whenever the plugin switches from one wire connection to another, the current state of the user connection may change. The applications must be aware of this.

The following list shows what the connection state consists of. The list may not be complete.

Connection switches happen right before queries are executed. The plugin does not switch the current connection until the next statement is executed.

Broadcasted messages

The plugins philosophy is to align the state of connections in the pool only if the state is under full control of the plugin, or if it is necessary for security reasons. Just a few actions that change the state of the connection fall into this category.

The following is a list of connection client library calls that change state, and are broadcasted to all open connections in the connection pool.

If any of the listed calls below are to be executed, the plugin loops over all open master and slave connections. The loop continues until all servers have been contacted, and the loop does not break if a server indicates a failure. If possible, the failure will propagate to the called user API function, which may be detected depending on which underlying library function was triggered.

Broadcasting and lazy connections

The plugin does not proxy or “remember” all settings to apply them on connections opened in the future. This is important to remember, if using lazy connections. Lazy connections are connections which are not opened before the client sends the first connection. Use of lazy connections is the default plugin action.

The following connection library calls each changed state, and their execution is recorded for later use when lazy connections are opened. This helps ensure that the connection state of all connections in the connection pool are comparable.

Charsets and string escaping

Due to the use of lazy connections, which are a default, it can happen that an application tries to escape a string for use within SQL statements before a connection has been established. In this case string escaping is not possible. The string escape function does not know what charset to use before a connection has been established.

To overcome the problem a new configuration setting server_charset has been introduced in version 1.4.0.

Attention has to be paid on escaping strings with a certain charset but using the result on a connection that uses a different charset. Please note, that PECL/mysqlnd_ms manipulates connections and one application level connection represents a pool of multiple connections that all may have different default charsets. It is recommended to configure the servers involved to use the same default charsets. The configuration setting server_charset does help with this situation as well. If using server_charset, the plugin will set the given charset on all newly opened connections.

Copyright 1997-2012 the PHP Documentation Group.

Transaction handling is fundamentally changed. An SQL transaction is a unit of work that is run on one database server. The unit of work consists of one or more SQL statements.

By default the plugin is not aware of SQL transactions. The plugin may switch connections for load balancing at any point in time. Connection switches may happen in the middle of a transaction. This is against the nature of an SQL transaction. By default, the plugin is not transaction safe.

Any kind of MySQL load balancer must be hinted about the begin and end of a transaction. Hinting can either be done implicitly by monitoring API calls or using SQL hints. Both options are supported by the plugin, depending on your PHP version. API monitoring requires PHP 5.4.0 or newer. The plugin, like any other MySQL load balancer, cannot detect transaction boundaries based on the MySQL Client Server Protocol. Thus, entirely transparent transaction aware load balancing is not possible. The least intrusive option is API monitoring, which requires little to no application changes, depending on your application.

Please, find examples of using SQL hints or the API monitoring in the examples section. The details behind the API monitoring, which makes the plugin transaction aware, are described below.

Beginning with PHP 5.4.0, the mysqlnd library allows this plugin to subclass the library C API call set_autocommit(), to detect the status of autocommit mode.

The PHP MySQL extensions either issue a query (such as SET AUTOCOMMIT=0|1), or use the mysqlnd library call set_autocommit() to control the autocommit setting. If an extension makes use of set_autocommit(), the plugin can be made transaction aware. Transaction awareness cannot be achieved if using SQL to set the autocommit mode. The library function set_autocommit() is called by the mysqli_autocommit and PDO::setAttribute(PDO::ATTR_AUTOCOMMIT) user API calls.

The plugin configuration option trx_stickiness=master can be used to make the plugin transactional aware. In this mode, the plugin stops load balancing if autocommit becomes disabled, and directs all statements to the master until autocommit gets enabled.

An application that does not want to set SQL hints for transactions but wants to use the transparent API monitoring to avoid application changes must make sure that the autocommit settings is changed exclusively through the listed API calls.

API based transaction boundary detection has been improved with PHP 5.5.0 and PECL/mysqlnd_ms 1.5.0 to cover not only calls to mysqli_autocommit but also mysqli_begin, mysqli_commit and mysqli_rollback.

Copyright 1997-2012 the PHP Documentation Group.

Applications using PECL/mysqlnd_ms should implement proper error handling for all user API calls. And because the plugin changes the semantics of a connection handle, API calls may return unexpected errors. If using the plugin on a connection handle that no longer represents an individual network connection, but a connection pool, an error code and error message will be set on the connection handle whenever an error occurs on any of the network connections behind.

If using lazy connections, which is the default, connections are not opened until they are needed for query execution. Therefore, an API call for a statement execution may return a connection error. In the example below, an error is provoked when trying to run a statement on a slave. Opening a slave connection fails because the plugin configuration file lists an invalid host name for the slave.

{
    "myapp": {
        "master": {
            "master_0": {
                "host": "localhost",
                "socket": "\/tmp\/mysql.sock"
            }
        },
        "slave": {
            "slave_0": {
                "host": "invalid_host_name",
            }
        },
        "lazy_connections": 1
    }
}

The explicit activation of lazy connections is for demonstration purpose only.

<?php
$mysqli = new mysqli("myapp", "username", "password", "database");
if (mysqli_connect_errno())
  /* Of course, your error handling is nicer... */
  die(sprintf("[%d] %s\n", mysqli_connect_errno(), mysqli_connect_error()));

/* Connection 1, connection bound SQL user variable, no SELECT thus run on master */
if (!$mysqli->query("SET @myrole='master'")) {
 printf("[%d] %s\n", $mysqli->errno, $mysqli->error);
}

/* Connection 2, run on slave because SELECT, provoke connection error */
if (!($res = $mysqli->query("SELECT @myrole AS _role"))) {
 printf("[%d] %s\n", $mysqli->errno, $mysqli->error);
} else {
 $row = $res->fetch_assoc();
 $res->close();
 printf("@myrole = '%s'\n", $row['_role']);
}
$mysqli->close();
?>

    

PHP Warning:  mysqli::query(): php_network_getaddresses: getaddrinfo failed: Name or service not known in %s on line %d
PHP Warning:  mysqli::query(): [2002] php_network_getaddresses: getaddrinfo failed: Name or service not known (trying to connect via tcp://invalid_host_name:3306) in %s on line %d
[2002] php_network_getaddresses: getaddrinfo failed: Name or service not known

Applications are expected to handle possible connection errors by implementing proper error handling.

Depending on the use case, applications may want to handle connection errors differently from other errors. Typical connection errors are 2002 (CR_CONNECTION_ERROR) - Can't connect to local MySQL server through socket '%s' (%d), 2003 (CR_CONN_HOST_ERROR) - Can't connect to MySQL server on '%s' (%d) and 2005 (CR_UNKNOWN_HOST) - Unknown MySQL server host '%s' (%d). For example, the application may test for the error codes and manually perform a fail over. The plugins philosophy is not to offer automatic fail over, beyond master fail over, because fail over is not a transparent operation.

{
    "myapp": {
        "master": {
            "master_0": {
                "host": "localhost"
            }
        },
        "slave": {
            "slave_0": {
                "host": "invalid_host_name"
            },
            "slave_1": {
                "host": "192.168.78.136"
            }
        },
        "lazy_connections": 1,
        "filters": {
            "roundrobin": [

            ]
        }
    }
}

Explicitly activating lazy connections is done for demonstration purposes, as is round robin load balancing as opposed to the default random once type.

<?php
$mysqli = new mysqli("myapp", "username", "password", "database");
if (mysqli_connect_errno())
  /* Of course, your error handling is nicer... */
  die(sprintf("[%d] %s\n", mysqli_connect_errno(), mysqli_connect_error()));

/* Connection 1, connection bound SQL user variable, no SELECT thus run on master */
if (!$mysqli->query("SET @myrole='master'")) {
 printf("[%d] %s\n", $mysqli->errno, $mysqli->error);
}

/* Connection 2, first slave */
$res = $mysqli->query("SELECT VERSION() AS _version");
/* Hackish manual fail over */
if (2002 == $mysqli->errno || 2003 == $mysqli->errno || 2004 == $mysqli->errno) {
  /* Connection 3, first slave connection failed, trying next slave */
  $res = $mysqli->query("SELECT VERSION() AS _version");
}

if (!$res) {
  printf("ERROR, [%d] '%s'\n", $mysqli->errno, $mysqli->error);
} else {
 /* Error messages are taken from connection 3, thus no error */
 printf("SUCCESS, [%d] '%s'\n", $mysqli->errno, $mysqli->error);
 $row = $res->fetch_assoc();
 $res->close();
 printf("version = %s\n", $row['_version']);
}
$mysqli->close();
?>

    

[1045] Access denied for user 'username'@'localhost' (using password: YES)
PHP Warning:  mysqli::query(): php_network_getaddresses: getaddrinfo failed: Name or service not known in %s on line %d
PHP Warning:  mysqli::query(): [2002] php_network_getaddresses: getaddrinfo failed: Name or service not known (trying to connect via tcp://invalid_host_name:3306) in %s on line %d
SUCCESS, [0] ''
version = 5.6.2-m5-log

In some cases, it may not be easily possible to retrieve all errors that occur on all network connections through a connection handle. For example, let's assume a connection handle represents a pool of three open connections. One connection to a master and two connections to the slaves. The application changes the current database using the user API call mysqli_select_db, which then calls the mysqlnd library function to change the schemata. mysqlnd_ms monitors the function, and tries to change the current database on all connections to harmonize their state. Now, assume the master succeeds in changing the database, and both slaves fail. Upon the initial error from the first slave, the plugin will set an appropriate error on the connection handle. The same is done when the second slave fails to change the database. The error message from the first slave is lost.

Such cases can be debugged by either checking for errors of the type E_WARNING (see above) or, if no other option, investigation of the mysqlnd_ms debug and trace log.

Copyright 1997-2012 the PHP Documentation Group.

Some distributed database clusters make use of transient errors. A transient error is a temporary error that is likely to disappear soon. By definition it is safe for a client to ignore a transient error and retry the failed operation on the same database server. The retry is free of side effects. Clients are not forced to abort their work or to fail over to another database server immediately. They may enter a retry loop before to wait for the error to disappear before giving up on the database server. Transient errors can be seen, for example, when using MySQL Cluster. But they are not bound to any specific clustering solution per se.

PECL/mysqlnd_ms can perform an automatic retry loop in case of a transient error. This increases distribution transparency and thus makes it easier to migrate an application running on a single database server to run on a cluster of database servers without having to change the source of the application.

The automatic retry loop will repeat the requested operation up to a user configurable number of times and pause between the attempts for a configurable amount of time. If the error disappears during the loop, the application will never see it. If not, the error is forwarded to the application for handling.

In the example below a duplicate key error is provoked to make the plugin retry the failing query two times before the error is passed to the application. Between the two attempts the plugin sleeps for 100 milliseconds.

mysqlnd_ms.enable=1
mysqlnd_ms.collect_statistics=1

    

{
    "myapp": {
        "master": {
            "master_0": {
                "host": "localhost"
            }
        },
        "slave": {
            "slave_0": {
                "host": "192.168.78.136",
                "port": "3306"
            }
       },
       "transient_error": {
          "mysql_error_codes": [
            1062
          ],
          "max_retries": 2,
          "usleep_retry": 100
       }
    }
}

<?php
$mysqli = new mysqli("myapp", "username", "password", "database");
if (mysqli_connect_errno())
  /* Of course, your error handling is nicer... */
  die(sprintf("[%d] %s\n", mysqli_connect_errno(), mysqli_connect_error()));

if (!$mysqli->query("DROP TABLE IF EXISTS test") ||
    !$mysqli->query("CREATE TABLE test(id INT PRIMARY KEY)") ||
    !$mysqli->query("INSERT INTO test(id) VALUES (1))")) {
  printf("[%d] %s\n", $mysqli->errno, $mysqli->error);
}

/* Retry loop is completely transparent. Checking statistics is
 the only way to know about implicit retries */
$stats = mysqlnd_ms_get_stats();
printf("Transient error retries before error: %d\n", $stats['transient_error_retries']);

/* Provoking duplicate key error to see statistics change */
if (!$mysqli->query("INSERT INTO test(id) VALUES (1))")) {
  printf("[%d] %s\n", $mysqli->errno, $mysqli->error);
}

$stats = mysqlnd_ms_get_stats();
printf("Transient error retries after error: %d\n", $stats['transient_error_retries']);

$mysqli->close();
?>

    

Transient error retries before error: 0
[1062] Duplicate entry '1' for key 'PRIMARY'
Transient error retries before error: 2

Because the execution of the retry loop is transparent from a users point of view, the example checks the statistics provided by the plugin to learn about it.

As the example shows, the plugin can be instructed to consider any error transient regardless of the database servers error semantics. The only error that a stock MySQL server considers temporary has the error code 1297 . When configuring other error codes but 1297 make sure your configuration reflects the semantics of your clusters error codes.

The following mysqlnd C API calls are monitored by the plugin to check for transient errors: query(), change_user(), select_db(), set_charset(), set_server_option() prepare(), execute(), set_autocommit(), tx_begin(), tx_commit(), tx_rollback(), tx_commit_or_rollback(). The corresponding user API calls have similar names.

The maximum time the plugin may sleep during the retry loop depends on the function in question. The a retry loop for query(), prepare() or execute() will sleep for up to max_retries * usleep_retry milliseconds.

However, functions that control connection state are dispatched to all all connections. The retry loop settings are applied to every connection on which the command is to be run. Thus, such a function may interrupt program execution for longer than a function that is run on one server only. For example, set_autocommit() is dispatched to connections and may sleep up to (max_retries * usleep_retry) * number_of_open_connections) milliseconds. Please, keep this in mind when setting long sleep times and large retry numbers. Using the default settings of max_retries=1, usleep_retry=100 and lazy_connections=1 it is unlikely that you will ever see a delay of more than 1 second.

Copyright 1997-2012 the PHP Documentation Group.

By default, connection failover handling is left to the user. The application is responsible for checking return values of the database functions it calls and reacting to possible errors. If, for example, the plugin recognizes a query as a read-only query to be sent to the slave servers, and the slave server selected by the plugin is not available, the plugin will raise an error after not executing the statement.

Default: manual failover

It is up to the application to handle the error and, if required, re-issue the query to trigger the selection of another slave server for statement execution. The plugin will make no attempts to failover automatically, because the plugin cannot ensure that an automatic failover will not change the state of the connection. For example, the application may have issued a query which depends on SQL user variables which are bound to a specific connection. Such a query might return incorrect results if the plugin would switch the connection implicitly as part of automatic failover. To ensure correct results, the application must take care of the failover, and rebuild the required connection state. Therefore, by default, no automatic failover is performed by the plugin.

A user that does not change the connection state after opening a connection may activate automatic failover. Please note, that automatic failover logic is limited to connection attempts. Automatic failover is not used for already established connections. There is no way to instruct the plugin to attempt failover on a connection that has been connected to MySQL already in the past.

Automatic failover

The failover policy is configured in the plugins configuration file, by using the failover configuration directive.

Automatic and silent failover can be enabled through the failover configuration directive. Automatic failover can either be configured to try exactly one master after a slave failure or, alternatively, loop over slaves and masters before returning an error to the user. The number of connection attempts can be limited and failed hosts can be excluded from future load balancing attempts. Limiting the number of retries and remembering failed hosts are considered experimental features, albeit being reasonable stable. Syntax and semantics may change in future versions.

Please note, since version 1.5.0 automatic failover is disabled for the duration of a transaction if transaction stickiness is enabled and transaction boundaries have been detected. The plugin will not switch connections for the duration of a transaction. It will also not perform automatic and silent failover. Instead an error will be thrown. It is then left to the user to handle the failure of the transaction. Please check, the trx_stickiness documentation how to do this.

A basic manual failover example is provided within the error handling section.

Standby servers

Using weighted load balancing, introduced in PECL/mysqlnd 1.4.0, it is possible to configure standby servers that are sparsely used during normal operations. A standby server that is primarily used as a worst-case standby failover target can be assigned a very low weight/priority in relation to all other servers. As long as all servers are up and running the majority of the workload is assigned to the servers which have hight weight values. Few requests will be directed to the standby system which has a very low weight value.

Upon failure of the servers with a high priority, you can still failover to the standby, which has been given a low load balancing priority by assigning a low weight to it. Failover can be some manually or automatically. If done automatically, you may want to combine it with the remember_failed option.

At this point, it is not possible to instruct the load balancer to direct no requests at all to a standby. This may not be much of a limitation given that the highest weight you can assign to a server is 65535. Given two slaves, of which one shall act as a standby and has been assigned a weight of 1, the standby will have to handle far less than one percent of the overall workload.

Failover and primary copy

Please note, if using a primary copy cluster, such as MySQL Replication, it is difficult to do connection failover in case of a master failure. At any time there is only one master in the cluster for a given dataset. The master is a single point of failure. If the master fails, clients have no target to fail over write requests. In case of a master outage the database administrator must take care of the situation and update the client configurations, if need be.

Copyright 1997-2012 the PHP Documentation Group.

Four load balancing strategies are supported to distribute statements over the configured MySQL slave servers:

The load balancing policy is configured in the plugins configuration file using the random, roundrobin, and user filters.

Servers can be prioritized assigning a weight. A server that has been given a weight of two will get twice as many requests as a server that has been given the default weight of one. Prioritization can be handy in heterogenous environments. For example, you may want to assign more requests to a powerful machine than to a less powerful. Or, you may have configured servers that are close or far from the client, thus expose different latencies.

Copyright 1997-2012 the PHP Documentation Group.

The plugin executes read-only statements on the configured MySQL slaves, and all other queries on the MySQL master. Statements are considered read-only if they either start with SELECT, the SQL hint /*ms=slave*/, or if a slave had been chosen for running the previous query and the query starts with the SQL hint /*ms=last_used*/. In all other cases, the query will be sent to the MySQL replication master server. It is recommended to use the constants MYSQLND_MS_SLAVE_SWITCH , MYSQLND_MS_MASTER_SWITCH and MYSQLND_MS_LAST_USED_SWITCH instead of /*ms=slave*/. See also the list of mysqlnd_ms constants.

SQL hints are a special kind of standard compliant SQL comments. The plugin does check every statement for certain SQL hints. The SQL hints are described within the mysqlnd_ms constants documentation, constants that are exported by the extension. Other systems involved with the statement processing, such as the MySQL server, SQL firewalls, and SQL proxies, are unaffected by the SQL hints, because those systems are designed to ignore SQL comments.

The built-in read-write splitter can be replaced by a user-defined filter, see also the user filter documentation.

A user-defined read-write splitter can request the built-in logic to send a statement to a specific location, by invoking mysqlnd_ms_is_select.

Copyright 1997-2012 the PHP Documentation Group.

filters. PHP applications that implement a MySQL replication cluster must first identify a group of servers in the cluster which could execute a statement before the statement is executed by one of the candidates. In other words: a defined list of servers must be filtered until only one server is available.

The process of filtering may include using one or more filters, and filters can be chained. And they are executed in the order they are defined in the plugins configuration file.

Available filters:

The random filter implements the 'random' and 'random once' load balancing policies. The 'round robin' load balancing can be configured through the roundrobin filter. Setting a 'user defined callback' for server selection is possible with the user filter. The quality_of_service filter finds cluster nodes capable of delivering a certain service, for example, read-your-writes or, not lagging more seconds behind the master than allowed.

Filters can accept parameters to change their behaviour. The random filter accepts an optional sticky parameter. If set to true, the filter changes load balancing from random to random once. Random picks a random server every time a statement is to be executed. Random once picks a random server when the first statement is to be executed and uses the same server for the rest of the PHP request.

One of the biggest strength of the filter concept is the possibility to chain filters. This strength does not become immediately visible because tje random, roundrobin and user filters are supposed to output no more than one server. If a filter reduces the list of candidates for running a statement to only one server, it makes little sense to use that one server as input for another filter for further reduction of the list of candidates.

An example filter sequence that will fail:

A second type of filter exists: multi filter. A multi filter emits zero, one or multiple servers after processing. The quality_of_service filter is an example. If the service quality requested sets an upper limit for the slave lag and more than one slave is lagging behind less than the allowed number of seconds, the filter returns more than one cluster node. A multi filter must be followed by other to further reduce the list of candidates for statement execution until a candidate is found.

A filter sequence with the quality_of_service multi filter followed by a load balancing filter.

A filter sequence must not end with a multi filter. If trying to use a filter sequence which ends with a multi filter the plugin may emit a warning like (mysqlnd_ms) Error in configuration. Last filter is multi filter. Needs to be non-multi one. Stopping in %s on line %d. Furthermore, an appropriate error on the connection handle may be set.

Copyright 1997-2012 the PHP Documentation Group.

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.

Copyright 1997-2012 the PHP Documentation Group.

Idea and client-side emulation

PECL/mysqlnd_ms can do client-side transparent global transaction ID injection. In its most basic form, a global transaction identifier is a counter which is incremented for every transaction executed on the master. The counter is held in a table on the master. Slaves replicate the counter table.

In case of a master failure a database administrator can easily identify the most recent slave for promiting it as a new master. The most recent slave has the highest transaction identifier.

Application developers can ask the plugin for the global transaction identifier (GTID) for their last successful write operation. The plugin will return an identifier that refers to an transaction no older than that of the clients last write operation. Then, the GTID can be passed as a parameter to the quality of service (QoS) filter as an option for session consistency. Session consistency ensures read your writes. The filter ensures that all reads are either directed to a master or a slave which has replicated the write referenced by the GTID.

When injection is done

The plugin transparently maintains the GTID table on the master. In autocommit mode the plugin injects an UPDATE statement before executing the users statement for every master use. In manual transaction mode, the injection is done before the application calls commit() to close a transaction. The configuration option report_error of the GTID section in the plugins configuration file is used to control whether a failed injection shall abort the current operation or be ignored silently (default).

Please note, the PHP version requirements for transaction boundary monitoring and their limits.

Limitations

Client-side global transaction ID injection has shortcomings. The potential issues are not specific to PECL/mysqlnd_ms but are rather of general nature.

Using server-side global transaction identifier

Starting with PECL/mysqlnd_ms 1.3.0-alpha the MySQL 5.6.5-m8 or newer built-in global transaction identifier feature is supported. Use of the server feature lifts all of the above listed limitations. Please, see the MySQL Reference Manual for limitations and preconditions for using server built-in global transaction identifiers.

Whether to use the client-side emulation or the server built-in functionality is a question not directly related to the plugin, thus it is not discussed in depth. There are no plans to remove the client-side emulation and you can continue to use it, if the server-side solution is no option. This may be the case in heterogenous environments with old MySQL server or, if any of the server-side solution limitations is not acceptable.

From an applications perspective there is hardly a difference in using one or the other approach. The following properties differ.

Copyright 1997-2012 the PHP Documentation Group.

Support for MySQL replication clusters (asynchronous primary copy) is the main focus of PECL/mysqlnd_ms. The slaves of a MySQL replication cluster may or may not reflect the latest updates from the master. Slaves are asynchronous and can lag behind the master. A read from a slave is eventual consistent from a cluster-wide perspective.

The same level of consistency is offered by a local cache using time-to-live (TTL) invalidation strategy. Current data or stale data may be served. Eventually, data searched for in the cache is not available and the source of the cache needs to be accessed.

Given that both a MySQL Replication slave (asynchronous secondary) and a local TTL-driven cache deliver the same level of service it is possible to transparently replace a remote database access with a local cache access to gain better possibility.

As of PECL/mysqlnd_ms 1.3.0-beta the plugin is capable of transparently controlling PECL/mysqlnd_ms 1.1.0-alpha or newer to cache a read-only query if explicitly allowed by setting an appropriate quality of service through mysqlnd_ms_set_qos. Please, see the quickstart for a code example. Both plugins must be installed, PECL/mysqlnd_ms must be compiled to support the cache feature and PHP 5.4.0 or newer has to be used.

Applications have full control of cache usage and can request fresh data at any time, if need be. Thec ache usage can be enabled and disabled time during the execution of a script. The cache will be used if mysqlnd_ms_set_qos sets the quality of service to eventual consistency and enables cache usage. Cache usage is disabled by requesting higher consistency levels, for example, session consistency (read your writes). Once the quality of service has been relaxed to eventual consistency the cache can be used again.

If caching is enabled for a read-only statement, PECL/mysqlnd_ms may inject SQL hints to control caching by PECL/mysqlnd_qc. It may modify the SQL statement it got from the application. Subsequent SQL processors are supposed to ignore the SQL hints. A SQL hint is a SQL comment. Comments must not be ignored, for example, by the database server.

The TTL of a cache entry is computed on a per statement basis. Applications set an maximum age for the data they want to retrieve using mysqlnd_ms_set_qos. The age sets an approximate upper limit of how many seconds the data returned may lag behind the master.

The following logic is used to compute the actual TTL if caching is enabled. The logic takes the estimated slave lag into account for choosing a TTL. If, for example, there are two slaves lagging 5 and 10 seconds behind and the maximum age allowed is 60 seconds, the TTL is set to 50 seconds. Please note, the age setting is no more than an estimated guess.

The algorithm may seem expensive. SHOW SLAVE STATUS is a very fast operation. Given a sufficient number of requests and cache hits per second the cost of checking the slaves lag can easily outweight the costs of the cache decision.

Suggestions on a better algorithm are always welcome.

Copyright 1997-2012 the PHP Documentation Group.

Any application using any kind of MySQL cluster is faced with the same tasks:

The plugin is optimized for fulfilling these tasks in the context of a classical asynchronous MySQL replication cluster consisting of a single master and many slaves (primary copy). When using classical, asynchronous MySQL replication all of the above listed tasks need to be mastered at the client side.

Other types of MySQL cluster may have lower requirements on the application side. For example, if all nodes in the cluster can answer read and write requests, no read-write splitting needs to be done (multi-master, update-all). If all nodes in the cluster are synchronous, they automatically provide the highest possible quality of service which makes choosing a node easier. In this case, the plugin may serve the application after some reconfiguration to disable certain features, such as built-in read-write splitting.

Primary copy (MySQL Replication)

This is the primary use case of the plugin. Follow the hints given in the descriptions of each feature.

mysqlnd_ms.enable=1
mysqlnd_ms.config_file=/path/to/mysqlnd_ms_plugin.ini

{
  "myapp": {
    "master": {
      "master_1": {
        "host": "localhost",
        "socket": "\/tmp\/mysql57.sock"
      }
    },
    "slave": {
      "slave_0": {
        "host": "127.0.0.1",
        "port": 3308
      },
      "slave_1": {
        "host": "192.168.2.28",
        "port": 3306
      }
    },
    "filters": {
      "random": {
        "sticky": "1"
      }
    }
  }
}

Primary copy with multi primaries (MMM - MySQL Multi Master)

MySQL Replication allows you to create cluster topologies with multiple masters (primaries). Write-write conflicts are not handled by the replication system. This is no update anywhere setup. Thus, data must be partitioned manually and clients must redirected in accordance to the partitioning rules. The recommended setup is equal to the sharding setup below.

Manual sharding, possibly combined with primary copy and multiple primaries

Use SQL hints and the node group filter for clusters that use data partitioning but leave query redirection to the client. The example configuration shows a multi master setup with two shards.

mysqlnd_ms.enable=1
mysqlnd_ms.config_file=/path/to/mysqlnd_ms_plugin.ini
mysqlnd_ms.multi_master=1

{
  "myapp": {
    "master": {
      "master_1": {
        "host": "localhost",
        "socket": "\/tmp\/mysql57.sock"
      }
      "master_2": {
        "host": "192.168.2.27",
        "socket": "3306"
      }
    },
    "slave": {
      "slave_1": {
        "host": "127.0.0.1",
        "port": 3308
      },
      "slave_2": {
        "host": "192.168.2.28",
        "port": 3306
      }
    },
    "filters": {
      "node_groups": {
        "Partition_A" : {
          "master": ["master_1"],
          "slave": ["slave_1"]
        },
        "Partition_B" : {
          "master": ["master_2"],
          "slave": ["slave_2"]
        }
      },
      "roundrobin": []
    }
  }
}

The plugin can also be used with a loose collection of unrelated shards. For such a cluster, configure masters only and disable read write splitting. The nodes of such a cluster are called masters in the plugin configuration as they accept both reads and writes for their partition.

Using synchronous update everywhere clusters such as MySQL Cluster

MySQL Cluster is a synchronous cluster solution. All cluster nodes accept read and write requests. In the context of the plugin, all nodes shall be considered as masters.

Use the load balancing and fail over features only.

Disabling built-in read-write splitting.

Configure masters only.

mysqlnd_ms.enable=1
mysqlnd_ms.config_file=/path/to/mysqlnd_ms_plugin.ini
mysqlnd_ms.multi_master=1
mysqlnd_ms.disable_rw_split=1

  "myapp": {
    "master": {
      "master_1": {
        "host": "localhost",
        "socket": "\/tmp\/mysql57.sock"
      },
      "master_2": {
        "host": "192.168.2.28",
        "port": 3306
      }
    },
    "slave": {
    },
    "filters": {
      "roundrobin": {
      }
    },
    "failover": {
      "strategy": "loop_before_master",
      "remember_failed": true
    }
  }
}

If running an update everywhere cluster that has no built-in partitioning to avoid hot spots and high collision rates, consider using the node groups filter to keep updates on a frequently accessed table on one of the nodes. This may help to reduce collision rates and thus improve performance.