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

* Introduct new "quality-of-service" filter

• [done] develop new JSON-based plugin config syntax
  • [ongoing] support definition of replication setups including schemata based partitioning
• [open] make load balancer schemata aware
  • for each statement detect database/table
  • follow schemata based partitioning rules
  • basic idea: http://blog.ulf-wendel.de/?p=310
• [done] improve trx_stickiness using MYSQL*/MYSQLND* in_transaction flag
  • cancelled: http://blog.ulf-wendel.de/?p=313 - No protocol flag for transaction aware load balancing
• [open] bail on unknown config settings. For example, bail if someone uses round_robin instead of roundrobin

• [open] >= 90% code coverage through tests
• [open] write report on parsing overhead, goal: <5% in worst case micro benchmarks

• [done] impact of connection limits such as allow_persistent, max_persistent, max_links

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.

MySQL replication is asnychronous. Slaves are eventual consistent because there is a time lag between an update on the master and the ability of all clients to read that update from the slaves. Slaves may serve stale data.

MySQL replication used for read scale-out requires applications to be able to cope with stale data. If stale data is acceptable, a replication system may replace a MySQL slave read access with an access to a local, eventually stale, cache. The cache access will lower network latency resulting in faster reply to the query. Furthermore, it will reduce the load on the MySQL replication slaves, thus requiring less machines for a given read workload.

For certain queries, PECL/mysqlnd_ms shall automatically populate a local cache and fetch results from the cache. PECL/mysqlnd_ms shall use PECL/mysqlnd_qc, if possible, in a transparent way.

(MySQL semi-synchronous replication does not change the matter as it requires only one slave to have received but not yet applied the update.)

PECL/mysqlnd_qc supports Memcache, APC and other storage media.

Applications shall be able to define a “quality of service” (the term is borrowed from Tungsten) for a statement, a connection or globally. The “quality of service” shall be guaranteed by PECL/mysqlnd_ms. The service level defines if eventual consistency is allowed:

• [RW_]CONSISTENT
• [RO_]EVENTUAL_CONSISTENT

With CONSISTENT every statement shall be run on the master to avoid stale data. EVENTUAL_CONSISTENT allows the use of slaves and thus, stale, non-consistent data may be read.

PECL/mysqlnd_ms supports the logic of service levels already as of 1.0.0, but does not call them that way. PECL/mysqlnd_ms features SQL hints (USE_MASTER) to enforce the use of the master (CONSISTENT), the use of the last used server (USE_LAST_USED) for consistent reads from slaves (CONSISTENT) and has an ini-setting (master_on_write) to automatically use only the master after the first write (CONSISTENT). It also features the concept of EVENTUAL_CONSISTENT which is the default operation mode.

However, PECL/mysqlnd_ms does not call it “quality of service” and has not extra API calls for setting the service level. It may be convenient to introduce an extra API call, which removes the need of having to prefix statements with SQL hint. Prefixing is inconvenient.

/* Force use of master - consistent read although no SQL hint is used */
mysqlnd_ms_set_service_level($link, CONSISTENT);
$link->query("SELECT ...");
$link->query("SELECT ...");

/* Use master or slaves for reads - allow read stale, non-consistent data is acceptable */  
mysqlnd_ms_set_service_level($link, EVENTUAL_CONSISTENT);
$link->query("SELECT ...");

The service level EVENTUAL_CONSISTENT can be combined with automatic, local caching. It shall be configurable if and for which queries caching shall be used. Configuration can, for example, be done via the PECL/mysqlnd_ms plugin configuration file. It may be also useful to introduce a EVENTUAL_CONSISTENT_CACHE service level to control caching via API call.

If PECL/mysqlnd_ms runs in caching mode, it shall automatically populate and read from the PECL/mysqlnd_qc query cache. PECL/mysqlnd_qc is TTL based. The TTL could either be set manually via PECL/mysqlnd_ms function call, globally in the PECL/mysqlnd_ms configuration file or automatically derived from SHOW SLAVE STATUS Seconds_Behind_Master.

Use and integration of PECL/mysqlnd_qc shall be as transparent as possible from an applications point of view. For example, PECL/mysqlnd_ms shall inject all SQL hints required to control caching by PECL/mysqlnd_qc.

In the scope of MySQL master - slave replication global transaction IDs can be understood as a measure to identify a database state. Replicas/slaves can use global transaction ids to identify change sets (from the master) that need to be applied. Clients can use global transaction ids to monitor the replication status of the replicas in the database cluster.

