Categories: Software Preview | PHP | MySQLDevelopment

MySQLnd Query Cache Plugin for PHP


Contents

[edit] New Home: relocation going on

The software has been checked in to the PHP [PECL] repository. No package download is available yet. Version 1.0.0. has been tagged in the PECL SVN repository and Prepared Statement support has been added to break the code from trunk ;-).

Initial documentation has been added to the PHP manual. The documentation has not been rendered and does not yet show up on php.net. You can expect to see a download package and documentation within the next few days (Ulf - 2010/07/12).

Meanwhile use the stable download from this page.

[edit] Introduction

The Mysqlnd Query Result Cache plugin for PHP is an easy to use client-side query cache for PHP 5.3.3-dev or newer.

The cache plugin is published under the terms of the PHP license.

As of version PHP 5.3.2 the MySQL native driver for PHP (mysqlnd) features an internal plugin C API. C plugins, such as the Query Cache plugin, can extend the functionality of mysqlnd.

The MySQL native driver for PHP is a C library which ships together with PHP as of PHP 5.3.0. It serves as a drop-in replacement for the MySQL Client Library (AKA libmysql/libmysqlclient). Using mysqlnd has several advantages: no extra downloads because it comes with PHP, PHP license, lower memory consumption in certain cases, new functionality such as asynchronous queries.

Mysqlnd plugins such as the query cache plugin operate transparent from a user perspective. The cache plugin supports all PHP applications and all PHP MySQL extension (ext/mysqli, ext/mysql, PDO_MYSQL). It does not change existing APIs.

No significant application changes are required to cache a query. The cache has two operation modes. It will either cache all queries (not recommended) or only those queries marked with a certain SQL hint (recommended).

[edit] Key Features

[edit] Status

The mysqlnd query result cache plugin is released as a software preview, a prototype. Use in production environments or with mission-critical data is not recommended.

We have build and tested the cache on some 30 platforms. The download archive contains some 50 test files. The tests cover well more than 85% of the source code. Although we believe the code to be of acceptable quality and better than "preview", we release it as a software preview because of the lack of any user testing.


[edit] Download

Source (*.zip)

There are no binary downloads - it's PHP, isn't it?

Please find installation notes below.

[edit] Documentation

More documentation will follow during the next days. As a start, please read this wiki page, check the 60+ slides presentation "MySQL native driver for PHP: A query cache plugin" (PDF download) and PlanetMySQL for blog postings.

[edit] Limitations

The Query Cache plugin prototype will not cache unbuffered queries or results from prepared statements. However, the following popular user API calls use buffered queries which can be cached:

[edit] Architecture

The Query Cache is implemented as a PHP extension. It is written in C and operates "under the hood" of PHP. During the start up of the PHP interpreter it gets registered as a mysqlnd plugin to replaces selected mysqlnd methods.

At PHP run time it proxies queries send from mysqlnd/PHP to the MySQL server. If a query string starts with the SQL hint (/*qc=on*/) to enable caching of it and the query is not cached (Cache miss), the query cache plugin will record the raw wire protocol data send from MySQL to PHP to answer the query. The query cache records the wire protocol data in its cache medium and replays it, if still valid, on a cache hit.

Note that the query cache does not hold decoded result sets consisting of zvals (C struct representing a PHP variable). It stores the raw wire data of the MySQL client server protocol. In case of a cache hits, mysqlnd still needs to decode the cached raw wire data into PHP variables before passing the result to the user space. This approach has one major advantage: simplicity.

[edit] Download

Source (*.zip)

There are no binary downloads - it's PHP, isn't it?

Please find installation notes below.

[edit] Installation

Requirements:

1. Add the query cache plugin to your PHP build directory 

