22.9.7.4. Quickstart and Examples

22.9.7.4.1. Architecture and Concepts

The query cache plugin is implemented as a PHP extension. It is written in C and operates under the hood of PHP. During the startup of the PHP interpreter, it gets registered as a mysqlnd plugin to replace selected mysqlnd C methods. Hereby, it can change the behaviour of any PHP MySQL extension (mysqli, PDO_MYSQL, mysql) compiled to use the mysqlnd library without changing the extensions API. This makes the plugin compatible with each and every PHP MySQL application. Because existing APIs are not changed, it is almost transparent to use. Please, see the mysqlnd plugin API description for a discussion of the advantages of the plugin architecture and a comparison with proxy based solutions.

Transparent to use

At PHP run time PECL/mysqlnd_qc can proxy queries send from PHP (mysqlnd) to the MySQL server. It then inspects the statement string to find whether it shall cache its results. If so, result set is cached using a storage handler and further executions of the statement are served from the cache for a user-defined period. The Time to Live (TTL) of the cache entry can either be set globally or on a per statement basis.

A statement is either cached if the plugin is instructed to cache all statements globally using a or, if the query string starts with the SQL hint (/*qc=on*/). The plugin is capable of caching any query issued by calling appropriate API calls of any of the existing PHP MySQL extensions.

Flexible storage: various storage handler

Various storage handler are supported to offer different scopes for cache entries. Different scopes allow for different degrees in sharing cache entries among clients.

default (built-in): process memory, scope: process, one or more web requests depending on PHP deployment model used
APC: shared memory, scope: single server, multiple web requests
SQLite: memory or file, scope: single server, multiple web requests
MEMCACHE: main memory, scope: single or multiple server, multiple web requests
user (built-in): user-defined - any, scope: user-defined - any

Support for the APC, SQLite and MEMCACHE storage handler has to be enabled at compile time. The default and user handler are built-in. It is possible to switch between compiled-in storage handlers on a per query basis at run time. However, it is recommended to pick one storage handler and use it for all cache entries.

Built-in slam defense to avoid overloading

To avoid overload situations the cache plugin has a built-in slam defense mechanism. If a popular cache entries expires many clients using the cache entries will try to refresh the cache entry. For the duration of the refresh many clients may access the database server concurrently. In the worst case, the database server becomes overloaded and it takes more and more time to refresh the cache entry, which in turn lets more and more clients try to refresh the cache entry. To prevent this from happening the plugin has a slam defense mechanism. If slam defense is enabled and the plugin detects an expired cache entry it extends the life time of the cache entry before it refreshes the cache entry. This way other concurrent accesses to the expired cache entry are still served from the cache for a certain time. The other concurrent accesses to not trigger a concurrent refresh. Ideally, the cache entry gets refreshed by the client which extended the cache entries lifespan before other clients try to refresh the cache and potentially cause an overload situation.

Unique approach to caching

PECL/mysqlnd_qc has a unique approach to caching result sets that is superior to application based cache solutions. Application based solutions first fetch a result set into PHP variables. Then, the PHP variables are serialized for storage in a persistent cache, and then unserialized when fetching. The mysqlnd query cache stores the raw wire protocol data sent from MySQL to PHP in its cache and replays it, if still valid, on a cache hit. This way, it saves an extra serialization step for a cache put that all application based solutions have to do. It can store the raw wire protocol data in the cache without having to serialize into a PHP variable first and deserializing the PHP variable for storing in the cache again.

22.9.7.4.2. Setup

The plugin is implemented as a PHP extension. See also the installation instructions to install the PECL/mysqlnd_qc extension.

Compile or configure the PHP MySQL extension (mysqli, PDO_MYSQL, mysql) that you plan to use with support for the mysqlnd library. PECL/mysqlnd_qc is a plugin for the mysqlnd library. To use the plugin with any of the existing PHP MySQL extensions (APIs), the extension has to use the mysqlnd library.

Then, load the extension into PHP and activate the plugin in the PHP configuration file using the PHP configuration directive named mysqlnd_qc.enable_qc.

Example 22.307. Enabling the plugin (php.ini)

mysqlnd_qc.enable_qc=1

22.9.7.4.3. Caching queries

There are four ways to trigger caching of a query.

Use of SQL hints on a per query basis
User supplied callbacks to decide on a per query basis, for example, using mysqlnd_qc_is_select
mysqlnd_set_cache_condition for rule based automatic per query decisions
mysqlnd_qc.cache_by_default = 1 to cache all queries blindly

Use of SQL hints and mysqlnd_qc.cache_by_default = 1 are explained below. Please, refer to the function reference on mysqlnd_qc_is_select for a description of using a callback and, mysqlnd_qc_set_cache_condition on how to set rules for automatic caching.

A SQL hint is a SQL standards compliant comment. As a SQL comment it is ignored by the database. A statement is considered eligible for caching if it either begins with the SQL hint enabling caching or it is a SELECT statement.

An individual query which shall be cached must begin with the SQL hint /*qc=on*/. It is recommended to use the PHP constant MYSQLND_QC_ENABLE_SWITCH instead of using the string value.

not eligible for caching and not cached: INSERT INTO test(id) VALUES (1)
not eligible for caching and not cached: SHOW ENGINES
eligible for caching but uncached: SELECT id FROM test
eligible for caching and cached: /*qc=on*/SELECT id FROM test

