Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id F37209FB48A for ; Wed, 1 Nov 2006 15:34:29 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 63198-02-4 for ; Wed, 1 Nov 2006 15:34:17 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from mail.metronet.co.uk (mail.metronet.co.uk [213.162.97.75]) by postgresql.org (Postfix) with ESMTP id AEB3F9FB80B for ; Wed, 1 Nov 2006 13:13:54 -0400 (AST) Received: from mainbox.archonet.com (84-51-143-99.archon037.adsl.metronet.co.uk [84.51.143.99]) by smtp.metronet.co.uk (MetroNet Mail) with ESMTP id 86B9B7410C; Wed, 1 Nov 2006 17:13:49 +0000 (GMT) Received: from [192.168.1.17] (client17.office.archonet.com [192.168.1.17]) by mainbox.archonet.com (Postfix) with ESMTP id 40D2515EAB; Wed, 1 Nov 2006 17:13:49 +0000 (GMT) Message-ID: <4548D624.1070208@archonet.com> Date: Wed, 01 Nov 2006 17:15:16 +0000 From: Richard Huxton User-Agent: Thunderbird 1.5.0.7 (X11/20060918) MIME-Version: 1.0 To: Ben Cc: pgsql-performance@postgresql.org Subject: Re: big transaction slows down over time - but disk seems References: <45486959.60103@archonet.com> <4548CC84.9000605@archonet.com> In-Reply-To: Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200611/14 X-Sequence-Number: 21784 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