News Archive
PhpRiot Newsletter
Your Email Address:

More information

PECL/mysqlnd_ms: quality of service filter

Note: This article was originally published at Planet PHP on 5 December 2011.
Planet PHP

What if your PHP application could tell the mysqlnd library what service quality you need when using a MySQL replication cluster? If you wanted read-your-writes, the driver would select replication nodes for you which can offer it. If you can allow replication lag but no more than three seconds, the driver would selecta One function call and you get the service you need. That's what version 1.2 of PECL/mysqlnd_ms is about.

The quality of service filter

In the world of PECL/mysqlnd_ms, the free and Open Source replication and load balancing plugin for mysqlnd, a so-called filter is responsible for choosing nodes for statement execution. A filter looks at the SQL statement to be executed and picks a capable server. The current production ready 1.1 release has three filters. Two load balancing filter (random, round-robin) and a user hook filter (user).

For example, if you run a SELECT statement, the filters of PECL/mysqlnd_ms ensure it ends up on a slave.

query(SELECT id FROM test) | mysqli PDO_MySQL mysql mysqlnd library PECL/mysqlnd_ms magic | A Master A Slave 1 Slave 2 Slave 3

PECL/mysqlnd_ms 1.2 brings a new quality-of-service filter. The quality of service filter can be configured in the plugins configuration file but also at runtime. The latter is new and unique to this filter.

Reading from slaves no more than n seconds behind

Certain elements on a web site qualify for simple TTL based caching. Others don't. One could say, that the quality of service demands for cacheable contents are lower than for non-cacheable. The same goes for SQL statements. Stale data may be OK, but it may not be older than n seconds, you may want to set a time-to-live, so to say.

MySQL Replication is asynchronous. It takes some time until a write operation on the master has been replicated to all slaves. Slaves may not always serve current data. The MySQL administrative statement SHOW SLAVE STATUS give a hint how many seconds a slave is behind the master (Seconds_Behind_Master). It is a rather rough estimation that MySQL makes but its the best built-in logic I am aware of. Please, check the MySQL reference manual for details.

A Master A Slave 1 Slave 2 Slave 3 Seconds_Behind_Master = 0 Seconds_Behind_Master = 3 Seconds_Behind_Master = 5

Say your application is fine with reading from slaves that lag no more than three seconds behind the master. With PECL/mysqlnd_ms 1.2 it will be one line to set the required service quality.


After the function call, PECL/mysqlnd_ms checks for every statement nodes quality for executing the statement. If the statement is a write, the quality-of-service (qos) filter, returns a list of all masters. If the statement is a read, the qos filter returns all masters and all slaves for which Seconds_Behind_Master is true. In the example, a SELECT statement would be run on the master, slave 1 or slave 2, depending on the choice of the load balancing filter. Sla

Truncated by Planet PHP, read more at the original (another 5060 bytes)