Re: Query meltdown: caching results

Gordon wrote:
> I need to find a way of not running queries that I don't need to,
> either in the PHP script, or in the Postgres database. What I need is
> for a result set to be cached somewhere, either by Postgres or PHP, so
> when it sees the same query again in a given session it just returns
> the previously fetched result set. The cache also needs to be able to
> disregard its cached result sets when an event that changes a table
> occurs (insert, update, delete, etc).

It's the second part that's fiddly (in the general case) if you do it in
PHP.

If you're looking for a large-scale cache then memcached would suit your
needs. There's an add-on for PG that can keep it notigi
http://pgfoundry.org/projects/pgmemcache/

> On the PHP side I've written a simple Database class that extends PDO
> and that I use in its place. It's a simple class that basically I use
> to allow me to nest calls to beginTransaction(), commit () and
> rollback () (It only starts an actual transaction of a counter is 0.
> Otherwide it just increments the counter. Commit only actually
> commits when the counter is 1, and decrements it otherwise. Rollback
> sets an error flag and decrements the counter, and only rolls back
> when the counter is 1. If the error flag is set then commit will
> actually roll back instead. )
>
> 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

There are a whole bunch of Pear classes for caching - Cache_Lite is
simple to plug into an existing structure.

> 2) get Postgres itself to cache the results of queries to avoid
> running them repeatedly during a session.
>
> I seem to remember MySQL providing some kind of results caching, can
> Postgres do the same? Has anyone else run into similar problems and
> how did they overcome them?

No, but if you're serious about the caching you'll want to do it well
above the data-access layer.

The main gains I've seen with a simple caching system have been:
1. Big, static lookup lists (countries, catalogue sections etc).
2. Whole pages / sections of pages
The trick with both is to cache as close to rendering as possible. So,
the HTML in the case of pages/controls.

Make sure your data-access layer invalidates any relevant cache entries
and you'll be fine (as long as you don't do any database manipulation
outside your app - always have an "invalidate whole cache" function /
script available for this).

Oh, and *do* make sure you've identified real gains first. It's
distressing to spend two days optimising your caching only to realise
you've gained 2% because you've missed the real bottle-neck.

--
Richard Huxton
Archonet Ltd