Visibility map and freezing

What's about add second bit which mark frozen page (all tuples have freeze XID)?
It should avoid full scan, but extend size of map.

Zdenek

Heikki Linnakangas napsal(a):

Zdenek Kotala wrote:

What's about add second bit which mark frozen page (all tuples have
freeze XID)? It should avoid full scan, but extend size of map.

First of all, we'd still have to make the decision of when to scan pages
that need freezing, and when to only scan pages that have dead tuples.

Secondly, if it's just one bit, we'd have to freeze all tuples on the
page to set the bit, which is a lot more aggressive than we do now.

Thirdly, those frozen bits would be critical, not just hints like the
visibility map is right now. Corrupt frozen bits would lead to data
loss. That means we'd have to solve the problem of how to make sure the
bits are always cleared when tuples are updated on a page. That's not
completely crash-proof at the moment, see comments in visibilitymap.c.
Even if we did solve that, I'd rather live with a hints-only visibility
map for one release, before we take the plunge and make it a critical
part. Just in case...

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

Zdenek Kotala <Zdenek.Kotala@Sun.COM> writes:

What's about add second bit which mark frozen page (all tuples have freeze
XID)? It should avoid full scan, but extend size of map.

That would only really work if you have a very static table where entire pages
get frozen and stay frozen long before the freeze_max_age is reached. Even
that wouldn't really work because the partial vacuums would never see those
pages.

One option is to keep a frozenxid per page which would allow us to visit only
pages that need freezing.

A more complex scheme would be to have a bit which indicates that all
non-frozen xids are > relfrozenxid+100M. When we find all the bits set we can
clear them all and bump relfrozenxid by 100M. This would allow regular partial
vacuums to gradually move the frozenxid forward.

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Ask me about EnterpriseDB's 24x7 Postgres support!

Heikki Linnakangas wrote:

I think we need a threshold similar to autovacuum_freeze_max_age for
manual vacuums as well: vacuum_freeze_max_age. If you run VACUUM, and
relfrozenxid is older than vacuum_freeze_max_age, the visibility map is
ignored and all pages are scanned.

Would one parameter to control both suffice? (i.e., rename
autovacuum_freeze_max_age to vacuum_freeze_max_age)

Peter Eisentraut wrote:

Heikki Linnakangas wrote:

I think we need a threshold similar to autovacuum_freeze_max_age for
manual vacuums as well: vacuum_freeze_max_age. If you run VACUUM, and
relfrozenxid is older than vacuum_freeze_max_age, the visibility map
is ignored and all pages are scanned.

Would one parameter to control both suffice? (i.e., rename
autovacuum_freeze_max_age to vacuum_freeze_max_age)

Imagine that you run a nightly VACUUM from cron, and have autovacuum
disabled. If autovacuum_freeze_max_age is the same as
vacuum_freeze_max_age, as soon as that age is reached, an
anti-wraparound autovacuum launched. What we'd want to happen is for the
next nightly VACUUM to do the work instead. So they need to be separate
settings, with some space between them by default.

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

Heikki Linnakangas wrote:

Peter Eisentraut wrote:

Heikki Linnakangas wrote:

I think we need a threshold similar to autovacuum_freeze_max_age for
manual vacuums as well: vacuum_freeze_max_age. If you run VACUUM, and
relfrozenxid is older than vacuum_freeze_max_age, the visibility map
is ignored and all pages are scanned.

Would one parameter to control both suffice? (i.e., rename
autovacuum_freeze_max_age to vacuum_freeze_max_age)

Imagine that you run a nightly VACUUM from cron, and have autovacuum
disabled. If autovacuum_freeze_max_age is the same as
vacuum_freeze_max_age, as soon as that age is reached, an
anti-wraparound autovacuum launched. What we'd want to happen is for the
next nightly VACUUM to do the work instead. So they need to be separate
settings, with some space between them by default.

Attached is a proposed patch to handle freezing. In a nutshell:

Introduce new vacuum_freeze_max_age setting. Manual VACUUM will scan the
whole table and advance relfrozenxid, if relfrozenxid is older than
vacuum_freeze_max_age.

If vacuum_freeze_max_age is greater than autovacuum_freeze_max_age - 1
million transactions, it's effectively capped at that value. It doesn't
make sense to have vacuum_freeze_max_age > autovacuum_freeze_max_age,
because the manual VACUUM wouldn't have a chance to do the full sweep
before the anti-wraparound autovacuum is launched. The "minus one
million transactions" is to give some headroom.

