Re: freeze cannot be finished - Mailing list pgsql-general

From Jeff Janes
Subject Re: freeze cannot be finished
Date
Msg-id CAMkU=1wQ1wWc=MTEm9=Z23NOCAzu6aLOKhQj2oQY5AcFuTeq3g@mail.gmail.com
Whole thread Raw
In response to freeze cannot be finished  (Миша Тюрин <tmihail@bk.ru>)
Responses Re: freeze cannot be finished
List pgsql-general
On Wed, Nov 13, 2013 at 7:29 AM, Миша Тюрин <tmihail@bk.ru> wrote:

Hello!
We are experiencing suspicious and very painful case in our top-business-critical database. We have only 7 weeks before emergency stop of the cluster cause wraparound task in autovacuum process can not be finished again and again.

PostgreSQL 9.2.4 on x86_64-unknown-linux-gnu, compiled by gcc-4.4.real (Debian 4.4.5-8) 4.4.5, 64-bit
writing transactions rate ~ 100-200/s (100 000000 per week)

There were changes to vacuum's locking that were introduced in 9.2.4 which had some unintended consequences, which were then fixed in 9.2.5.  I didn't think that those would affect wraparound vacuums, but maybe they did.

When did you upgrade to 9.2.4?  can you go to 9.2.5?

 

autovacuum_freeze_max_age  1000 000000
vacuum_freeze_min_age             300 000000
vacuum_freeze_table_age           900 000000

vacuum_cost_delay 40 (autovacuum_vacuum_cost_delay -1)
vacuum_cost_limit 700
vacuum_cost_page_dirty 1
vacuum_cost_page_hit 1
vacuum_cost_page_miss 10


The ratio between miss and dirty seems unusual to me.
 

Freeze autovacuum ("autovacuum: VACUUM ANALYZE public.items (to prevent wraparound)") runs 4 days
! but it does not update pg_class.relfrozenxid

Does the server log show anything about vacuuming?  If it were aborting without updating, I would think it would leave a message of some kind (even if a misleading one)

Cheers,

Jeff 

pgsql-general by date:

Previous
From: Torsten Förtsch
Date:
Subject: Re: Partitioned table question
Next
From: LPlateAndy
Date:
Subject: expression index not used within function