Horton
Menu

Thread

  1. Default setting for autovacuum_freeze_max_age

    Bruce Momjian <bruce@momjian.us> — 2016-10-21T13:19:34Z

    Why is autovacuum_freeze_max_age's default set to 200 million, rather
than something like 2 billion?  It seems 2 billion is half way to
wrap-around and would be a better default.  Right now, the default seems
to freeze 10x more often than it has to.

Does it default to 200 million so clog can be trimmed?  Is that
reasonable?  We have tuple status flags of commit status so I assume
changing from a normal xid to a frozen one doesn't have a performance
benefit, does it?

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

+ As you are, so once was I.  As I am, so you will be. +
+                      Ancient Roman grave inscription +

  2. Re: Default setting for autovacuum_freeze_max_age

    Tom Lane <tgl@sss.pgh.pa.us> — 2016-10-21T14:44:41Z

    Bruce Momjian <bruce@momjian.us> writes:
> Why is autovacuum_freeze_max_age's default set to 200 million, rather
> than something like 2 billion?  It seems 2 billion is half way to
> wrap-around and would be a better default.  Right now, the default seems
> to freeze 10x more often than it has to.

Please see the archives.  I do not remember the reasoning, but there
was some, and you need to justify why it was wrong not just assert
that you think it's silly.

			regards, tom lane

  3. Re: Default setting for autovacuum_freeze_max_age

    Bruce Momjian <bruce@momjian.us> — 2016-10-21T15:03:00Z

    On Fri, Oct 21, 2016 at 10:44:41AM -0400, Tom Lane wrote:
> Bruce Momjian <bruce@momjian.us> writes:
> > Why is autovacuum_freeze_max_age's default set to 200 million, rather
> > than something like 2 billion?  It seems 2 billion is half way to
> > wrap-around and would be a better default.  Right now, the default seems
> > to freeze 10x more often than it has to.
> 
> Please see the archives.  I do not remember the reasoning, but there
> was some, and you need to justify why it was wrong not just assert
> that you think it's silly.

I think the reasoning was to avoid checking old clog files, but with
tuple transaction status bits, e.g. HEAP_XMIN_COMMITTED, which were
added long ago, I don't remember why either.  I remember asking years
ago and not getting a good answer, and giving up.

If no one can give an answer, I suggest we change the default.

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

+ As you are, so once was I.  As I am, so you will be. +
+                      Ancient Roman grave inscription +

  4. Re: Default setting for autovacuum_freeze_max_age

    Alvaro Herrera <alvherre@2ndquadrant.com> — 2016-10-21T16:55:50Z

    Bruce Momjian wrote:
> On Fri, Oct 21, 2016 at 10:44:41AM -0400, Tom Lane wrote:
> > Bruce Momjian <bruce@momjian.us> writes:
> > > Why is autovacuum_freeze_max_age's default set to 200 million, rather
> > > than something like 2 billion?  It seems 2 billion is half way to
> > > wrap-around and would be a better default.  Right now, the default seems
> > > to freeze 10x more often than it has to.
> > 
> > Please see the archives.  I do not remember the reasoning, but there
> > was some, and you need to justify why it was wrong not just assert
> > that you think it's silly.
> 
> I think the reasoning was to avoid checking old clog files, but with
> tuple transaction status bits, e.g. HEAP_XMIN_COMMITTED, which were
> added long ago, I don't remember why either.

HEAP_XMIN_COMMITTED existed way before autovacuum, so that doesn't add
up, does it.  As I recall, the reason was to be able to truncate
pg_clog.  I suppose nowadays it's possible to claim that we're not
really bothered by a gigabyte or two of pg_clog?

*If* we're to raise the default then it should not be to 2 billion.
That gives users no breathing room if they find themselves struggling
with the freezing; with the current default, it's possible to increase
it 2x or 4x if you're in trouble, which gives some breathing room until
a permanent solution is found (better vacuuming).  That disappears if
you set the max to its max.

> I remember asking years ago and not getting a good answer, and giving
> up.

[citation needed]

-- 
Álvaro Herrera                https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

  5. Re: Default setting for autovacuum_freeze_max_age

    Josh Berkus <josh@agliodbs.com> — 2016-10-21T17:17:08Z

    On 10/21/2016 07:44 AM, Tom Lane wrote:
> Bruce Momjian <bruce@momjian.us> writes:
>> Why is autovacuum_freeze_max_age's default set to 200 million, rather
>> than something like 2 billion?  It seems 2 billion is half way to
>> wrap-around and would be a better default.  Right now, the default seems
>> to freeze 10x more often than it has to.
> 
> Please see the archives.  I do not remember the reasoning, but there
> was some, and you need to justify why it was wrong not just assert
> that you think it's silly.

