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?
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)