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.