From: Bruno Wolff III <bruno@wolff.to>
To: Assaf Yaari <assafy@mobixell.com>
Cc: Jan Wieck <JanWieck@Yahoo.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Performance degradation after successive UPDATE's
Date: Wed, 7 Dec 2005 14:04:34 -0600
Message-ID: <20051207200434.GA6624@wolff.to> (raw)
In-Reply-To: <A3F53DEA945DA44386457F03BA78465F9D12B4@mobiexc.mobixell.com>
References: <A3F53DEA945DA44386457F03BA78465F9D12B4@mobiexc.mobixell.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-F...> 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 #
> >
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: bruno@wolff.to, assafy@mobixell.com, JanWieck@Yahoo.com
Subject: Re: Performance degradation after successive UPDATE's
In-Reply-To: <20051207200434.GA6624@wolff.to>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox