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.
> 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
--
Greg Smith 2ndQuadrant US Baltimore, MD
PostgreSQL Training, Services and Support
greg@2ndQuadrant.com www.2ndQuadrant.us