Re: Starting PostgreSQL 8.0.4 with more memory [FreeBSD

On Sun, 2005-10-30 at 23:08 -0500, Tom Lane wrote:
> Vlad <marchenko(at)gmail(dot)com> writes:
> > I'm looking for some help in regards to letting Posresql use more
> > memory.
>
> 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.

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

> BTW, where did you get the idea that it was sensible to set work_mem
> higher than maintenance_work_mem? That's just nuts.

Surely if you choose to favour query sort performance say over vacuum
performance that is a reasonable design choice in some specific
circumstances? Not the general case, agreed.

There are no assumptions in the code that work_mem is always smaller.
Tasks are assigned to use maintenance_work_mem when they are considered
to be "maintenance" tasks.

Best Regards, Simon Riggs