Re: Performance degradation after successive UPDATE's

On Wed, Dec 07, 2005 at 14:14:31 +0200,
Assaf Yaari <assafy(at)mobixell(dot)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(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 #
> >