This is a community wiki page on the mysqlnd replication and load balancing plugin (short: mysqlnd_ms).

PECL/mysqlnd_ms adds MySQL replication and load balancing support to all PHP MySQL extensions (mysql, mysqli, PDO_MySQL), if compiled to use the mysqlnd library (PHP 5.3+). The plugin does read-write splitting to send write statements to a MySQL master and read statements to a MySQL slave. A built-in load balancer distributes the statements over the the configured MySQL servers. Multiple load balancing policies are available.

Application -> PHP MySQL extension (mysql, mysqli, PDO_MySQL) -> mysqlnd library -> mysqlnd_ms (read-write split, load balancing) -> MySQL

PECL/mysqlnd_ms extends the mysqlnd library. The PHP MySQL extensions (mysql, mysqli, PDO_MySQL) can be compiled to use the mysqlnd library. The mysqlnd library is part of the PHP source tree as of version 5.3 (ext/mysqlnd). Mysqlnd is a compile time default for all PHP MySQL extensions as of PHP 5.4. Because the PHP MySQL extensions, the mysqlnd library and the mysqlnd_ms library plugin are written in C, they operate transparent from an applications point of view.

Please, find further details in the reference manual, http://php.net/mysqlnd_ms .

Advantages:

• transparent from an applications point of view, depending on application use
• no application changes required for basic use cases
• easy to add to existing set ups
• client-side plugin
  • horizontal scale-out
  • no single point of failure

... blame: Andrey Hristov, Ulf Wendel, Johannes Schlüter

The plugin is available from PECL, http://pecl.php.net/package/mysqlnd_ms . The plugin is available under the terms of the PHP License.

PECL, http://svn.php.net/viewvc/pecl/mysqlnd_ms/

• 04/2011 - 1.0.1 alpha, first release of practical relevance
• 09/2010 - Initial check in, proof-of-concept

The manual is available from http://php.net/mysqlnd_ms .

* Assorted at http://blog.ulf-wendel.de

• Intro to PECL/mysqlnd_ms (4/7/2011) from Chris Barber, http://www.slideshare.net/cb1kenobi/intro-to-peclmysqlndms-472011
  • A practical view
  • Focus on 1.0 series
• The mysqlnd replication and load balancing plugin (9/2011), http://www.slideshare.net/nixnutz/the-mysqlnd-replication-and-load-balancing-plugin
  • A high-level overview
  • Motivation (web scale), quick overview on MySQL scale-out solutions (MySQL replication, MySQL Cluster, custom cluster)
  • How the plugin helps with typical application tasks and challenges if using any kind of DB cluster
  • Plugin features for MySQL replication
  • Outlook: MySQL replication filter support (client-side partitioning)

The following should be read as a proposal.

• [done] implement read/write splitter
  • logic: everything which does not begin with “SELECT” (or hint) is a write
• [done] implement load balancer
  • policies: random, round-robin, random-once
• [done] implement SQL hint parser
  • support hint to force use of any slave
  • support hint to force use of the master
  • support hint to force use of last used server for transaction support

• [done] >= 85% code coverage through tests
• [done] add micro benchmark framework to test directory
• [done] check if plugin and SQL hints cause <5% slowdown in worst case micro benchmarks (SELECT 1 FROM DUAL [WHERE 1=0])

• [done] publish through PECL

• [done] write and publish initial documentation covering all features
• [done]] get documentation reviewed

Refactored, stable 1.0 successor.

• [done] develop new JSON-based plugin config syntax

• [done] significant higher number of tests (achieved: almost 5x more compared to 1.0 alpha)
• [done] 75-90% code coverage through tests
• [done] automated cross-platform building and testing

• [done] High-level overview presentation
• [open] Documentation update

• [done] Introduce quality-of-service (QoS) filter. Service levels provided by QoS filter:
  • [done] eventual consistency, optional option slave lag
  • [done] session consistency, optional option GTID
  • [done] strong consistency

• [done] Allow setting connection quality at run time
• [done] Client-side global transaction ID injection (GTID)
• [done] Support fetching of most recent GTID

• [done] 85-90% code coverage through tests

• [done] Documentation update
• [done] Load balancing for PHP MySQL presentation
• [done] Assorted blog postings on GTID and QoS

• [done] Introduce QoS filter for caching
• [done] Update GTID related QoS logic for MySQL 5.6

• [done] 85-90% code coverage through tests

• [done] Documentation update
• [done] Presentations on GTID and MySQL 5.6
• [done] Assorted blog postings

