Re: Postgresql Caching

On Mon, Oct 16, 2006 at 05:14:59AM +0930, Shane Ambler wrote:
> mark(at)mark(dot)mielke(dot)cc wrote:
> >On Mon, Oct 16, 2006 at 03:08:39AM +0930, Shane Ambler wrote:
> >>You could setup a table in memory to contain known popular data, you
> >>could also use this to create a temporary table in memory to speed up
> >>multiple intermediate calculations without touching disks.
> >I'm not sure what this would save. If the table is read-only, there
> >shouldn't be writes happening. If it's small, and frequently accessed,
> >it should fit in the buffer cache.
> Because it is frequently accessed doesn't mean that it is small - the
> main point is control over what is cached and a starting point for other
> options mentioned later.

This presumes that your instructions will do a better job than it is
already doing. You are telling it "use this much cache memory". Only,
if you specify more than the amount of RAM you have, this will be
swapped to disk, and you won't have avoided a disk access. If you
specify less than the amount of RAM you have, you are preventing
PostgreSQL or the kernel from deciding that another page is more
valuable than your "large static table".

> >None of this avoids the cost of query planning, or query execution.
> No but you can avoid costly disk access and still have the postgres
> level of integrity and integration that memcached doesn't offer.

If PostgreSQL or the kernel is caching it, this is zero.

As a thought experiment, I'm not seeing the benefit. I think if you
could prove a benefit, then any proof you provided could be used to
improve the already existing caching layers, and would apply equally
to read-only or read-write pages. For example, why not be able to
hint to PostgreSQL that a disk-based table should be considered a
priority to keep in RAM. That way, PostgreSQL would avoid pushing
pages from this table out.

I'm not convinced that this would be a gain, though. I highly expect
that an LRU rule is better than a statically defined "keep this in
RAM" rule.

Cheers,
mark

--
mark(at)mielke(dot)cc / markm(at)ncf(dot)ca / markm(at)nortel(dot)com __________________________
. . _ ._ . . .__ . . ._. .__ . . . .__ | Neighbourhood Coder
|\/| |_| |_| |/ |_ |\/| | |_ | |/ |_ |
| | | | | \ | \ |__ . | | .|. |__ |__ | \ |__ | Ottawa, Ontario, Canada

One ring to rule them all, one ring to find them, one ring to bring them all
and in the darkness bind them...

http://mark.mielke.cc/