| Re: PDO based Abstraction Layer for MySQL with Caching [message #180819 is a reply to message #180809] |
Wed, 20 March 2013 08:51  |
Axel Schwenke
Messages: 2
Registered: May 2011
Karma:
|
Junior Member |
|
|
The Natural Philosopher <tnp(at)invalid(dot)invalid> wrote:
> On 19/03/13 22:56, CPHR wrote:
>> I am curious if anyone here can recommend a good abstraction layer
>> for MySQL preferably PDO based. Something that has good caching
>> that's automatic, for example I run the query and it will check to
>> see if it has a cached result before attempting to do the query. I
>> have been using EZ SQL but the caching leaves a lot to be desired and
>> it uses the old mysql_* functions of PHP which are going to be
>> depreciated.
>
> mysql caches its results anyway.
> There is no need to have an abstraction layer to do that.
I strongly disagree. There are several reasons why caching in the
app layer can/will be more efficient than any cache in the database
(be it the query cache or whatever)
1. even if the result is cached in the database, the app needs a
network roundtrip to fetch it. Not with a local cache.
2. it's much easier to distribute the app to multiple machines than
to distribute the database. In turn that means that an app cache
grows with each new app server. The cache in the database does not.
3. caches in the database need to be 100% correct (the database must
never return outdated results). The app OTOH has intimate knowledge
of it's data and how old a result might be and still correct.
Thus an app cache can hold results much longer and/or can use more
efficient garbage collection.
Remark: the garbage collection (purging outdated results from the
cache) was/is *the* challenge in design and implementation of the
MySQL query cache. In order to be fast, it must be simple. Actually
the query cache works so that each time you modify some table, all
results related to that table (including JOINs) are purged. Hence an
innocent looking UPDATE foo SET bar=42 WHERE foo_id=4711 can cause
the query cache to remove hundreds to thousands of results. Including
memory management (you don't want to get fragmentation in the QC) etc.
This is not only expensive (the UPDATE takes longer), you also cannot
insert new results for table `foo` while the purge is running
(locking conflict).
A simple age based cache invalidation strategy (like memcached) has
none of those problems. Results are purged at about the same rate
than inserted and strictly in the same order. You don't need a global
mutex (which will become a hotspot) because insertion and purging is
done on opposite ends of the storage queue.
> This is all done at Mysql level: the PHP mysql libraries are only shims
> to allow PHP to call into the daemon.
>
> old or new, they do the same basic calls and ALL the time is in the
> mysqld daemon on anything beyond a short select into a small table.
While this is mostly true, I'd like to add two points regarding the
(long overdue IMHO) deprecation of the 'mysql' PHP extension.
1. 'mysql' cannot make use of server side prepared statements.
This is very bad because those are not only faster, they also give
a basic protection against SQL injection.
2. 'mysql' is not maintained for years now. It's old and crammy code
with who-knows-many bugs and limitations.
XL
|
|
|
|
Calendar
Search
Help
Members
Register
Login
Home
]