Date: Wed, 7 Dec 2005 14:04:34 -0600
From: Bruno Wolff III <bruno@wolff.to>
To: Assaf Yaari <assafy@mobixell.com>
Cc: Jan Wieck <JanWieck@Yahoo.com>, pgsql-performance@postgresql.org
Subject: Re: Performance degradation after successive UPDATE's
Message-ID: <20051207200434.GA6624@wolff.to>
Mail-Followup-To: Bruno Wolff III <bruno@wolff.to>,
	Assaf Yaari <assafy@mobixell.com>, Jan Wieck <JanWieck@Yahoo.com>,
	pgsql-performance@postgresql.org
References: <A3F53DEA945DA44386457F03BA78465F9D12B4@mobiexc.mobixell.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <A3F53DEA945DA44386457F03BA78465F9D12B4@mobiexc.mobixell.com>
User-Agent: Mutt/1.4.2.1i

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