The examples SELECT statement string is prefixed with the MYSQLND_QC_ENABLE_SWITCH SQL hint to enable caching of the statement. The SQL hint must be given at the very beginning of the statement string to enable caching.

Example 22.308. Using the MYSQLND_QC_ENABLE_SWITCH SQL hint

mysqlnd_qc.enable_qc=1

<?php/* Connect, create and populate test table */$mysqli = new mysqli("host", "user", "password", "schema", "port", "socket");$mysqli->query("DROP TABLE IF EXISTS test");$mysqli->query("CREATE TABLE test(id INT)");$mysqli->query("INSERT INTO test(id) VALUES (1), (2)");/* Will be cached because of the SQL hint */$start = microtime(true);$res   = $mysqli->query("/*" . MYSQLND_QC_ENABLE_SWITCH . "*/" . "SELECT id FROM test WHERE id = 1");var_dump($res->fetch_assoc());$res->free();printf("Total time uncached query: %.6fs\n", microtime(true) - $start);/* Cache hit */$start = microtime(true);$res   = $mysqli->query("/*" . MYSQLND_QC_ENABLE_SWITCH . "*/" . "SELECT id FROM test WHERE id = 1");var_dump($res->fetch_assoc());$res->free();printf("Total time cached query: %.6fs\n", microtime(true) - $start);?>

The above examples will output something similar to:

array(1) {  ["id"]=>  string(1) "1"}Total time uncached query: 0.000740sarray(1) {  ["id"]=>  string(1) "1"}Total time cached query: 0.000098s

If nothing else is configured, as it is the case in the quickstart example, the plugin will use the built-in default storage handler. The default storage handler uses process memory to hold a cache entry. Depending on the PHP deployment model, a PHP process may serve one or more web requests. Please, consult the web server manual for details. Details make no difference for the examples given in the quickstart.

The query cache plugin will cache all queries regardless if the query string begins with the SQL hint which enables caching or not, if the PHP configuration directive mysqlnd_qc.cache_by_default is set to 1. The setting mysqlnd_qc.cache_by_default is evaluated by the core of the query cache plugins. Neither the built-in nor user-defined storage handler can overrule the setting.

The SQL hint /*qc=off*/ can be used to disable caching of individual queries if mysqlnd_qc.cache_by_default = 1 It is recommended to use the PHP constant MYSQLND_QC_DISABLE_SWITCH instead of using the string value.

Example 22.309. Using the MYSQLND_QC_DISABLE_SWITCH SQL hint

mysqlnd_qc.enable_qc=1mysqlnd_qc.cache_by_default=1

<?php/* Connect, create and populate test table */$mysqli = new mysqli("host", "user", "password", "schema", "port", "socket");$mysqli->query("DROP TABLE IF EXISTS test");$mysqli->query("CREATE TABLE test(id INT)");$mysqli->query("INSERT INTO test(id) VALUES (1), (2)");/* Will be cached although no SQL hint is present because of mysqlnd_qc.cache_by_default = 1*/$res = $mysqli->query("SELECT id FROM test WHERE id = 1");var_dump($res->fetch_assoc());$res->free();$mysqli->query("DELETE FROM test WHERE id = 1");/* Cache hit - no automatic invalidation and still valid! */$res = $mysqli->query("SELECT id FROM test WHERE id = 1");var_dump($res->fetch_assoc());$res->free();/* Cache miss - query must not be cached because of the SQL hint */$res = $mysqli->query("/*" . MYSQLND_QC_DISABLE_SWITCH . "*/SELECT id FROM test WHERE id = 1");var_dump($res->fetch_assoc());$res->free();?>

The above examples will output:

array(1) {  ["id"]=>  string(1) "1"}array(1) {  ["id"]=>  string(1) "1"}NULL

PECL/mysqlnd_qc forbids caching of statements for which at least one column from the statements result set shows no table name in its meta data by default. This is usually the case for columns originating from SQL functions such as NOW() or LAST_INSERT_ID(). The policy aims to prevent pitfalls if caching by default is used.

Example 22.310. Example showing which type of statements are not cached

mysqlnd_qc.enable_qc=1mysqlnd_qc.cache_by_default=1

<?php/* Connect, create and populate test table */$mysqli = new mysqli("host", "user", "password", "schema", "port", "socket");$mysqli->query("DROP TABLE IF EXISTS test");$mysqli->query("CREATE TABLE test(id INT)");$mysqli->query("INSERT INTO test(id) VALUES (1)");for ($i = 0; $i < 3; $i++) {    $start = microtime(true);    /* Note: statement will not be cached because of NOW() use */    $res = $mysqli->query("SELECT id, NOW() AS _time FROM test");    $row = $res->fetch_assoc();    /* dump results */    var_dump($row);    printf("Total time: %.6fs\n", microtime(true) - $start);    /* pause one second */    sleep(1);}?>

The above examples will output something similar to:

array(2) {  ["id"]=>  string(1) "1"  ["_time"]=>  string(19) "2012-01-11 15:43:10"}Total time: 0.000540sarray(2) {  ["id"]=>  string(1) "1"  ["_time"]=>  string(19) "2012-01-11 15:43:11"}Total time: 0.000555sarray(2) {  ["id"]=>  string(1) "1"  ["_time"]=>  string(19) "2012-01-11 15:43:12"}Total time: 0.000549s

