Re: Starting PostgreSQL 8.0.4 with more memory [FreeBSD

On Mon, 2005-10-31 at 14:14 +0100, Martijn van Oosterhout wrote:
> On Mon, Oct 31, 2005 at 12:16:59PM +0000, Simon Riggs wrote:
> > > 8.0 can't go past 2Gb of shared memory, and there is really no reason
> > > to try because its performance will get worse not better with more than
> > > about 50000 shared buffers.
> >
> > Unless you turn off the bgwriter, in which case going higher can still
> > have benefit given the right circumstances.
>
> Is there any particular reason to turn that off?

Well yeh. If things work faster without it, then off it goes - or at
least parameter settings vastly altered.

> You want dirty pages
> written out. Doing them asyncronously beforehand means you don't have
> to wait for it at commit time. It also allows the OS to schedule the
> blocks into a better write order.

Only assuming you have a constant heavy write workload.

> > > 8.1 will relax the 2Gb limit, but it's still far from clear that there's
> > > any point in it. The conventional wisdom is that you should leave most
> > > of memory free for kernel disk cache, not try to eat it all in shared
> > > buffers. I haven't seen any evidence that that's changed in 8.1. It
> > > might possibly make sense to use several Gb of shared buffers in a
> > > machine with 16Gb or more of RAM, but not in one with only 4Gb RAM.
> >
> > I'm not sure we have any good tests of that either way, do we? I'm not
> > certain why we would trust OS cache any more than we could trust the
> > shared buffers. But setting it too high would probably overuse backend
> > memory for most variable query workloads.
>
> Well, it comes down to a thought experiment. Any disk blocks you have in
> the shared buffers will also be in the system cache.

Each have different and independent cache replacement...

> If you give 4GB to
> shared buffers, then there will be 4GB of data in the system cache which
> is not directly useful. So it seems shared buffers should be large
> enough to hold all the info PostgreSQL needs at any particular moment,
> anything else is just wasteful. Getting data out of the system cache is
> not terribly expensive, I timed it at 50 microseconds per page on my
> oldish laptop.
>
> Secondly, you're assuming that PostgreSQLs caching is at least as
> efficient as the OS caching, which is more of an assertion than
> anything else.

Do you doubt that? Why would shared_buffers be variable otherwise?

Best Regards, Simon Riggs