IIRC, there were a couple reasons (and I think they're still good
reasons, which is why I haven't asked to change the default):

1. By setting it to 10% of the max space, we give users plenty of room
to raise the number if they need to without getting into crisis territory.

2. Raising this threshold isn't an unalloyed good.  The longer you wait
to freeze, the more work you'll need to do when autovac freeze rolls
around.  There's actually situations where you want to make this
threshold *lower*, although generally scheduled manual vacuum freezes
serve that.

Particularly, with 9.6's freeze map, point (2) is even stronger reason
to *lower* autovacuum_max_freeze_age.  Since there's little duplicate
work in a freeze scan, a lot of users will find that frequent freezing
benefits them a lot ... especially if they can take advantage of
index-only scans.

-- 
--
Josh Berkus
Red Hat OSAS
(any opinions are my own)

  6. Re: Default setting for autovacuum_freeze_max_age

    Robert Haas <robertmhaas@gmail.com> — 2016-10-21T17:29:42Z

    On Fri, Oct 21, 2016 at 1:17 PM, Josh Berkus <josh@agliodbs.com> wrote:
> Particularly, with 9.6's freeze map, point (2) is even stronger reason
> to *lower* autovacuum_max_freeze_age.  Since there's little duplicate
> work in a freeze scan, a lot of users will find that frequent freezing
> benefits them a lot ...

That's a very good point, although I hope that vacuum is mostly being
triggered by vacuum_freeze_table_age rather than
autovacuum_freeze_max_age.

On Bruce's original question, there is an answer written into our
documentation: "Vacuum also allows removal of old files from the
pg_clog subdirectory, which is why the default is a relatively low 200
million transactions."

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

  7. Re: Default setting for autovacuum_freeze_max_age

    Josh Berkus <josh@agliodbs.com> — 2016-10-26T17:55:50Z

    On 10/21/2016 10:29 AM, Robert Haas wrote:
> On Fri, Oct 21, 2016 at 1:17 PM, Josh Berkus <josh@agliodbs.com> wrote:
>> Particularly, with 9.6's freeze map, point (2) is even stronger reason
>> to *lower* autovacuum_max_freeze_age.  Since there's little duplicate
>> work in a freeze scan, a lot of users will find that frequent freezing
>> benefits them a lot ...
> 
> That's a very good point, although I hope that vacuum is mostly being
> triggered by vacuum_freeze_table_age rather than
> autovacuum_freeze_max_age.

Well, depends on the nature of writes to the table.  For insert-mostly
tables, vacuum_freeze_table_age is pretty much never triggered.  Isn't
there a patch for that somewhere?

> 
> On Bruce's original question, there is an answer written into our
> documentation: "Vacuum also allows removal of old files from the
> pg_clog subdirectory, which is why the default is a relatively low 200
> million transactions."

Point.


-- 
--
Josh Berkus
Red Hat OSAS
(any opinions are my own)

  8. Re: Default setting for autovacuum_freeze_max_age

    Thomas Munro <thomas.munro@enterprisedb.com> — 2016-10-26T19:46:14Z

    On Thu, Oct 27, 2016 at 6:55 AM, Josh Berkus <josh@agliodbs.com> wrote:
> On 10/21/2016 10:29 AM, Robert Haas wrote:
>> On Fri, Oct 21, 2016 at 1:17 PM, Josh Berkus <josh@agliodbs.com> wrote:
>>> Particularly, with 9.6's freeze map, point (2) is even stronger reason
>>> to *lower* autovacuum_max_freeze_age.  Since there's little duplicate
>>> work in a freeze scan, a lot of users will find that frequent freezing
>>> benefits them a lot ...
>>
>> That's a very good point, although I hope that vacuum is mostly being
>> triggered by vacuum_freeze_table_age rather than
>> autovacuum_freeze_max_age.
>
> Well, depends on the nature of writes to the table.  For insert-mostly
> tables, vacuum_freeze_table_age is pretty much never triggered.  Isn't
> there a patch for that somewhere?
>
>>
>> On Bruce's original question, there is an answer written into our
>> documentation: "Vacuum also allows removal of old files from the
>> pg_clog subdirectory, which is why the default is a relatively low 200
>> million transactions."
>
> Point.

It also affects pg_commit_ts size (if enabled), and it uses 40x more
space per xid than pg_clog if I've read the code right.  I have
wondered before[1] if we should document that.

[1] https://www.postgresql.org/message-id/CAEepm=3PM05_T__3PSXBacDLm7WwMYrbR_3mYFcKE2tRkXK8LQ@mail.gmail.com

-- 
Thomas Munro
http://www.enterprisedb.com