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