Обсуждение: 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)
) ;
(
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@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.
Thanks Bruno, Issuing VACUUM FULL seems not to have influence on the time. I've added to my script VACUUM ANALYZE every 100 UPDATE's and run the test again (on different record) and the time still increase. Any other ideas? Thanks, Assaf. > -----Original Message----- > From: Bruno Wolff III [mailto:bruno@wolff.to] > Sent: Monday, December 05, 2005 10:36 PM > To: Assaf Yaari > Cc: pgsql-performance@postgresql.org > Subject: Re: Performance degradation after successive UPDATE's > > 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. >
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
On 12/6/2005 4:08 AM, Assaf Yaari wrote:
> Thanks Bruno,
>
> Issuing VACUUM FULL seems not to have influence on the time.
> I've added to my script VACUUM ANALYZE every 100 UPDATE's and run the
> test again (on different record) and the time still increase.
I think he meant
- run VACUUM FULL once,
- adjust FSM settings to database size and turnover ratio
- run VACUUM ANALYZE more frequent from there on.
Jan
>
> Any other ideas?
>
> Thanks,
> Assaf.
>
>> -----Original Message-----
>> From: Bruno Wolff III [mailto:bruno@wolff.to]
>> Sent: Monday, December 05, 2005 10:36 PM
>> To: Assaf Yaari
>> Cc: pgsql-performance@postgresql.org
>> Subject: Re: Performance degradation after successive UPDATE's
>>
>> 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.
>>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
> choose an index scan if your joining column's datatypes do not
> match
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #
On Tue, Dec 06, 2005 at 11:08:07 +0200, Assaf Yaari <assafy@mobixell.com> wrote: > Thanks Bruno, > > Issuing VACUUM FULL seems not to have influence on the time. That was just to get the table size back down to something reasonable. > I've added to my script VACUUM ANALYZE every 100 UPDATE's and run the > test again (on different record) and the time still increase. Vacuuming every 100 updates should put an upperbound on how slow things get. I doubt you need to analyze every 100 updates, but that doesn't cost much more on top of a vacuum. However, if there is another transaction open while you are doing the updates, that would prevent clearing out the deleted rows, since they are potentially visible to it. This is something you want to rule out. > Any other ideas? Do you have any triggers on this table? Are you updating any other tables at the same time? In particular ones that are referred to by the problem table.
Hi Jan, As I'm novice with PostgreSQL, can you elaborate the term FSM and settings recommendations? BTW: I'm issuing VACUUM ANALYZE every 15 minutes (using cron) and also changes the setting of fsync to false in postgresql.conf but still time seems to be growing. Also no other transactions are open. Thanks, Assaf. > -----Original Message----- > From: Jan Wieck [mailto:JanWieck@Yahoo.com] > Sent: Tuesday, December 06, 2005 2:35 PM > To: Assaf Yaari > Cc: Bruno Wolff III; pgsql-performance@postgresql.org > Subject: Re: [PERFORM] Performance degradation after > successive UPDATE's > > On 12/6/2005 4:08 AM, Assaf Yaari wrote: > > Thanks Bruno, > > > > Issuing VACUUM FULL seems not to have influence on the time. > > I've added to my script VACUUM ANALYZE every 100 UPDATE's > and run the > > test again (on different record) and the time still increase. > > I think he meant > > - run VACUUM FULL once, > - adjust FSM settings to database size and turnover ratio > - run VACUUM ANALYZE more frequent from there on. > > > Jan > > > > > Any other ideas? > > > > Thanks, > > Assaf. > > > >> -----Original Message----- > >> From: Bruno Wolff III [mailto:bruno@wolff.to] > >> Sent: Monday, December 05, 2005 10:36 PM > >> To: Assaf Yaari > >> Cc: pgsql-performance@postgresql.org > >> Subject: Re: Performance degradation after successive UPDATE's > >> > >> 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. > >> > > > > ---------------------------(end of > > broadcast)--------------------------- > > TIP 9: In versions below 8.0, the planner will ignore your desire to > > choose an index scan if your joining column's > datatypes do not > > match > > > -- > #============================================================= > =========# > # It's easier to get forgiveness for being wrong than for > being right. # > # Let's break this rule - forgive me. > # > #================================================== > JanWieck@Yahoo.com # >
On Wed, Dec 07, 2005 at 14:14:31 +0200, Assaf Yaari <assafy@mobixell.com> wrote: > Hi Jan, > > As I'm novice with PostgreSQL, can you elaborate the term FSM and > settings recommendations? http://developer.postgresql.org/docs/postgres/runtime-config-resource.html#RUNTIME-CONFIG-RESOURCE-FSM > BTW: I'm issuing VACUUM ANALYZE every 15 minutes (using cron) and also > changes the setting of fsync to false in postgresql.conf but still time > seems to be growing. You generally don't want fsync set to false. > Also no other transactions are open. Have you given us explain analyse samples yet? > > Thanks, > Assaf. > > > -----Original Message----- > > From: Jan Wieck [mailto:JanWieck@Yahoo.com] > > Sent: Tuesday, December 06, 2005 2:35 PM > > To: Assaf Yaari > > Cc: Bruno Wolff III; pgsql-performance@postgresql.org > > Subject: Re: [PERFORM] Performance degradation after > > successive UPDATE's > > > > On 12/6/2005 4:08 AM, Assaf Yaari wrote: > > > Thanks Bruno, > > > > > > Issuing VACUUM FULL seems not to have influence on the time. > > > I've added to my script VACUUM ANALYZE every 100 UPDATE's > > and run the > > > test again (on different record) and the time still increase. > > > > I think he meant > > > > - run VACUUM FULL once, > > - adjust FSM settings to database size and turnover ratio > > - run VACUUM ANALYZE more frequent from there on. > > > > > > Jan > > > > > > > > Any other ideas? > > > > > > Thanks, > > > Assaf. > > > > > >> -----Original Message----- > > >> From: Bruno Wolff III [mailto:bruno@wolff.to] > > >> Sent: Monday, December 05, 2005 10:36 PM > > >> To: Assaf Yaari > > >> Cc: pgsql-performance@postgresql.org > > >> Subject: Re: Performance degradation after successive UPDATE's > > >> > > >> 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. > > >> > > > > > > ---------------------------(end of > > > broadcast)--------------------------- > > > TIP 9: In versions below 8.0, the planner will ignore your desire to > > > choose an index scan if your joining column's > > datatypes do not > > > match > > > > > > -- > > #============================================================= > > =========# > > # It's easier to get forgiveness for being wrong than for > > being right. # > > # Let's break this rule - forgive me. > > # > > #================================================== > > JanWieck@Yahoo.com # > >
I hope that this will demonstrate the problem and will give the needed
information (global_content_id=90 is the record that was all the time
updated):
V-Mark=# UPDATE active_content_t SET ac_counter_mm4_outbound=100 WHERE
global_content_id=90;
UPDATE 1
Time: 396.089 ms
V-Mark=# UPDATE active_content_t SET ac_counter_mm4_outbound=100 WHERE
global_content_id=80;
UPDATE 1
Time: 1.320 ms
V-Mark=# EXPLAIN UPDATE active_content_t SET
ac_counter_mm4_outbound=100 WHERE global_content_id=90;
QUERY PLAN
------------------------------------------------------------------------
------------------------
Index Scan using active_content_t_pkey on active_content_t
(cost=0.00..5.50 rows=1 width=236)
Index Cond: (global_content_id = 90)
(2 rows)
Time: 9.092 ms
V-Mark=# EXPLAIN UPDATE active_content_t SET
ac_counter_mm4_outbound=100 WHERE global_content_id=80;
QUERY PLAN
------------------------------------------------------------------------
------------------------
Index Scan using active_content_t_pkey on active_content_t
(cost=0.00..5.50 rows=1 width=236)
Index Cond: (global_content_id = 80)
(2 rows)
Time: 0.666 ms
> -----Original Message-----
> From: Bruno Wolff III [mailto:bruno@wolff.to]
> Sent: Wednesday, December 07, 2005 10:05 PM
> To: Assaf Yaari
> Cc: Jan Wieck; pgsql-performance@postgresql.org
> Subject: Re: [PERFORM] Performance degradation after
> successive UPDATE's
>
> On Wed, Dec 07, 2005 at 14:14:31 +0200,
> Assaf Yaari <assafy@mobixell.com> wrote:
> > Hi Jan,
> >
> > As I'm novice with PostgreSQL, can you elaborate the term FSM and
> > settings recommendations?
> http://developer.postgresql.org/docs/postgres/runtime-config-r
> esource.html#RUNTIME-CONFIG-RESOURCE-FSM
>
> > BTW: I'm issuing VACUUM ANALYZE every 15 minutes (using
> cron) and also
> > changes the setting of fsync to false in postgresql.conf but still
> > time seems to be growing.
>
> You generally don't want fsync set to false.
>
> > Also no other transactions are open.
>
> Have you given us explain analyse samples yet?
>
> >
> > Thanks,
> > Assaf.
> >
> > > -----Original Message-----
> > > From: Jan Wieck [mailto:JanWieck@Yahoo.com]
> > > Sent: Tuesday, December 06, 2005 2:35 PM
> > > To: Assaf Yaari
> > > Cc: Bruno Wolff III; pgsql-performance@postgresql.org
> > > Subject: Re: [PERFORM] Performance degradation after successive
> > > UPDATE's
> > >
> > > On 12/6/2005 4:08 AM, Assaf Yaari wrote:
> > > > Thanks Bruno,
> > > >
> > > > Issuing VACUUM FULL seems not to have influence on the time.
> > > > I've added to my script VACUUM ANALYZE every 100 UPDATE's
> > > and run the
> > > > test again (on different record) and the time still increase.
> > >
> > > I think he meant
> > >
> > > - run VACUUM FULL once,
> > > - adjust FSM settings to database size and turnover ratio
> > > - run VACUUM ANALYZE more frequent from there on.
> > >
> > >
> > > Jan
> > >
> > > >
> > > > Any other ideas?
> > > >
> > > > Thanks,
> > > > Assaf.
> > > >
> > > >> -----Original Message-----
> > > >> From: Bruno Wolff III [mailto:bruno@wolff.to]
> > > >> Sent: Monday, December 05, 2005 10:36 PM
> > > >> To: Assaf Yaari
> > > >> Cc: pgsql-performance@postgresql.org
> > > >> Subject: Re: Performance degradation after successive UPDATE's
> > > >>
> > > >> 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.
> > > >>
> > > >
> > > > ---------------------------(end of
> > > > broadcast)---------------------------
> > > > TIP 9: In versions below 8.0, the planner will ignore
> your desire to
> > > > choose an index scan if your joining column's
> > > datatypes do not
> > > > match
> > >
> > >
> > > --
> > > #=============================================================
> > > =========#
> > > # It's easier to get forgiveness for being wrong than for being
> > > right. #
> > > # Let's break this rule - forgive me.
> > > #
> > > #==================================================
> > > JanWieck@Yahoo.com #
> > >
>