Ulf Wendel

2010/10/13
by admin
Comments Off on The mysqlnd query cache meets Oxid at the IPC 2010

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 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.

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.

Commercial

thePHP.cc

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.

team@thephp.cc
 

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 …

Commercial

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.

http://www.mayflower.de/en/contact
 

… 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));
  }
}
?>

Commercial

Qafoo

Increase your Return On Investment

Ease your project management

Raise developer motivation

contact@qafoo.com
 

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?

Commercial

PS

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.

2010/09/26
by admin
2 Comments

PHP Unconference 2011 – jetzt Tickets sichern!

Die PHP Unconference Hamburg 2010 endete vor wenigen Stunden. Meine anfängliche Freude wurde längst von einer Frage verdrängt: was wäre, wenn ich 2011 keines der begehrten Tickets erhalte? Angst und Frust bauen sich auf. Ein Ticket für die PHP Unconference zu bekommen ist genauso schwer wie eines der vererbten Dauertickets für Heimspiele des THW Kiel zu bekommen. Nein, eigentlich ist es genauso aussichtsslos wie davon zu träumen ein Ticket für ein Wise Guys-Konzert in der Krusenkoppel während der Kieler Woche zu ergattern. Es ist zum Heulen. Dieses Jahr waren die 250 Tickets wieder binnen Stunden ausverkauft.

PHP Unconference Hamburg

Die diesjährige Meisterprüfung des OrgaTeams (Markus Wolff, Florian Blasel, Ekkehard Dörre, Stefan J. Morgenroth, Sven Rautenberg) verlief ereignislos. Alle Prüfungsfragen wurden souverän beantwortet. Bestanden mit Auszeichnung: von den Räumlichkeiten, dem Wireless LAN, der Verpflegung bis hin zum abendlichen (Flash-)Mob stimmte alles, die Erfahrung aus den Ausbildungsjahren (2007, 2008, 2009) hat Meister geformt. Die Sponsoren (InnoGames, Mayflower, Microsoft, apprupt, MySQL) , ohne die es keine Meisterausbildung gegeben hätte, haben eine nachhaltige Investition getätigt.

Das Grundkonzept der Veranstaltung blieb auch dieses Jahr unverändert. Am Morgen eines jeden Veranstaltungstages werden Vortrags- und Diskussionsvorschläge gesammelt. Die gefundenen Themen werden auf Schautafeln festgehalten. Jeder Teilnehmer erhält einige farbige Aufkleber mittels derer er für ein Thema stimmen kann. Manch einer kann es kaum erwarten seine Stimmen abzugeben. Oft entwickelt sich eine Eigendynamik und Kreativität, die kein Programmkomitee einer "normalen" klassischen Konferenz leisten kann. Kein Komitee nimmt Rücksicht auf Firmeninteressen, kein Komitee entscheidet nach Sponsoring, kein Komitee rät welche Themen von Interesse sein könnten. Es sind die Teilnehmer, welche die Inhalte bestimmen.

Bisweilen erhalten vermeintlich "sichere" Vortragsvorschläge aus den Reihen des deutschen Wer-ist-Wer der PHP-Szene wenig Zuspruch. Neue Gesichter präsentieren und profilieren sich mit neuen Ideen. Der offene Wettstreit bringt einen Mix aus Klassikern und Themen, die vermeintlich nicht zu einer PHP-Unkonferenz passen wollen, beispielsweise "Verstehe deinen Projektmanager!" Die Qualität der Vorträge ist durchweg sehr gut. Die Konkurrenz ist zu groß für mittelmäßige Vorträge.

Für das kommende Jahr sehe ich schwarz. Es gibt nichts mehr zu verbessern und es wird wieder ein nervenzehrendes Rennen um die wenigen Plätze entbrennen. Die Hamburger können nichts mehr besser machen.