It is possible to enable caching for all statements including those which has columns in their result set for which MySQL reports no table, such as the statement from the example. Set mysqlnd_qc.cache_no_table = 1 to enable caching of such statements. Please, note the difference in the measured times for the above and below examples.

Example 22.311. Enabling caching for all statements using themysqlnd_qc.cache_no_table ini setting

mysqlnd_qc.enable_qc=1mysqlnd_qc.cache_by_default=1mysqlnd_qc.cache_no_table=1

<?php/* Connect, create and populate test table */$mysqli = new mysqli("host", "user", "password", "schema", "port", "socket");$mysqli->query("DROP TABLE IF EXISTS test");$mysqli->query("CREATE TABLE test(id INT)");$mysqli->query("INSERT INTO test(id) VALUES (1)");for ($i = 0; $i < 3; $i++) {    $start = microtime(true);    /* Note: statement will not be cached because of NOW() use */    $res = $mysqli->query("SELECT id, NOW() AS _time FROM test");    $row = $res->fetch_assoc();    /* dump results */    var_dump($row);    printf("Total time: %.6fs\n", microtime(true) - $start);    /* pause one second */    sleep(1);}?>

The above examples will output something similar to:

array(2) {  ["id"]=>  string(1) "1"  ["_time"]=>  string(19) "2012-01-11 15:47:45"}Total time: 0.000546sarray(2) {  ["id"]=>  string(1) "1"  ["_time"]=>  string(19) "2012-01-11 15:47:45"}Total time: 0.000187sarray(2) {  ["id"]=>  string(1) "1"  ["_time"]=>  string(19) "2012-01-11 15:47:45"}Total time: 0.000167s

Note

Although mysqlnd_qc.cache_no_table = 1 has been created for use with mysqlnd_qc.cache_by_default = 1 it is bound it. The plugin will evaluate the mysqlnd_qc.cache_no_table whenever a query is to be cached, no matter whether caching has been enabled using a SQL hint or any other measure.

22.9.7.4.4. Setting the TTL

The default invalidation strategy of the query cache plugin is Time to Live (TTL). The built-in storage handlers will use the default TTL defined by the PHP configuration value mysqlnd_qc.ttl unless the query string contains a hint for setting a different TTL. The TTL is specified in seconds. By default cache entries expire after 30 seconds

The example sets mysqlnd_qc.ttl=3 to cache statements for three seconds by default. Every second it updates a database table record to hold the current time and executes a SELECT statement to fetch the record from the database. The SELECT statement is cached for three seconds because it is prefixed with the SQL hint enabling caching. The output verifies that the query results are taken from the cache for the duration of three seconds before they are refreshed.

Example 22.312. Setting the TTL with the mysqlnd_qc.ttl ini setting

mysqlnd_qc.enable_qc=1mysqlnd_qc.ttl=3

<?php/* Connect, create and populate test table */$mysqli = new mysqli("host", "user", "password", "schema", "port", "socket");$mysqli->query("DROP TABLE IF EXISTS test");$mysqli->query("CREATE TABLE test(id VARCHAR(255))");for ($i = 0; $i < 7; $i++) {    /* update DB row  */    if (!$mysqli->query("DELETE FROM test") ||        !$mysqli->query("INSERT INTO test(id) VALUES (NOW())"))      /* Of course, a real-life script should do better error handling */      die(sprintf("[%d] %s\n", $mysqli->errno, $mysqli->error));    /* select latest row but cache results */    $query  = "/*" . MYSQLND_QC_ENABLE_SWITCH . "*/";    $query .= "SELECT id AS _time FROM test";    if (!($res = $mysqli->query($query)) ||        !($row = $res->fetch_assoc()))    {      printf("[%d] %s\n", $mysqli->errno, $mysqli->error);    }    $res->free();    printf("Wall time %s - DB row time %s\n", date("H:i:s"), $row['_time']);    /* pause one second */    sleep(1);}?>

The above examples will output something similar to:

Wall time 14:55:59 - DB row time 2012-01-11 14:55:59Wall time 14:56:00 - DB row time 2012-01-11 14:55:59Wall time 14:56:01 - DB row time 2012-01-11 14:55:59Wall time 14:56:02 - DB row time 2012-01-11 14:56:02Wall time 14:56:03 - DB row time 2012-01-11 14:56:02Wall time 14:56:04 - DB row time 2012-01-11 14:56:02Wall time 14:56:05 - DB row time 2012-01-11 14:56:05

As can be seen from the example, any TTL based cache can serve stale data. Cache entries are not automatically invalidated, if underlying data changes. Applications using the default TTL invalidation strategy must be able to work correctly with stale data.

A user-defined cache storage handler can implement any invalidation strategy to work around this limitation.

The default TTL can be overruled using the SQL hint /*qc_tt=seconds*/. The SQL hint must be appear immediately after the SQL hint which enables caching. It is recommended to use the PHP constant MYSQLND_QC_TTL_SWITCH instead of using the string value.

Example 22.313. Setting TTL with SQL hints

