Re: freeze cannot be finished

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