On Tue, 1 Jul 2003, Michael Mattox wrote:
> My understanding is to use as much effect cache as possible, so figure out
> how much ram you need for your other applications & OS and then give the
> rest to postgres as effective cache.
>
> What I learned to day is the shared_buffers 25% of RAM guideline.
Note that the best guideline is the one that your testing shows you makes
the most sense. If you never access more than a few megs at a time, then
there's no need to have 25% of a machine with 1 gig given over to the
database's shared buffers, it's better to let the machine cache that for
you. If you access hundreds of megs at a time, then 25% of RAM is a good
idea. Usually 25% of RAM is about the max that gives good results, but in
some corner cases, using more still makes sense. Usually at that point,
you've also increased sort_mem up a bit too, but be careful, sort_mem is
PER SORT, not per backend or per database cluster, so it can add up very
quickly and make the machine run out of RAM.
Setting these settings is a lot like playing Jenga (the game with the
wooden blocks stacked up where you pull one out and put them on top one at
a time.) Everything seems just fine, the machine's getting faster and
faster, everybody's loving life, then you crank one up a little too high,
cause a swap storm, and the whole thing slows to a crawl.