Date: Fri, 15 Jul 2005 12:26:09 +1000
To: pgsql-performance@postgresql.org
Subject: Re: lots of updates on small table
Message-ID: <42D71EC1.nail8BK11QC2Q@pluto.mirrabooka.com>
References: <42D5F34E.nail9QE1195IE@pluto.mirrabooka.com>
	<20050714162114.GA19778@alvh.no-ip.org>
	<42D6F854.nail3K81VIICH@pluto.mirrabooka.com>
	<5893.1121385447@sss.pgh.pa.us>
In-Reply-To: <5893.1121385447@sss.pgh.pa.us>
User-Agent: nail 11.6 9/7/04
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
From: alison@mirrabooka.com (Alison Winters)

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