Re: shared_buffers documentation

On Fri, Apr 16, 2010 at 7:24 PM, Greg Smith <greg(at)2ndquadrant(dot)com> wrote:
> Robert Haas wrote:
>> It seems intuitive to me that setting shared_buffers too small will
>> also cause a performance problem, especially for write-heavy
>> workloads, but I'm less sure I can clearly explain why.
>
> More text to add:
>
> When the server needs to allocate more space for reading or writing blocks,
> and the next available space available is a block that's been modified but
> not used recently, that block will be written out to the operating system.
>  With large settings for shared_buffers, that prefers evicting blocks that
> are used infrequently from the cache.  The main downside to tuning in that
> direction is that all recently modified blocks not already written must be
> flushed to disk during each checkpoint, which can cause large amounts of
> disk writes grouped together.  But if shared_buffers is set too low instead,
> and therefore only a portion of the active working set can be kept in the
> buffer cache at once, that can cause the same block to be written out more
> frequently than is optimal.

Well, why can't they just hang out as dirty buffers in the OS cache,
which is also designed to solve this problem?

>> And I'm curious why the correct setting is different on Windows than it is
>> on
>> other platforms.  Can anyone shed some light on this?
>>
>
> No one has ever come up with a good explanation for why this is other than
> "Windows doesn't seem to like large amounts of shared memory".  But we've
> seen it show up in too many benchmarks to dismiss.    Dave and Greg Stark
> did benchmarks focused on this:
>  http://archives.postgresql.org/pgsql-hackers/2008-12/msg00003.php that
> Magnus concurred with last time I tried to dig for more info about this
> specific subject.  And the last time I remember this caming up it was with
> someone who suggested 8MB (!) worked best on their Windows system:
>  http://archives.postgresql.org/pgsql-general/2009-12/msg00475.php

I guess the obvious question is whether Windows "doesn't need" more
shared memory than that, or whether it "can't effectively use" more
memory than that.

...Robert