Re: vacuumdb -v output

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/>