From: Jan Wieck <JanWieck@Yahoo.com>
To: Assaf Yaari <assafy@mobixell.com>
Cc: Bruno Wolff III <bruno@wolff.to>
Cc: pgsql-performance@postgresql.org
Subject: Re: Performance degradation after successive UPDATE's
Date: Tue, 06 Dec 2005 07:34:39 -0500
Message-ID: <4395855F.5050705@Yahoo.com> (raw)
In-Reply-To: <A3F53DEA945DA44386457F03BA78465F9D12AC@mobiexc.mobixell.com>
References: <A3F53DEA945DA44386457F03BA78465F9D12AC@mobiexc.mobixell.com>
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: JanWieck@Yahoo.com, assafy@mobixell.com, bruno@wolff.to
Subject: Re: Performance degradation after successive UPDATE's
In-Reply-To: <4395855F.5050705@Yahoo.com>
* 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