Re: eWeek Poll: Which database is most critical to your

If you were to expire the cache when the table was updated it wouldn't
be so bad, and you certainly don't want to cache everything under the
assumption something may come along -- as it probably won't. But if
you're interested in speeding this up I can't see any harm in adding a
stat into statistics that stores query frequency. If the frequency
goes above a certain notch (without the results changing -- static
table data) then cache results.

However, I think 99% of these cases could be easily rectified with a
php interface which caches the results into memory at the users
request as I really don't see hundreds of machines making the same
queries frequently -- rather 1 or 2 poorly written ones :)

Then again, I regularly hit one row database tables which act as
markers for data changes to see if the program should reload the
entire data set. (Parallel machines for interface purposes cache
table data in ram normally, but test for changes with every request --
goal was that they shouldn't have to communicate to eachother). But a
1 row table is very quick to select from. Can this make it faster, if
so it'll be useful.

Anyway, write it up. If you can speed up some without making a hit
against others it'll be a large advantage.
--
Rod Taylor

This message represents the official view of the voices in my head

----- Original Message -----
From: "Neil Conway" <nconway(at)klamath(dot)dyndns(dot)org>
To: "Dann Corbit" <DCorbit(at)connx(dot)com>
Cc: <pgsql-hackers(at)postgresql(dot)org>
Sent: Tuesday, February 26, 2002 6:42 PM
Subject: Re: [HACKERS] eWeek Poll: Which database is most critical to
your

> On Tue, 2002-02-26 at 18:20, Dann Corbit wrote:
> > I don't see how it will do any good. There is no "prepare" in
> > Postgresql
> > and therefore you will simply be reexecuting the queries every
time any
> > way. Also, parameter markers only work in embedded SQL and that
is a
> > single tasking system.
>
> Perhaps I wasn't clear. The feature I'm proposing is this:
>
> When processing SELECT queries but before any real work has been
> done, lookup the query in a hash table. If it already exists, return
the
> cached result. If it doesn't exist, execute the query and cache the
> result in the hash table. Optionally, we could not immediately cache
the
> query, just increment a "frequency" counter stored in the hash
table. If
> the counter goes above a certain constant, we decide that the query
is
> worth caching, so we cache the full result in the hash table.
>
> When processing INSERTs, UPDATEs and DELETEs, check if the query
> would affect any of the tables for which we are maintaing this
cache. If
> so, flush the cache. This ensures that we will never return invalid
> results. We could perhaps be fancy and keep stats on which columns
our
> cached queries utilize and which columns the modifying query will
> affect, but that is unlikely to be an overall win.
>
> HOWEVER -- I don't see this feature as something that will appeal
to,
> say, 75% of PgSQL users. If the table in question is being modified
on a
> regular basis, or if a wide variety of queries are being issued,
this
> cache isn't a good idea. Nevertheless, I think there are certainly
some
> situations in which this cache is useful -- and furthermore, these
kinds
> of "mostly read-only" situations are often where MySQL is chosen
over
> PostgreSQL.
>
> Anyway, just putting this on the table -- if the consensus is that
this
> isn't a very worthwhile feature, I won't bother with it.
>
> Cheers,
>
> Neil
>
> --
> Neil Conway <neilconway(at)rogers(dot)com>
> PGP Key ID: DB3C29FC
>
>
> ---------------------------(end of
broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>