Re: shared_buffers documentation

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. Here's what might
make sense from there to insert into the docs, removing the bits
referring to older versions, rewriting a bit for manual tone, and noting
the checkpoint issues:

If you have a system with 1GB or more of RAM, a reasonable starting
value for shared_buffers on a dedicated database server is 25% of the
memory in your system. If you have less RAM, you'll have to account more
carefully for how much memory the operating system is taking up,
allocating a fraction of the free memory instead. There are some
workloads where even larger settings for shared_buffers are effective.
But given the way PostgreSQL also relies on the operating system cache,
it's unlikely you'll find using more than 40% of RAM to work better than
a smaller amount.

On Windows, large values for shared_buffers aren't as effective. You
may find better results keeping the setting relatively low and using the
OS cache more instead. The useful size range for shared_buffers on
Windows systems is generally from 64MB to 512MB of RAM.

Larger settings for shared_buffers usually require a corresponding
increase in checkpoint_segments, in order to spread out writing large
quantities of changed or new data in the cache over a longer period of time.

--
Greg Smith 2ndQuadrant US Baltimore, MD
PostgreSQL Training, Services and Support
greg(at)2ndQuadrant(dot)com www.2ndQuadrant.us