Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.lang.php > #8743 > unrolled thread

PDO based Abstraction Layer for MySQL with Caching

Back to article view | Back to comp.lang.php

Contents

  PDO based Abstraction Layer for MySQL with Caching CPHR <ryan@cloudpresshosting.com> - 2013-03-19 15:56 -0700
    Re: PDO based Abstraction Layer for MySQL with Caching "M. Strobel" <sorry_no_mail_here@nowhere.dee> - 2013-03-20 00:32 +0100
    Re: PDO based Abstraction Layer for MySQL with Caching The Natural Philosopher <tnp@invalid.invalid> - 2013-03-19 23:49 +0000
      Re: PDO based Abstraction Layer for MySQL with Caching Axel Schwenke <axel.schwenke@gmx.de> - 2013-03-20 09:51 +0100

#8743 — PDO based Abstraction Layer for MySQL with Caching

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.

[toc] | [next] | [standalone]

#8744

Am 19.03.2013 23:56, schrieb CPHR:
> 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.
> 

It might not help you much if all you want is a caching abstraction layer, but it
must be said:

Anybody in need of general database caching is probably doing something wrong:
selections are done in code, and joins are done in code, while they should be done in
the database.

This is sadly true for many frameworks as far as I can tell. The result are scaling
problems.

Your application specific abstraction is easily done with ~ 10 functions in a PDO
derived class.

And an application specific cache for objects is quite trivial, for example
$order->getCustomer() or $invoice->getDebitor() can easily look up the cache by
[$classname][$id], and give you one (1) object.

/Str.

[toc] | [prev] | [next] | [standalone]

#8745

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.

ITYM deprecated. Not depreciated :-)

There is no need to have an abstraction layer to do that.

One might suggest that you investigate how to improve mysql caching -
give it more memory for example.

In my /etc/mysql/my.cnf are these lines

query_cache_limit	= 1M
query_cache_size        = 16M

Those are highly adjustable. On a big system I would up the size to a GB 
or more, and the limit to 256MB. If that system is being worked hard 
with similar selects ..

See:

http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html#sysvar_query_cache_limit

In addition judicious indexing can massively improve (some) slow queries.

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.

When I was working on a complex join, the uncached select was over 4 
seconds, the cached was 30ms. With indexes added (occasional insert 
update, MANY reads via select)  the uncached was down to 250ms..

The actual query result wasn't large on that so it always cached OK


-- 
Ineptocracy

(in-ep-toc’-ra-cy) – a system of government where the least capable to
lead are elected by the least capable of producing, and where the
members of society least likely to sustain themselves or succeed, are
rewarded with goods and services paid for by the confiscated wealth of a
diminishing number of producers.

[toc] | [prev] | [next] | [standalone]

#8755

The Natural Philosopher <tnp@invalid.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

[toc] | [prev] | [standalone]

Back to top | Article view | comp.lang.php

csiph-web