Malcolm McLean wrote:
Richard Huxton wrote:
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.
I tested this theory by stopping java applications that were connected
to the database and all other connections that were using transactions
and the full vacuum was still unable to remove the dead rows.
What I'm still wondering about, is why the dead row count rises
incredibly high, then all of a sudden drops to 0 again when the java
apps never stop running.
Are you certain there's no open transaction? Perhaps keep an eye on
SELECT * FROM pg_stat_activity - there might be something you don't know
about.
If it was the autovacuum interfering, I'd expect a lock failure.
--
Richard Huxton
Archonet Ltd