• [done] More fail over options
  • [done] Automatic on-connect fail over, if activated, shall be done in a loop until a connection can be opened. Currently we stop after the first attempt. If automatic fail over is on, we try “next” and in case of a failure of “next” we stop. With the new logic there shall be no stop until all possible “next” have been tried.
  • [done] Remember failed hosts for the duration of a web request (the plugins' typical lifespan)

• [done] Refine QoS session consistency server selection policy
  • [done] Support “wait for GTID”. Currently we loop over all servers until we find a matching one. MySQL 5.6 allows SQL users either to fetch the latest GTID or SQL users can ask for a GTID and their request will block until the GTID has been replicated on the server. We should support the latter logic as well.

• [done] Improve load balancing
  • [done] Allow users to set a ratio/weight for query distribution. There may be machines in a cluster/config which can handle twice as many requests as others. Such machines shall be given a weight of, for example, two to indicate that the load balancer shall direct twice as many requests to them as to a machine with the weight of one.

• [done] 85-90% code coverage through tests

• [done] Update documentation
• [done] Assorted blog postings

Work has not started yet.

• [open] More fail over options
  • [open] Silent and automatic connection fail over if server returns configured error code
    • NOTE: this may require refactoring of four filters.
  • [open] Add failover timeout and failover (maximum) attempts limits/configuration settings

• [open] Support directing statements manually to a group of nodes for more efficient server cache usage
  • [open] Remember the most current server and test this one first when searching for a GTID (a synchronous server). Use of cached information is possible for the duration of a read-only request sequence. The cache must be flushed and refreshed for every write.

• [open] Work with php.net Windows team to provide PECL/mysqlnd_ms windows builds
  • Ensure the code builds
  • Encourage Windows team to create builds

• [open] 85-90% code coverage through tests
• [open] Cross-platform testing on all major MySQL build hosts

• [open] Update documentation
• [open] Assorted blog postings

If you got any feature proposal or, if you have any replication related client-side task which you want to see solved, please, describe and add it here. Use the existing entries as a pattern.

Whenever the plugin tries to connect to a node it may fail to do so. Connection attempts can be made when opening a connection or later, when executing a statement (lazy connections). By default the plugin bails out if the connection attempt fails. To make using the plugin as transparent as possible it is possible to optionally enable automatic fail over in case of failed connection attempts.

The fail over logic itself is basic. Smaller improvements will make it much more capable.

Automatic fail over is basic. We shall:

- make it configurable whether fail over node search tries one or all possible alternatives - link fail over to certain error codes - remember failed hosts to skip them for the rest of the web request

Up to version 1.3 the automatic fail over stops after trying one alternative. For example, if a connection to A fails we try B. If connecting to B fails we stop and bail out. In that case the user must handle the error although there may be node C and D, ... which could be used. In the future the search for an alternative shall not stop after B but continue until a connection has been established or there are no alternatives. If a connect to A fails, the plugin shall try B, C, D and so forth. Automatic fail over, automatic connection attempts shall not stop after trying B and failing to connect to B.

Whether this shall become a new default or become configurable is to be decided.

Furthermore, it shall be possible to link the automatic fail over to certain error codes: if A returns error n, try next. If A returns m, stop the search and bail out. This way we can differentiate between a server that rejects new requests, e.g. because its overloaded, and a server that has failed.

Upon request the plugin will remember failed nodes for the duration of a web request. If connecting to a node has failed once no further attempts will be made to connect to the node. This may lead to situations where nodes are skipped although they became available again in the meantime. This is ignore because most web requests are short-lived.

In large clusters users can improve performance by optimizing query distribution and server selection using criteria such as cache usage, distance or latency. Application developers shall be allowed to annotate a statement in a way that its executed on a certain group of nodes.

Server cache usage can be optimized by distributing queries in a way that they hit hot caches. For example, clients may want to run all accesses to table A, B and C on the node group 1 and table accesses to D preferably on group 2. Because we do not support automatic table filtering/partitioning clients shall be allowed to manually route requests to the appropriate groups.

Some nodes may be co-located to clients whereas others may be remote. This is often done in large systems when storing multiple copies, e.g. on the machine, on the rack, in the same data center, in a different data center. In case of MySQL Replication its unlikely to find such highly optimized setups, however, there may be nodes closer to the client than others. Nodes closer to a client may be given a certain alias or group name and application developers shall be allowed to hint routing to a group of such nodes.

For every node in the configuration users shall be able to set one or more group names. A SQL hint, for example, MS_GROUP=name can be used to hint the load balancer to direct a request to a certain node group.

Users can request session consistency. Session consistency guarantees that a user will only be redirected to nodes that have already replicated his changes. Currently we check the status of all configured nodes before we pick a node for statement execution. Checking the status causes extra load on the nodes.

Two additional ways for finding candidates help lowering the overhead:

• Wait for GTID
• Cache/persist GTID/synchron-status

There are two ways to check in MySQL 5.6 if a server has replicated a GTID. One can ask a node whether a GTID has been replicated and either get an immediate response (yes/no) or delay the reply until the node has replicated the GTID (wait for GTID). Currently only the first logic is used. We shall also support “wait for GTID”. In that case we pick a candidate and wait until the candidate has replicated the GTID. This will lower the checking overhead as only one node is checked but not all configured nodes.

Furthermore we can persist GTID/synchron-status information and attempt to reuse it. If we search for a candidate for the first time we query all configured nodes and persist their responses. The responses are cached and used for all subsequent read-only operations until a write is done. After the write we flush the cache and, if a read-only operation follows, we check again all nodes and cache their synchro-status.