From: John Summerfield <postgres@herakles.homelinux.org>
To: pgsql-sql@postgresql.org
Subject: Re: A long-running transaction
Date: Wed, 11 Apr 2007 05:54:45 +0800
Message-ID: <461C07A5.30000@herakles.homelinux.org> (raw)
In-Reply-To: <20070410165523.GB20033@phlogiston.dyndns.org>
References: <461261AD.6060108@herakles.homelinux.org>
<20070403201420.GB1519@phlogiston.dyndns.org>
<461BBE33.9030807@herakles.homelinux.org>
<20070410165523.GB20033@phlogiston.dyndns.org>
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@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@Lyrebird:~>
Also:
model name : AMD Sempron(tm) 2400+
2.6.17-6-server-xen0
summer@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
>
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: <461C07A5.30000@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