New in the PHP manual: a quickstart for the mysqlnd query cache plugin. PECL/mysqlnd_qc, the mysqlnd query cache plugin, is transparent and ease to use. But, how? Some pointers have been given in assorted presentations, here on my blog and in some, few examples from the manual. Fixed. You can now browse a quickstart to gain a quick overview.
- Architecture and Concepts
- Setup
- Setting the TTL
- Pattern based caching
- Slam defense
- Finding cache candidates
- Measuring cache efficiency
- Beyond TTL: user-defined storage
Should I consider it?
The PECL/mysqlnd_qc manual is well worth 15 minutes of your attention if you either have a remote MySQL server or, you cannot use the query cache built-in to the MySQL server but want to use query caching. If that’s not the case, you may still want to check its query traces to abuse them for performance monitoring. More on that below.
Caching for the web always follows the same pattern. First, you move caches as close to the client as possible. Then, you select an appropriate granularity for cache contents on all layers for which you plan to use caching. The MySQL server query cache is neat: it never serves stale data and, it comes with MySQL. But, it is not as close to the client as possible. This adds latency and makes scale-by-client a bit more difficult. Unfortunately, there is still no way to connect it to a client-side cache. Thus, PECL/mysqlnd_qc.
Simple monitor from the source distributions web/ directory.
The mysqlnd query cache plugin with its various storage handlers (process memory, APC, Memcache, SQLite, user-defined) has a slide edge over an application centric solution:
- No or minimal application changes
- no hassle when updating 3rd party solutions
- can be used even if code change is not possible but auto_prepend can be set
- compatible with all PHP MySQL APIs (mysqli, PDO_MySQL, mysql)
- plugs into to the driver: no extra PHP library/software to install
- Flexible storage
- process scope: process memory
- single machine scope: APC, Memcache, SQLite (memory)
- multiple machines scope: Memcache
- Solid monitoring
Whether your cache solution shall have a granularity of individual SQL statements or, for example, should cache rendered HTML fragments is a different story… A nice aspect of PECL/mysqlnd_qc is that you can use it to evaluate the impact of database caching pretty quickly. Turn on caching of all statements, run a benchmark. Whatever database cache solution you end up with, results won’t be better than that. Based on the figures you can decide if its worth the efforts.
The story about monitoring
Even if the caching aspect is not for you, mysqlnd_qc_get_query_trace_log() may appeal to you. To help finding cache candidates the plugin can be instructed to collect a query trace log. The trace contains a statements string, the statements run time, its store time and a backtrace to its origin in the source. Each and every query inspected by the plugin is listed. Because PECL/mysqlnd_qc operated on the driver level, you see all queries from every PHP MySQL extension. The background is described in more detail here.
mysqlnd_qc.enable_qc=1 mysqlnd_qc.collect_query_trace=1
/* 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());
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)
}
}
Happy hacking!