pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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



view thread (9+ messages)  latest in thread

Message-ID: <5e744e3d0512060152s14eeb0abg99b3c0c6df5174b5@mail.gmail.com>
Permalink:  ../5e744e3d0512060152s14eeb0abg99b3c0c6df5174b5@mail.gmail.com/
Also on:    postgresql.org/message-id/5e744e3d0512060152s14eeb0abg99b3c0c6df5174b5@mail.gmail.com

 · 

reply

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