On Wed, Nov 13, 2013 at 7:29 AM, Миша Тюрин <tmihail@xxxxx> 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
