On Fri, Apr 16, 2010 at 7:24 PM, Greg Smith <g...@2ndquadrant.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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
