From: | Robert Haas <robertmhaas(at)gmail(dot)com> |
---|---|
To: | Greg Smith <greg(at)2ndquadrant(dot)com> |
Cc: | Kevin Grittner <Kevin(dot)Grittner(at)wicourts(dot)gov>, pgsql-hackers(at)postgresql(dot)org |
Subject: | Re: shared_buffers documentation |
Date: | 2010-04-17 00:37:18 |
Message-ID: | r2j603c8f071004161737mfa16cc64l62b83f11dd8a927c@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Robert Haas | 2010-04-17 01:47:00 | Re: walreceiver is uninterruptible on win32 |
Previous Message | Greg Smith | 2010-04-16 23:24:49 | Re: shared_buffers documentation |