Re: Transaction ID wrap limit is log entries

On Mon, May 20, 2013 at 3:21 PM, Armand du Plessis <adp(at)bank(dot)io> wrote:

> On Mon, May 20, 2013 at 3:11 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
>> Armand du Plessis <adp(at)bank(dot)io> writes:
>> > 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))
>>
>> I suspect it's unable to move the table's min XID forward at all because
>> there is some old open transaction preventing cleanup of very old dead
>> tuples. Look for old xact_start times in pg_stat_activity. Also look
>> for old entries in pg_prepared_xacts. If you find any, get rid of them.
>
>
This now had a full vacuum on the table but the table's xid is still
increasing.

"messages";1061103361;"62 GB"

It's gone up from 105xxx yesterday. I've confirmed there's no old
transactions in pg_stat_activity or pg_prepared_xacts and everything else
seems fine. I can see the completion in the log and see it reflected as
last_vacuum in pg_stat_user_tables.

2013-05-21 08:01:44.910
UTC,"postgres","datase",6921,"[local]",519a9497.1b09,3,"VACUUM",2013-05-20
21:24:39 UTC,51/0,0,LOG,00000,"duration: 38210856.820 ms statement: vacuum
messages;",,,,,,,,,"psql"

I've bumped up the autovacuum_freeze_max_age yesterday so this was a normal
vacuum unlike the wrap-around autovacuums from the original post.

Settings :
autovacuum_freeze_max_age : 1500000000
vacuum_freeze_min_age : 25000000
vacuum_freeze_table_age: 150000000

It's Postgres 9.2.3.

I might just be missing something but it's worrying me that it's just
increasing even after the vacuum.

Kind regards,

Armand