The feature requires use of
1.3.0-beta or later, and
1.1.0-alpha or newer.
be compiled to support the feature. PHP 5.4.0 or newer is
PECL/mysqlnd_ms must be loaded before
PECL/mysqlnd_qc, when using shared
The cache integration is of beta quality.
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 query caching.
Support for MySQL replication clusters (asynchronous primary
copy) is the main focus of
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 accessed.
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 possibility.
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
P lease, see the
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
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
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
The TTL of a cache entry is computed on a per statement basis.
Applications set an maximum age for the data they want to
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 used.
SHOW SLAVE STATUSto 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.
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 filtering.
Search for the maximum value of
Seconds_Behind_Masterfrom 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 TTL.
- 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 master.
Inject the appropriate SQL hints to enable caching by
- Proceed with the connection filtering, e.g. apply load balancing rules to pick a slave.
PECL/mysqlnd_qcis loaded after
PECL/mysqlnd_msby PHP. Thus, it will see all query modifications of
PECL/mysqlnd_msand cache the query if instructed to do so.
The algorithm may seem expensive.
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
Suggestions on a better algorithm are always welcome.