How amusing: an ancient write-up on the first PHP Kongress in 2000 edition written by one who later became a mentor of the autor of phpOpenTracker. How boring: a silver guy talking at the 10th aniversary of the conference about some 60% performace benefit for Oxid eShop, an example of a modern award-winning software, if using the mysqlnd query cache plugin (PECL/mysqlnd_qc). How confusing: the same silver guy calls his own benchmarks irrelevant and faulty.

Benchmark impressions

At the 10th International PHP Conference I presented 10 benchmark results for Oxid eShop. The benchmark is simple and non-scientific. I am using two machines with x86_64 two-core CPUs, 4 GB RAM, RAID-0 and a 1GBit ethernet. One machine serves as a database server, the other one runs Apache and PHP. My load generator is Apache bench: ab -c8 -n80 http://127.0.0.1/oxid.

I have not modified Oxid for the benchmark. No application changes are needed to use the mysqlnd query cache plugin. The only variable I changed was the PHP binary. I have tested assorted versions of PHP with and without support for PECL/mysqlnd_qc. The query cache plugin works with every PHP MySQL application.

1. mysqlnd_uh compiled in but no user space query monitor registered.
2. mysqlnd_qc compiled in but no caching performed.
3. mysqlnd_uh compiled in and every query monitored using user space query monitor.
4. mysqlnd_qc compiled in, cache turned on, mysqlnd_qc.cache_by_default = 1.
5. mysqlnd_qc compiled in, cache turned on, mysqlnd_qc.cache_by_default = 1, mysqlnd_qc.cache_no_table = 1.

Oxid loves the query cache! How much, no much enough? No, problem. Check the slides how you can get from 60% to 200% performance win!

Note, however, that the mysqlnd query cache plugins uses Time-To-Live (TTL) as its default invalidation strateg. Any TTL based cache can serve stale data. For the sake of the benchmark this has been ignored.

Easy!

Lifting the TTL limitation is easy. It can be done without touching the PHP MySQL application that uses the cache. Using Andrey-hates-me technology it is trivial to intercept all queries. If you detect an INSERT, UPDATE or DELETE operation, just flush the cache. However, as a first step you may be able to identify queries that can easily be cached and for which stale data can be served. How about search-engine optimizsation (SEO) related queries. Do you really need to update search-engine hints on a per request basis? Google will probably not do real-time monitoring of your shop.

[PHP]
mysqlnd_qc.collect_normalized_query_trace=1
mysqlnd_qc.collect_query_trace=1
mysqlnd_qc.query_trace_bt_depth=20
mysqlnd_qc.collect_statistics=1
auto_prepend_file=/home/nixnutz/www/htdocs/oxid/prepend.php
auto_append_file=/home/nixnutz/www/htdocs/oxid/append.php

Let’s start the adventure game. The goal is to cache only selected queries. We want to cache only those queries for which TTL is no serious limitation. Leisure Suit Larry, the hero of our game, gives it a try: Use php.ini …

… with auto_prepend_file=prepend.php

<?php
/* prepend.php */

class my_qc extends mysqlnd_qc_handler_default {
  public function is_select($query) {
    if (preg_match("@from oxseo where@ism", $query)) {
      /* cache query for mysqlnd_qc.ttl seconds */
      return true;
    }
    return parent::is_select($query);
  }
}
$qc = new my_qc();
mysqlnd_qc_change_handler($qc);
?>

… and auto_append_file=append.php .

<?php
/* append.php */
print "<pre>";
var_dump(mysqlnd_qc_get_core_stats());

$queries = mysqlnd_qc_get_query_trace_log();
foreach ($queries as $k => $details) {
  if ($details['eligible_for_caching']) {
    printf("(%d/%d) %s ...\n",
      $details['run_time'],
      $details['store_time'],
      substr($details['query'], 0, 40));
  }
}
?>

Good choice! After a few reloads the cache gets filled and 90 SEO related queries are served from the cache.

array(26) {
  ["cache_hit"]  =>   string(3) "270"
  ["cache_miss"] =>   string(3) "446"
  ["cache_put"]  =>   string(2) "90"
  [.. snip ...]
}
(30/14) select oxfixed, oxseourl, oxexpired, oxt ...
(30/27) select oxfixed, oxseourl, oxexpired, oxt ...

The story continues: per query statistics, per query backtrace – regardless if the query is cached or not. The slides tell you how you get it with less than 30 lines of PHP code.

I hope to see you at the next International PHP Conference! Thanks to everybody for making the IPC happen! Where would PHP be, if there had been no PHP Kongress 2000, no IPC 2001, no IPC 2002 and so on?