From: Pandurangan R S <pandurangan.r.s@gmail.com>
To: Assaf Yaari <assafy@mobixell.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Performance degradation after successive UPDATE's
Date: Tue, 6 Dec 2005 15:22:01 +0530
Message-ID: <5e744e3d0512060152s14eeb0abg99b3c0c6df5174b5@mail.gmail.com> (raw)
In-Reply-To: <A3F53DEA945DA44386457F03BA78465F9D12AB@mobiexc.mobixell.com>
References: <A3F53DEA945DA44386457F03BA78465F9D12AB@mobiexc.mobixell.com>
Hi,
You might try these steps
1. Do a vacuum full analyze
2. Reindex the index on id column
3. Cluster the table based on this index
On 12/5/05, 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.
>
> I succeeded to re-produce this with a simple test:
>
> I created a very simple table that looks like that:
> CREATE TABLE test1
> (
> id int8 NOT NULL,
> counter int8 NOT NULL DEFAULT 0,
> CONSTRAINT "Test1_pkey" PRIMARY KEY (id)
> ) ;
>
> I've inserted 15 entries and wrote a script that increase the counter of
> specific record over and over. The SQL command looks like this:
> UPDATE test1 SET counter=number WHERE id=10;
>
> At the beginning the UPDATE time was around 15ms. After ~90000 updates, the
> execution time increased to be more than 120ms.
>
> 1. What is the reason for this phenomena?
> 2. Is there anything that can be done in order to improve this?
>
> Thanks,
> Assaf
--
Regards
Pandu
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 from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-performance@postgresql.org
Cc: pandurangan.r.s@gmail.com, assafy@mobixell.com
Subject: Re: Performance degradation after successive UPDATE's
In-Reply-To: <5e744e3d0512060152s14eeb0abg99b3c0c6df5174b5@mail.gmail.com>
References: <A3F53DEA945DA44386457F03BA78465F9D12AB@mobiexc.mobixell.com> <5e744e3d0512060152s14eeb0abg99b3c0c6df5174b5@mail.gmail.com>
* 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