pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedVacuum problems with 9.1
3+ messages / 3 participants
[nested] [flat]
* Vacuum problems with 9.1
@ 2012-08-28 04:33 Nimesh Satam <nimesh.zedo@gmail.com>
0 siblings, 2 replies; 3+ messages in thread
From: Nimesh Satam @ 2012-08-28 04:33 UTC (permalink / raw)
To: pgsql-performance
Hi,
We have been using the current version of postgres i.e. 9.1.4 with
streaming replication on. While vacuuming we noticed that certain dead rows
are not getting removed and following debug information is printed:
"DETAIL: 12560 dead row versions cannot be removed yet."
As per suggestion, we made sure that no long running transactions are
active. Also all the applications were stopped during this time.
Can anybody highlight the possible reason for the dead rows not been
cleaned?
FYI: We used the command VACUUM FULL ANALYZE VERBOSE table_name; command.
Regards,
Nimesh.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Vacuum problems with 9.1
@ 2012-08-28 06:33 Pavan Deolasee <pavan.deolasee@gmail.com>
parent: Nimesh Satam <nimesh.zedo@gmail.com>
1 sibling, 0 replies; 3+ messages in thread
From: Pavan Deolasee @ 2012-08-28 06:33 UTC (permalink / raw)
To: Nimesh Satam <nimesh.zedo@gmail.com>; +Cc: pgsql-performance
On Tue, Aug 28, 2012 at 10:03 AM, Nimesh Satam <nimesh.zedo@gmail.com>wrote:
> Hi,
>
> We have been using the current version of postgres i.e. 9.1.4 with
> streaming replication on. While vacuuming we noticed that certain dead rows
> are not getting removed and following debug information is printed:
>
> "DETAIL: 12560 dead row versions cannot be removed yet."
>
> As per suggestion, we made sure that no long running transactions are
> active. Also all the applications were stopped during this time.
>
> Can anybody highlight the possible reason for the dead rows not been
> cleaned?
>
>
Are you absolutely sure that there are no other client connections open
which are actively deleting/updating records ? The above message would
usually come when certain rows which are otherwise DEAD (meaning, deleting
or updating transaction has already committed) but can't be removed just
yet because there is at least one old transaction that may still see the
tuple as visible. If there are no open transactions, then I can only think
about a concurrent auto-analyze running that can prevent some tuples from
being vacuumed.
What happens if you run the command again ? Do you get the exact same
number again ?
Also note that any concurrent transaction can cause this, even if the
transaction does not access the table under vacuum operation.
> FYI: We used the command VACUUM FULL ANALYZE VERBOSE table_name; command.
>
>
I hope you are aware that VACUUM FULL is a costly operation because it
rewrites the entire table again. You need VACUUM FULL only in cases of
severe bloat. Otherwise a plain VACUUM (or auto-vacuum) should be enough to
handle regular bloat.
Thanks,
Pavan
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Vacuum problems with 9.1
@ 2012-08-28 06:53 Craig Ringer <ringerc@ringerc.id.au>
parent: Nimesh Satam <nimesh.zedo@gmail.com>
1 sibling, 0 replies; 3+ messages in thread
From: Craig Ringer @ 2012-08-28 06:53 UTC (permalink / raw)
To: Nimesh Satam <nimesh.zedo@gmail.com>; +Cc: pgsql-performance
On 08/28/2012 12:33 PM, Nimesh Satam wrote:
> Hi,
>
> We have been using the current version of postgres i.e. 9.1.4 with
> streaming replication on. While vacuuming we noticed that certain dead
> rows are not getting removed and following debug information is printed:
>
> "DETAIL: 12560 dead row versions cannot be removed yet."
>
> As per suggestion, we made sure that no long running transactions are
> active. Also all the applications were stopped during this time.
>
> Can anybody highlight the possible reason for the dead rows not been
> cleaned?
I don't know if prepared transactions could cause this exact message,
but check:
select * from pg_prepared_xacts ;
to see if you have any prepared transactions (from two-phase commit)
lying around.
If you don't use XA or 2PC, consider setting max_prepared_transactions
to 0 in postgresql.conf if it isn't already.
--
Craig Ringer
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2012-08-28 06:53 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2012-08-28 04:33 Vacuum problems with 9.1 Nimesh Satam <nimesh.zedo@gmail.com>
2012-08-28 06:33 ` Pavan Deolasee <pavan.deolasee@gmail.com>
2012-08-28 06:53 ` Craig Ringer <ringerc@ringerc.id.au>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox