From: alison@mirrabooka.com
To: pgsql-performance@postgresql.org
Subject: Re: lots of updates on small table
Date: Fri, 15 Jul 2005 12:26:09 +1000
Message-ID: <42D71EC1.nail8BK11QC2Q@pluto.mirrabooka.com> (raw)
In-Reply-To: <5893.1121385447@sss.pgh.pa.us>
References: <42D5F34E.nail9QE1195IE@pluto.mirrabooka.com>
<20050714162114.GA19778@alvh.no-ip.org>
<42D6F854.nail3K81VIICH@pluto.mirrabooka.com>
<5893.1121385447@sss.pgh.pa.us>
Hi all,
> No wonder, considering that your "less than 10 rows" table contains
> something upwards of 100000 tuples:
>
> > INFO: --Relation public.plc_fldio--
> > INFO: Pages 1221: Changed 3, reaped 256, Empty 0, New 0; Tup 108137: Vac 4176, Keep/VTL 108133/108133, UnUsed 19, MinLen 84, MaxLen 84; Re-using: Free/Avail. Space 445176/371836; EndEmpty/Avail. Pages 0/256.
> > CPU 0.04s/0.14u sec elapsed 0.18 sec.
>
> What you need to do is find out why VACUUM is unable to reclaim all
> those dead row versions. The reason is likely that some process is
> sitting on a open transaction for days at a time.
>
Cheers mate, that was one of our theories but we weren't sure if it'd be
worth rebuilding everything to check. We've been compiling without the
-t (autocommit) flag to ecpg, and i believe what's happening is
sometimes a transaction is begun and then the processes cycle around
doing hardware i/o and never commit or only commit way too late. What
we're going to try now is remove all the begins and commits from the
code and compile with -t to make sure that any updates happen
immediately. Hopefully that'll avoid any hanging transactions.
We'll also set up a 10-minutely vacuum (not full) as per some other
suggestions here. I'll let you know how it goes - we'll probably slot
everything in on Monday so we have a week to follow it.
Thanks everyone
Alison
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-performance@postgresql.org
Cc: alison@mirrabooka.com
Subject: Re: lots of updates on small table
In-Reply-To: <42D71EC1.nail8BK11QC2Q@pluto.mirrabooka.com>
* 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