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

Hi,

I haven't heard any negative comments on the proposed extension.

I assume people are fine, if I add the query cache plugin for mysqlnd to 
PECL during the next couple of days. If not, please raise your voice!

Below is a "fictive" FAQ based on feedback we got so gar. Please have a 
look before you vote with -1 for the proposed package.

Thanks,
Ulf



Q: *Can you add support for (native) Prepared Statements?*
A: Yes, it may be possible for 1.0.1.

The proposed 1.0.0-prototype code is rather stable and well tested. New 
major features may have a negative impact on stability.

We need to have a public starting point in order to be able to discuss 
new ideas in public, such as Prepared Statement support.

Andrey has prototyped some ideas how to cache results from prepared 
statements. However, prepared statements use 2 phases: prepare and 
execute. So far, the cache has been designed for one phase: execute. 
That's quite a major difference!

Let's get 1.0.0 out and create a 1.0.1 playground to restructure the 
code for Prepared Statement support.



Q: *Can you add support for unbuffered queries?*
A: Yes, it may be possible. It depends on Prepared Statement support.

Prepared Statements are unbuffered by default. If we want to support 
Prepared Statements properly - in 1.0.1 or later - we have to add 
support for unbuffered queries. Likely this will also hint how to handle 
unbuffered non-prepared result sets.



Q: *Can I disable caching functionality on a per-connection basis?*
A: Not yet, but such a feature could be added. Nice idea!

Currently you can enable/disable caching on a per-connection basis using 
SQL hints (/*qc=on*/, /*qc=off*/) and globally by enabling/disabling the 
query cache plugin extension (mysqlnd_qc.enable_qc=0/1).

You may want to disable caching on a per-connection basis if, for 
example, you use one connection to run more or less (un-)filtered SQL 
statements based on user input and you want to prevent cache attacks 
without having to change the global enable/disable flag all the time.

Mysqlnd plugins can associate arbitrary data - e.g. a cache on/off flag 
- with a connection, see also the C mysqlnd plugin API "docs" at [1]. It 
is not a big deal to add the requested feature to the C code. Question 
is how to expose it do the user space without(!) touching the existing 
PHP MySQL APIs.

A possibility would be a new function mysqlnd_qc_set_caching(link 
mysql/mysqli/PDO_MySQL, bool on_off).



Q: *Why should I use the plugin instead of PHP based caching?*
A: It is integrated, easy to use and likely faster.

Of course you can do almost anything what the plugin does in user space! 
  Of course, there is no big difference between adding a SQL hint or 
adding some apc_store() calls to persist arrays with results.

But you can't do this with PHP based caching: want to cache all queries 
- set mysqlnd_qc.cache_by_default=1, that's it! You can't do PHP based 
caching with the performance of C. And, you can't do user space caching 
with only one data serialization.

With PHP based caching you always have two serializsations eating CPU time:

   Within MySQL: main memory result set -> network representation
   Within PHP  : network representation -> PHP variable (serialisation 1)

   Cache Miss  : PHP variable -> Cache representation
   Cache Hit   : Cache representation -> PHP variable (serialisation 2)

With the proposed plugin you get:

   Within MySQL: main memory result set -> network representation
   Within PHP  : network representation -> PHP variable (serialisation 1)
   Cache Miss  : network representation -> Cache
   Cache Hit   : network representation -> PHP variable (see above)



Q: *Why shall I cache query results? I do cache HTML-snippets already.*
A: You shall not - continue walking on the "right" track!

If you already have a caching solution in place, the proposed extension 
is not for you. Same story, if you are clever and you have designed your 
application to cache data computed from database results, such as 
HTML-snippets.

However, in most other cases, give it a try, if your database is a 
bottle-neck.



[1] http://blog.ulf-wendel.de/mysqlnd_plugin_ipc2010.html