Date: Fri, 15 Jul 2005 09:42:12 +1000
To: pgsql-performance@postgresql.org
Subject: Re: lots of updates on small table
Message-ID: <42D6F854.nail3K81VIICH@pluto.mirrabooka.com>
References: <42D5F34E.nail9QE1195IE@pluto.mirrabooka.com>
	<20050714162114.GA19778@alvh.no-ip.org>
In-Reply-To: <20050714162114.GA19778@alvh.no-ip.org>
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,

> > Our application requires a number of processes to select and update rows
> > from a very small (<10 rows) Postgres table on a regular and frequent
> > basis.  These processes often run for weeks at a time, but over the
> > space of a few days we find that updates start getting painfully slow.
> > We are running a full vacuum/analyze and reindex on the table every day,
> Full vacuum, eh?  I wonder if what you really need is very frequent
> non-full vacuum.  Say, once in 15 minutes (exact rate depending on dead
> tuple rate.)
>
Is there a difference between vacuum and vacuum full?  Currently we have
a cron job going every hour that does:

VACUUM FULL VERBOSE ANALYZE plc_fldio
REINDEX TABLE plc_fldio

The most recent output was this:

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.
INFO:  Index plcpage_idx: Pages 315; Tuples 108137: Deleted 4176.
        CPU 0.03s/0.04u sec elapsed 0.14 sec.
INFO:  Rel plc_fldio: Pages: 1221 --> 1221; Tuple(s) moved: 0.
        CPU 0.03s/0.04u sec elapsed 0.36 sec.
INFO:  Analyzing public.plc_fldio
VACUUM
REINDEX

We'll up it to every 15 minutes, but i don't know if that'll help
because even with the current vacuuming the updates are still getting
slower and slower over the course of several days.  What really puzzles
me is why restarting the processes fixes it.  Does PostgreSQL keep some
kind of backlog of transactions all for one database connection?  Isn't
it normal to have processes that keep a single database connection open
for days at a time?

Regarding the question another poster asked: all the transactions are
very short.  The table is essentially a database replacement for a
shared memory segment - it contains a few rows of byte values that are
constantly updated byte-at-a-time to communicate data between different
industrial control processes.

Thanks for the thoughts everyone,

Alison