From: Bruno Wolff III <bruno@wolff.to>
To: Assaf Yaari <assafy@mobixell.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Performance degradation after successive UPDATE's
Date: Mon, 5 Dec 2005 14:36:04 -0600
Message-ID: <20051205203604.GA19356@wolff.to> (raw)
In-Reply-To: <A3F53DEA945DA44386457F03BA78465F9D12AB@mobiexc.mobixell.com>
References: <A3F53DEA945DA44386457F03BA78465F9D12AB@mobiexc.mobixell.com>
On Mon, Dec 05, 2005 at 19:05:01 +0200,
Assaf Yaari <assafy@mobixell.com> wrote:
> Hi,
>
> I'm using PostgreSQL 8.0.3 on Linux RedHat WS 3.0.
>
> My application updates counters in DB. I left a test over the night that
> increased counter of specific record. After night running (several
> hundreds of thousands updates), I found out that the time spent on
> UPDATE increased to be more than 1.5 second (at the beginning it was
> less than 10ms)! Issuing VACUUM ANALYZE and even reboot didn't seemed to
> solve the problem.
You need to be running vacuum more often to get rid of the deleted rows
(update is essentially insert + delete). Once you get too many, plain
vacuum won't be able to clean them up without raising the value you use for
FSM. By now the table is really bloated and you probably want to use
vacuum full on it.
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: bruno@wolff.to, assafy@mobixell.com
Subject: Re: Performance degradation after successive UPDATE's
In-Reply-To: <20051205203604.GA19356@wolff.to>
* 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