Re: Query meltdown: caching results

Gordon wrote:
> On Feb 26, 5:26 pm, st(dot)(dot)(dot)(at)enterprisedb(dot)com (Gregory Stark) wrote:
>
>> "Norman Peelman" <npeel(dot)(dot)(dot)(at)cfl(dot)rr(dot)com> writes:
>>
>>>> My options are, as far as I can tell,
>>>>
>>>> 1) replace the Database PDO extending class with something else that
>>>> provides query results caching in PHP, or
>>>> 2) get Postgres itself to cache the results of queries to avoid
>>>> running them repeatedly during a session.
>>>>
>> You might consider looking at memcached. One way to use it would be to have
>> the PHP application check for the cached object first and use it rather than
>> do any database queries. Then you can use pgmemcached to allow triggers to
>> invalidate cached objects whenever the underlying data changes. (Or you could
>> even just use pl/php to update or invalidate the cached object through the
>> same code library)
>>
>> --
>> Gregory Stark
>> EnterpriseDB http://www.enterprisedb.com
>> Ask me about EnterpriseDB's Slony Replication support!
>>
>> ---------------------------(end of broadcast)---------------------------
>> TIP 6: explain analyze is your friend
>>
> The problem is that there is a vast number of redundant queries being
> run. .... the principle that objects should not depend on a knowledge
> of the inner workings of unrelated objects. .... Results caching would eliminate the problem of
> the same queries beign run over and over ....
> The problem is the mechanics of actually implementing this caching.
> I'm using prepared statements almost exclusivly throughout the design,
> meaning that the PDOStatement class probably needs to be extended
> somehow and my Database prepare() .... I can't have been the first person to run up against this problem
>

With memcached, your methods to retrieve data go from "get data from db"
to "get data from cache, and on cache miss get from db and leave a copy
for the next guy in cache". Updating the data is not much more
complicated. I don't see why this doesn't work for you? It won't
compromise anything on the encapsulation front you are concerned about,
and you can still use your prepared statements for hitting the db, etc.?

Regards,
Paul