The suggestion of reducing autovacuum_vacuum_cost_delay should be documented
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/11/runtime-config-autovacuum.html Description: The `autovacuum_vacuum_cost_delay` setting changes to 2ms in PostgreSQL 12 but in the old Postgresql version, the default setting is still 20ms. I would suggest adding a suggestion in the old document to lower the autovacuum_vacuum_cost_delay as: > The default value of autovacuum_vacuum_cost_delay is reduced to 2ms in PostgreSQL 12. Reducing the autovacuum_vacuum_cost_delay will make the autovacuum more aggressive and might reduce the vacuum cost for write-intensive workload on big table. Detail: I run the PostgreSQL 11 on a table with 1 million record under a write-instensive workload by sysbench. The result shows that when the autovacuum_vacuum_cost_delay is 20ms, the automatic vacuum takes 15 seconds. If I reduce the autovacuum_vacuum_cost_delay to 2ms, the automatic vacuum just uses 3 seconds. When autovacuum_vacuum_cost_delay = 20ms LOG: automatic vacuum of table "test.public.sbtest1": index scans: 1 pages: 0 removed, 27172 remain, 0 skipped due to pins, 0 skipped frozen tuples: 2787 removed, 993634 remain, 387 are dead but not yet removable, oldest xmin: 1358042 buffer usage: 21557 hits, 9160 misses, 1262 dirtied avg read rate: 4.783 MB/s, avg write rate: 0.659 MB/s system usage: CPU: user: 1.21 s, system: 0.04 s, elapsed: 14.96 s When autovacuum_vacuum_cost_delay = 2ms LOG: automatic vacuum of table "test.public.sbtest1": index scans: 1 pages: 0 removed, 27172 remain, 0 skipped due to pins, 0 skipped frozen tuples: 2817 removed, 994303 remain, 85 are dead but not yet removable, oldest xmin: 1329367 buffer usage: 23129 hits, 6987 misses, 3873 dirtied avg read rate: 20.532 MB/s, avg write rate: 11.381 MB/s system usage: CPU: user: 0.92 s, system: 0.02 s, elapsed: 2.65 s
dot dot dot dot
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/12/tutorial-sql-intro.html Description: is wrong.
https://www.postgresql.org/docs/11/typeconv-union-case.html
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/11/typeconv-union-case.html Description: I think it should be CAST instead of CASE.
Re: https://www.postgresql.org/docs/11/typeconv-union-case.html
On Tuesday, May 12, 2020, PG Doc comments form wrote: > The following documentation comment has been logged on the website: > > Page: https://www.postgresql.org/docs/11/typeconv-union-case.html > Description: > > I think it should be CAST instead of CASE. > Why? There no “process” needed to perform type conversion using cast. David J.
Re: The suggestion of reducing autovacuum_vacuum_cost_delay should be documented
On Tue, May 12, 2020 at 10:26:27AM +, PG Doc comments form wrote: > The following documentation comment has been logged on the website: > > Page: https://www.postgresql.org/docs/11/runtime-config-autovacuum.html > Description: > > The `autovacuum_vacuum_cost_delay` setting changes to 2ms in PostgreSQL 12 > but in the old Postgresql version, the default setting is still 20ms. I > would suggest adding a suggestion in the old document > to lower the autovacuum_vacuum_cost_delay as: > > > The default value of autovacuum_vacuum_cost_delay is reduced to 2ms in > PostgreSQL 12. Reducing the autovacuum_vacuum_cost_delay will make the > autovacuum more aggressive and might reduce the vacuum cost for > write-intensive workload on big table. Uh, we usually don't suggest new defaults in back branches. -- Bruce Momjian https://momjian.us EnterpriseDB https://enterprisedb.com + As you are, so once was I. As I am, so you will be. + + Ancient Roman grave inscription +
Re: The suggestion of reducing autovacuum_vacuum_cost_delay should be documented
On Tue, May 12, 2020 at 05:40:14PM -0400, Bruce Momjian wrote: > On Tue, May 12, 2020 at 10:26:27AM +, PG Doc comments form wrote: > > The following documentation comment has been logged on the website: > > > > Page: https://www.postgresql.org/docs/11/runtime-config-autovacuum.html > > Description: > > > > The `autovacuum_vacuum_cost_delay` setting changes to 2ms in PostgreSQL 12 > > but in the old Postgresql version, the default setting is still 20ms. I > > would suggest adding a suggestion in the old document > > to lower the autovacuum_vacuum_cost_delay as: > > > > > The default value of autovacuum_vacuum_cost_delay is reduced to 2ms in > > PostgreSQL 12. Reducing the autovacuum_vacuum_cost_delay will make the > > autovacuum more aggressive and might reduce the vacuum cost for > > write-intensive workload on big table. > > Uh, we usually don't suggest new defaults in back branches. Basically, what I am saying is that if you want this, it would be a new behavior that would need general discussion. -- Bruce Momjian https://momjian.us EnterpriseDB https://enterprisedb.com + As you are, so once was I. As I am, so you will be. + + Ancient Roman grave inscription +
Re: The suggestion of reducing autovacuum_vacuum_cost_delay should be documented
On Tue, May 12, 2020 at 2:41 PM Bruce Momjian wrote: > On Tue, May 12, 2020 at 05:40:14PM -0400, Bruce Momjian wrote: > > On Tue, May 12, 2020 at 10:26:27AM +, PG Doc comments form wrote: > > > The following documentation comment has been logged on the website: > > > > > > Page: > https://www.postgresql.org/docs/11/runtime-config-autovacuum.html > > > Description: > > > > > > The `autovacuum_vacuum_cost_delay` setting changes to 2ms in > PostgreSQL 12 > > > but in the old Postgresql version, the default setting is still 20ms. I > > > would suggest adding a suggestion in the old document > > > to lower the autovacuum_vacuum_cost_delay as: > > > > > > > The default value of autovacuum_vacuum_cost_delay is reduced to 2ms > in > > > PostgreSQL 12. Reducing the autovacuum_vacuum_cost_delay will make the > > > autovacuum more aggressive and might reduce the vacuum cost for > > > write-intensive workload on big table. > > > > Uh, we usually don't suggest new defaults in back branches. > > Basically, what I am saying is that if you want this, it would be a new > behavior that would need general discussion. > > The proposal is to document in versions 9.4 to 11 that the recommended value for the setting is 2ms while for reasons of continuity the default in these versions is 20ms. I don't really see any harm in it. Its not like the choice to reduce the value was made because of new features introduced in 12 - it was a re-evaluation of a 15 year old default. David J.
Re: The suggestion of reducing autovacuum_vacuum_cost_delay should be documented
On Tue, May 12, 2020 at 03:38:16PM -0700, David G. Johnston wrote: > On Tue, May 12, 2020 at 2:41 PM Bruce Momjian wrote: > > On Tue, May 12, 2020 at 05:40:14PM -0400, Bruce Momjian wrote: > > On Tue, May 12, 2020 at 10:26:27AM +, PG Doc comments form wrote: > > > The following documentation comment has been logged on the website: > > > > > > Page: > https://www.postgresql.org/docs/11/runtime-config-autovacuum.html > > > Description: > > > > > > The `autovacuum_vacuum_cost_delay` setting changes to 2ms in > PostgreSQL > 12 > > > but in the old Postgresql version, the default setting is still 20ms. > I > > > would suggest adding a suggestion in the old document > > > to lower the autovacuum_vacuum_cost_delay as: > > > > > > > The default value of autovacuum_vacuum_cost_delay is reduced to 2ms > in > > > PostgreSQL 12. Reducing the autovacuum_vacuum_cost_delay will make the > > > autovacuum more aggressive and might reduce the vacuum cost for > > > write-intensive workload on big table. > > > > Uh, we usually don't suggest new defaults in back branches. > > Basically, what I am saying is that if you want this, it would be a new > behavior that would need general discussion. > > > > The proposal is to document in versions 9.4 to 11 that the recommended value > for the setting is 2ms while for reasons of continuity the default in these > versions is 20ms. > > I don't really see any harm in it. Its not like the choice to reduce the > value > was made because of new features introduced in 12 - it was a re-evaluation of > a > 15 year old default. Well, we really need to have some general discussion about whether changing defaults in major releases should trigger a mention to change the defaults in back branches. This is something that would have to be discussed on the hackers list. -- Bruce Momjian https://momjian.us EnterpriseDB https://enterprisedb.com + As you are, so once was I. As I am, so you will be. + + Ancient Roman grave inscription +
Re: The suggestion of reducing autovacuum_vacuum_cost_delay should be documented
Bruce Momjian writes: > On Tue, May 12, 2020 at 03:38:16PM -0700, David G. Johnston wrote: >> The proposal is to document in versions 9.4 to 11 that the recommended value >> for the setting is 2ms while for reasons of continuity the default in these >> versions is 20ms. >> I don't really see any harm in it. Its not like the choice to reduce the >> value >> was made because of new features introduced in 12 - it was a re-evaluation >> of a >> 15 year old default. > Well, we really need to have some general discussion about whether > changing defaults in major releases should trigger a mention to change > the defaults in back branches. This is something that would have to be > discussed on the hackers list. It's not immediately obvious that the new default value established in version N is appropriate for version N-minus-several. Certainly, whatever testing was done to justify the new default wouldn't have been done on old versions; and there might have been relevant changes. In short: nope, I'm not on board with blindly back-patching such recommendations. regards, tom lane
Re: The suggestion of reducing autovacuum_vacuum_cost_delay should be documented
On Tue, May 12, 2020 at 07:26:07PM -0400, Tom Lane wrote: > Bruce Momjian writes: > > On Tue, May 12, 2020 at 03:38:16PM -0700, David G. Johnston wrote: > >> The proposal is to document in versions 9.4 to 11 that the recommended > >> value > >> for the setting is 2ms while for reasons of continuity the default in these > >> versions is 20ms. > >> I don't really see any harm in it. Its not like the choice to reduce the > >> value > >> was made because of new features introduced in 12 - it was a re-evaluation > >> of a > >> 15 year old default. > > > Well, we really need to have some general discussion about whether > > changing defaults in major releases should trigger a mention to change > > the defaults in back branches. This is something that would have to be > > discussed on the hackers list. > > It's not immediately obvious that the new default value established in > version N is appropriate for version N-minus-several. Certainly, whatever > testing was done to justify the new default wouldn't have been done on old > versions; and there might have been relevant changes. > > In short: nope, I'm not on board with blindly back-patching such > recommendations. That was my analysis too. -- Bruce Momjian https://momjian.us EnterpriseDB https://enterprisedb.com + As you are, so once was I. As I am, so you will be. + + Ancient Roman grave inscription +
Re: The suggestion of reducing autovacuum_vacuum_cost_delay should be documented
On Tue, May 12, 2020 at 4:30 PM Bruce Momjian wrote: > On Tue, May 12, 2020 at 07:26:07PM -0400, Tom Lane wrote: > > Bruce Momjian writes: > > > On Tue, May 12, 2020 at 03:38:16PM -0700, David G. Johnston wrote: > > >> The proposal is to document in versions 9.4 to 11 that the > recommended value > > >> for the setting is 2ms while for reasons of continuity the default in > these > > >> versions is 20ms. > > >> I don't really see any harm in it. Its not like the choice to reduce > the value > > >> was made because of new features introduced in 12 - it was a > re-evaluation of a > > >> 15 year old default. > > > > > Well, we really need to have some general discussion about whether > > > changing defaults in major releases should trigger a mention to change > > > the defaults in back branches. This is something that would have to be > > > discussed on the hackers list. > > > > It's not immediately obvious that the new default value established in > > version N is appropriate for version N-minus-several. Certainly, > whatever > > testing was done to justify the new default wouldn't have been done on > old > > versions; and there might have been relevant changes. > > > > In short: nope, I'm not on board with blindly back-patching such > > recommendations. > > That was my analysis too. > To be clear, because my cursory reading of the thread that was linked from the commit suggested that this specific situation was more "lets catch up to modern times", my position isn't that such documentation changes should be done as a rule, I am suggesting that we give a yes/no decision on this specific change (in advance of bike-shedding the wording). IMO neither a blanket rule allowing or prohibiting such a change to the documentation makes sense given the rarity of the event. David J.
Re: The suggestion of reducing autovacuum_vacuum_cost_delay should be documented
"David G. Johnston" writes: > To be clear, because my cursory reading of the thread that was linked from > the commit suggested that this specific situation was more "lets catch up > to modern times", my position isn't that such documentation changes should > be done as a rule, I am suggesting that we give a yes/no decision on this > specific change (in advance of bike-shedding the wording). IMO neither a > blanket rule allowing or prohibiting such a change to the documentation > makes sense given the rarity of the event. Sure. My point was just that changing the back-branch documentation would require doing additional testing to verify that the proposed value is an improvement in those branches. regards, tom lane
Re: The suggestion of reducing autovacuum_vacuum_cost_delay should be documented
On Tue, May 12, 2020 at 4:59 PM Tom Lane wrote: > "David G. Johnston" writes: > > To be clear, because my cursory reading of the thread that was linked > from > > the commit suggested that this specific situation was more "lets catch up > > to modern times", my position isn't that such documentation changes > should > > be done as a rule, I am suggesting that we give a yes/no decision on this > > specific change (in advance of bike-shedding the wording). IMO neither a > > blanket rule allowing or prohibiting such a change to the documentation > > makes sense given the rarity of the event. > > Sure. My point was just that changing the back-branch documentation would > require doing additional testing to verify that the proposed value is > an improvement in those branches. > > Yeah, details of any testing during v12 didn't make it into the thread, the main conclusion that did was: """ However, the current settings are predicated on the assumption that you can't get the kernel to give you a sleep of less than circa 10ms. That assumption is way outdated, I believe; poking around on systems I have here, the minimum delay time using pg_usleep(1) seems to be generally less than 100us, and frequently less than 10us, on anything released in the last decade. """ Hence assuming this was more of a "modernization". v12 has been out for a year (yeah, not a super long time, I know) and I'm not recalling any complaints about the new default. I don't have a feel, though, for what informal or other measurements that all you hackers do to get a feel for how safe and useful the change from 20ms to 2ms was. David's original post: """ I've had to do quite a bit of performance investigation work this year and it seems that I only too often discover that the same problem is repeating itself... """ I'd be inclined to assume many of those investigations were not on v12 which lends itself to at least wanting to give admins of older versions a heads-up in the documentation. There seems to be consensus that this is a win - and people have a couple of months now to demonstrate that it isn't on older versions. (IOW, I'm not going to prove it one way or another but everything that went into having this committed in v12 leads me to at least tell people of older versions we changed things in v12 for reasons - even if we cover our rear ends and say we simply didn't test older versions and mileage may vary). David J.
