Copyright 1997-2012 the PHP Documentation Group.
The mysqlnd query cache plugin is easy to use. This quickstart will demo typical use-cases, and provide practical advice on getting started.
It is strongly recommended to read the reference sections in addition to the quickstart. It is safe to begin with the quickstart. However, before using the plugin in mission critical environments we urge you to read additionally the background information from the reference sections.
Most of the examples use the mysqli extension because it is the most feature complete PHP MySQL extension. However, the plugin can be used with any PHP MySQL extension that is using the mysqlnd library.
Copyright 1997-2012 the PHP Documentation Group.
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.
Copyright 1997-2012 the PHP Documentation Group.
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.
Copyright 1997-2012 the PHP Documentation Group.
There are four ways to trigger caching of a query.
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 20.305. 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.000740s
array(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 20.306. Using the MYSQLND_QC_DISABLE_SWITCH SQL hint
mysqlnd_qc.enable_qc=1
mysqlnd_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 20.307. Example showing which type of statements are not cached
mysqlnd_qc.enable_qc=1
mysqlnd_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.000540s
array(2) {
["id"]=>
string(1) "1"
["_time"]=>
string(19) "2012-01-11 15:43:11"
}
Total time: 0.000555s
array(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 20.308. Enabling caching for all statements using the
mysqlnd_qc.cache_no_table ini setting
mysqlnd_qc.enable_qc=1
mysqlnd_qc.cache_by_default=1
mysqlnd_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.000546s
array(2) {
["id"]=>
string(1) "1"
["_time"]=>
string(19) "2012-01-11 15:47:45"
}
Total time: 0.000187s
array(2) {
["id"]=>
string(1) "1"
["_time"]=>
string(19) "2012-01-11 15:47:45"
}
Total time: 0.000167s
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.
Copyright 1997-2012 the PHP Documentation Group.
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 20.309. Setting the TTL with the mysqlnd_qc.ttl ini setting
mysqlnd_qc.enable_qc=1
mysqlnd_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:59
Wall time 14:56:00 - DB row time 2012-01-11 14:55:59
Wall time 14:56:01 - DB row time 2012-01-11 14:55:59
Wall time 14:56:02 - DB row time 2012-01-11 14:56:02
Wall time 14:56:03 - DB row time 2012-01-11 14:56:02
Wall time 14:56:04 - DB row time 2012-01-11 14:56:02
Wall 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 20.310. 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 seconds
array(1) {
["id"]=>
string(1) "1"
}
array(1) {
["id"]=>
string(1) "1"
}
NULL
Script runtime : 3 seconds
Copyright 1997-2012 the PHP Documentation Group.
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 20.311. Setting a callback with
mysqlnd_qc_set_is_select
mysqlnd_qc.enable_qc=1
mysqlnd_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 cache
is_select( CREATE TABLE test(id INT)): do not cache
is_select( INSERT INTO test(id) VALUES (1), (2), (3)): do not cache
is_select( SELECT id FROM test WHERE id = 1): cache
is_select( SELECT id FROM test WHERE id = 1): cache
is_select( SELECT * FROM test): cache
Cache put: 2
Cache 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.
Copyright 1997-2012 the PHP Documentation Group.
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 20.312. Enabling the slam defense mechanism
mysqlnd_qc.slam_defense=1
mysqlnd_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.
Copyright 1997-2012 the PHP Documentation Group.
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 20.313. Collecting a query trace
mysqlnd_qc.enable_qc=1
mysqlnd_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 20.314. Setting the backtrace depth with the
mysqlnd_qc.query_trace_bt_depth ini
setting
mysqlnd_qc.enable_qc=1
mysqlnd_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)
Copyright 1997-2012 the PHP Documentation Group.
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 20.315. Collecting statistics data with the
mysqlnd_qc.time_statistics ini setting
mysqlnd_qc.enable_qc=1
mysqlnd_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 20.316. Example
mysqlnd_qc_get_cache_info
usage
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 socket
3306
root
test|/*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 socket
3306
root
test|/*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 20.317. Example
mysqlnd_qc_get_normalized_query_trace_log
usage
mysqlnd_qc.enable_qc=1
mysqlnd_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.
Copyright 1997-2012 the PHP Documentation Group.
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 20.318. 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)'): FALSE
Cache 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'): FALSE
Cache 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