Re: [PECL-DEV] Package proposal: a query cache plugin for mysqlnd

Hi,

This very interesting, as I was thinking about solution for transparent
DB queries caching (but still in user control) as well, I definitely
like low level solution like this. Just wanted to ask you whether there
is a way to invalidate cached record on update or delete, or is it done
automatically based on record key?

Stan


On 06/24/2010 12:59 PM, Ulf Wendel wrote:
> Hi,
> 
> we would like to add a query cache plugin extension for mysqlnd to PECL
> [1].
> 
> As of PHP 5.3.2 the MySQL native driver for PHP (mysqlnd), which can be
> used with ext/mysql, ext/mysqli and PDO_MYSQL, features an internal C
> plugin API [2, 3]. The proposed query cache plugin is a mysqlnd plugin.
> It is written in C and comes as a PHP extension. C gives you the best
> possible performance.
> 
> *Basic usage* example (see also [1]):
> 
>   $mysqli = new mysqli($host, $user, $password, $db, $port, $socket);
>   // Will be cached because of the SQL hint
>   $res = $mysqli->query("/*qc=on*/SELECT id FROM test WHERE id = 1");
>   var_dump($res->fetch_assoc());
>   $res->free();
> 
> The plugin hooks internal mysqlnd C calls. From a user space point of
> view it is virtually transparent because it does not change existing
> APIs. Like every other mysqlnd plugin it can be used with every PHP
> MySQL API (ext/mysql, ext/mysqli, PDO_MySQL).
> 
> *Key feaures* of the query cache [1]:
> 
>   - works with ext/mysql, ext/mysqli and PDO_MySQL
>   - supports unbuffered queries
> 
>   - controlled through SQL hints
> 
>   - default invalidation strategy: Time-to-live (TTL)
>     - TTL can be set globally or per query
>     - user space storage handler can implement any other
>       more sophisticated invalidation strategy
> 
>   - built-in storage handler:
>       - default (PHP hashes)
>       - APC
>       - Memcache
>       - SQLite
>       - user
> 
> *License*:
> 
>    PHP License
> 
> *Source* download, build instructions, requirements, ... :
> 
>   [1] http://forge.mysql.com/wiki/MySQLnd_Query_Cache_Plugin_for_PHP
> 
> *Testing/QA status*:
> 
> We officially call it a "prototype".
> 
> However, that is the official description. The extension comes with 58
> *.phpt tests which should cover more than 85% of the extensions source
> code, if you run the tests against all storage handler before generating
> code coverage figures.
> 
> All tests pass.
> 
> The code has been build and tested on some 30 platforms:
> 
>  - FreeBSD 6 (x86, x86_64)
>  - FreeBSD 7 (x86, x86_64)
>  - Linux ia64
>  - Linux RHEL 4, 5 (ia64, x86, x86_64)
>  - Linux SLES 9, 10 (ia64, x86, x86_64)
>  - Solaris 11 (x86, x86_64)
>  - a couple of Ubuntu and OpenSuSE x86/x86_64 used for development
>  - Win XP, VC9, x86
> 
> Valgrind likes the code with the exception of (my) APC handler. No
> valgrind warnings when using default, Memcache, SQLite and user space
> handler.
> 
> The package.xml calls the API "beta".
> 
> *Documentation*:
> 
>    Prepared, see [1] and [4].
> 
> *TODO*:
> 
>  - check almost untested package.xml
>  - align config.win32 and config.m4
>  - fix APC handler valgrind warnings
>  - write documentation in PECL/php.net format
> 
> Ulf
> 
> [1] http://forge.mysql.com/wiki/MySQLnd_Query_Cache_Plugin_for_PHP
> [2] http://blog.ulf-wendel.de/mysqlnd_plugin_ipc2010.html
> [3] http://www.slideshare.net/nixnutz/the-mysql
> [4]
> http://www.slideshare.net/nixnutz/buildin-query-caching-for-all-php-mysql-extensionsapis
> 
>