Re: [HACKERS] Default setting for autovacuum_freeze_max_age
On Thu, Oct 27, 2016 at 6:55 AM, Josh Berkus wrote: > On 10/21/2016 10:29 AM, Robert Haas wrote: >> On Fri, Oct 21, 2016 at 1:17 PM, Josh Berkus 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_3myfcke2trkxk...@mail.gmail.com -- Thomas Munro http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Default setting for autovacuum_freeze_max_age
On 10/21/2016 10:29 AM, Robert Haas wrote: > On Fri, Oct 21, 2016 at 1:17 PM, Josh Berkus 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) -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Default setting for autovacuum_freeze_max_age
On Fri, Oct 21, 2016 at 1:17 PM, Josh Berkus 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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Default setting for autovacuum_freeze_max_age
On 10/21/2016 07:44 AM, Tom Lane wrote: > Bruce Momjian 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) -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Default setting for autovacuum_freeze_max_age
Bruce Momjian wrote: > On Fri, Oct 21, 2016 at 10:44:41AM -0400, Tom Lane wrote: > > Bruce Momjian 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 Herrerahttps://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Default setting for autovacuum_freeze_max_age
On Fri, Oct 21, 2016 at 10:44:41AM -0400, Tom Lane wrote: > Bruce Momjian 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 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 + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Default setting for autovacuum_freeze_max_age
Bruce Momjian 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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
