From: John Summerfield <postgres@herakles.homelinux.org>
To: pgsql-sql@postgresql.org
Subject: Re: A long-running transaction
Date: Wed, 11 Apr 2007 00:41:23 +0800
Message-ID: <461BBE33.9030807@herakles.homelinux.org> (raw)
In-Reply-To: <20070403201420.GB1519@phlogiston.dyndns.org>
References: <461261AD.6060108@herakles.homelinux.org>
<20070403201420.GB1519@phlogiston.dyndns.org>
Andrew Sullivan wrote:
> 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:
For the first day or so, the disk light was switching off about half the
time.
>
>> IOwait: 2d 0:46:37.33 28.5% page dea: 16218135
>
> which is pretty awful. Also
It was some days in before it became so. Eventually, the machines (I
kept it running on the laptop because of the amount of RAM plus another)
did begin to thrash, but that certainly wasn't the case at first.
Note that the proportion of system time is fairly unusual:
user : 1d 3:17:04.03 16.0% page in : 131097310 disk 1:
3079516r20087664w
nice : 0:05:39.64 0.1% page out: 197016649
system: 2d 20:38:37.13 40.1% page act: 87906251
The Linux kernel's clearly doing a lot of work, and the disk supports
DMA and DMA is turned on, so it's not using A PIO mode. According to
hdparm, it's using udma5.
>
>> 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,
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).
>
> A
>
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-sql@postgresql.org
Cc: postgres@herakles.homelinux.org
Subject: Re: A long-running transaction
In-Reply-To: <461BBE33.9030807@herakles.homelinux.org>
* 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