DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=Yahoo.com;
	h=Received:Message-ID:Date:From:User-Agent:X-Accept-Language:MIME-Version:To:CC:Subject:References:In-Reply-To:Content-Type:Content-Transfer-Encoding;
	b=fXVVE0y1iBb3ag7/VpciRb/eSJggfxWiyLQJJatQRt2NXB8JHECaqzXrV21IC2SLKtBt89UvYk9ldd1e2+P3qW6fWP6fk4WO2JEzHrEtnkXg2nfcsA7WtuTBch+TqH2vamhAI8J+ePDWGic2EScWwg5y13jKkW3oDn25rkExoGE=
	;
Message-ID: <4395855F.5050705@Yahoo.com>
Date: Tue, 06 Dec 2005 07:34:39 -0500
From: Jan Wieck <JanWieck@Yahoo.com>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.0; en-US;
	rv:1.7.1) Gecko/20040707
MIME-Version: 1.0
To: Assaf Yaari <assafy@mobixell.com>
CC: Bruno Wolff III <bruno@wolff.to>, pgsql-performance@postgresql.org
Subject: Re: Performance degradation after successive UPDATE's
References: <A3F53DEA945DA44386457F03BA78465F9D12AC@mobiexc.mobixell.com>
In-Reply-To: <A3F53DEA945DA44386457F03BA78465F9D12AC@mobiexc.mobixell.com>
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit

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 #