Thread: Vacuum problems with 9.1
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.
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
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