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 g...@2ndquadrant.com www.2ndQuadrant.us -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