<?php$start = microtime(true);/* Connect, create and populate test table */$mysqli = new mysqli("host", "user", "password", "schema", "port", "socket");$mysqli->query("DROP TABLE IF EXISTS test");$mysqli->query("CREATE TABLE test(id INT)");$mysqli->query("INSERT INTO test(id) VALUES (1), (2)");printf("Default TTL\t: %d seconds\n", ini_get("mysqlnd_qc.ttl"));/* Will be cached for 2 seconds */$sql = sprintf("/*%s*//*%s%d*/SELECT id FROM test WHERE id = 1", MYSQLND_QC_ENABLE_SWITCH, MYSQLND_QC_TTL_SWITCH, 2);$res = $mysqli->query($sql);var_dump($res->fetch_assoc());$res->free();$mysqli->query("DELETE FROM test WHERE id = 1");sleep(1);/* Cache hit - no automatic invalidation and still valid! */$res = $mysqli->query($sql);var_dump($res->fetch_assoc());$res->free();sleep(2);/* Cache miss - cache entry has expired */$res = $mysqli->query($sql);var_dump($res->fetch_assoc());$res->free();printf("Script runtime\t: %d seconds\n", microtime(true) - $start);?>

The above examples will output something similar to:

Default TTL     : 30 secondsarray(1) {  ["id"]=>  string(1) "1"}array(1) {  ["id"]=>  string(1) "1"}NULLScript runtime  : 3 seconds

22.9.7.4.5. Pattern based caching

An application has three options for telling PECL/mysqlnd_qc whether a particular statement shall be used. The most basic approach is to cache all statements by setting mysqlnd_qc.cache_by_default = 1. This approach is often of little practical value. But it enables users to make a quick estimation about the maximum performance gains from caching. An application designed to use a cache may be able to prefix selected statements with the appropriate SQL hints. However, altering an applications source code may not always be possible or desired, for example, to avoid problems with software updates. Therefore, PECL/mysqlnd_qc allows setting a callback which decides if a query is to be cached.

The callback is installed with the mysqlnd_qc_set_is_select function. The callback is given the statement string of every statement inspected by the plugin. Then, the callback can decide whether to cache the function. The callback is supposed to return FALSE if the statement shall not be cached. A return value of TRUE makes the plugin try to add the statement into the cache. The cache entry will be given the default TTL (mysqlnd_qc.ttl). If the callback returns a numerical value it is used as the TTL instead of the global default.

Example 22.314. Setting a callback withmysqlnd_qc_set_is_select

mysqlnd_qc.enable_qc=1mysqlnd_qc.collect_statistics=1

<?php/* callback which decides if query is cached */function is_select($query) {    static $patterns = array(      /* true - use default from mysqlnd_qc.ttl */      "@SELECT\s+.*\s+FROM\s+test@ismU" => true,      /* 3 - use TTL = 3 seconds */      "@SELECT\s+.*\s+FROM\s+news@ismU" => 3    );    /* check if query does match pattern */    foreach ($patterns as $pattern => $ttl) {        if (preg_match($pattern, $query)) {            printf("is_select(%45s): cache\n", $query);            return $ttl;        }    }    printf("is_select(%45s): do not cache\n", $query);    return false;}/* install callback */mysqlnd_qc_set_is_select("is_select");/* Connect, create and populate test table */$mysqli = new mysqli("host", "user", "password", "schema", "port", "socket");$mysqli->query("DROP TABLE IF EXISTS test");$mysqli->query("CREATE TABLE test(id INT)");$mysqli->query("INSERT INTO test(id) VALUES (1), (2), (3)");/* cache put */$mysqli->query("SELECT id FROM test WHERE id = 1");/* cache hit */$mysqli->query("SELECT id FROM test WHERE id = 1");/* cache put */$mysqli->query("SELECT * FROM test");$stats = mysqlnd_qc_get_core_stats();printf("Cache put: %d\n", $stats['cache_put']);printf("Cache hit: %d\n", $stats['cache_hit']);?>

The above examples will output something similar to:

is_select(                    DROP TABLE IF EXISTS test): do not cacheis_select(                    CREATE TABLE test(id INT)): do not cacheis_select(    INSERT INTO test(id) VALUES (1), (2), (3)): do not cacheis_select(             SELECT id FROM test WHERE id = 1): cacheis_select(             SELECT id FROM test WHERE id = 1): cacheis_select(                           SELECT * FROM test): cacheCache put: 2Cache hit: 1

The examples callback tests if a statement string matches a pattern. If this is the case, it either returns TRUE to cache the statement using the global default TTL or an alternative TTL.

To minimize application changes the callback can put into and registered in an auto prepend file.

22.9.7.4.6. Slam defense

A badly designed cache can do more harm than good. In the worst case a cache can increase database server load instead of minimizing it. An overload situation can occur if a highly shared cache entry expires (cache stampeding).

Cache entries are shared and reused to a different degree depending on the storage used. The default storage handler stores cache entries in process memory. Thus, a cache entry can be reused for the life-span of a process. Other PHP processes cannot access it. If Memcache is used, a cache entry can be shared among multiple PHP processes and even among multiple machines, depending on the set up being used.

If a highly shared cache entry stored, for example, in Memcache expires, many clients gets a cache miss. Many client requests can no longer be served from the cache but try to run the underlying query on the database server. Until the cache entry is refreshed, more and more clients contact the database server. In the worst case, a total lost of service is the result.

The overload can be avoided using a storage handler which limits the reuse of cache entries to few clients. Then, at the average, its likely that only a limited number of clients will try to refresh a cache entry concurrently.

Additionally, the built-in slam defense mechanism can and should be used. If slam defense is activated an expired cache entry is given an extended life time. The first client getting a cache miss for the expired cache entry tries to refresh the cache entry within the extended life time. All other clients requesting the cache entry are temporarily served from the cache although the original TTL of the cache entry has expired. The other clients will not experience a cache miss before the extended life time is over.

