Re: Performance degradation after successive UPDATE's

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(at)Yahoo(dot)com]
> Sent: Tuesday, December 06, 2005 2:35 PM
> To: Assaf Yaari
> Cc: Bruno Wolff III; pgsql-performance(at)postgresql(dot)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(at)wolff(dot)to]
> >> Sent: Monday, December 05, 2005 10:36 PM
> >> To: Assaf Yaari
> >> Cc: pgsql-performance(at)postgresql(dot)org
> >> Subject: Re: Performance degradation after successive UPDATE's
> >>
> >> 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.
> >>
> >
> > ---------------------------(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(at)Yahoo(dot)com #
>