Обсуждение: Default setting for autovacuum_freeze_max_age
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 +
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
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 +
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
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)
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
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)
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