Re: Performance degradation after successive UPDATE's - Mailing list pgsql-performance

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 #
> > >
>