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

On Wed, Apr 11, 2007 at 12:41:23AM +0800, John Summerfield wrote:

> The Linux kernel's clearly doing a lot of work, and the disk supports

You might also be into context-switch hell.  What processor, which
kernel, and which Postgres version again?

> >>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.  
> 
> It should be in cache: it's all one transaction, and on the laptop,

It's not in cache if you're updating -- you have to write it.   

> there's over a Gbyte of RAM. Indeed, I would expect postgresql itself to
> cache it (except the fact it uses so little RAM suggests it doesn't do
> that at all).

What do you have configured as your shared buffers?  If you haven't
given very much, there won't be much in the way of buffers used, of
course.  Note that there's a much earlier diminishing return on the
size of shared buffers in Postgres than in many systems.

A

-- 
Andrew Sullivan  | ajs@crankycanuck.ca
The whole tendency of modern prose is away from concreteness.
		--George Orwell