Re: shared_buffers documentation

On Wed, Apr 14, 2010 at 4:18 PM, Greg Smith <greg(at)2ndquadrant(dot)com> wrote:
> Kevin Grittner wrote:
>> I wonder if we should add any hints telling people
>> what they might see as problems if they are too far one way or the
>> other.  (Or does that go beyond the scope of what makes sense in TFM?)
>
> It's hard to figure that out.  One of the talks I'm doing at PGCon next
> month is focusing on how to monitor things when increasing shared_buffers
> and the related checkpoint parameters, so that you don't make things worse.
>  It's going to take a solid 45 minutes to cover that, and a section of the
> manual covering this bit of trivial would be a few pages long and hard to
> follow.  Maybe I'll get that in shape to insert into TFM eventually, but
> it's a bit bleeding edge to put into there now.  Trying to explain it live
> to other people a couple of times should make it clearer how to describe
> what I do.
>
> As for updating the size recommendations, the text at
> http://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server has been
> beaten into the status quo by a number of people.

I've incorporated most of this text, with some further editing, into
the documentation. I'm halfway tempted to backpatch it to 8.4 and
maybe even 8.3, but have refrained from so doing for now.

From reading this and other threads, I think I generally understand
that the perils of setting shared_buffers too high: memory is needed
for other things, like work_mem, a problem which is exacerbated by the
fact that there is some double buffering going on. Also, if the
buffer cache gets too large, checkpoints can involve writing out
enormous amounts of dirty data, which can be bad.

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

...Robert