me@host:/path/to/php-5_3_3dev> mkdir ext/mysqlnd_qc
me@host:/path/to/php-5_3_3dev> cp -R /path/to/mysqlnd_qc_plugin_1_0_0_prototype/* ext/mysqlnd_qc

2: Optional: enable the APC handler

Note: it is recommended to use the latest APC development version from the PECL SVN repository. Do not use the release. 

me@host:/path/to/php-5_3_3dev> mkdir ext/apc
me@host:/path/to/php-5_3_3dev> cp /path/to/apc-3.1.3p1-trunk/


3. Rebuild the configure script 

me@host:/path/to/php-5_3_3dev> ./buildconf --force

4. Configure PHP

Check the new configure options of the MySQLnd Query Cache Plugin. 

me@host:/path/to/php-5_3_3dev> ./configure --help | grep qc
  --enable-mysqlnd-qc           Enable mysqlnd_qc support
  --enable-mysqlnd-qc-apc       Enable mysqlnd_qc APC support
  --enable-mysqlnd-qc-memcache  Enable mysqlnd_qc Memcache support
  --enable-mysqlnd-qc-sqlite    Enable mysqlnd_qc SQLite support

Add the selected configure options to your typical configure string. If you enable the APC handler, you have to enable the APC extensions as well. Memcache support requires libmemcache. The path to libmemcache can be set with --with-libmemcached-dir. The SQLlite storage handler requires --with-sqlite3.

This is an example of a configure line to build PHP with support for all PHP MySQL extensions, the mysqlnd query result cache plugin and all its storage handlers. 

me@host:/path/to/php-5_3_3dev>  ./configure \
--with-mysqli=mysqlnd \
--with-mysql=mysqlnd \
--enable-pdo-mysql=mysqlnd \
--enable-mysqlnd-qc \
--enable-mysqlnd-qc-apc --enable-apc \
--enable-mysqlnd-qc-memcache --enable-memcache --with-libmemcached-dir=/path/to/libmemcached \
--enable-mysqlnd-qc-sqlite --with-sqlite3


5. Build PHP 

me@host:/path/to/php-5_3_2dev> make clean && make

[edit] Basic usage

The Query Cache plugin supports caching of queries issued by the following user API calls:

A query which shall be cached must begin with the SQL hint "/*qc=on*/":

Example using the most advanced PHP MySQL API, which is ext/mysqli: 

<?php
$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();

// Will NOT be cached - no SQL hint
$res = $mysqli->query("SELECT id FROM test WHERE id = 1");
var_dump($res->fetch_assoc());
$res->free();
?>

The SQL hint /*qc=on*/ may be followed by another optional SQL hint to specify the TTL (in seconds) for the query. If the TTL SQL hint is omitted the cache plugin will use the default TTL setting from the PHP configuration file (php.ini: mysqlnd_qc.ttl).



[edit] Functions

More and detailed descriptions are to come.

[edit] mysqlnd_qc_set_user_handlers()

Sets the call backs functions for a user defined storage handler. 

 boolean mysqlnd_qc_set_user_handlers(string $get_hash, string $find,
      string $return_to_cache, string $add,
      string $is_select, string $update_stats,
      strung $get_stats, string $clear_cache)

Returns TRUE on success or FALSE on FAILURE.

See also tests/mysqlnd_qc_user_handler_basics.phpt and tests/user_handler_helper.inc .

[edit] mysqlnd_qc_change_handler() 

 bool mysqlnd_qc_change_handler(mixed $handler)

Returns TRUE on success or FALSE on FAILURE.

See also tests/mysqlnd_qc_change_standard_handle.phpt, tests/mysqlnd_qc_oo_default.phpt.

See also mysqlnd_qc_get_handler().

[edit] mysqlnd_qc_get_handler() 

array mysqlnd_qc_get_handler()

Returns a list of the built-in storage handler and their versions. 

var_dump(mysqlnd_qc_get_handler());

array(5) {
 ["default"]=>
 array(2) {
   ["version"]=>
   string(5) "1.0.0"
   ["version_number"]=>
   int(100000)
 }
 ["user"]=>
 array(2) {
   ["version"]=>
   string(5) "1.0.0"
   ["version_number"]=>
   int(100000)
 }
 ["APC"]=>
 array(2) {
   ["version"]=>
   string(5) "1.0.0"
   ["version_number"]=>
   int(100000)
 }
 ["MEMCACHE"]=>
 array(2) {
   ["version"]=>
   string(5) "1.0.0"
   ["version_number"]=>
   int(100000)
 }
 ["sqlite"]=>
 array(2) {
   ["version"]=>
   string(5) "1.0.0"
   ["version_number"]=>
   int(100000)
 }
}

See also test/mysqlnd_qc_get_handler.phpt

[edit] mysqlnd_qc_get_cache_info() 

 array mysqlnd_qc_get_cache_info()

Returns information on the on the current cache contents, if available. If and what data will be returned is subject to the active storage handler. Storage handler are free to return any data. Storage handler are recommended to return at least the data provided by the default handler, if technically possible. 


