From: Tom Lane <tgl@sss.pgh.pa.us>
To: Hannu Krosing <hannu@tm.ee>
Cc: Chadwick, Russell <Russell.Chadwick@idc-mcs.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Excessive rows/tuples seriously degrading query
Date: Wed, 17 Dec 2003 13:34:24 -0500
Message-ID: <8941.1071686064@sss.pgh.pa.us> (raw)
In-Reply-To: <1071609885.5397.8.camel@fuji.krosing.net>
References: <B37E267CB3D7C841A09F5078F117AFBC059409@q.idc-mcs.com>
<1071609885.5397.8.camel@fuji.krosing.net>
Hannu Krosing <hannu@tm.ee> writes:
> Chadwick, Russell kirjutas L, 13.12.2003 kell 00:40:
>> Can anyone explain why this table which has never had more than a
>> couple rows in it shows > 500k in the query planner even after running
>> vacuum full.
> It can be that there is an idle transaction somewhere that has locked a
> lot of rows (i.e. all your updates have been running inside the same
> transaction for hour or days)
In fact an old open transaction is surely the issue, given that the
VACUUM report shows a huge number of "kept" tuples:
>> INFO: Pages 4773: Changed 1, reaped 767, Empty 0, New 0; Tup 613737: Vac 57620, Keep/VTL 613735/613713, UnUsed 20652, MinLen 52, MaxLen 52; Re-using: Free/Avail. Space 4322596/4322596; EndEmpty/Avail. Pages 0/4773.
>> CPU 9.11s/13.68u sec elapsed 22.94 sec.
"Keep" is the number of tuples that are committed dead but can't be
removed yet because there is some other open transaction that is old
enough that it should be able to see them if it looks.
Apparently the access pattern on this table is constant updates of the
two logical rows, leaving lots and lots of dead versions. You need to
vacuum it more often to keep down the amount of deadwood, and you need
to avoid having very-long-running transactions open when you vacuum.
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, hannu@tm.ee, Russell.Chadwick@idc-mcs.com
Subject: Re: Excessive rows/tuples seriously degrading query
In-Reply-To: <8941.1071686064@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