Re: Performance degradation after successive UPDATE's

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

On Mon, Dec 05, 2005 at 19:05:01 +0200,
Assaf Yaari <assafy(at)mobixell(dot)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.

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(at)mobixell(dot)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