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
