Re: Database Caching

Marc G. Fournier wrote:
> On Fri, 1 Mar 2002, Jan Wieck wrote:
>
> > Tom Lane wrote:
> > > "Greg Sabino Mullane" <greg(at)turnstep(dot)com> writes:
> > > > III. Relation caching
> > >
> > > > The final cache is the relation itself, and simply involves putting the entire
> > > > relation into memory. This cache has a field for the name of the relation,
> > > > the table info itself, the type (indexes should ideally be cached more than
> > > > tables, for example), the access time, and the acccess number. Loading could
> > > > be done automatically, but most likely should be done according to a flag
> > > > on the table itself or as an explicit command by the user.
> > >
> > > This would be a complete waste of time; the buffer cache (both Postgres'
> > > own, and the kernel's disk cache) serves the purpose already.
> > >
> > > As I've commented before, I have deep misgivings about the idea of a
> > > query-result cache, too.
> >
> > I wonder how this sort of query result caching could work in
> > our MVCC and visibility world at all. Multiple concurrent
> > running transactions see different snapshots of the table,
> > hence different result sets for exactly one and the same
> > querystring at the same time ... er ... yeah, one cache set
> > per query/snapshot combo, great!
> >
> > To really gain some speed with this sort of query cache, we'd
> > have to adopt the #1 MySQL design rule "speed over precision"
> > and ignore MVCC for query-cached relations, or what?
>
> Actually, you are missing, I think, as is everyone, the 'semi-static'
> database ... you know? the one where data gets dumped to it by a script
> every 5 minutes, but between dumps, there are hundreds of queries per
> second/minute between the updates that are the same query repeated each
> time ...
>
> As soon as there is *any* change to the data set, the query cache should
> be marked dirty and reloaded ... mark it dirty on any update, delete or
> insert ...
>
> So, if I have 1000 *pure* SELECTs, the cache is fine ... as soon as one
> U/I/D pops up, its invalidated ...

But in that case, why not caching the entire HTML result for
the URL or search request? That'd save some wasted cycles in
Tomcat as well.

Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck(at)Yahoo(dot)com #

_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com