• Concepts
  • Cache integration

  • Cache integration
  • Cache integration

    Cache integration

    Note: Version

    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
    load order

    PECL/mysqlnd_ms must be loaded before
    PECL/mysqlnd_qc, when using shared extensions.

    Note: Feature

    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
    query caching.

    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
      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
    • PECL/mysqlnd_qc
      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
    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