Обсуждение: Persistent dead rows

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.

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

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
theintended recipient, you are hereby notified that any dissemination, distribution or copying of this e-mail is
strictlyprohibited. 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
delayin its receipt or that the mail is virus-free. 

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

Richard Huxton wrote:
> Malcolm McLean wrote:
>> 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.

Okay, I just stopped all java processes again and all pg_stat_activity
returned were IDLE's and no IDLE in transactions. The strange this is
that a cluster command removes the dead rows, and this can only be run
when all the java apps have been stopped.

> If it was the autovacuum interfering, I'd expect a lock failure.

I doubt autovacuum is interfering, I think it is the process that is
clearing the dead rows. If that is the case, then why is only autovacuum
able to clear them and not a manual vacuum.

Malcolm
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
theintended recipient, you are hereby notified that any dissemination, distribution or copying of this e-mail is
strictlyprohibited. 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
delayin its receipt or that the mail is virus-free. 

Malcolm McLean wrote:
> Richard Huxton wrote:
>> Malcolm McLean wrote:
>>> 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.
>
> Okay, I just stopped all java processes again and all pg_stat_activity
> returned were IDLE's and no IDLE in transactions. The strange this is
> that a cluster command removes the dead rows, and this can only be run
> when all the java apps have been stopped.

Well, both cluster and vacuum full will require a lock on the table. But
  they're more or less doing the same thing, so why the one should work
and the other not I don't know.

>> If it was the autovacuum interfering, I'd expect a lock failure.
>
> I doubt autovacuum is interfering, I think it is the process that is
> clearing the dead rows. If that is the case, then why is only autovacuum
> able to clear them and not a manual vacuum.

Makes no sense to me - autovacuum has no magic about it, it just runs
vacuum.

You've got the permissions to vacuum the table, otherwise you'd get an
error. You've checked for transactions in pg_stat_activity. Don't see
how it's worth checking pg_locks if you don't have any transactions.
This one's puzzling me.

I've had a quick look at the release notes for 8.1.x and can't see
anything obvious on this issue, but it might be worth upgrading to 8.1.8
to see if that just makes it go away.

--
   Richard Huxton
   Archonet Ltd

Richard Huxton <dev@archonet.com> writes:
> Well, both cluster and vacuum full will require a lock on the table. But
>   they're more or less doing the same thing, so why the one should work
> and the other not I don't know.

CLUSTER isn't MVCC-safe --- it'll drop rows that are committed dead
even if they are potentially still visible to some open transaction.
The OP has *clearly* got an open-transaction problem.

            regards, tom lane