Jetzt sind die Münchener gefragt: 2 x 250 = 500! Gelingt der Export des Konzepts vom PHP Vikinger über die PHP Unconference Hamburg zur PHP Unconference Bavaria im Frühsommer 2011?

Weitere Fotos

2010/08/22
by admin
1 Comment

PHP @ FrOSCon: the power of mysqlnd plugins

Slowly the power of mysqlnd plugins becomes visible. Mysqlnd plugins challenge MySQL Proxy and are often a noteworthy, if not superior, alternative alternative to MySQL Proxy for PHP users. Plugins can do almost anything MySQL Proxy can do – but on the client. Please find details in the slides. The presentation has been given today at the PHP track at FrOSCon.

The biggest news is certainly the work from David Soria Parra. David made it possible to write mysqlnd plugins in PHP! No C coding skills needed. It can’t get any easier. Insipired by the hints given in the C mysqlnd plugin API documentation he and his employer Mayflower decided to develop the “mysqlnd_uh” PECL extension. “mysqlnd_uh” stands for “mysqlnd userhandler”.


class QueryLogger extends MysqlndUhConnection {

  public function query($res, $query) {
    error_log($query);
    return parent::query($res, $query);
  }

}

mysqlnd_uh_set_connection_proxy(new QueryLogger());

Assume you have installed a PHP application and you want to know what queries it runs. Using PECL/mysqlnd_uh it is as little as the above code needed to audit the application. Install PECL/mysqlnd_uh, put the code in your auto_prepend_file and find the queries in your error log. This works with every PHP application running on PHP 5.3.3 or newer. It works without touching the application. Not having to change the application itself makes updates much easier. Whenever the application vendor releases a new version you can just install it. Your QueryLogger is not part of the application. It will continue to work after the application update.

Query logging is trivial. Even load balancing or failover should be easy to implement. Future will proof, stay tuned!

2010/07/12
by admin
Comments Off on Präsentationen zum PHP mysqlnd Anfrage-Cache

Präsentationen zum PHP mysqlnd Anfrage-Cache

Von einer Sommerpause fehlt am Horizont über Kiel jede Spur wie auch von einer schattenspenden Wolke. Heute berichtet der PHP Blog "PHP hates me" vom integrierten PHP mysqlnd Anfrage-Zwischenspeicher: Das MySQL Mofa. Der Artikel wiederholt bereits bekannte Grundlagen. Wer das "mysqlnd query result cache plugin" noch nicht kennt und wissen möchte wie alle PHP MySQL Erweiterungen um einen Anfrage-Zwischenspeicher erweitert werden können, der sollte einen Blick riskieren.

Neue und weitergehende Einblicke verschaffen die in den letzten Wochen veröffentlichten Präsentationen auf über 200 Vortragsfolien.

Es wird eine Weile dauern bis die Vortragsfolien in die reguläre Dokumentation eingearbeitet sind. Die erste Fassung der regulären Dokumentation wurde Ende letzter Woche fertiggestellt. Gerade noch rechtzeitig vor der ersten Erstellung eines PECL-Paketes. Die Query Cache Erweiterung zieht in diesen Tagen von der noch gültigen Projektseite unter http://forge.mysql.com/wiki/MySQLnd_Query_Cache_Plugin_for_PHP um auf PECL. Der Quellcode wurde bereits dem PECL-SVN-Archiv hinzugefügt, eine 1.0.0 getaggt, ein Paket hochgeladen und neue Funktionen wurden hinzugefügt. Trotz der Hitze ist das Projekt nicht ausgedörrt. Eine Sommerpause wird es kaum geben.

2010/06/24
by admin
Comments Off on Ein integrierter Anfrage-Cache für alle PHP MySQL Datenbankschnittstellen

Ein integrierter Anfrage-Cache für alle PHP MySQL Datenbankschnittstellen

