pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Chris Browne <cbbrowne@acm.org>
To: pgsql-admin@postgresql.org
Subject: Re: vacuumdb -v output
Date: Thu, 21 Apr 2005 14:43:56 -0400
Message-ID: <607jiw2cnn.fsf@dba2.int.libertyrms.com> (raw)
References: <003301c5469b$eead09c0$0b02a594@csekcopley2>

kcopley@bart.gov ("Kevin Copley") writes:
> Hi,
>
> I've just put a system into production in which some tables are updated frequently - several times per
> second.
>
> I'm doing a nightly vacuumdb -v, but am not sure if it's achieving anything. Here's the output for one
> table:
>
>  
>
> ----------------------------------------------------------------------------------------------------------------------------------------------------
>
> INFO:  vacuuming "public.fip_track_circuit"
> INFO:  index "fip_track_circuit_pk" now contains 1557427 row versions in 4538 pages
> DETAIL:  10 index row versions were removed.
> 0 index pages have been deleted, 0 are currently reusable.
> CPU 0.22s/0.14u sec elapsed 6.51 sec.
> INFO:  "fip_track_circuit": removed 10 row versions in 9 pages
> DETAIL:  CPU 0.00s/0.00u sec elapsed 0.00 sec.
> INFO:  "fip_track_circuit": found 10 removable, 1557427 nonremovable row versions in 14305 pages
> DETAIL:  1555321 dead row versions cannot be removed yet.
> There were 1 unused item pointers.
> 0 pages are entirely empty.
> CPU 0.42s/0.24u sec elapsed 6.82 sec.
> INFO:  vacuuming "public.fip_xl_switch"
>
>
> ----------------------------------------------------------------------------------------------------------------------------------------------------
>
>  
>
> I'm concerned about ".... 1555321 dead row versions cannot be removed yet...."
>
>  
>
> I'd be extremely grateful if anyone could cast some light on this.

That sounds pretty bad...

It seems likely to me that you have some idle transaction hanging
around that is preventing VACUUM from doing anything useful about
cleaning out that table.

If you have command statistics turned on, you might try the query:

select * from pg_stat_activity where current_query <> '<IDLE>';

If you see some items that indicate '<IDLE> in transaction' that are
rather old, that's a nice "smoking gun" to indicate where the problem
lies.

Long running transactions are the bane of our existence, and are an
all too common result of buggy connection pool implementations :-(.

Go "thump" the offending connection, and you should see things clear out.

After that, you may need to do a VACUUM FULL to get things totally
cleared out, and probably a REINDEX after that...
-- 
(format nil "~S@~S" "cbbrowne" "acm.org")
http://www.ntlug.org/~cbbrowne/sap.html
Rules of the Evil Overlord #78.  "I will not tell my Legions of Terror
"And he must  be taken alive!" The command will be:  ``And try to take
him alive if it is reasonably practical.''"
<http://www.eviloverlord.com/;



view thread (2+ messages)

Message-ID: <607jiw2cnn.fsf@dba2.int.libertyrms.com>
Permalink:  ../607jiw2cnn.fsf@dba2.int.libertyrms.com/
Also on:    postgresql.org/message-id/607jiw2cnn.fsf@dba2.int.libertyrms.com

 · 

reply

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 from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-admin@postgresql.org
  Cc: cbbrowne@acm.org
  Subject: Re: vacuumdb -v output
  In-Reply-To: <607jiw2cnn.fsf@dba2.int.libertyrms.com>
  References: <003301c5469b$eead09c0$0b02a594@csekcopley2> <607jiw2cnn.fsf@dba2.int.libertyrms.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