Visibility map and freezing

The way VACUUM works with the visibility map is that if any pages are
skipped, relfrozenxid can't be updated. That means that plain VACUUM
won't advance relfrozenxid, and doesn't protect from XID wraparound.

We discussed this in the context of autovacuum before, and we have that
covered now. Autovacuum will launch a full-scanning vacuum that advances
relfrozenxid, when autovacuum_freeze_max_age is reached, and partial
vacuums otherwise.

Autovacuum will launch anti-wraparound vacuum even if it's otherwise
disabled. Which is good, but it'll be an unpleasant surprise for someone
who performs a simple manual database-wide "VACUUM", for example, every
night from a cron job. You could run VACUUM FREEZE, say monthly, to
force a full-scanning vacuum, but that's unnecessarily aggressive, and
you need to know about the issue to set that up in the first place.

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.

This ensures that you don't run into forced anti-wraparound autovacuums
if you do your VACUUMs manually.

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

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):
> The way VACUUM works with the visibility map is that if any pages are
> skipped, relfrozenxid can't be updated. That means that plain VACUUM
> won't advance relfrozenxid, and doesn't protect from XID wraparound.
>
> We discussed this in the context of autovacuum before, and we have that
> covered now. Autovacuum will launch a full-scanning vacuum that advances
> relfrozenxid, when autovacuum_freeze_max_age is reached, and partial
> vacuums otherwise.
>
> Autovacuum will launch anti-wraparound vacuum even if it's otherwise
> disabled. Which is good, but it'll be an unpleasant surprise for someone
> who performs a simple manual database-wide "VACUUM", for example, every
> night from a cron job. You could run VACUUM FREEZE, say monthly, to
> force a full-scanning vacuum, but that's unnecessarily aggressive, and
> you need to know about the issue to set that up in the first place.
>
> 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.
>
> This ensures that you don't run into forced anti-wraparound autovacuums
> if you do your VACUUMs manually.
>

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(dot)Kotala(at)Sun(dot)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(dot)linnakangas(at)enterprisedb(dot)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(dot)linnakangas(at)enterprisedb(dot)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(at)enterprisedb(dot)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(at)enterprisedb(dot)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

Simon Riggs wrote:
> 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.

That sounds pretty complex, approaching what the planner does. I'd
rather keep it simple.

Attached is a simple patch to only start skipping pages after 20
consecutive pages marked as visible in the visibility map. This doesn't
do any "look-ahead", so it will always scan the first 20 pages of a
table before it starts to skip pages, and whenever there's even one page
that needs vacuuming, the next 19 pages will also be vacuumed.

We could adjust that figure 20 according to table size. Or by
seq_page_cost/random_page_cost. But I'm leaning towards a simple
hard-coded value for now.

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

On Tue, 2009-01-20 at 21:51 +0200, Heikki Linnakangas wrote:

> Attached is a simple patch to only start skipping pages after 20
> consecutive pages marked as visible in the visibility map. This doesn't
> do any "look-ahead", so it will always scan the first 20 pages of a
> table before it starts to skip pages, and whenever there's even one page
> that needs vacuuming, the next 19 pages will also be vacuumed.
>
> We could adjust that figure 20 according to table size. Or by
> seq_page_cost/random_page_cost. But I'm leaning towards a simple
> hard-coded value for now.

Yes, sounds good. Can we stick to multiples of 2 as the OS readahead
does IIRC? So either 16 or 32. I'd go 32.

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

Simon Riggs escreveu:
> On Tue, 2009-01-20 at 21:51 +0200, Heikki Linnakangas wrote:
>
>> Attached is a simple patch to only start skipping pages after 20
>> consecutive pages marked as visible in the visibility map. This doesn't
>> do any "look-ahead", so it will always scan the first 20 pages of a
>> table before it starts to skip pages, and whenever there's even one page
>> that needs vacuuming, the next 19 pages will also be vacuumed.
>>
>> We could adjust that figure 20 according to table size. Or by
>> seq_page_cost/random_page_cost. But I'm leaning towards a simple
>> hard-coded value for now.
>
> Yes, sounds good. Can we stick to multiples of 2 as the OS readahead
> does IIRC? So either 16 or 32. I'd go 32.
>
Agreed. And do it a constant (ALL_VISIBLE_VM_THRESHOLD?).

--
Euler Taveira de Oliveira
http://www.timbira.com/

Heikki Linnakangas wrote:
> ITAGAKI Takahiro wrote:
>> - 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.

Ok, how does this sound:

<para>
+ <command>VACUUM</> normally skips pages that don't have any dead row
+ versions, but those pages might still have tuples with old XID values.
+ To replace them too, a scan of the whole table is needed every once
+ in a while. <varname>vacuum_freeze_table_age</> controls when
+ <command>VACUUM</> does that: a whole table sweep is forced if
+ <structfield>relfrozenxid</> is more than
+ <varname>vacuum_freeze_table_age</> transactions old. Setting it to 0
+ makes <command>VACUUM</> to ignore the visibility map and always
scan all
+ pages. The effective maximum is 0.95 *
<varname>autovacuum_freeze_max_age</>;
+ a setting higher than that will be capped to that maximum. A value
+ higher than <varname>autovacuum_freeze_max_age</> wouldn't make sense
+ because an anti-wraparound autovacuum would be triggered at that point
+ anyway, and the 0.95 multiplier leaves some breathing room to run a
manual
+ <command>VACUUM</> before that happens. As a rule of thumb,
+ <command>vacuum_freeze_table_age</> should be set to a value somewhat
+ below <varname>autovacuum_freeze_max_age</>. Setting it too close could
+ lead to anti-wraparound autovacuums, even though the table was recently
+ manually vacuumed, whereas lower values lead to more frequent
whole-table
+ scans.
+ </para>

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

Euler Taveira de Oliveira wrote:
> Simon Riggs escreveu:
>> On Tue, 2009-01-20 at 21:51 +0200, Heikki Linnakangas wrote:
>>
>>> Attached is a simple patch to only start skipping pages after 20
>>> consecutive pages marked as visible in the visibility map. This doesn't
>>> do any "look-ahead", so it will always scan the first 20 pages of a
>>> table before it starts to skip pages, and whenever there's even one page
>>> that needs vacuuming, the next 19 pages will also be vacuumed.
>>>
>>> We could adjust that figure 20 according to table size. Or by
>>> seq_page_cost/random_page_cost. But I'm leaning towards a simple
>>> hard-coded value for now.
>> Yes, sounds good. Can we stick to multiples of 2 as the OS readahead
>> does IIRC? So either 16 or 32. I'd go 32.
>>
> Agreed. And do it a constant (ALL_VISIBLE_VM_THRESHOLD?).

Okay-dokay. I committed this with the constant as a #define, at value 32.

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