| PDO based Abstraction Layer for MySQL with Caching [message #180807] |
Tue, 19 March 2013 22:56  |
CPHR
Messages: 1
Registered: March 2013
Karma: 0
|
Junior Member |
|
|
|
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.
|
|
|
|
| Re: PDO based Abstraction Layer for MySQL with Caching [message #180808 is a reply to message #180807] |
Tue, 19 March 2013 23:32  |
M. Strobel
Messages: 386
Registered: December 2011
Karma: 0
|
Senior Member |
|
|
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.
|
|
|
|
| Re: PDO based Abstraction Layer for MySQL with Caching [message #180809 is a reply to message #180807] |
Tue, 19 March 2013 23:49 |
The Natural Philosoph
Messages: 993
Registered: September 2010
Karma: 0
|
Senior Member |
|
|
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.
|
|
|
|
| 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: 0
|
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
]