The MySQL built-in replication does not feature global transaction IDs. MySQL replication uses transaction log files and log file positions to identify change sets that replicas (slaves) need to apply. Log file and log file position are local to a machine. A tuple of (log file, log file position) does not uniquely identify a change set in the whole cluster.

If, for example, your setup is A -> B -> C (A is the master, B is a slave reading from A, C is a slave reading from B) and B fails you are faced with the challenge to identify the log file and log file position on A from which C shall continue to read and apply change sets. C cannot continue reading from the same (log file, log position) as it was reading from B. The tuple (log file=2, log position=17) on B can identify a different change set than the tuple (log file=2, log position=17) on A. C might get out of sync, loose change sets or apply them multiple times, if it would blindly continue reading from the same position on A.

Today, it can become a bit of a time consuming guessing game to identify the next change set that B needs to process in this fail-over example.

Fail-over and circular-replication is much easier to do if the database cluster maintains a global transaction ID.

The plugin shall inject SQL to manually maintain a global transaction ID on the master.

Global transaction IDs can be used for multiple purposes:

• Simplify server fail-over (server-side, not client-side)

• Monitor slave replication status (client-side, plugin relevant)
  • Identify “up-to-date” slave for “synchronous” reads
  • Identify slaves which are “recent enough” (quality of service)
  • Let the load balancer skip slaves which have fallen to far behind to reduce their load

The plugin shall support using a global transaction ID for:

1. server fail-over (e.g. promoting new master after master failure)
2. selection of “recent enough” (quality of service) slaves for reading

Gobal transaction ID generation shall be done by SQL injection. The plugin shall insert SQL at the end of each (write) transaction (see http://jan.kneschke.de/projects/mysql/mysql-proxy-and-a-global-transaction-id/) on the master. The inserted SQL shall generate an ID which helps with the desired use cases.

The SQL used for globals transaction ID generation can be different for the different purposes. It may be good enough to inject SET @_g_trx_id=UUID_SHORT() on the master before a SQL transaction commits to help with fail-over. Whereas the selection of slaves of a certain state (“up-to-date, “recent enough”) may require maintaining a sequence number or a tuple of server-id and sequence number. Cluster topology and replication method (statement based, row based, mixed).

Thus, the user shall be able to configure:

• SQL used for ID generation
• SQL used to retrieve ID

If no SQL is given to retrieve the ID, the plugin does not feature '2) selection of “recent enough” (quality of service) slaves for reading'. If given, the user can restrict the choice of slaves for reading by setting a minimum global transaction id which has to be already replicated on the slaves.

This is useful if, for example, an application does a write and needs to read the modified data “immediately” after the write. Not all slaves my have replicated the write “immediately”. Thus, the read can only be performed on the master or some, selected slaves which have replicated the changes already. Using a global transaction id it is possible to identify those slaves.

/* write to master */
$link->query("INSERT ...");
$write_trx_id = mysqlnd_ms_get_last_global_transaction_id();

/* Force use of master - consistent read although no SQL hint is used */
mysqlnd_ms_set_service_level($link, CONSISTENT);
$link->query("SELECT ...");
$link->query("SELECT ...");
 
/* Read from a slave which has replicated global transaction ID or, if no slave found, use master */
mysqlnd_ms_set_service_level($link, MIN_GLOBAL_TRX_ID, $write_trx_id);
$link->query("SELECT ...");
$link->query("SELECT ...");

/* Use master or slaves for reads - allow read stale, non-consistent data is acceptable */  
mysqlnd_ms_set_service_level($link, EVENTUAL_CONSISTENT);
$link->query("SELECT ...");

The global transaction ID feature of the plugin is turned off by default. The feature is seen as an experts topic.

Give users a way to overrule all automatic decisions, including the use of host credentials from the config when broadcasting COM_CHANGE_USER after mysqlnd_change_user(master_user, master_pass, master_db).

bool mysqlnd_ms_change_user(mixed $connection, mixed $where, $user, $password, $db)

$where: MYSQLND_MS_ALL_CONNECTIONS, MYSQLND_MS_LAST_USED_CONNECTION

Consider:

$where: MYSQLND_MS_ALL_CONNECTIONS, MYSQLND_MS_LAST_USED_CONNECTION, name of server

Not all MS errors can be bubbled up through the standard APIs. For example, failing to parse parts of the MS config may result in connection error. Upon connect, the user will get the error code for the failed connect. However, the failure to parse the config, the cause of the problem, is only shown as a warning.

array mysqlnd_ms_get_errors([mixed $connection])

TDB.