Re: Transaction ID wrap limit is log entries

On Sun, May 19, 2013 at 6:12 PM, Armand du Plessis <adp(at)bank(dot)io> wrote:

> On Sun, May 19, 2013 at 6:08 PM, Simon Riggs <simon(at)2ndquadrant(dot)com>wrote:
>
>> On 19 May 2013 13:54, Armand du Plessis <adp(at)bank(dot)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.
>
>
>>
>> --
>> Simon Riggs http://www.2ndQuadrant.com/
>> PostgreSQL Development, 24x7 Support, Training & Services
>>
>
>
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.

Kind regards,

Armand