On 19 May 2013 13:54, Armand du Plessis <adp@bank.io> wrote:
> We started seeing 1000s of messages like the ones below in our logs starting > last night. There's been no changes but performance has dropped > significantly.
> Any ideas what would be causing this?
The database has not been issuing VACUUMs on some or all of the relations, so the system needs to issue "anti-transactional wraparound" VACUUMs.
Thanks, once I logged in with another user I saw there's one relation that is being vacuumed to prevent tx wraparound. The sheer number of those log entries made it seem like the autovacuum is in a loop.
The autovacuum completed (after many hours) however it didn't seem to have frozen any old pages as it just kicks off again right away with the same reason (VACUUM ANALYZE public.messages (to prevent wraparound))
Which makes sense if I look at the settings but what bugs me is that the max xid (as seen below) is still > autovacuum_freeze_max_age (1000000000) when it finished. Should this not be reset because now we're stuck in a really crippling loop.
postgres=# select max(age(datfrozenxid)) from pg_database;
max
------------
1043200153
Looking at that table's vacuum stats this morning, it finished an autovacuum "2013-05-20 07:25:51.040327+00" and immediately started again at "2013-05-20 07:26:45.448314+00 "
As an interim measure I'm going to up the autovacuum_freeze_max_age and bite the bullet and restart postgres but something seems not right here. I'm planning to partition this specific table in the next week so it shouldn't be a problem running with the higher setting but ideally would like to track down what is happening here.
