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)
) ;
(
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