Example 22.315. Enabling the slam defense mechanism

mysqlnd_qc.slam_defense=1mysqlnd_qc.slam_defense_ttl=1

The slam defense mechanism is enabled with the PHP configuration directive mysqlnd_qc.slam_defense. The extended life time of a cache entry is set with mysqlnd_qc.slam_defense_ttl.

The function mysqlnd_qc_get_core_stats returns an array of statistics. The statistics slam_stale_refresh and slam_stale_hit are incremented if slam defense takes place.

It is not possible to give a one-fits-all recommendation on the slam defense configuration. Users are advised to monitor and test their setup and derive settings accordingly.

22.9.7.4.7. Finding cache candidates

A statement should be considered for caching if it is executed often and has a long run time. Cache candidates are found by creating a list of statements sorted by the product of the number of executions multiplied by the statements run time. The function mysqlnd_qc_get_query_trace_log returns a query log which help with the task.

Collecting a query trace is a slow operation. Thus, it is disabled by default. The PHP configuration directive mysqlnd_qc.collect_query_trace is used to enable it. The functions trace contains one entry for every query issued before the function is called.

Example 22.316. Collecting a query trace

mysqlnd_qc.enable_qc=1mysqlnd_qc.collect_query_trace=1

<?php/* connect to MySQL */$mysqli = new mysqli("host", "user", "password", "schema", "port", "socket");/* dummy queries to fill the query trace */for ($i = 0; $i < 2; $i++) {    $res = $mysqli->query("SELECT 1 AS _one FROM DUAL");    $res->free();}/* dump trace */var_dump(mysqlnd_qc_get_query_trace_log());?>

The above examples will output:

array(2) {  [0]=>  array(8) {    ["query"]=>    string(26) "SELECT 1 AS _one FROM DUAL"    ["origin"]=>    string(102) "#0 qc.php(7): mysqli->query('SELECT 1 AS _on...')#1 {main}"    ["run_time"]=>    int(0)    ["store_time"]=>    int(25)    ["eligible_for_caching"]=>    bool(false)    ["no_table"]=>    bool(false)    ["was_added"]=>    bool(false)    ["was_already_in_cache"]=>    bool(false)  }  [1]=>  array(8) {    ["query"]=>    string(26) "SELECT 1 AS _one FROM DUAL"    ["origin"]=>    string(102) "#0 qc.php(7): mysqli->query('SELECT 1 AS _on...')#1 {main}"    ["run_time"]=>    int(0)    ["store_time"]=>    int(8)    ["eligible_for_caching"]=>    bool(false)    ["no_table"]=>    bool(false)    ["was_added"]=>    bool(false)    ["was_already_in_cache"]=>    bool(false)  }}

Assorted information is given in the trace. Among them timings and the origin of the query call. The origin property holds a code backtrace to identify the source of the query. The depth of the backtrace can be limited with the PHP configuration directive mysqlnd_qc.query_trace_bt_depth. The default depth is 3.

Example 22.317. Setting the backtrace depth with the mysqlnd_qc.query_trace_bt_depth inisetting

mysqlnd_qc.enable_qc=1mysqlnd_qc.collect_query_trace=1

<?php/* connect to MySQL */$mysqli = new mysqli("host", "user", "password", "schema", "port", "socket");$mysqli->query("DROP TABLE IF EXISTS test");$mysqli->query("CREATE TABLE test(id INT)");$mysqli->query("INSERT INTO test(id) VALUES (1), (2), (3)");/* dummy queries to fill the query trace */for ($i = 0; $i < 3; $i++) {    $res = $mysqli->query("SELECT id FROM test WHERE id = " . $mysqli->real_escape_string($i));    $res->free();}$trace = mysqlnd_qc_get_query_trace_log();$summary = array();foreach ($trace as $entry) {    if (!isset($summary[$entry['query']])) {        $summary[$entry['query']] = array(            "executions" => 1,            "time"       => $entry['run_time'] + $entry['store_time'],        );    } else {        $summary[$entry['query']]['executions']++;        $summary[$entry['query']]['time'] += $entry['run_time'] + $entry['store_time'];    }}foreach ($summary as $query => $details) {    printf("%45s: %5dms (%dx)\n",    $query, $details['time'], $details['executions']);}?>

The above examples will output something similar to:

                    DROP TABLE IF EXISTS test:     0ms (1x)                    CREATE TABLE test(id INT):     0ms (1x)    INSERT INTO test(id) VALUES (1), (2), (3):     0ms (1x)             SELECT id FROM test WHERE id = 0:    25ms (1x)             SELECT id FROM test WHERE id = 1:    10ms (1x)             SELECT id FROM test WHERE id = 2:     9ms (1x)

22.9.7.4.8. Measuring cache efficiency

PECL/mysqlnd_qc offers three ways to measure the cache efficiency. The function mysqlnd_qc_get_normalized_query_trace_log returns statistics aggregated by the normalized query string, mysqlnd_qc_get_cache_info gives storage handler specific information which includes a list of all cached items, depending on the storage handler. Additionally, the core of PECL/mysqlnd_qc collects high-level summary statistics aggregated per PHP process. The high-level statistics are returned by mysqlnd_qc_get_core_stats.

