Re: Persistent dead rows

From: "Malcolm McLean" <MalcolmM(at)Interpharm(dot)co(dot)za>
To: "Richard Huxton" <dev(at)archonet(dot)com>
Cc: <pgsql-general(at)postgresql(dot)org>
Subject: Re: Persistent dead rows
Date: 2007-02-08 09:45:38
Message-ID: 3823FE6BD7EA064C961B2559C9FED9345DE7CA@zaidsexch001.interpharm.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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.

Malcolm

--
Richard Huxton
Archonet Ltd
This information is intended only for the person or entity to which it is addressed and may contain private, confidential, proprietary and/or privileged material and may be subject to confidentiality agreements. If you are not the intended recipient, you are hereby notified that any dissemination, distribution or copying of this e-mail is strictly prohibited. If you have received this e-mail in error, please notify us immediately by return email or telephone +27 12 673-1400 and destroy the original message.

Any review, retransmission, dissemination, or any other use of or taking of any action in reliance upon this information, by persons or entities other than the intended recipient, is prohibited.

The company is neither liable for proper, complete transmission of the information contained in this communication, any delay in its receipt or that the mail is virus-free.

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Ged 2007-02-08 10:17:46 Array OUT columns in a record returned from a function - part deux
Previous Message Richard Huxton 2007-02-08 08:57:10 Re: Persistent dead rows