pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedAvoiding Refreezing XIDs Repeatedly
9+ messages / 4 participants
[nested] [flat]
* Avoiding Refreezing XIDs Repeatedly
@ 2015-02-09 15:58 bkrug <bkrug@usatech.com>
2015-02-09 16:48 ` Re: Avoiding Refreezing XIDs Repeatedly Matheus de Oliveira <matioli.matheus@gmail.com>
0 siblings, 1 reply; 9+ messages in thread
From: bkrug @ 2015-02-09 15:58 UTC (permalink / raw)
To: pgsql-performance
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Avoiding Refreezing XIDs Repeatedly
2015-02-09 15:58 Avoiding Refreezing XIDs Repeatedly bkrug <bkrug@usatech.com>
@ 2015-02-09 16:48 ` Matheus de Oliveira <matioli.matheus@gmail.com>
2015-02-09 17:06 ` Re: Avoiding Refreezing XIDs Repeatedly Alvaro Herrera <alvherre@2ndquadrant.com>
2015-02-09 18:45 ` Re: Avoiding Refreezing XIDs Repeatedly bkrug <bkrug@usatech.com>
0 siblings, 2 replies; 9+ messages in thread
From: Matheus de Oliveira @ 2015-02-09 16:48 UTC (permalink / raw)
To: bkrug <bkrug@usatech.com>; +Cc: pgsql-performance
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.
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Avoiding Refreezing XIDs Repeatedly
2015-02-09 15:58 Avoiding Refreezing XIDs Repeatedly bkrug <bkrug@usatech.com>
2015-02-09 16:48 ` Re: Avoiding Refreezing XIDs Repeatedly Matheus de Oliveira <matioli.matheus@gmail.com>
@ 2015-02-09 17:06 ` Alvaro Herrera <alvherre@2ndquadrant.com>
1 sibling, 0 replies; 9+ messages in thread
From: Alvaro Herrera @ 2015-02-09 17:06 UTC (permalink / raw)
To: Matheus de Oliveira <matioli.matheus@gmail.com>; +Cc: bkrug <bkrug@usatech.com>; pgsql-performance
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
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Avoiding Refreezing XIDs Repeatedly
2015-02-09 15:58 Avoiding Refreezing XIDs Repeatedly bkrug <bkrug@usatech.com>
2015-02-09 16:48 ` Re: Avoiding Refreezing XIDs Repeatedly Matheus de Oliveira <matioli.matheus@gmail.com>
@ 2015-02-09 18:45 ` bkrug <bkrug@usatech.com>
2015-02-09 18:53 ` Re: Avoiding Refreezing XIDs Repeatedly Alvaro Herrera <alvherre@2ndquadrant.com>
2015-02-09 18:56 ` Re: Avoiding Refreezing XIDs Repeatedly Matheus de Oliveira <matioli.matheus@gmail.com>
1 sibling, 2 replies; 9+ messages in thread
From: bkrug @ 2015-02-09 18:45 UTC (permalink / raw)
To: pgsql-performance
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Avoiding Refreezing XIDs Repeatedly
2015-02-09 15:58 Avoiding Refreezing XIDs Repeatedly bkrug <bkrug@usatech.com>
2015-02-09 16:48 ` Re: Avoiding Refreezing XIDs Repeatedly Matheus de Oliveira <matioli.matheus@gmail.com>
2015-02-09 18:45 ` Re: Avoiding Refreezing XIDs Repeatedly bkrug <bkrug@usatech.com>
@ 2015-02-09 18:53 ` Alvaro Herrera <alvherre@2ndquadrant.com>
1 sibling, 0 replies; 9+ messages in thread
From: Alvaro Herrera @ 2015-02-09 18:53 UTC (permalink / raw)
To: bkrug <bkrug@usatech.com>; +Cc: pgsql-performance
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
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Avoiding Refreezing XIDs Repeatedly
2015-02-09 15:58 Avoiding Refreezing XIDs Repeatedly bkrug <bkrug@usatech.com>
2015-02-09 16:48 ` Re: Avoiding Refreezing XIDs Repeatedly Matheus de Oliveira <matioli.matheus@gmail.com>
2015-02-09 18:45 ` Re: Avoiding Refreezing XIDs Repeatedly bkrug <bkrug@usatech.com>
@ 2015-02-09 18:56 ` Matheus de Oliveira <matioli.matheus@gmail.com>
2015-02-09 19:07 ` Re: Avoiding Refreezing XIDs Repeatedly bkrug <bkrug@usatech.com>
1 sibling, 1 reply; 9+ messages in thread
From: Matheus de Oliveira @ 2015-02-09 18:56 UTC (permalink / raw)
To: bkrug <bkrug@usatech.com>; +Cc: pgsql-performance
On Mon, Feb 9, 2015 at 4:45 PM, bkrug <bkrug@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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Avoiding Refreezing XIDs Repeatedly
2015-02-09 15:58 Avoiding Refreezing XIDs Repeatedly bkrug <bkrug@usatech.com>
2015-02-09 16:48 ` Re: Avoiding Refreezing XIDs Repeatedly Matheus de Oliveira <matioli.matheus@gmail.com>
2015-02-09 18:45 ` Re: Avoiding Refreezing XIDs Repeatedly bkrug <bkrug@usatech.com>
2015-02-09 18:56 ` Re: Avoiding Refreezing XIDs Repeatedly Matheus de Oliveira <matioli.matheus@gmail.com>
@ 2015-02-09 19:07 ` bkrug <bkrug@usatech.com>
2015-02-09 19:10 ` Re: Avoiding Refreezing XIDs Repeatedly Alvaro Herrera <alvherre@2ndquadrant.com>
0 siblings, 1 reply; 9+ messages in thread
From: bkrug @ 2015-02-09 19:07 UTC (permalink / raw)
To: pgsql-performance
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Avoiding Refreezing XIDs Repeatedly
2015-02-09 15:58 Avoiding Refreezing XIDs Repeatedly bkrug <bkrug@usatech.com>
2015-02-09 16:48 ` Re: Avoiding Refreezing XIDs Repeatedly Matheus de Oliveira <matioli.matheus@gmail.com>
2015-02-09 18:45 ` Re: Avoiding Refreezing XIDs Repeatedly bkrug <bkrug@usatech.com>
2015-02-09 18:56 ` Re: Avoiding Refreezing XIDs Repeatedly Matheus de Oliveira <matioli.matheus@gmail.com>
2015-02-09 19:07 ` Re: Avoiding Refreezing XIDs Repeatedly bkrug <bkrug@usatech.com>
@ 2015-02-09 19:10 ` Alvaro Herrera <alvherre@2ndquadrant.com>
2015-02-09 20:00 ` Re: Avoiding Refreezing XIDs Repeatedly Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 9+ messages in thread
From: Alvaro Herrera @ 2015-02-09 19:10 UTC (permalink / raw)
To: bkrug <bkrug@usatech.com>; +Cc: pgsql-performance
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
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Avoiding Refreezing XIDs Repeatedly
2015-02-09 15:58 Avoiding Refreezing XIDs Repeatedly bkrug <bkrug@usatech.com>
2015-02-09 16:48 ` Re: Avoiding Refreezing XIDs Repeatedly Matheus de Oliveira <matioli.matheus@gmail.com>
2015-02-09 18:45 ` Re: Avoiding Refreezing XIDs Repeatedly bkrug <bkrug@usatech.com>
2015-02-09 18:56 ` Re: Avoiding Refreezing XIDs Repeatedly Matheus de Oliveira <matioli.matheus@gmail.com>
2015-02-09 19:07 ` Re: Avoiding Refreezing XIDs Repeatedly bkrug <bkrug@usatech.com>
2015-02-09 19:10 ` Re: Avoiding Refreezing XIDs Repeatedly Alvaro Herrera <alvherre@2ndquadrant.com>
@ 2015-02-09 20:00 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 9+ messages in thread
From: Tom Lane @ 2015-02-09 20:00 UTC (permalink / raw)
To: Alvaro Herrera <alvherre@2ndquadrant.com>; +Cc: bkrug <bkrug@usatech.com>; pgsql-performance
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
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2015-02-09 20:00 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2015-02-09 15:58 Avoiding Refreezing XIDs Repeatedly bkrug <bkrug@usatech.com>
2015-02-09 16:48 ` Matheus de Oliveira <matioli.matheus@gmail.com>
2015-02-09 17:06 ` Alvaro Herrera <alvherre@2ndquadrant.com>
2015-02-09 18:45 ` bkrug <bkrug@usatech.com>
2015-02-09 18:53 ` Alvaro Herrera <alvherre@2ndquadrant.com>
2015-02-09 18:56 ` Matheus de Oliveira <matioli.matheus@gmail.com>
2015-02-09 19:07 ` bkrug <bkrug@usatech.com>
2015-02-09 19:10 ` Alvaro Herrera <alvherre@2ndquadrant.com>
2015-02-09 20:00 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox