Re: Starting PostgreSQL 8.0.4 with more memory [FreeBSD

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? 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.

Anyway, the original writer didn't specify an architechure. If it is a
32bit one it is entirly possible that the memory map simply has no
large contiguous space to map the shared memory.

> > 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. 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.

Have a nice day,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.