sapi/cli/php -r '$mysqli = new mysqli('localhost', 'root', 'root', 'test');
                 $mysqli->query("/*qc=on*/SELECT id FROM test");
                 var_dump(mysqlnd_qc_get_cache_info());'

array(4) {
 ["num_entries"]=>
 int(1)
 ["handler"]=>
 string(7) "default"
 ["handler_version"]=>
 string(5) "1.0.0"
 ["data"]=>
 array(1) {
   ["Localhost via UNIX socket 3306 root test|/*qc=on*/SELECT id FROM test"]=>
   array(2) {
     ["statistics"]=>
     array(11) {
       ["rows"]=>
       int(6)
       ["stored_size"]=>
       int(101)
       ["cache_hits"]=>
       int(0)
       ["run_time"]=>
       int(471)
       ["store_time"]=>
       int(27)
       ["min_run_time"]=>
       int(0)
       ["max_run_time"]=>
       int(0)
       ["min_store_time"]=>
       int(0)
       ["max_store_time"]=>
       int(0)
       ["avg_run_time"]=>
       int(0)
       ["avg_store_time"]=>
       int(0)
     }
     ["metadata"]=>
     array(1) {
       [0]=>
       array(8) {
         ["name"]=>
         string(2) "id"
         ["orig_name"]=>
         string(2) "id"
         ["table"]=>
         string(4) "test"
         ["orig_table"]=>
         string(4) "test"
         ["db"]=>
         string(4) "test"
         ["max_length"]=>
         int(1)
         ["length"]=>
         int(11)
         ["type"]=>
         int(3)
       }
     }
   }
 }
}

[edit] mysqlnd_qc_get_core_stats() 

array mysqlnd_qc_get_core_stats

Returns an array of statistics collected by the core of the cache plugin extension. Because the data is collected by the core you the same data fields will be reported for any storage handler. 

array(26) {
 ["cache_hit"]=>
 string(1) "0"
 ["cache_miss"]=>
 string(1) "0"
 ["cache_put"]=>
 string(1) "0"
 ["query_should_cache"]=>
 string(1) "0"
 ["query_should_not_cache"]=>
 string(1) "0"
 ["query_not_cached"]=>
 string(1) "0"
 ["query_could_cache"]=>
 string(1) "0"
 ["query_found_in_cache"]=>
 string(1) "0"
 ["query_uncached_other"]=>
 string(1) "0"
 ["query_uncached_no_table"]=>
 string(1) "0"
 ["query_uncached_no_result"]=>
 string(1) "0"
 ["query_uncached_use_result"]=>
 string(1) "0"
 ["query_aggr_run_time_cache_hit"]=>
 string(1) "0"
 ["query_aggr_run_time_cache_put"]=>
 string(1) "0"
 ["query_aggr_run_time_total"]=>
 string(1) "0"
 ["query_aggr_store_time_cache_hit"]=>
 string(1) "0"
 ["query_aggr_store_time_cache_put"]=>
 string(1) "0"
 ["query_aggr_store_time_total"]=>
 string(1) "0"
 ["receive_bytes_recorded"]=>
 string(1) "0"
 ["receive_bytes_replayed"]=>
 string(1) "0"
 ["send_bytes_recorded"]=>
 string(1) "0"
 ["send_bytes_replayed"]=>
 string(1) "0"
 ["slam_stale_refresh"]=>
 string(1) "0"
 ["slam_stale_hit"]=>
 string(1) "0"
 ["request_counter"]=>
 int(1)
 ["process_hash"]=>
 int(3957718347)
}

[edit] mysqlnd_qc_clear_cache() 

bool mysqlnd_qc_clear_cache()

Flush all cache contents.

Returns TRUE on success or FALSE on FAILURE.


[edit] Runtime configuration

The following php.ini settings are available:

[edit] Changes

[edit] 1.0.0-prototype

Initial public release.

Retrieved from "http://forge.mysql.com/wiki/MySQLnd_Query_Cache_Plugin_for_PHP"

This page has been accessed 14,140 times. This page was last modified 20:12, 22 October 2010.

Find

Browse
MySQLForge
Main Page
Current events
Recent changes
Random page
Help
Edit
Edit this page
Editing help
This page
Discuss this page
Post a comment
Printable version
Context
Page history
What links here
Related changes
My pages
Special pages
New pages
File list
Statistics
Bug reports
More...