The functions mysqlnd_qc_get_normalized_query_trace_log and mysqlnd_qc_get_core_stats will not collect data unless data collection has been enabled through their corresponding PHP configuration directives. Data collection is disabled by default for performance considerations. It is configurable with the mysqlnd_qc.time_statistics option, which determines if timing information should be collected. Collection of time statistics is enabled by default but only performed if data collection as such has been enabled. Recording time statistics causes extra system calls. In most cases, the benefit of the monitoring outweighs any potential performance penalty of the additional system calls.

Example 22.318. Collecting statistics data with themysqlnd_qc.time_statistics ini setting

mysqlnd_qc.enable_qc=1mysqlnd_qc.collect_statistics=1

<?php/* connect to MySQL */$mysqli = new mysqli("host", "user", "password", "schema", "port", "socket");$mysqli->query("DROP TABLE IF EXISTS test");$mysqli->query("CREATE TABLE test(id INT)");$mysqli->query("INSERT INTO test(id) VALUES (1), (2), (3)");/* dummy queries */for ($i = 1; $i <= 4; $i++) {    $query = sprintf("/*%s*/SELECT id FROM test WHERE id = %d", MYSQLND_QC_ENABLE_SWITCH, $i % 2);    $res   = $mysqli->query($query);        $res->free();}var_dump(mysqlnd_qc_get_core_stats());?>

The above examples will output something similar to:

array(26) {  ["cache_hit"]=>  string(1) "2"  ["cache_miss"]=>  string(1) "2"  ["cache_put"]=>  string(1) "2"  ["query_should_cache"]=>  string(1) "4"  ["query_should_not_cache"]=>  string(1) "3"  ["query_not_cached"]=>  string(1) "3"  ["query_could_cache"]=>  string(1) "4"  ["query_found_in_cache"]=>  string(1) "2"  ["query_uncached_other"]=>  string(1) "0"  ["query_uncached_no_table"]=>  string(1) "0"  ["query_uncached_no_result"]=>  string(1) "0"  ["query_uncached_use_result"]=>  string(1) "0"  ["query_aggr_run_time_cache_hit"]=>  string(2) "28"  ["query_aggr_run_time_cache_put"]=>  string(3) "900"  ["query_aggr_run_time_total"]=>  string(3) "928"  ["query_aggr_store_time_cache_hit"]=>  string(2) "14"  ["query_aggr_store_time_cache_put"]=>  string(2) "40"  ["query_aggr_store_time_total"]=>  string(2) "54"  ["receive_bytes_recorded"]=>  string(3) "136"  ["receive_bytes_replayed"]=>  string(3) "136"  ["send_bytes_recorded"]=>  string(2) "84"  ["send_bytes_replayed"]=>  string(2) "84"  ["slam_stale_refresh"]=>  string(1) "0"  ["slam_stale_hit"]=>  string(1) "0"  ["request_counter"]=>  int(1)  ["process_hash"]=>  int(1929695233)}

For a quick overview, call mysqlnd_qc_get_core_stats. It delivers cache usage, cache timing and traffic related statistics. Values are aggregated on a per process basis for all queries issued by any PHP MySQL API call.

Some storage handler, such as the default handler, can report cache entries, statistics related to the entries and meta data for the underlying query through the mysqlnd_qc_get_cache_info function. Please note, that the information returned depends on the storage handler. Values are aggregated on a per process basis.

Example 22.319. Example mysqlnd_qc_get_cache_infousage

mysqlnd_qc.enable_qc=1

<?php/* connect to MySQL */$mysqli = new mysqli("host", "user", "password", "schema", "port", "socket");$mysqli->query("DROP TABLE IF EXISTS test");$mysqli->query("CREATE TABLE test(id INT)");$mysqli->query("INSERT INTO test(id) VALUES (1), (2), (3)");/* dummy queries to fill the query trace */for ($i = 1; $i <= 4; $i++) {    $query = sprintf("/*%s*/SELECT id FROM test WHERE id = %d", MYSQLND_QC_ENABLE_SWITCH, $i % 2);    $res   = $mysqli->query($query);      $res->free();}var_dump(mysqlnd_qc_get_cache_info());?>

The above examples will output something similar to:

array(4) {  ["num_entries"]=>  int(2)  ["handler"]=>  string(7) "default"  ["handler_version"]=>  string(5) "1.0.0"  ["data"]=>  array(2) {    ["Localhost via UNIX socket3306roottest|/*qc=on*/SELECT id FROM test WHERE id = 1"]=>    array(2) {      ["statistics"]=>      array(11) {        ["rows"]=>        int(1)        ["stored_size"]=>        int(71)        ["cache_hits"]=>        int(1)        ["run_time"]=>        int(391)        ["store_time"]=>        int(27)        ["min_run_time"]=>        int(16)        ["max_run_time"]=>        int(16)        ["min_store_time"]=>        int(8)        ["max_store_time"]=>        int(8)        ["avg_run_time"]=>        int(8)        ["avg_store_time"]=>        int(4)      }      ["metadata"]=>      array(1) {        [0]=>        array(8) {          ["name"]=>          string(2) "id"          ["orig_name"]=>          string(2) "id"          ["table"]=>          string(4) "test"          ["orig_table"]=>          string(4) "test"          ["db"]=>          string(4) "test"          ["max_length"]=>          int(1)          ["length"]=>          int(11)          ["type"]=>          int(3)        }      }    }    ["Localhost via UNIX socket3306roottest|/*qc=on*/SELECT id FROM test WHERE id = 0"]=>    array(2) {      ["statistics"]=>      array(11) {        ["rows"]=>        int(0)        ["stored_size"]=>        int(65)        ["cache_hits"]=>        int(1)        ["run_time"]=>        int(299)        ["store_time"]=>        int(13)        ["min_run_time"]=>        int(11)        ["max_run_time"]=>        int(11)        ["min_store_time"]=>        int(6)        ["max_store_time"]=>        int(6)        ["avg_run_time"]=>        int(5)        ["avg_store_time"]=>        int(3)      }      ["metadata"]=>      array(1) {        [0]=>        array(8) {          ["name"]=>          string(2) "id"          ["orig_name"]=>          string(2) "id"          ["table"]=>          string(4) "test"          ["orig_table"]=>          string(4) "test"          ["db"]=>          string(4) "test"          ["max_length"]=>          int(0)          ["length"]=>          int(11)          ["type"]=>          int(3)        }      }    }  }}

