The feature requires use of
PECL/mysqlnd_ms 1.3.0-beta or later, and
PECL/mysqlnd_qc 1.1.0-alpha or newer.
PECL/mysqlnd_ms must be compiled to support the feature.
PHP 5.4.0 or newer is required.
Note: Setup: extension
PECL/mysqlnd_ms must be loaded before
PECL/mysqlnd_qc, when using shared extensions.
The cache integration is of beta quality.
Note: Suitable MySQL
The feature is targeted for use with MySQL
Replication (primary copy). Currently, no other kinds of MySQL
clusters are supported. Users of such cluster must control
PECL/mysqlnd_qc manually if they are interested in client-side
Support for MySQL replication clusters
(asynchronous primary copy) is the main focus of
PECL/mysqlnd_ms. The slaves of a MySQL replication cluster
may or may not reflect the latest updates from the master. Slaves
are asynchronous and can lag behind the master. A read from a slave
is eventual consistent from a cluster-wide perspective.
The same level of consistency is offered by a local
cache using time-to-live (TTL) invalidation strategy. Current data
or stale data may be served. Eventually, data searched for in the
cache is not available and the source of the cache needs to be
Given that both a MySQL Replication slave
(asynchronous secondary) and a local TTL-driven cache deliver the
same level of service it is possible to transparently replace a
remote database access with a local cache access to gain better
As of PECL/mysqlnd_ms 1.3.0-beta the
plugin is capable of transparently controlling
PECL/mysqlnd_ms 1.1.0-alpha or newer to cache a read-only
query if explicitly allowed by setting an appropriate quality of
service through mysqlnd_ms_set_qos(). P lease, see the
quickstart for a code example. Both plugins must be
installed, PECL/mysqlnd_ms must be compiled to support the
cache feature and PHP 5.4.0 or newer has to be used.
Applications have full control of cache usage and
can request fresh data at any time, if need be. The cache usage can
be enabled and disabled time during the execution of a script. The
cache will be used if mysqlnd_ms_set_qos() sets the quality of
service to eventual consistency and enables cache usage. Cache
usage is disabled by requesting higher consistency levels, for
example, session consistency (read your writes). Once the quality
of service has been relaxed to eventual consistency the cache can
be used again.
If caching is enabled for a read-only statement,
PECL/mysqlnd_ms may inject SQL hints to
control caching by PECL/mysqlnd_qc. It may modify the SQL
statement it got from the application. Subsequent SQL processors
are supposed to ignore the SQL hints. A SQL hint is a SQL comment.
Comments must not be ignored, for example, by the database
The TTL of a cache entry is computed on a per
statement basis. Applications set an maximum age for the data they
want to retrieve using mysqlnd_ms_set_qos(). The age sets an
approximate upper limit of how many seconds the data returned may
lag behind the master.
The following logic is used to compute the actual
TTL if caching is enabled. The logic takes the estimated slave lag
into account for choosing a TTL. If, for example, there are two
slaves lagging 5 and 10 seconds behind and the maximum age allowed
is 60 seconds, the TTL is set to 50 seconds. Please note, the age
setting is no more than an estimated guess.
- Check whether the
statement is read-only. If not, don’t cache.
- If caching is enabled,
check the slave lag of all configured slaves. Establish slave
connections if none exist so far and lazy connections are
- Send SHOW SLAVE
STATUS to all slaves. Do not wait for the first slave to reply
before sending to the second slave. Clients often wait long for
replies, thus we send out all requests in a burst before fetching
in a second stage.
- Loop over all slaves.
For every slave wait for its reply. Do not start checking another
slave before the currently waited for slave has replied. Check for
Slave_IO_Running=Yes and Slave_SQL_Running=Yes.
If both conditions hold true, fetch the value of
Seconds_Behind_Master. In case of any errors or if
conditions fail, set an error on the slave connection. Skip any
such slave connection for the rest of connection
- Search for the maximum
value of Seconds_Behind_Master from all slaves that passed
the previous conditions. Subtract the value from the maximum age
provided by the user with mysqlnd_ms_set_qos(). Use the result as a
- The filtering may sort
out all slaves. If so, the maximum age is used as TTL, because the
maximum lag found equals zero. It is perfectly valid to sort out
all slaves. In the following it is up to subsequent filter to
decide what to do. The built-in load balancing filter will pick the
- Inject the appropriate
SQL hints to enable caching by
- Proceed with the
connection filtering, e.g. apply load balancing rules to pick a
is loaded after PECL/mysqlnd_ms by PHP. Thus, it will see
all query modifications of PECL/mysqlnd_ms and cache the
query if instructed to do so.
The algorithm may seem expensive. SHOW SLAVE
STATUS is a very fast operation. Given a sufficient number of
requests and cache hits per second the cost of checking the slaves
lag can easily outweigh the costs of the cache decision.
Suggestions on a better algorithm are always