I'm lowering vacuum_freeze_min_age from 100 million transactions to 50
million, so that the whole-table vacuum doesn't need to run as often.
Note that since VACUUM normally only scans pages that need vacuuming
according to the visibility map, tuples on skipped pages are not frozen
any earlier even though vacuum_freeze_min_age is lower.

To recap, here's the new defaults:
autovacuum_freeze_max_age 200000000
vacuum_freeze_max_age 150000000
vacuum_freeze_min_age 50000000

This means that with defaults, autovacuum will launch a whole-table
vacuum every 150 million transactions (autovacuum_freeze_max_age -
vacuum_freeze_min_age). If you run a nightly manual VACUUM, you'll get a
whole-table vacuum every 100 million transactions.

vacuum_freeze_max_age also affects autovacuums. If an autovacuum is
launched on table to remove dead tuples, and vacuum_freeze_max_age has
been reached (but not yet autovacuum_freeze_max_age), the autovacuum
will scan the whole table. I'm not sure if this is desirable, to avoid
having to launch separate anti-wraparound autovacuums even when there's
not many dead tuples, or just confusing.

If you set vacuum_freeze_max_age to 0, the visibility map is not used to
skip pages, so you'll get the pre-8.4 old behavior.

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

On Tue, Dec 23, 2008 at 4:24 AM, Heikki Linnakangas
<heikki.linnakangas@enterprisedb.com> wrote:

Heikki Linnakangas wrote:

Peter Eisentraut wrote:

Heikki Linnakangas wrote:

I think we need a threshold similar to autovacuum_freeze_max_age for
manual vacuums as well: vacuum_freeze_max_age. If you run VACUUM, and
relfrozenxid is older than vacuum_freeze_max_age, the visibility map is
ignored and all pages are scanned.

Would one parameter to control both suffice? (i.e., rename
autovacuum_freeze_max_age to vacuum_freeze_max_age)

Imagine that you run a nightly VACUUM from cron, and have autovacuum
disabled. If autovacuum_freeze_max_age is the same as vacuum_freeze_max_age,
as soon as that age is reached, an anti-wraparound autovacuum launched. What
we'd want to happen is for the next nightly VACUUM to do the work instead.
So they need to be separate settings, with some space between them by
default.

Attached is a proposed patch to handle freezing. In a nutshell:

Introduce new vacuum_freeze_max_age setting. Manual VACUUM will scan the
whole table and advance relfrozenxid, if relfrozenxid is older than
vacuum_freeze_max_age.

If vacuum_freeze_max_age is greater than autovacuum_freeze_max_age - 1
million transactions, it's effectively capped at that value. It doesn't make
sense to have vacuum_freeze_max_age > autovacuum_freeze_max_age, because the
manual VACUUM wouldn't have a chance to do the full sweep before the
anti-wraparound autovacuum is launched. The "minus one million transactions"
is to give some headroom.

I'm lowering vacuum_freeze_min_age from 100 million transactions to 50
million, so that the whole-table vacuum doesn't need to run as often. Note
that since VACUUM normally only scans pages that need vacuuming according to
the visibility map, tuples on skipped pages are not frozen any earlier even
though vacuum_freeze_min_age is lower.

To recap, here's the new defaults:
autovacuum_freeze_max_age 200000000
vacuum_freeze_max_age 150000000
vacuum_freeze_min_age 50000000

This means that with defaults, autovacuum will launch a whole-table vacuum
every 150 million transactions (autovacuum_freeze_max_age -
vacuum_freeze_min_age). If you run a nightly manual VACUUM, you'll get a
whole-table vacuum every 100 million transactions.

vacuum_freeze_max_age also affects autovacuums. If an autovacuum is launched
on table to remove dead tuples, and vacuum_freeze_max_age has been reached
(but not yet autovacuum_freeze_max_age), the autovacuum will scan the whole
table. I'm not sure if this is desirable, to avoid having to launch separate
anti-wraparound autovacuums even when there's not many dead tuples, or just
confusing.

If you set vacuum_freeze_max_age to 0, the visibility map is not used to
skip pages, so you'll get the pre-8.4 old behavior.

It seems to be strange that "max" can be less than "min". Is it worth dividing
a parameter into two(min/max)?

Regards,

--
Fujii Masao
NIPPON TELEGRAPH AND TELEPHONE CORPORATION
NTT Open Source Software Center

On Mon, 2008-12-22 at 21:24 +0200, Heikki Linnakangas wrote:

Introduce new vacuum_freeze_max_age setting. Manual VACUUM will scan the
whole table and advance relfrozenxid, if relfrozenxid is older than
vacuum_freeze_max_age.

