At 12:35 PM 10/19/2006, Tobias Brox wrote:
[Jim C. Nasby - Thu at 10:28:31AM -0500]
> I think it'd be much better to experiment with using much larger
> shared_buffers settings. The conventional wisdom there is from 7.x days
> when you really didn't want a large buffer, but that doesn't really
> apply with the new buffer management we got in 8.0. I know of one site
> that doubled their performance by setting shared_buffers to 50% of
> memory.
I've upped it a bit, but it would require a server restart to get the
new setting into effect. This is relatively "expensive" for us. Does
anyone else share the viewpoint of Nasby, and does anyone have
recommendation for a good value? Our previous value was 200M, and I
don't want to go to the extremes just yet. We have 6G of memory
totally.
Jim is correct that traditional 7.x folklore regarding shared buffer
size is nowhere near as valid for 8.x. Jim tends to know what he is
talking about when speaking about pg operational issues.
Nonetheless, "YMMV". The only sure way to know what is best for your
SW running on your HW under your load conditions is to test, test, test.
A= Find out how much RAM your OS image needs.
Usually 1/3 to 2/3 of a GB is plenty.
B= Find out how much RAM pg tasks need during typical peak usage and
how much each of those tasks is using.
This will tell you what work_mem should be.
Note that you may well find out that you have not been using the best
size for work_mem for some tasks when you investigate this.
(Total RAM) - A - B - (small amount for error margin) = 1st pass at
shared_buffers setting.
If this results in better performance that your current settings,
either declare victory and stop or cut the number in half and see
what it does to performance.
Then you can either set it to what experiment thus far has shown to
be best or use binary search to change the size of shared_buffers and
do experiments to your heart's content.
Ron
