Re: A long-running transaction

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
>