Re: Performance Tuning Document?

On Thu, 28 Mar 2002, Steve Wolfe wrote:

> > I haven't been able to find anything resembling a performance
> > tuning document. Does such a thing exist?
>
> Unfortunately, not in any complete sense.
>
> There are a few guides from Bruce that make a good effort, but there
> seems to be a *lot* of other information that can only be gleaned by
> either being a developper or following the list very closely for a few
> years. Bruce's hardware tuning guide also doesn't really give any
> sorts of guidelines or numbers to start from, it merely explains
> concepts and leaves the investigation and twiddling to you.

Yep. Guess I have a few busy evenings and weekends ahead...

> > 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?

> Huge, raging amounts of shared buffers do have the consequence of
> diminishing your disk cache size. You want to make sure that you can
> always keep the *entire* database in disk cache, or you end up taking
> a performance hit by having to read from disk, in the same spirit of
> keeping your machine from swapping.

I watched the run with "vmstat 1" and there was very close to
zero disk read activity (and no swapping). My test machine
has ~400Mb or RAM and the shared_buffers shouldn't have been
over 80Mb, so that makes sense to me.

I guess there's a linear list, a poor hash function, or some
heavy lock contention somewhere causing this.

Matthew.