Re: Caching (was Re: choosing the right platform)

From: Jean-Luc Lachance <jllachan(at)nsd(dot)ca>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: jim(at)nasby(dot)net, "scott(dot)marlowe" <scott(dot)marlowe(at)ihs(dot)com>, Matthew Nuzum <cobalt(at)bearfruit(dot)org>, "'Josh Berkus'" <josh(at)agliodbs(dot)com>, "'Pgsql-Performance'" <pgsql-performance(at)postgresql(dot)org>
Subject: Re: Caching (was Re: choosing the right platform)
Date: 2003-04-10 14:27:16
Message-ID: 3E957F44.A1106F6A@nsd.ca
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

Tom,

What appends when PG scans a table that is is too big to fit in the
cache?
Won't the whole cache get trashed and swapped off to disk?
Shouldn't there be a way to lock some tables in PG cache?
Who about caracterizing some of the RAM like: scan, index, small
frequently used tables.

JLL

Tom Lane wrote:
> [...]
> PG is *not* any smarter about the usage patterns of its disk buffers
> than the kernel is; it uses a simple LRU algorithm that is surely no
> brighter than what the kernel uses. (We have looked at smarter buffer
> recycling rules, but failed to see any performance improvement.) So the
> notion that PG can do a better job of cache management than the kernel
> is really illusory. About the only advantage you gain from having data
> directly in PG buffers rather than kernel buffers is saving the CPU
> effort needed to move data across the userspace boundary --- which is
> not zero, but it's sure a lot less than the time spent for actual I/O.
>
> So my take on it is that you want shared_buffers fairly small, and let
> the kernel do the bulk of the heavy lifting for disk cache. That's what
> it does for a living, so let it do what it does best. You only want
> shared_buffers big enough so you don't spend too many CPU cycles shoving
> data back and forth between PG buffers and kernel disk cache. The
> default shared_buffers setting of 64 is surely too small :-(, but my
> feeling is that values in the low thousands are enough to get past the
> knee of that curve in most cases.

In response to

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Tom Lane 2003-04-10 14:40:15 Re: Caching (was Re: choosing the right platform)
Previous Message Shridhar Daithankar 2003-04-10 09:59:05 Re: Caching (was Re: choosing the right platform)