Das erste öffentliche PHP mysqlnd-Plugin ist da ! Das "mysqlnd query result plugin" (PHP-Lizenz Quellcode) erweitert die Funktionalität aller PHP MySQL Schnittstellen (ext/mysqli, ext/mysql, PDO_MySQL) um Anfragecaching. Das Plugin kann Anfrageergebnisse im Hauptspeicher, in Memcached, APC und in SQLite (mit Wrapper auch BerkeleyDB) ablegen. Damit integriert es sich in etablierte Open Source Standardlösungen.

Continue Reading →

2010/06/24
by admin
9 Comments

PHP: Client side caching for all MySQL extensions

The first public mysqlnd plugin adds client side query result caching to all MySQL extensions of PHP (ext/mysql, ext/mysqli, PDO_MySQL). The cache is written in C. It does not change any of the PHP MySQL APIs and works with any PHP application using MySQL. Query results are stored on the client. Cached data can be stored in main memory, APC, Memcache, SQLite (theoretically – via SQLite wrapperBerkeleyDB). The default invalidation strategy is TTL (Time to live): cache entries are valid for n-seconds. The TTL can be set per query or globally for all queries. User-defined storage handler can implement any invalidation method. Some built-in storage handler offer a special slam defense mode which you may know from Memcache or APC. Statistics help you to identify cache candidates and to measure cache efficiency. The plugin is available in source (PHP License)

Download :

C performance, almost transparent, horizontal scale out

The mysqlnd query result plugin is written in C like every other mysqlnd plugin today. It plugs into the MySQL native driver for PHP (mysqlnd) and adds query caching functionality to it. C gives you the best possible performance – better than any PHP based cache. The plugin is also a PHP extension. Adding a new PHP extension to an existing PHP deployment infrastructure should be easy going technically. Because it is a PHP extension and part of the database library, it gives many users, who do not compile PHP themselves, an "out-of-the box" experience. For those users caching will be just "built-in".

If you are new to mysqlnd and mysqlnd plugins, please note the background information given at the end of the article.

Any PHP MySQL application
|
ext/mysql, ext/mysqli, PDO_MySQL
|
MySQL native driver for PHP (mysqlnd)
mysqlnd query result cache plugin
Hit   Miss
Cache:
main memory, Memcached, APC, SQLite
  MySQL Server

The cache plugin operates on a new level in between the MySQL Server and the existing PHP MySQL APIs. Therefore it does not change any of the existing APIs. It works with every PHP MySQL application including existing ones. This is comparable to a proxy cache.

Continue Reading →

2010/06/22
by admin
Comments Off on PHP BBQs 2010 – Karlsruhe today, Frankfurt 27th

PHP BBQs 2010 – Karlsruhe today, Frankfurt 27th

Pretty much exactly a year ago many german PHP meetups have held BBQs to celebrate the summer, to invite guests to join their meetups in a very relaxed atmosphere and to enjoy themselves. During the PHP BBQ week 2009 more than 140 people got connected and had fun . And, of course, I loved visiting all the meetups. I am not touring through the user groups this year. However, and that is fantastic news, some user groups have organized BBQs again. Today, June 22th – 19:00, you can enjoy a BBQ in Karlsruhe organized by the PHP user group Karlsruhe and sponsored by urlaubswerk.de! Check their homepage for the latest details.

KIT Campus Süd – Center für Innovation & Entrepreneurship (CIE)
CIE Cube – Geb. 30.29
Engesserstr. 9 (vor dem Gerthsen Hörsaal)
76131 Karlsruhe

Map
Planning and food

PHP BBQ Karlsruhe 2009

The PHP user group Frankfurt invites you to join a (not only) PHP BBQ on Sunday, June 27th.

PHP BBQ Frankfurt 2010

I hear some rumors about Berlin but nothing seems decided yet or I am just uninformed… In any case, I strongly recommend to visit your local PHP user group to enjoy the events. Credits go to last and this years sponsors and the user groups which did it again!

If you hestitate visiting your local meetup, check the faces of the people which attended PHP BBQ 2009…