Re: A long-running transaction

Andrew Sullivan wrote:
> 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?
on opensuse 10.2 (the laptop)
model name : Intel(R) Pentium(R) M processor 1500MHz
2.6.18.8-0.1-xen
Might not have been xen-enabled, I switch fairly often
summer(at)Lyrebird:~> rpm -qa postg\*
postgresql-server-8.1.5-13
postgresql-libs-8.1.5-13
postgresql-devel-8.1.5-13
postgresql-jdbc-8.1-12.2
postgresql-contrib-8.1.5-13
postgresql-pl-8.1.5-15
postgresql-8.1.5-13
summer(at)Lyrebird:~>

Also:
model name : AMD Sempron(tm) 2400+
2.6.17-6-server-xen0
summer(at)Bandicoot:~$ dpkg --list postg\* | grep ^ii
ii postgresql-8.2 8.2.3-1~edgy1 object-relational SQL
database, version 8.2

>
>>>> 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.

Linux caches writes, I don't think it should be hitting disk at all. The
table being updated contains records 7482 (658K raw data) of which
probably fewer than 2000 are being updated, and typically the same ones
all the time: we're updating the date of the latest trade.

>
>> 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.
Laptop (1.25 Gbytes)
shared_buffers = 1000 # min 16 or max_connections*2,
8KB each

AMD (512 Mbytes less some for video)
shared_buffers = 24MB # min 128kB or max_connections*16kB

Note we're only running one connexion here, and data transfers (I
believe) are tens of bytes: here is a typical data record:
2005-02-03,AAC,1.900,1.800,1.850,1.820,328984,0

>
> A
>