Ulf Wendel

The mysqlnd query cache meets Oxid at the IPC 2010

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

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.

PHP APC mysqlnd mysqlnd_uh mysqlnd_qc Req/s %
5.2.15-dev no no no no 4.61 100%
5.2.15-dev yes no no no 7.44 161%
5.3.4-dev yes no no align=”right”no 7.99 173%
5.3.4-dev yes yes no no 8.31 180%
5.3.4-dev yes yes yes [1] no 7.99 173%
5.3.4-dev yes yes yes [1] yes [2] 7.77 168%
5.3.4-dev yes yes yes [3] yes [2] 7.70 167%
5.3.4-dev yes yes no yes [4] 8.70 188%
5.3.4-dev yes yes no yes [5] 8.94 194%
5.3.99-dev yes yes no yes [5] 11.49 249%
  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.



In times of tight budgets, quality is a key success factor to reduce risks in IT projects.

We help businesses and enterprises to reduce IT costs in the long term with Open Source technologies and PHP in particular.


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.


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.


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 …


To new shores with MAYFLOWER

Mayflower is Germany’s largest service provider in terms of PHP programming.
No matter whether you need corporation-wide Intranets, communities, e-business and e-commerce systems or customised solutions – we are the right partner to cooperate with. Find out more about our services in the field of software development.


… with auto_prepend_file=prepend.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();

… and auto_append_file=append.php .

/* append.php */
print "<pre>";

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



Increase your Return On Investment

Ease your project management

Raise developer motivation


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?



At some point during the very first PHP Kongress in Cologne in 2000, Björn Schotte came on stage.
Björn was one of the organizers of the event. He was about to start his talk when he noticed that the doors were open. He asked to close them. Some people in the background started to joke about Björn, who had been given the nickname Ferengi back then: attention, doors will be locked, the sales starts, Ferengi will not allow anybody to leave the room before he has sold something to each of us!

What may sound like a respectless joke is telling some truth. Most of us had been well aware of the importance of a commercial conference. Björn was one of the first telling that truth. Regardless of the jokes!

Anyway, if there is kind of a sales tradition, why not have some commericals during the 10th aniversary?

I am looking forward for the next 10 years. I would be glad to be allowed to speak again at the event. Congratulations to the PHP Kongress 2000 … IPC 2010 team !

The very first speaker of the PHP Kongress 2000.

Comments are closed.