It's confusing to have two GUCs named vacuum_freeze_min_age and
vacuum_freeze_max_age with incompatible definitions. The former is the
minimum transaction age of a tuple found during the scan of a table,
while the latter is the maximum transaction age of the relfrozenxid of
the table.

If you set vacuum_freeze_max_age to 0, the visibility map is not used to
skip pages, so you'll get the pre-8.4 old behavior.

Seems like a strange way to turn off visibility maps, and the meaning
doesn't seem to fit with either vacuum_freeze_min_age or
autovacuum_freeze_max_age.

The proposal itself makes sense, but I think we need to decide on some
better names. Right now the meanings of autovacuum_freeze_max_age and
vacuum_freeze_min_age are incompatible, so we're not in a good position,
but there has to be something less confusing.

For one thing, there isn't even a common definition of "min" or "max"
between them. They both trigger an event (freezing or vacuuming) when
something (tuple xmin or relfrozenxid) exceeds some number. Why is one
called a min and the other a max?

Regards,
Jeff Davis

Jeff Davis wrote:

On Mon, 2008-12-22 at 21:24 +0200, Heikki Linnakangas wrote:

Introduce new vacuum_freeze_max_age setting. Manual VACUUM will scan the
whole table and advance relfrozenxid, if relfrozenxid is older than
vacuum_freeze_max_age.

It's confusing to have two GUCs named vacuum_freeze_min_age and
vacuum_freeze_max_age with incompatible definitions. The former is the
minimum transaction age of a tuple found during the scan of a table,
while the latter is the maximum transaction age of the relfrozenxid of
the table.

If you set vacuum_freeze_max_age to 0, the visibility map is not used to
skip pages, so you'll get the pre-8.4 old behavior.

Seems like a strange way to turn off visibility maps, and the meaning
doesn't seem to fit with either vacuum_freeze_min_age or
autovacuum_freeze_max_age.

The proposal itself makes sense, but I think we need to decide on some
better names. Right now the meanings of autovacuum_freeze_max_age and
vacuum_freeze_min_age are incompatible, so we're not in a good position,
but there has to be something less confusing.

For one thing, there isn't even a common definition of "min" or "max"
between them. They both trigger an event (freezing or vacuuming) when
something (tuple xmin or relfrozenxid) exceeds some number. Why is one
called a min and the other a max?

Yeah, you're right. Fuji's point that it's confusing that you can have a
min greater than max is also valid.

How about

autovacuum_freeze_max_age -> autovacuum_freeze_scan_age
vacuum_freeze_max_age -> vacuum_freeze_scan_age
vacuum_freeze_min_age -> vacuum_freeze_tuple_age

*_scan_age settings control when the table is fully scanned to freeze
tuples and advance relfrozenxid, and vacuum_freeze_tuple_age controls
how old a tuple needs to be to be frozen. One objection is that you can
read "freeze_scan" to mean that a scan is frozen, like a tuple is
frozen. Any better ideas?

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

On Wed, 2009-01-07 at 09:34 +0200, Heikki Linnakangas wrote:

autovacuum_freeze_max_age -> autovacuum_freeze_scan_age
vacuum_freeze_max_age -> vacuum_freeze_scan_age
vacuum_freeze_min_age -> vacuum_freeze_tuple_age

*_scan_age settings control when the table is fully scanned to freeze
tuples and advance relfrozenxid, and vacuum_freeze_tuple_age controls
how old a tuple needs to be to be frozen. One objection is that you can
read "freeze_scan" to mean that a scan is frozen, like a tuple is
frozen. Any better ideas?

I see what you mean about the possible misinterpretation, but I think
it's a big improvement, and I don't have a better suggestion.

Thanks,
Jeff Davis

Jeff Davis wrote:

On Wed, 2009-01-07 at 09:34 +0200, Heikki Linnakangas wrote:

autovacuum_freeze_max_age -> autovacuum_freeze_scan_age
vacuum_freeze_max_age -> vacuum_freeze_scan_age
vacuum_freeze_min_age -> vacuum_freeze_tuple_age

*_scan_age settings control when the table is fully scanned to freeze
tuples and advance relfrozenxid, and vacuum_freeze_tuple_age controls
how old a tuple needs to be to be frozen. One objection is that you can
read "freeze_scan" to mean that a scan is frozen, like a tuple is
frozen. Any better ideas?

I see what you mean about the possible misinterpretation, but I think
it's a big improvement, and I don't have a better suggestion.

Thinking about this some more, I'm not too happy with those names
either. vacuum_freeze_scan_age and autovacuum_freeze_scan_age don't mean
quite the same thing, like vacuum_cost_delay and
autovacuum_vacuum_cost_delay do, for example.

I'm now leaning towards:

autovacuum_freeze_max_age
vacuum_freeze_table_age
vacuum_freeze_min_age

where autovacuum_freeze_max_age and vacuum_freeze_min_age are unchanged,
and vacuum_freeze_table_age is the new setting that controls when VACUUM
or autovacuum should perform a full scan of the table to advance
relfrozenxid.

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

On Fri, 2009-01-09 at 13:49 +0200, Heikki Linnakangas wrote:

Thinking about this some more, I'm not too happy with those names
either. vacuum_freeze_scan_age and autovacuum_freeze_scan_age don't mean
quite the same thing, like vacuum_cost_delay and
autovacuum_vacuum_cost_delay do, for example.

If the distinction you're making is that autovacuum_freeze_max_age
affects the launching of a vacuum rather than the behavior of a vacuum,
maybe we could incorporate the word "launch" like:

autovacuum_launch_freeze_threshold

I'm now leaning towards:

autovacuum_freeze_max_age
vacuum_freeze_table_age
vacuum_freeze_min_age

where autovacuum_freeze_max_age and vacuum_freeze_min_age are unchanged,
and vacuum_freeze_table_age is the new setting that controls when VACUUM
or autovacuum should perform a full scan of the table to advance
relfrozenxid.

I'm still bothered by the fact that "max" and "min" really mean the same
thing here.

I don't think we can perfectly capture the meaning of these GUCs in the
name. I think our goal should be to avoid confusion between them.

Regards,
Jeff Davis

Jeff Davis wrote:

On Fri, 2009-01-09 at 13:49 +0200, Heikki Linnakangas wrote:
If the distinction you're making is that autovacuum_freeze_max_age
affects the launching of a vacuum rather than the behavior of a vacuum,
maybe we could incorporate the word "launch" like:

autovacuum_launch_freeze_threshold

Hmm, I think I'd like it in the form autovacuum_freeze_launch_threshold
more.

I'm now leaning towards:

autovacuum_freeze_max_age
vacuum_freeze_table_age
vacuum_freeze_min_age

where autovacuum_freeze_max_age and vacuum_freeze_min_age are unchanged,
and vacuum_freeze_table_age is the new setting that controls when VACUUM
or autovacuum should perform a full scan of the table to advance
relfrozenxid.

I'm still bothered by the fact that "max" and "min" really mean the same
thing here.

Yeah. Those are existing names, though, and I don't recall any
complaints from users.

I don't think we can perfectly capture the meaning of these GUCs in the
name. I think our goal should be to avoid confusion between them.

Agreed.

Well, for better or worse here's a patch leaving the existing setting
names alone, and the new GUC is called "vacuum_freeze_table_age". I'm
not opposed to changing the names of the existing GUCs. If we do change
them, the columns in pg_autovacuum need to be changed too.

There's documentation changes included to descr�be the new GUC, and the
new behavior of VACUUM with visibility map in general. Is it readable,
and is it enough?

Alvaro, are you getting rid of pg_autovacuum in favor of the reloptions
for 8.4? This patch adds a new column to pg_autovacuum, reflecting the
new vacuum_freeze_table_age GUC just like freeze_min_age column reflects
vacuum_freeze_min_age. If I commit this tomorrow, will I cause you a lot
of trouble with the reloptions patch?

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

Heikki Linnakangas escribi�:

Alvaro, are you getting rid of pg_autovacuum in favor of the reloptions
for 8.4?

That's the intention, yes.

This patch adds a new column to pg_autovacuum, reflecting the new
vacuum_freeze_table_age GUC just like freeze_min_age column reflects
vacuum_freeze_min_age. If I commit this tomorrow, will I cause you a
lot of trouble with the reloptions patch?

No -- go ahead, I'll merge it.

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

Heikki Linnakangas <heikki.linnakangas@enterprisedb.com> writes:

Jeff Davis wrote:

On Fri, 2009-01-09 at 13:49 +0200, Heikki Linnakangas wrote:

I'm now leaning towards:

autovacuum_freeze_max_age
vacuum_freeze_table_age
vacuum_freeze_min_age

where autovacuum_freeze_max_age and vacuum_freeze_min_age are unchanged, and
vacuum_freeze_table_age is the new setting that controls when VACUUM or
autovacuum should perform a full scan of the table to advance relfrozenxid.

I'm still bothered by the fact that "max" and "min" really mean the same
thing here.

Yeah. Those are existing names, though, and I don't recall any complaints from
users.

I don't think we can perfectly capture the meaning of these GUCs in the
name. I think our goal should be to avoid confusion between them.

