Re: Performance Tuning Document?

Bruce Momjian wrote:
> Matthew Kirkwood wrote:
> > Hi,
> >
> > I'm playing with OSDB (http://osdb.sf.net/) and trying to get
> > the best numbers possible out of it.
> >
> > I haven't been able to find anything resembling a performance
> > tuning document. Does such a thing exist?
> >
> > Bruce's "Hardware performance tuning" guide mentions a bunch
> > of options but doesn't suggest what they should be set to. It
> > also implies that, simplistically stated, "bigger is better,
> > until it makes you swap", but that seems not to be always true:
> >
> > Under the "crossSectionTests(Mixed IR)" part of an OSDB run, a
> > large number of shared_buffers causes severe slowdown on one of
> > the tests -- it goes from a little over 200 seconds to nearly
> > 2000. I suspect internal lock contention, or maybe it's just
> > that the read() path in Linux is quicker than PG's own cache?
> >
> > Any tips and tricks available? Thus far, I have tried:
>
> Gererally, I think 1/4 RAM for shared buffers is a good start, and
> perhaps 2-4% for sort memory.
>

I have added this to my performance paper:

As a start for tuning, use 25% of ram for cache size, and 2-4% for sort
size. Increase if no swapping, and decrease to prevent swapping. Of
course, if the frequently accessed tables already fit in the cache,
continuing to increase the cache size no longer dramatically improves
performance.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026