From: Andrew Sullivan <ajs@crankycanuck.ca>
To: postgres performance list <pgsql-performance@postgresql.org>
Subject: Re: Help tuning postgres
Date: Thu, 13 Oct 2005 12:07:14 -0400
Message-ID: <20051013160714.GG16317@phlogiston.dyndns.org> (raw)
In-Reply-To: <1129209283.2995.226.camel@coppola.muc.ecircle.de>
References: <1129128315.2995.174.camel@coppola.muc.ecircle.de>
<f3c0b4080510120939x6c08feebvfad37c98df306538@mail.gmail.com>
<1129136130.2995.188.camel@coppola.muc.ecircle.de>
<20051012220036.GE13571@phlogiston.dyndns.org>
<1129191303.2995.192.camel@coppola.muc.ecircle.de>
<20051013124007.GA15592@phlogiston.dyndns.org>
<1129209283.2995.226.camel@coppola.muc.ecircle.de>
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@crankycanuck.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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: ajs@crankycanuck.ca
Subject: Re: Help tuning postgres
In-Reply-To: <20051013160714.GG16317@phlogiston.dyndns.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox