Autovacuum and XID wraparound

David Fetter <david@fetter.org> writes:

Per Neil Conway, here's some doc patches re: the autovacuum daemon's
behavior. Should this be back-patched to 8.2x?

This fact is already documented in at least three places; do we really
need two more? The proposed addition to postgresql.conf seems
particularly over-the-top, since there is no entry in that file that
even pretends to offer a complete description of the associated
behavior.

regards, tom lane

On Sun, May 13, 2007 at 10:06:40PM -0400, Tom Lane wrote:

David Fetter <david@fetter.org> writes:

Per Neil Conway, here's some doc patches re: the autovacuum
daemon's behavior. Should this be back-patched to 8.2x?

This fact is already documented in at least three places; do we
really need two more?

Yes.

The proposed addition to postgresql.conf seems particularly
over-the-top, since there is no entry in that file that even
pretends to offer a complete description of the associated behavior.

I think that a boolean that doesn't do what you expect booleans to do,
i.e. turn the thing all the way off, is worth a mention.

Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
phone: +1 415 235 3778 AIM: dfetter666
Skype: davidfetter

Remember to vote!
Consider donating to PostgreSQL: http://www.postgresql.org/about/donate

On Sun, 2007-13-05 at 22:06 -0400, Tom Lane wrote:

This fact is already documented in at least three places; do we really
need two more?

I think we need to at least modify the documentation for the autovacuum
GUC parameter, which currently states only that it "controls whether the
server should run the autovacuum launcher daemon" -- this is not
strictly true, and in any case, it isn't the whole story.

The proposed addition to postgresql.conf seems particularly
over-the-top

I agree that this information doesn't really belong in postgresql.conf.

-Neil

David Fetter wrote:

On Sun, May 13, 2007 at 10:06:40PM -0400, Tom Lane wrote:

David Fetter <david@fetter.org> writes:

Per Neil Conway, here's some doc patches re: the autovacuum
daemon's behavior. Should this be back-patched to 8.2x?

This fact is already documented in at least three places; do we
really need two more?

Yes.

The proposed addition to postgresql.conf seems particularly
over-the-top, since there is no entry in that file that even
pretends to offer a complete description of the associated behavior.

I think that a boolean that doesn't do what you expect booleans to do,
i.e. turn the thing all the way off, is worth a mention.

I agree with Tom. I don't think the current behavior is a major issue
for users for it to be mentioned more than it already is, though if you
want to move one of those, we can do that.

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://www.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

neilc@samurai.com (Neil Conway) writes:

On Sun, 2007-13-05 at 22:06 -0400, Tom Lane wrote:

This fact is already documented in at least three places; do we really
need two more?

I think we need to at least modify the documentation for the autovacuum
GUC parameter, which currently states only that it "controls whether the
server should run the autovacuum launcher daemon" -- this is not
strictly true, and in any case, it isn't the whole story.

The proposed addition to postgresql.conf seems particularly
over-the-top

I agree that this information doesn't really belong in postgresql.conf.

Question... (note: this does not strictly fit into the purview of the
.patches list)

Would the following 'maintenance' regimen be truly safe against XID
wraparound:

- Most tables are being vacuumed regularly, so that
pg_class.relfrozenxid is kept "safe."

- There are some tables that periodically get TRUNCATEd so that, in
principle, they never need to be vacuumed.

Is it actually true that we'd never need to vacuum those tables
(assuming 8.2+)? I suppose it would be rather cheap to VACUUM
immediately after the TRUNCATE...

The application is one where we might use partitioning, rolling from
table to table every so often, with the expectation that we'll
TRUNCATE the eldest data often enough that we shouldn't need to VACUUM
any of the partitions.
--
let name="cbbrowne" and tld="linuxdatabases.info" in name ^ "@" ^ tld;;
http://linuxdatabases.info/info/linuxxian.html
Why are men like blenders?
You need one, but you're not quite sure why.

Chris Browne wrote:

Would the following 'maintenance' regimen be truly safe against XID
wraparound:

- Most tables are being vacuumed regularly, so that
pg_class.relfrozenxid is kept "safe."

- There are some tables that periodically get TRUNCATEd so that, in
principle, they never need to be vacuumed.

Is it actually true that we'd never need to vacuum those tables
(assuming 8.2+)? I suppose it would be rather cheap to VACUUM
immediately after the TRUNCATE...

You'd need to vacuum after the truncate. It would be pretty cheap, the
tables being empty.

I suppose it would be pretty trivial to set the relfrozenxid to
RecentXmin or something during TRUNCATE.

--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

Alvaro Herrera <alvherre@commandprompt.com> writes:

I suppose it would be pretty trivial to set the relfrozenxid to
RecentXmin or something during TRUNCATE.

I had the idea we were doing that already --- at least I'm pretty sure I
remember it being discussed. But I see it's not being done in HEAD.

regards, tom lane

On Mon, 2007-14-05 at 16:22 -0400, Bruce Momjian wrote:

I agree with Tom. I don't think the current behavior is a major issue
for users for it to be mentioned more than it already is

Are you really suggesting that we shouldn't modify config.sgml to note
that "autovacuum = off" does not actually imply that "the autovacuum
daemon is disabled"? ISTM that plainly violates the principle of least
surprise -- it is almost the definition of what an entry in config.sgml
*should* include.

though if you want to move one of those, we can do that.

So the change would be okay if we also removed one of the other mentions
in an unrelated section of the manual? I don't see the logic.

-Neil

Neil Conway wrote:

On Mon, 2007-14-05 at 16:22 -0400, Bruce Momjian wrote:

I agree with Tom. I don't think the current behavior is a major issue
for users for it to be mentioned more than it already is

Are you really suggesting that we shouldn't modify config.sgml to note
that "autovacuum = off" does not actually imply that "the autovacuum
daemon is disabled"? ISTM that plainly violates the principle of least
surprise -- it is almost the definition of what an entry in config.sgml
*should* include.

I agree, the note should be added there (but it should be a short one
and refer the reader someplace else for more complete details).

Was there a doc patch proposed already? I seem to have missed it.

--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

On Tue, 2007-15-05 at 09:07 -0400, Alvaro Herrera wrote:

I agree, the note should be added there (but it should be a short one
and refer the reader someplace else for more complete details).

I've applied the attached patch to HEAD and REL8_2_STABLE.

-Neil

Tom Lane wrote:

Alvaro Herrera <alvherre@commandprompt.com> writes:

I suppose it would be pretty trivial to set the relfrozenxid to
RecentXmin or something during TRUNCATE.

I had the idea we were doing that already --- at least I'm pretty sure I
remember it being discussed. But I see it's not being done in HEAD.

Patch to do it attached. I am thinking we can do something similar in
CLUSTER as well.

--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

On Tue, May 15, 2007 at 06:13:47PM -0400, Alvaro Herrera wrote:

Tom Lane wrote:

Alvaro Herrera <alvherre@commandprompt.com> writes:

I suppose it would be pretty trivial to set the relfrozenxid to
RecentXmin or something during TRUNCATE.

I had the idea we were doing that already --- at least I'm pretty sure I
remember it being discussed. But I see it's not being done in HEAD.

Patch to do it attached. I am thinking we can do something similar in
CLUSTER as well.

Actually, it already happens for CLUSTER because cluster calls
heap_create_with_catalog, which calls AddNewRelationTuple. See
backend/catalog/heap.c line 716.
--
Jim Nasby decibel@decibel.org
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)

Jim C. Nasby wrote:

On Tue, May 15, 2007 at 06:13:47PM -0400, Alvaro Herrera wrote:

Tom Lane wrote:

Alvaro Herrera <alvherre@commandprompt.com> writes:

I suppose it would be pretty trivial to set the relfrozenxid to
RecentXmin or something during TRUNCATE.

I had the idea we were doing that already --- at least I'm pretty sure I
remember it being discussed. But I see it's not being done in HEAD.

Patch to do it attached. I am thinking we can do something similar in
CLUSTER as well.

Actually, it already happens for CLUSTER because cluster calls
heap_create_with_catalog, which calls AddNewRelationTuple. See
backend/catalog/heap.c line 716.

Right, but that heap is dropped later, and only the relfilenode remains,
because they are swapped.

In any case the change is a very small patch, which I attach but I
haven't tested. This only works if the new rewriteheap stuff actually
changes Xids to follow OldestXmin, i.e. all tuples that have older
Xmin/Xmax are frozen (or marked with the current Xid). Heikki, can you
confirm that this is the case?

--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

Alvaro Herrera <alvherre@commandprompt.com> writes:

Tom Lane wrote:

I had the idea we were doing that already --- at least I'm pretty sure I
remember it being discussed. But I see it's not being done in HEAD.

Patch to do it attached. I am thinking we can do something similar in
CLUSTER as well.

Umm ... you'd have to be a lot more conservative in CLUSTER now that
it's MVCC-safe. I don't say that CLUSTER can't push up relfrozenxid,
but there's something wrong if CLUSTER and TRUNCATE are trying to
push it up the same amount.

regards, tom lane

Alvaro Herrera wrote:

In any case the change is a very small patch, which I attach but I
haven't tested. This only works if the new rewriteheap stuff actually
changes Xids to follow OldestXmin, i.e. all tuples that have older
Xmin/Xmax are frozen (or marked with the current Xid). Heikki, can you
confirm that this is the case?

No, CLUSTER doesn't freeze tuples. It could do that easily, it didn't
occur to me when I wrote it. It would make it harder to debug, though,
should we have any problems with it in the future.

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com

Tom Lane wrote:

Alvaro Herrera <alvherre@commandprompt.com> writes:

Tom Lane wrote:

I had the idea we were doing that already --- at least I'm pretty sure I
remember it being discussed. But I see it's not being done in HEAD.

Patch to do it attached. I am thinking we can do something similar in
CLUSTER as well.

Umm ... you'd have to be a lot more conservative in CLUSTER now that
it's MVCC-safe. I don't say that CLUSTER can't push up relfrozenxid,
but there's something wrong if CLUSTER and TRUNCATE are trying to
push it up the same amount.

No, TRUNCATE will use RecentXmin while the CLUSTER patch I posted uses
OldestXmin, which is what the HeapTupleSatisfiesUpdate test was using.
However, given that Heikki just confirmed that CLUSTER does not freeze
tuples, it's not really possible to do this, so I'll drop the CLUSTER
patch for now.

This means that people using CLUSTER to compact tables won't have the
benefit of advancing relfrozenxid, so they will have to run VACUUM on
those tables at some point anyway, even though there will be no dead
tuples :-(

--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

Alvaro Herrera wrote:

However, given that Heikki just confirmed that CLUSTER does not freeze
tuples, it's not really possible to do this, so I'll drop the CLUSTER
patch for now.

This means that people using CLUSTER to compact tables won't have the
benefit of advancing relfrozenxid, so they will have to run VACUUM on
those tables at some point anyway, even though there will be no dead
tuples :-(

Now that I think this a bit more, I think CLUSTER should freeze the
tuples. I was worried about losing valuable debug information by doing
that, but thinking a bit more that's not a big concern: we wouldn't
freeze tuples newer than recent xmin. The update chain logic is the most
risky part of the code, and we wouldn't lose the xmin and xmax of tuples
that are part of update chains.

Patch attached. You'll need the changes to cluster.c to set the
relfrozenid as well.

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com

Heikki Linnakangas wrote:

Alvaro Herrera wrote:

However, given that Heikki just confirmed that CLUSTER does not freeze
tuples, it's not really possible to do this, so I'll drop the CLUSTER
patch for now.

This means that people using CLUSTER to compact tables won't have the
benefit of advancing relfrozenxid, so they will have to run VACUUM on
those tables at some point anyway, even though there will be no dead
tuples :-(

Now that I think this a bit more, I think CLUSTER should freeze the
tuples. I was worried about losing valuable debug information by doing
that, but thinking a bit more that's not a big concern: we wouldn't
freeze tuples newer than recent xmin. The update chain logic is the most
risky part of the code, and we wouldn't lose the xmin and xmax of tuples
that are part of update chains.

Patch attached. You'll need the changes to cluster.c to set the
relfrozenid as well.

Thanks, committed. (I changed NULL for InvalidBuffer).

--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

Alvaro Herrera wrote:

Tom Lane wrote:

Alvaro Herrera <alvherre@commandprompt.com> writes:

I suppose it would be pretty trivial to set the relfrozenxid to
RecentXmin or something during TRUNCATE.

I had the idea we were doing that already --- at least I'm pretty sure I
remember it being discussed. But I see it's not being done in HEAD.

Patch to do it attached. I am thinking we can do something similar in
CLUSTER as well.

Applied.

--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.