Agreed.

I was thinking it would be clearer if the options which control *when*
autovacuum fires off a worker consistently had some action word in them like
"trigger" or "start" or "launch".

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Ask me about EnterpriseDB's Slony Replication support!

Alvaro Herrera wrote:

Heikki Linnakangas escribi�:

This patch adds a new column to pg_autovacuum, reflecting the new
vacuum_freeze_table_age GUC just like freeze_min_age column reflects
vacuum_freeze_min_age. If I commit this tomorrow, will I cause you a
lot of trouble with the reloptions patch?

No -- go ahead, I'll merge it.

Ok, committed.

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

Gregory Stark <stark@enterprisedb.com> wrote:

I don't think we can perfectly capture the meaning of these GUCs in the
name. I think our goal should be to avoid confusion between them.

I was thinking it would be clearer if the options which control *when*
autovacuum fires off a worker consistently had some action word in them like
"trigger" or "start" or "launch".

I think we need more explanations about those variables,
not only "how to work" but also "how to tune" them.
I feel they are un-tunable parameters.

Our documentation says:
| Larger values of these settings
| preserve transactional information longer, while smaller values increase
| the number of transactions that can elapse before the table must be
| vacuumed again.
i.e, we are explaining the variables only as "Larger is better",
but is it really true?

I think we should have answers about the following questions:

- What relation are there between autovacuum_freeze_max_age,
vacuum_freeze_min_age and vacuum_freeze_table_age? If we increase
one of them, should we also increase the others?

- Is it ok to increase the variables to maximum values?
Are there any trade-off?

- Are there some conditions where whole-table-scanning vacuum is more
effective than vacuums using visibility map? If so, we should switch
to full-scan *automatically*, without relying on user configurations.

Regards,
---
ITAGAKI Takahiro
NTT Open Source Software Center

ITAGAKI Takahiro wrote:

Gregory Stark <stark@enterprisedb.com> wrote:

I don't think we can perfectly capture the meaning of these GUCs in the
name. I think our goal should be to avoid confusion between them.

I was thinking it would be clearer if the options which control *when*
autovacuum fires off a worker consistently had some action word in them like
"trigger" or "start" or "launch".

I think we need more explanations about those variables,
not only "how to work" but also "how to tune" them.
I feel they are un-tunable parameters.

Our documentation says:
| Larger values of these settings
| preserve transactional information longer, while smaller values increase
| the number of transactions that can elapse before the table must be
| vacuumed again.
i.e, we are explaining the variables only as "Larger is better",
but is it really true?

Yes, that is explicitly explained in the docs:

The sole disadvantage of increasing <varname>vacuum_freeze_table_age</>
and <varname>autovacuum_freeze_max_age</>
is that the <filename>pg_clog</> subdirectory of the database cluster
will take more space, because it must store the commit status for all
transactions back to the <varname>autovacuum_freeze_max_age</> horizon.
...

- What relation are there between autovacuum_freeze_max_age,
vacuum_freeze_min_age and vacuum_freeze_table_age? If we increase
one of them, should we also increase the others?

Yeah, that's a fair question. I'll try to work a doc patch to explain
that better.

- Is it ok to increase the variables to maximum values?
Are there any trade-off?

Disk space.

- Are there some conditions where whole-table-scanning vacuum is more
effective than vacuums using visibility map? If so, we should switch
to full-scan *automatically*, without relying on user configurations.

Hmm, the only downside I can see is that skipping a page here and there
could defeat the OS read-ahead. Perhaps we should call
posix_fadvise(SEQUENTIAL) to compensate. Or, we could modify the logic
to only skip pages when there's at least N consecutive pages that can be
skipped.

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

On Tue, 2009-01-20 at 11:03 +0200, Heikki Linnakangas wrote:

- Are there some conditions where whole-table-scanning vacuum is

more

effective than vacuums using visibility map? If so, we should

switch

to full-scan *automatically*, without relying on user

configurations.

Hmm, the only downside I can see is that skipping a page here and
there could defeat the OS read-ahead. Perhaps we should call
posix_fadvise(SEQUENTIAL) to compensate. Or, we could modify the logic
to only skip pages when there's at least N consecutive pages that can
be skipped.

I would rather we didn't skip any pages at all unless the gains are
significant. Skipping the odd page makes no difference from a
performance perspective but may have a robustness impact.

"Significant gains" should take into account the size of both heap and
indexes, and recognise that we still scan whole indexes in either case.

--
Simon Riggs www.2ndQuadrant.com
PostgreSQL Training, Services and Support