Malcolm McLean wrote:
> Hi,
>
> We are currently having a problem with one of our tables containing far
> too many dead rows. The table in question will have a few hundred
> thousand inserts and deletes per day and usually builds up quite a large
> dead row count that starts to affect the performance of the queries
> select from the table.
>
> However, it seems that when the dead row count reaches around 700000 it
> drops to 0 again and all is fast once more.
>
> I know that vacuuming is supposed to recover these, but it doesn't seem
> to be happening. Here is output from my analyze, vacuum, reindex and
> cluster commands:
>
> claim=# ANALYZE VERBOSE trans_queue;
> INFO: analyzing "public.trans_queue"
> INFO: "trans_queue": scanned 1749 of 1749 pages, containing 20383 live
> rows and 137327 dead rows; 20383 rows in sample, 20383 estimated total
> rows
> ANALYZE
> claim=# VACUUM VERBOSE ANALYZE trans_queue;
> INFO: "trans_queue": found 0 removable, 157730 nonremovable row
> versions in 1749 pages
> DETAIL: 137344 dead row versions cannot be removed yet.
This is usually because a transaction is hanging around that might be
able to see them. The vacuum can't recover them until that transaction
has completed.
> What is causing those dead rows to not get cleared even by a full
> vacuum? Is there any way keep them low without having to run a cluster
> command as that is a locking statement and requires me to close all java
> applications that are connecting to that table before running the
> cluster.
Aha! I'll bet your java app (or something in the stack) is issuing a
BEGIN and just sitting there. Try disconnecting the apps and seeing if
vacuum recovers rows then. If so, you'll need to get your java code to
stop sitting on open transactions.
--
Richard Huxton
Archonet Ltd
