Date: Tue, 3 Apr 2007 16:14:20 -0400
From: Andrew Sullivan <ajs@crankycanuck.ca>
To: pgsql-sql@postgresql.org
Subject: Re: A long-running transaction
Message-ID: <20070403201420.GB1519@phlogiston.dyndns.org>
References: <461261AD.6060108@herakles.homelinux.org>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <461261AD.6060108@herakles.homelinux.org>
User-Agent: Mutt/1.5.9i

On Tue, Apr 03, 2007 at 10:16:13PM +0800, John Summerfield wrote:
> It is hitting the disk pretty hard now on this machine, but the laptop's 
> still going too, and the disk seems to run about half the time, part of 
> a second running, part idle (but the intervals are getting shorter).
> 
> It struck me as fairly curious that neither postgresql nor the 
> application was hogging the CPU.

Why?  Nothing about this seems likely CPU bound.  It's probably I/O. 
I note is number:

> IOwait:   2d  0:46:37.33  28.5%  page dea:   16218135

which is pretty awful.  Also

> For each record, I update a non-key field in another table; the source 
> data for that other table is less than a megabyte.

this is a real issue.  Basically, you're constrained at the rotation
speed of your disk, because for each record, you have to first find
then update one row somewhere else.  

A

-- 
Andrew Sullivan  | ajs@crankycanuck.ca
The plural of anecdote is not data.
		--Roger Brinner