Filter

Note: Version requirement

Filters exist as of mysqlnd_ms version 1.1.0-beta.

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.

Note: Explanation: comparing filter chaining to pipes

The concept of chained filters can be compared to using pipes to connect command line utilities on an operating system command shell. For example, an input stream is passed to a processor, filtered, and then transferred to be output. Then, the output is passed as input to the next command, which is connected to the previous using the pipe operator.

Available filters:

• Load balancing filters: random and roundrobin.
• Selection filter: user, user_multi, quality_of_service.

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 behavior. 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 the 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:

• Statement to be executed: SELECT 1 FROM DUAL. Passed to all filters.
• All configured nodes are passed as input to the first filter. Master nodes: master_0. Slave nodes:slave_0, slave_1
• Filter: random, argument sticky=1. Picks a random slave once to be used for the rest of the PHP request. Output: slave_0.
• Output of slave_0 and the statement to be executed is passed as input to the next filter. Here: roundrobin, server list passed to filter is: slave_0.
• Filter: roundrobin. Server list consists of one server only, round robin will always return the same server.

If trying to use such a filter sequence, the plugin may emit a warning like (mysqlnd_ms) Error while creating filter '%s' . Non-multi filter '%s' already created. Stopping in %s on line %d. Furthermore, an appropriate error on the connection handle may be set.

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.

• Statement to be executed: SELECT sum(price) FROM orders WHERE order_id = 1. Passed to all filters.
• All configured nodes are passed as input to the first filter. Master nodes: master_0. Slave nodes: slave_0, slave_1, slave_2, slave_3
• Filter: quality_of_service, rule set: session_consistency (read-your-writes) Output: master_0
• Output of master_0 and the statement to be executed is passed as input to the next filter, which is roundrobin.
• Filter: roundrobin. Server list consists of one server. Round robin selects master_0.

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.

Note: Speculation towards the future: MySQL replication filtering

In future versions, there may be additional multi filters. For example, there may be a table filter to support MySQL replication filtering. This would allow you to define rules for which database or table is to be replicated to which node of a replication cluster. Assume your replication cluster consists of four slaves (slave_0, slave_1, slave_2, slave_3) two of which replicate a database named sales (slave_0, slave_1). If the application queries the database slaves, the hypothetical table filter reduces the list of possible servers to slave_0 and slave_1. Because the output and list of candidates consists of more than one server, it is necessary and possible to add additional filters to the candidate list, for example, using a load balancing filter to identify a server for statement execution.