It is possible to further break down the granularity of statistics to the level of the normalized statement string. The normalized statement string is the statements string with all parameters replaced with question marks. For example, the two statements SELECT id FROM test WHERE id = 0 and SELECT id FROM test WHERE id = 1 are normalized into SELECT id FROM test WHERE id = ?. Their both statistics are aggregated into one entry for SELECT id FROM test WHERE id = ?.

Example 22.320. Example mysqlnd_qc_get_normalized_query_trace_logusage

mysqlnd_qc.enable_qc=1mysqlnd_qc.collect_normalized_query_trace=1

<?php/* connect to MySQL */$mysqli = new mysqli("host", "user", "password", "schema", "port", "socket");$mysqli->query("DROP TABLE IF EXISTS test");$mysqli->query("CREATE TABLE test(id INT)");$mysqli->query("INSERT INTO test(id) VALUES (1), (2), (3)");/* dummy queries to fill the query trace */for ($i = 1; $i <= 4; $i++) {    $query = sprintf("/*%s*/SELECT id FROM test WHERE id = %d", MYSQLND_QC_ENABLE_SWITCH, $i % 2);    $res   = $mysqli->query($query);      $res->free();}var_dump(mysqlnd_qc_get_normalized_query_trace_log());?>

The above examples will output something similar to:

array(4) {  [0]=>  array(9) {    ["query"]=>    string(25) "DROP TABLE IF EXISTS test"    ["occurences"]=>    int(0)    ["eligible_for_caching"]=>    bool(false)    ["avg_run_time"]=>    int(0)    ["min_run_time"]=>    int(0)    ["max_run_time"]=>    int(0)    ["avg_store_time"]=>    int(0)    ["min_store_time"]=>    int(0)    ["max_store_time"]=>    int(0)  }  [1]=>  array(9) {    ["query"]=>    string(27) "CREATE TABLE test (id INT )"    ["occurences"]=>    int(0)    ["eligible_for_caching"]=>    bool(false)    ["avg_run_time"]=>    int(0)    ["min_run_time"]=>    int(0)    ["max_run_time"]=>    int(0)    ["avg_store_time"]=>    int(0)    ["min_store_time"]=>    int(0)    ["max_store_time"]=>    int(0)  }  [2]=>  array(9) {    ["query"]=>    string(46) "INSERT INTO test (id ) VALUES (? ), (? ), (? )"    ["occurences"]=>    int(0)    ["eligible_for_caching"]=>    bool(false)    ["avg_run_time"]=>    int(0)    ["min_run_time"]=>    int(0)    ["max_run_time"]=>    int(0)    ["avg_store_time"]=>    int(0)    ["min_store_time"]=>    int(0)    ["max_store_time"]=>    int(0)  }  [3]=>  array(9) {    ["query"]=>    string(31) "SELECT id FROM test WHERE id =?"    ["occurences"]=>    int(4)    ["eligible_for_caching"]=>    bool(true)    ["avg_run_time"]=>    int(179)    ["min_run_time"]=>    int(11)    ["max_run_time"]=>    int(393)    ["avg_store_time"]=>    int(12)    ["min_store_time"]=>    int(7)    ["max_store_time"]=>    int(25)  }}

The source distribution of PECL/mysqlnd_qc contains a directory web/ in which web based monitoring scripts can be found which give an example how to write a cache monitor. Please, follow the instructions given in the source.

Since PECL/mysqlnd_qc 1.1.0 it is possible to write statistics into a log file. Please, see mysqlnd_qc.collect_statistics_log_file.

22.9.7.4.9. Beyond TTL: user-defined storage

The query cache plugin supports the use of user-defined storage handler. User-defined storage handler can use arbitrarily complex invalidation algorithms and support arbitrary storage media.

All user-defined storage handlers have to provide a certain interface. The functions of the user-defined storage handler will be called by the core of the cache plugin. The necessary interface consists of seven public functions. Both procedural and object oriented user-defined storage handler must implement the same set of functions.

Example 22.321. Using a user-defined storage handler

