From: Richard Huxton <dev@archonet.com>
To: Ben <bench@silentmedia.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: big transaction slows down over time - but disk seems
Date: Wed, 01 Nov 2006 17:15:16 +0000
Message-ID: <4548D624.1070208@archonet.com> (raw)
In-Reply-To: <Pine.LNX.4.64.0611010847431.5452@GRD.cube42.tai.silentmedia.com>
References: <Pine.LNX.4.64.0610312141430.5452@GRD.cube42.tai.silentmedia.com>
<45486959.60103@archonet.com>
<A054DD90-B18E-430E-B3B0-A9DA5DA41B26@silentmedia.com>
<4548CC84.9000605@archonet.com>
<Pine.LNX.4.64.0611010847431.5452@GRD.cube42.tai.silentmedia.com>
Ben wrote:
>
>
> On Wed, 1 Nov 2006, Richard Huxton wrote:
>
>> 1. Avoid updating the same timestamp more than once (if that's happening)
>
> Each row is updated at most once, and not all rows are updated.
>
>> 2. Update timestamps in one go at the end of the transaction (perhaps
>> by loading updates into a temp table).
>
> Hey, that's not a bad idea. I'll give that a shot. Thanks!
>
>> 3. Split the transaction in smaller chunks of activity.
>
> I'd be happy to do this too, except that I need a simple way to rollback
> everything, and I don't see how I can get that with this.
Well, you could with a temp-table, but it probably won't be necessary if
you have one. You might wan to issue a vacuum on the updated table after
the transaction completes.
Note that this idea is built on a set of assumptions that might not be
true, so do test.
Oh - if you're processing rows one at a time with your stored procedure,
see if there's not a way to process the whole set. That can make a huge
difference.
--
Richard Huxton
Archonet Ltd
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: dev@archonet.com, bench@silentmedia.com
Subject: Re: big transaction slows down over time - but disk seems
In-Reply-To: <4548D624.1070208@archonet.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