Is it worth the efforts to cache the results of a MySQL query at the client? In most cases the answer is: try it, measure it! Install the development version of the mysqlnd query cache plugin, which can be used with PDO_MySQL, mysqli and mysql. Set three PHP directives and find the answer in a log file.
While updating the query cache plugin to support PHP 5.4, the latest versions of APC and Memcached for cache storage, I virtually stumbled upon an undocumented feature I had long forgotten. The plugin can periodically dump statistics into a log file. The plugin collects tons of statistics and query traces to find cache candidates and for measuring cache efficiency. Details can be found in the quickstart.
Quick and dirty evaluation
A quick and dirty evaluation of the maximum performance gain client-side query caching can give you is obtained by caching all statements. The first PHP directive to set is mysqlnd_qc.cache_by_default = 1. Ignore the fact that the cache may serve stale data because its default invalidation strategy is Time-to-Live (TTL). Quick and dirty…
Enable the collection of per-process cache statistics with mysqlnd_qc.collect_statistics. Tell the plugin to dump statistics into the log file set through mysqlnd_qc.collect_statistics_log_file. The plugin will now dump per-process cache statistics into the log at every 10th web request.
info : pid=17092
info : cache_hit=9
info : cache_miss=5
info : cache_put=5
info : query_should_cache=14
info : query_should_not_cache=21
info : query_not_cached=21
info : query_could_cache=14
info : query_found_in_cache=9
info : query_uncached_other=0
info : query_uncached_no_table=0
info : query_uncached_no_result=0
info : query_uncached_use_result=0
info : query_aggr_run_time_cache_hit=232
info : query_aggr_run_time_cache_put=1785
info : query_aggr_run_time_total=2017
info : query_aggr_store_time_cache_hit=113
info : query_aggr_store_time_cache_put=214
info : query_aggr_store_time_total=327
info : receive_bytes_recorded=355
info : receive_bytes_replayed=639
info : send_bytes_recorded=205
info : send_bytes_replayed=369
info : slam_stale_refresh=0
info : slam_stale_hit=0
info : -----------------------------
info : pid=17099
info : cache_hit=12
info : cache_miss=6
info : cache_put=6
info : query_should_cache=18
info : query_should_not_cache=27
info : query_not_cached=27
info : query_could_cache=18
info : query_found_in_cache=12
info : query_uncached_other=0
info : query_uncached_no_table=0
info : query_uncached_no_result=0
info : query_uncached_use_result=0
info : query_aggr_run_time_cache_hit=185
info : query_aggr_run_time_cache_put=3017
info : query_aggr_run_time_total=3202
info : query_aggr_store_time_cache_hit=145
info : query_aggr_store_time_cache_put=405
info : query_aggr_store_time_total=550
info : receive_bytes_recorded=426
info : receive_bytes_replayed=852
info : send_bytes_recorded=246
info : send_bytes_replayed=492
info : slam_stale_refresh=0
info : slam_stale_hit=0
Restart your web server, if needed, to make it recognize the new ini settings. Put some load on it, make sure the PHP scripts run a couple of MySQL queries. Note that its per-process statistics and that they are dumped on every 10th web request served by a process. In other words: you must have at least one process serve 10 requests before you can expect to find something in the log file.
The rest is simple math… pointers are given in the manual, for example, under mysqlnd_qc_get_core_stats(). No math, Perl, sed, grep, whatever-other-post-processing for you? Check out the web/ directory in the source distribution. There’s a basic web monitor.
The ini setting mysqlnd_qc.collect_statistics_log_file for setting a log file name is new and only available in the development tree. The feature itself – dump of statistics into a file – is old. Earlier versions have a compiled in file name of /tmp/mysqlnd.stats.
Happy hacking!
