From: Tom Lane <tgl@sss.pgh.pa.us>
To: alison@mirrabooka.com
Cc: pgsql-performance@postgresql.org
Subject: Re: lots of updates on small table
Date: Thu, 14 Jul 2005 19:57:27 -0400
Message-ID: <5893.1121385447@sss.pgh.pa.us> (raw)
In-Reply-To: <42D6F854.nail3K81VIICH@pluto.mirrabooka.com>
References: <42D5F34E.nail9QE1195IE@pluto.mirrabooka.com>
<20050714162114.GA19778@alvh.no-ip.org>
<42D6F854.nail3K81VIICH@pluto.mirrabooka.com>
alison@mirrabooka.com (Alison Winters) writes:
>>> 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.
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.
> Isn't it normal to have processes that keep a single database
> connection open for days at a time?
Database connection, sure. Single transaction, no.
> Regarding the question another poster asked: all the transactions are
> very short.
Somewhere you have one that isn't. Try watching the backends with ps,
or look at the pg_stat_activity view if your version of PG has it,
to see which sessions are staying "idle in transaction" indefinitely.
regards, tom lane
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: tgl@sss.pgh.pa.us, alison@mirrabooka.com
Subject: Re: lots of updates on small table
In-Reply-To: <5893.1121385447@sss.pgh.pa.us>
* 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