Thread: Avoiding Refreezing XIDs Repeatedly

For a large database with lots of activity (transactions), the XIDs are very
often re-frozen by AutoVacuum. Even when autovacuum_freeze_max_age is set to
2 billion, the XIDs can wrap every couple of days on an active database.
This causes unnecessary changes to otherwise unmodified files and archiving
processes that use rsync or similiar processes have way more work to do.
Couldn't postgres reserve a special XID that is never available for normal
transactions but that indicates that any transaction can see it because it
is so old? Then instead of constantly having to freeze old XIDs each time
the XID is going to wrap, vacuum can just set it to the special XID and
never touch it again unless something really changes.



--
View this message in context: http://postgresql.nabble.com/Avoiding-Refreezing-XIDs-Repeatedly-tp5837222.html
Sent from the PostgreSQL - performance mailing list archive at Nabble.com.

Matheus de Oliveira wrote:
> On Mon, Feb 9, 2015 at 1:58 PM, bkrug <bkrug@usatech.com> wrote:
>
> > Couldn't postgres reserve a special XID that is never available for normal
> > transactions but that indicates that any transaction can see it because it
> > is so old? Then instead of constantly having to freeze old XIDs each time
> > the XID is going to wrap, vacuum can just set it to the special XID and
> > never touch it again unless something really changes.
> >
>
>
> It changed in recent versions (9.3 or 9.4, I don't recall exactly which)
> and moved to tuple header, but what you described is exactly what was done,
> the xid was 2.

Actually, it's been done this way for ages -- it was introduced in 2001
(release 7.2) by these commits:

Author: Tom Lane <tgl@sss.pgh.pa.us>
Branch: master Release: REL7_2 [2589735da] 2001-08-25 18:52:43 +0000

    Replace implementation of pg_log as a relation accessed through the
    buffer manager with 'pg_clog', a specialized access method modeled
    on pg_xlog.  This simplifies startup (don't need to play games to
    open pg_log; among other things, OverrideTransactionSystem goes away),
    should improve performance a little, and opens the door to recycling
    commit log space by removing no-longer-needed segments of the commit
    log.  Actual recycling is not there yet, but I felt I should commit
    this part separately since it'd still be useful if we chose not to
    do transaction ID wraparound.


Author: Tom Lane <tgl@sss.pgh.pa.us>
Branch: master Release: REL7_2 [bc7d37a52] 2001-08-26 16:56:03 +0000

    Transaction IDs wrap around, per my proposal of 13-Aug-01.  More
    documentation to come, but the code is all here.  initdb forced.


--
Álvaro Herrera                http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Matheus de Oliveira wrote
> It changed in recent versions (9.3 or 9.4, I don't recall exactly which)
> and moved to tuple header, but what you described is exactly what was
> done,
> the xid was 2.

Should the relfrozenxid of pg_class then equal 2 for very old and already
vacuumed tables? Because that is not what I am seeing.



--
View this message in context: http://postgresql.nabble.com/Avoiding-Refreezing-XIDs-Repeatedly-tp5837222p5837247.html
Sent from the PostgreSQL - performance mailing list archive at Nabble.com.

bkrug wrote:
> Matheus de Oliveira wrote
> > It changed in recent versions (9.3 or 9.4, I don't recall exactly which)
> > and moved to tuple header, but what you described is exactly what was
> > done,
> > the xid was 2.
>
> Should the relfrozenxid of pg_class then equal 2 for very old and already
> vacuumed tables? Because that is not what I am seeing.

No.  The problem is that it's not easy to change the relfrozenxid when
an INSERT/UPDATE command creates a tuple with a non-frozen XID.

--
Álvaro Herrera                http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

The problem I'm facing is that I have many large (several GB) tables that are
not being changed (they are several days old) but auto-vacuum keeps scanning
and updating them every time the xid wraps around and thus my rsync back-up
process sees that the disk files have changed and must copy them.



--
View this message in context: http://postgresql.nabble.com/Avoiding-Refreezing-XIDs-Repeatedly-tp5837222p5837251.html
Sent from the PostgreSQL - performance mailing list archive at Nabble.com.

bkrug wrote:
> The problem I'm facing is that I have many large (several GB) tables that are
> not being changed (they are several days old) but auto-vacuum keeps scanning
> and updating them every time the xid wraps around and thus my rsync back-up
> process sees that the disk files have changed and must copy them.

We have considered changing this, but it needs a concerted effort.  It's
not a simple problem.

--
Álvaro Herrera                http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Alvaro Herrera <alvherre@2ndquadrant.com> writes:
> bkrug wrote:
>> The problem I'm facing is that I have many large (several GB) tables that are
>> not being changed (they are several days old) but auto-vacuum keeps scanning
>> and updating them every time the xid wraps around and thus my rsync back-up
>> process sees that the disk files have changed and must copy them.

> We have considered changing this, but it needs a concerted effort.  It's
> not a simple problem.

I'm not following.  Yes, the tables will be *scanned* at least once per
XID wraparound cycle, but if they are in fact static then they should not
be changing once the tuples have been frozen the first time.  If this is
incurring continuing rsync work then something else is going on.

            regards, tom lane