Re: Help tuning postgres

On Thu, Oct 13, 2005 at 03:14:44PM +0200, Csaba Nagy wrote:
> In any case, I suppose that those disk pages should be in OS cache
> pretty soon and stay there, so I still don't understand why the disk
> usage is 100% in this case (with very low CPU activity, the CPUs are
> mostly waiting/idle)... the amount of actively used data is not that
> big.

Ah, but if the sum of all the dead rows is large enough that they
start causing your shared memory (== Postgres buffers) to thrash,
then you start causing the memory subsystem to thrash on the box,
which means less RAM is available for disk buffers because the OS is
doing more work; and the disk buffers are full of a lot of garbage
_anyway_, so then you may find that you're ending up hitting the disk
for some of these reads after all. Around the office I have called
this the "buffer death spiral". And note that once you've managed to
get into a vacuum-starvation case, your free space map might be
exceeded, at which point your database performance really won't
recover until you've done VACUUM FULL (prior to 7.4 there's also an
index problem that's even worse, and that needs occasional REINDEX to
solve; I forget which version you said you were using).

The painful part about tuning a production system is really that you
have to keep about 50 variables juggling in your head, just so you
can uncover the one thing that you have to put your finger on to make
it all play nice.

A

--
Andrew Sullivan | ajs(at)crankycanuck(dot)ca
A certain description of men are for getting out of debt, yet are
against all taxes for raising money to pay it off.
--Alexander Hamilton