<?php/* Enable default caching of all statements */ini_set("mysqlnd_qc.cache_by_default", 1);/* Procedural user defined storage handler functions */$__cache = array();function get_hash($host_info, $port, $user, $db, $query) {    global $__cache;    printf("\t%s(%d)\n", __FUNCTION__, func_num_args());    return md5(sprintf("%s%s%s%s%s", $host_info, $port, $user, $db, $query));}function find_query_in_cache($key) {    global $__cache;    printf("\t%s(%d)\n", __FUNCTION__, func_num_args());    if (isset($__cache[$key])) {        $tmp = $__cache[$key];        if ($tmp["valid_until"] < time()) {            unset($__cache[$key]);            $ret = NULL;        } else {            $ret = $__cache[$key]["data"];        }    } else {        $ret = NULL;    }    return $ret;}function return_to_cache($key) {    /*     Called on cache hit after cached data has been processed,     may be used for reference counting    */    printf("\t%s(%d)\n", __FUNCTION__, func_num_args());}function add_query_to_cache_if_not_exists($key, $data, $ttl, $run_time, $store_time, $row_count) {    global $__cache;    printf("\t%s(%d)\n", __FUNCTION__, func_num_args());    $__cache[$key] = array(        "data"               => $data,        "row_count"          => $row_count,        "valid_until"        => time() + $ttl,        "hits"               => 0,        "run_time"           => $run_time,        "store_time"         => $store_time,        "cached_run_times"   => array(),        "cached_store_times" => array(),    );    return TRUE;}function query_is_select($query) {    printf("\t%s('%s'): ", __FUNCTION__, $query);    $ret = FALSE;    if (stristr($query, "SELECT") !== FALSE) {        /* cache for 5 seconds */        $ret = 5;    }    printf("%s\n", (FALSE === $ret) ? "FALSE" : $ret);    return $ret;}function update_query_run_time_stats($key, $run_time, $store_time) {    global $__cache;    printf("\t%s(%d)\n", __FUNCTION__, func_num_args());    if (isset($__cache[$key])) {        $__cache[$key]['hits']++;        $__cache[$key]["cached_run_times"][] = $run_time;        $__cache[$key]["cached_store_times"][] = $store_time;    }}function get_stats($key = NULL) {    global $__cache;    printf("\t%s(%d)\n", __FUNCTION__, func_num_args());    if ($key && isset($__cache[$key])) {        $stats = $__cache[$key];    } else {        $stats = array();        foreach ($__cache as $key => $details) {            $stats[$key] = array(               'hits'              => $details['hits'],               'bytes'             => strlen($details['data']),               'uncached_run_time' => $details['run_time'],               'cached_run_time'   => (count($details['cached_run_times']))                                      ? array_sum($details['cached_run_times']) / count($details['cached_run_times'])                                      : 0,            );        }    }    return $stats;}function clear_cache() {    global $__cache;    printf("\t%s(%d)\n", __FUNCTION__, func_num_args());    $__cache = array();    return TRUE;}/* Install procedural user-defined storage handler */if (!mysqlnd_qc_set_user_handlers("get_hash", "find_query_in_cache",    "return_to_cache", "add_query_to_cache_if_not_exists",    "query_is_select", "update_query_run_time_stats", "get_stats", "clear_cache")) {          printf("Failed to install user-defined storage handler\n");}/* Connect, create and populate test table */$mysqli = new mysqli("host", "user", "password", "schema", "port", "socket");$mysqli->query("DROP TABLE IF EXISTS test");$mysqli->query("CREATE TABLE test(id INT)");$mysqli->query("INSERT INTO test(id) VALUES (1), (2)");printf("\nCache put/cache miss\n");$res = $mysqli->query("SELECT id FROM test WHERE id = 1");var_dump($res->fetch_assoc());$res->free();/* Delete record to verify we get our data from the cache */$mysqli->query("DELETE FROM test WHERE id = 1");printf("\nCache hit\n");$res = $mysqli->query("SELECT id FROM test WHERE id = 1");var_dump($res->fetch_assoc());$res->free();printf("\nDisplay cache statistics\n");var_dump(mysqlnd_qc_get_cache_info());printf("\nFlushing cache, cache put/cache miss");var_dump(mysqlnd_qc_clear_cache());$res = $mysqli->query("SELECT id FROM test WHERE id = 1");var_dump($res->fetch_assoc());$res->free();?>

The above examples will output something similar to:

        query_is_select('DROP TABLE IF EXISTS test'): FALSE        query_is_select('CREATE TABLE test(id INT)'): FALSE        query_is_select('INSERT INTO test(id) VALUES (1), (2)'): FALSECache put/cache miss        query_is_select('SELECT id FROM test WHERE id = 1'): 5        get_hash(5)        find_query_in_cache(1)        add_query_to_cache_if_not_exists(6)array(1) {  ["id"]=>  string(1) "1"}        query_is_select('DELETE FROM test WHERE id = 1'): FALSECache hit        query_is_select('SELECT id FROM test WHERE id = 1'): 5        get_hash(5)        find_query_in_cache(1)        return_to_cache(1)        update_query_run_time_stats(3)array(1) {  ["id"]=>  string(1) "1"}Display cache statistics        get_stats(0)array(4) {  ["num_entries"]=>  int(1)  ["handler"]=>  string(4) "user"  ["handler_version"]=>  string(5) "1.0.0"  ["data"]=>  array(1) {    ["18683c177dc89bb352b29965d112fdaa"]=>    array(4) {      ["hits"]=>      int(1)      ["bytes"]=>      int(71)      ["uncached_run_time"]=>      int(398)      ["cached_run_time"]=>      int(4)    }  }}Flushing cache, cache put/cache miss    clear_cache(0)bool(true)        query_is_select('SELECT id FROM test WHERE id = 1'): 5        get_hash(5)        find_query_in_cache(1)        add_query_to_cache_if_not_exists(6)NULL