[PERFORM] 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. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Avoiding Refreezing XIDs Repeatedly
On Mon, Feb 9, 2015 at 1:58 PM, bkrug bk...@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. Anyway, an already frozen tuple won't be re-frozen again. Regards, -- Matheus de Oliveira Analista de Banco de Dados Dextra Sistemas - MPS.Br nível F! www.dextra.com.br/postgres
Re: [PERFORM] Avoiding Refreezing XIDs Repeatedly
Matheus de Oliveira wrote: On Mon, Feb 9, 2015 at 1:58 PM, bkrug bk...@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 t...@sss.pgh.pa.us Branch: master Release: REL7_2 [2589735da] 2001-08-25 18:52:43 + 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 t...@sss.pgh.pa.us Branch: master Release: REL7_2 [bc7d37a52] 2001-08-26 16:56:03 + Transaction IDs wrap around, per my proposal of 13-Aug-01. More documentation to come, but the code is all here. initdb forced. -- Álvaro Herrerahttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training Services -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Avoiding Refreezing XIDs Repeatedly
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. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Avoiding Refreezing XIDs Repeatedly
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 Herrerahttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training Services -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Avoiding Refreezing XIDs Repeatedly
Alvaro Herrera alvhe...@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 -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Avoiding Refreezing XIDs Repeatedly
On Mon, Feb 9, 2015 at 4:45 PM, bkrug bk...@usatech.com wrote: Should the relfrozenxid of pg_class then equal 2 for very old and already vacuumed tables? Because that is not what I am seeing. hm... You meant in the entire table? Like an static table? Then no, it is done tuple by tuple only. In older versions (I think up to 9.2) it was setting xmin column to 2. Regards, -- Matheus de Oliveira Analista de Banco de Dados Dextra Sistemas - MPS.Br nível F! www.dextra.com.br/postgres
Re: [PERFORM] Avoiding Refreezing XIDs Repeatedly
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 Herrerahttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training Services -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Avoiding Refreezing XIDs Repeatedly
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. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
