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:
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:
SELECT 1 FROM DUAL
. Passed to all filters.
master_0
.
Slave nodes:slave_0
, slave_1
random
, argument sticky=1
.
Picks a random slave once to be used for the rest of the PHP request.
Output: slave_0
.
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
.
roundrobin
. Server list consists of
one server only, round robin will always return the same server.
(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.
SELECT sum(price) FROM orders WHERE order_id = 1
.
Passed to all filters.
master_0
.
Slave nodes: slave_0
, slave_1
,
slave_2
, slave_3
quality_of_service
, rule set: session_consistency (read-your-writes)
Output: master_0
master_0
and the statement to be executed
is passed as input to the next filter, which is roundrobin
.
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 namedsales
(slave_0
,slave_1
). If the application queries the databaseslaves
, the hypotheticaltable
filter reduces the list of possible servers toslave_0
andslave_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.