Re: shared_buffers documentation - Mailing list pgsql-hackers

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@2ndQuadrant.com   www.2ndQuadrant.us