Обсуждение: Remove fsync ON/OFF as a visible option?
Hello, There are just as many people that are running with scissors that are now running (or attempting to run) our elephant in production. Does it make sense to remove fsync (and possibly full_page_writes) from such a visible place as postgresql.conf? I don't think we should remove the features themselves but perhaps only have them settable via the new alter system command? JD -- Command Prompt, Inc. - http://www.commandprompt.com/ 503-667-4564 PostgreSQL Support, Training, Professional Services and Development High Availability, Oracle Conversion, @cmdpromptinc Now I get it: your service is designed for a customer base that grew up with Facebook, watches Japanese seizure robot anime, and has the attention span of a gnat. I'm not that user., "Tyler Riddle"
"Joshua D. Drake" <jd@commandprompt.com> writes:
> There are just as many people that are running with scissors that are
> now running (or attempting to run) our elephant in production.
Evidence please.
> Does it
> make sense to remove fsync (and possibly full_page_writes) from such a
> visible place as postgresql.conf?
> I don't think we should remove the features themselves but perhaps only
> have them settable via the new alter system command?
I would object to that, because it would make it vastly more difficult
to use fsync=off easily for development.
regards, tom lane
On Fri, Mar 20, 2015 at 9:52 AM, Joshua D. Drake <jd@commandprompt.com> wrote: > There are just as many people that are running with scissors that are now > running (or attempting to run) our elephant in production. Does it make > sense to remove fsync (and possibly full_page_writes) from such a visible > place as postgresql.conf? -1 Anyone turning off fsync without even for a moment considering the consequences has only themselves to blame. I can't imagine why you'd want to remove full_page_writes or make it less visible either, since in principle it ought to be perfectly fine to turn it off in production once its verified as safe. -- Peter Geoghegan
On 03/20/2015 10:45 AM, Tom Lane wrote: > "Joshua D. Drake" <jd@commandprompt.com> writes: >> There are just as many people that are running with scissors that are >> now running (or attempting to run) our elephant in production. > > Evidence please. Fair enough. I am not going to name names but over the years (and just today) I ran into another user that corrupted their database by turning off fsync. > >> Does it >> make sense to remove fsync (and possibly full_page_writes) from such a >> visible place as postgresql.conf? >> I don't think we should remove the features themselves but perhaps only >> have them settable via the new alter system command? > > I would object to that, because it would make it vastly more difficult > to use fsync=off easily for development. How so? alter system fsync on/off (meta) restart That seems easier than editing the file and restarting? JD -- Command Prompt, Inc. - http://www.commandprompt.com/ 503-667-4564 PostgreSQL Support, Training, Professional Services and Development High Availability, Oracle Conversion, @cmdpromptinc Now I get it: your service is designed for a customer base that grew up with Facebook, watches Japanese seizure robot anime, and has the attention span of a gnat. I'm not that user., "Tyler Riddle"
On 03/20/2015 10:47 AM, Peter Geoghegan wrote: > > On Fri, Mar 20, 2015 at 9:52 AM, Joshua D. Drake <jd@commandprompt.com> wrote: >> There are just as many people that are running with scissors that are now >> running (or attempting to run) our elephant in production. Does it make >> sense to remove fsync (and possibly full_page_writes) from such a visible >> place as postgresql.conf? > > -1 > > Anyone turning off fsync without even for a moment considering the > consequences has only themselves to blame. This is an extremely valid point. Shall we hand them a loaded gun with the safety off too? We can't protect everyone but this is a rather simple change that hurts nobody. I am not going to raise a huge stink or anything but it seems rather simple. JD -- Command Prompt, Inc. - http://www.commandprompt.com/ 503-667-4564 PostgreSQL Support, Training, Professional Services and Development High Availability, Oracle Conversion, @cmdpromptinc Now I get it: your service is designed for a customer base that grew up with Facebook, watches Japanese seizure robot anime, and has the attention span of a gnat. I'm not that user., "Tyler Riddle"
On Fri, Mar 20, 2015 at 7:29 PM, Joshua D. Drake <jd@commandprompt.com> wrote:
I am not going to raise a huge stink or anything but it seems rather simple.
ALTER SYSTEM is, if anything, more accessible and easier to do without reading comments and warnings than config files.
If it were a green field then naming it data_integrity=off would do more to scare wave people away than hacking the config system. But making the change now after years seems kind of silly.
I do wonder if it could be made a synonym for setting a large value of wal_writer_delay. That wouldn't really help Tom's concern about development but it might provide the same performance for the traditional use case of initial loading of large databases. That would require a lot of empirical testing though.
greg
* Joshua D. Drake (jd@commandprompt.com) wrote:
> On 03/20/2015 10:45 AM, Tom Lane wrote:
> >I would object to that, because it would make it vastly more difficult
> >to use fsync=off easily for development.
>
> How so? alter system fsync on/off (meta)
> restart
>
> That seems easier than editing the file and restarting?
If it's that easy then I'm not sure why you're thinking it'd stop users
from using it..
Now, if you wanted to remove it from the default/shipped
postgresql.conf (but do nothing else), then that might reduce the number
of people who change it without reading the docs, but we might actually
be better off by improving the docs in postgresql.conf to carry a clear
warning about the option.
At the moment, one could look at our default postgresql.conf and the
"turns forced synchronization on or off" and think it's something akin
or somehow related to synchronous_commit (which is completely different,
but the options are right next to each other..).
How about a big warning around fsync and make it more indepenent from
the options around it?
Thanks,
Stephen
On Fri, Mar 20, 2015 at 3:26 PM, Joshua D. Drake <jd@commandprompt.com> wrote: > Fair enough. I am not going to name names but over the years (and just > today) I ran into another user that corrupted their database by turning off > fsync. My experience is different than yours: I haven't found this to be a particularly common mistake. I think I've had more people screw themselves by setting autovacuum_naptime=something_excessively_large or enable_seqscan=off. I'm very skeptical that removing stuff from postgresql.conf is going to help anything. If you go through your postgresql.conf and change settings at random, bad things will happen. But anyone who is doing that has a problem we can't fix. Thus far, the rule for postgresql.conf has been that pretty much everything goes in there, and that's a defensible position. Other reasonable options would be to ship the file with a small handful of settings in it and leave everything else, or to ship it completely empty of comments with only those settings that initdb sets and nothing else. I'd be OK a coherent policy change in this area, but just removing one or two setting seems like it will be confusing rather than helpful. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
On 3/20/15 2:49 PM, Stephen Frost wrote: > How about a big warning around fsync and make it more indepenent from > the options around it? +1, and the same for full_page_writes and wal_sync_method. I think that's the best we can do at this point. As for why; Postgres already has a big reputation for being "hard to use" and "hard to setup". Leaving footguns laying around that could easily be warned about is part of the reason for that reputation. Reality is that there are a lot of people using Postgres that are nowhere close to being DBAs and making it easy for them to munch their data on accident doesn't help anyone. -- Jim Nasby, Data Architect, Blue Treble Consulting Data in Trouble? Get it in Treble! http://BlueTreble.com
On 3/20/15 6:09 PM, Robert Haas wrote: > On Fri, Mar 20, 2015 at 3:26 PM, Joshua D. Drake <jd@commandprompt.com> wrote: >> Fair enough. I am not going to name names but over the years (and just >> today) I ran into another user that corrupted their database by turning off >> fsync. > > My experience is different than yours: I haven't found this to be a > particularly common mistake. I think I've had more people screw > themselves by setting autovacuum_naptime=something_excessively_large > or enable_seqscan=off. FWIW, I suspect a lot of that is due to CMD and EDB targeting different markets. > I'm very skeptical that removing stuff from postgresql.conf is going > to help anything. If you go through your postgresql.conf and change > settings at random, bad things will happen. But anyone who is doing > that has a problem we can't fix. I don't think people are making random changes; they're misunderstanding what the setting actually does. For dangerous settings (fsync, wal_sync_method and full_page_writes come to mind), a big WARNING in postgresql.conf would go a long way towards improving that. I do agree that simply removing the option isn't a great solution. > Thus far, the rule for postgresql.conf has been that pretty much > everything goes in there, and that's a defensible position. Other > reasonable options would be to ship the file with a small handful of > settings in it and leave everything else, or to ship it completely > empty of comments with only those settings that initdb sets and > nothing else. I'd be OK a coherent policy change in this area, but > just removing one or two setting seems like it will be confusing > rather than helpful. I agree with not being ad-hoc (and I think a documented postgresql.conf is much better than the other options). -- Jim Nasby, Data Architect, Blue Treble Consulting Data in Trouble? Get it in Treble! http://BlueTreble.com
On Sat, Mar 21, 2015 at 2:47 AM, Peter Geoghegan <pg@heroku.com> wrote: > On Fri, Mar 20, 2015 at 9:52 AM, Joshua D. Drake <jd@commandprompt.com> wrote: >> There are just as many people that are running with scissors that are now >> running (or attempting to run) our elephant in production. Does it make >> sense to remove fsync (and possibly full_page_writes) from such a visible >> place as postgresql.conf? > > -1 > > Anyone turning off fsync without even for a moment considering the > consequences has only themselves to blame. I can't imagine why you'd > want to remove full_page_writes or make it less visible either, since > in principle it ought to be perfectly fine to turn it off in > production once its verified as safe. -1 for its removal as well. It is still useful for developers to emulate CPU-bounded loads... -- Michael
On Fri, Mar 20, 2015 at 11:29 PM, Michael Paquier <michael.paquier@gmail.com> wrote: > On Sat, Mar 21, 2015 at 2:47 AM, Peter Geoghegan <pg@heroku.com> wrote: >> On Fri, Mar 20, 2015 at 9:52 AM, Joshua D. Drake <jd@commandprompt.com> wrote: >>> There are just as many people that are running with scissors that are now >>> running (or attempting to run) our elephant in production. Does it make >>> sense to remove fsync (and possibly full_page_writes) from such a visible >>> place as postgresql.conf? >> >> -1 >> >> Anyone turning off fsync without even for a moment considering the >> consequences has only themselves to blame. I can't imagine why you'd >> want to remove full_page_writes or make it less visible either, since >> in principle it ought to be perfectly fine to turn it off in >> production once its verified as safe. > > -1 for its removal as well. It is still useful for developers to > emulate CPU-bounded loads... I fought to remove fsync before so i understand JD concerns. and yes, i have seen fsync=off in the field too... what about not removing it but not showing it in postgresql.conf? as a side note, i wonder why trace_sort is not in postgresql.conf... other option is to make it a compile setting, that why if you want to have it you need to compile and postgres' developers do that routinely anyway just my 2c -- Jaime Casanova www.2ndQuadrant.com Professional PostgreSQL: Soporte 24x7 y capacitación
On 21/03/15 19:28, Jaime Casanova wrote: > On Fri, Mar 20, 2015 at 11:29 PM, Michael Paquier > <michael.paquier@gmail.com> wrote: >> On Sat, Mar 21, 2015 at 2:47 AM, Peter Geoghegan <pg@heroku.com> wrote: >>> On Fri, Mar 20, 2015 at 9:52 AM, Joshua D. Drake <jd@commandprompt.com> wrote: >>>> There are just as many people that are running with scissors that are now >>>> running (or attempting to run) our elephant in production. Does it make >>>> sense to remove fsync (and possibly full_page_writes) from such a visible >>>> place as postgresql.conf? >>> >>> -1 >>> >>> Anyone turning off fsync without even for a moment considering the >>> consequences has only themselves to blame. I can't imagine why you'd >>> want to remove full_page_writes or make it less visible either, since >>> in principle it ought to be perfectly fine to turn it off in >>> production once its verified as safe. >> >> -1 for its removal as well. It is still useful for developers to >> emulate CPU-bounded loads... > > I fought to remove fsync before so i understand JD concerns. and yes, > i have seen fsync=off in the field too... > > what about not removing it but not showing it in postgresql.conf? as a > side note, i wonder why trace_sort is not in postgresql.conf... > other option is to make it a compile setting, that why if you want to > have it you need to compile and postgres' developers do that routinely > anyway > -1 Personally I'm against hiding *any* settings. Choosing sensible defaults - yes! Hiding them - that reeks of secret squirrel nonsense and overpaid Oracle dbas that knew the undocumented settings for various capabilities. I think/hope that no open source project will try to emulate that meme! Regards Mark
Stephen Frost <sfrost@snowman.net> writes:
> At the moment, one could look at our default postgresql.conf and the
> "turns forced synchronization on or off" and think it's something akin
> or somehow related to synchronous_commit (which is completely different,
> but the options are right next to each other..).
> How about a big warning around fsync and make it more indepenent from
> the options around it?
Yeah, the main SGML docs are reasonably clear about the risks of fsync,
but postgresql.conf doesn't give you any hint that it's dangerous. Now
I'm not entirely sure that people who frob postgresql.conf without having
read the docs can be saved from themselves, but we could do something
like this:
# - Settings -#wal_level = minimal # minimal, archive, hot_standby, or logical # (change
requiresrestart)#fsync = on # turns forced synchronization on or off
+ # (fsync=off is dangerous, read the
+ # (manual before using it)#synchronous_commit = on # synchronization
level; # off, local, remote_write, or on#wal_sync_method = fsync # the default is the first
option # supported by the operating system:
Also, I think the short description "turns forced synchronization on or
off" could stand improvement; it really conveys zero information. Maybe
something like "force data to disk when committing"?
Also, whatever we do here should be reflected into the description strings
in guc.c.
regards, tom lane
On Sat, Mar 21, 2015 at 11:54:00AM -0400, Tom Lane wrote: > Stephen Frost <sfrost@snowman.net> writes: > > At the moment, one could look at our default postgresql.conf and the > > "turns forced synchronization on or off" and think it's something akin > > or somehow related to synchronous_commit (which is completely different, > > but the options are right next to each other..). > > > How about a big warning around fsync and make it more indepenent from > > the options around it? > > Yeah, the main SGML docs are reasonably clear about the risks of fsync, > but postgresql.conf doesn't give you any hint that it's dangerous. Now > I'm not entirely sure that people who frob postgresql.conf without having > read the docs can be saved from themselves, but we could do something > like this: > > # - Settings - > > #wal_level = minimal # minimal, archive, hot_standby, or logical > # (change requires restart) > #fsync = on # turns forced synchronization on or off > + # (fsync=off is dangerous, read the > + # (manual before using it) I think this will help people who find themselves in that file with few (or wrong) ideas of what this does. > #synchronous_commit = on # synchronization level; > # off, local, remote_write, or on > #wal_sync_method = fsync # the default is the first option > # supported by the operating system: > > Also, I think the short description "turns forced synchronization on or > off" could stand improvement; it really conveys zero information. Maybe > something like "force data to disk when committing"? > > Also, whatever we do here should be reflected into the description strings > in guc.c. I don't suppose there's a way to have a single point of truth... Cheers, David. -- David Fetter <david@fetter.org> http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fetter@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate
Stephen Frost <sfrost@snowman.net> writes:
> At the moment, one could look at our default postgresql.conf and the
> "turns forced synchronization on or off" and think it's something akin
> or somehow related to synchronous_commit (which is completely different,
> but the options are right next to each other..).
> How about a big warning around fsync and make it more indepenent from
> the options around it?
Yeah, the main SGML docs are reasonably clear about the risks of fsync,
but postgresql.conf doesn't give you any hint that it's dangerous. Now
I'm not entirely sure that people who frob postgresql.conf without having
read the docs can be saved from themselves, but we could do something
like this:
# - Settings -
#wal_level = minimal # minimal, archive, hot_standby, or logical
# (change requires restart)
#fsync = on # turns forced synchronization on or off
+ # (fsync=off is dangerous, read the
+ # (manual before using it)
#synchronous_commit = on # synchronization level;
# off, local, remote_write, or on
#wal_sync_method = fsync # the default is the first option
# supported by the operating system:
Also, I think the short description "turns forced synchronization on or
off" could stand improvement; it really conveys zero information. Maybe
something like "force data to disk when committing"?
Also, whatever we do here should be reflected into the description strings
in guc.c.
"enables or disables data durability promise of ACID." ?
David J.
On Sat, Mar 21, 2015 at 8:54 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Stephen Frost <sfrost@snowman.net> writes:
> At the moment, one could look at our default postgresql.conf and the
> "turns forced synchronization on or off" and think it's something akin
> or somehow related to synchronous_commit (which is completely different,
> but the options are right next to each other..).
> How about a big warning around fsync and make it more indepenent from
> the options around it?
Yeah, the main SGML docs are reasonably clear about the risks of fsync,
but postgresql.conf doesn't give you any hint that it's dangerous. Now
I'm not entirely sure that people who frob postgresql.conf without having
read the docs can be saved from themselves, but we could do something
like this:
# - Settings -
#wal_level = minimal # minimal, archive, hot_standby, or logical
# (change requires restart)
#fsync = on # turns forced synchronization on or off
+ # (fsync=off is dangerous, read the
+ # (manual before using it)
#synchronous_commit = on # synchronization level;
# off, local, remote_write, or on
#wal_sync_method = fsync # the default is the first option
# supported by the operating system:
Also, I think the short description "turns forced synchronization on or
off" could stand improvement; it really conveys zero information. Maybe
something like "force data to disk when committing"?
I agree the current description is lacking, but that proposed wording would be a better description of synchronous_commit. It is checkpointing and flush-WAL-before-data where fsync=off does its damage.
"Force data to disk when needed for integrity"?
Or just don't describe what it is at all, and refer to the documentation only.
Cheers,
Jeff
<p dir="ltr"><br /> El mar 21, 2015 2:00 AM, "Mark Kirkwood" <<a href="mailto:mark.kirkwood@catalyst.net.nz">mark.kirkwood@catalyst.net.nz</a>>escribió:<br /> ><br /> > On 21/03/1519:28, Jaime Casanova wrote:<br /> >><br /> >> what about not removing it but not showing it in postgresql.conf?as a<br /> >> side note, i wonder why trace_sort is not in postgresql.conf...<br /> >> otheroption is to make it a compile setting, that why if you want to<br /> >> have it you need to compile and postgres'developers do that routinely<br /> >> anyway<br /> >><br /> ><br /> > -1<br /> ><br /> >Personally I'm against hiding *any* settings. Choosing sensible defaults - yes! Hiding them - that reeks of secret squirrelnonsense and overpaid Oracle dbas that knew the undocumented settings for various capabilities. I think/hope thatno open source project will try to emulate that meme!<br /> ><p dir="ltr">That ship has already sailed.<p dir="ltr"><a href="http://www.postgresql.org/docs/9.4/static/runtime-config-developer.html">http://www.postgresql.org/docs/9.4/static/runtime-config-developer.html</a><p dir="ltr">--<br/> Jaime Casanova<br /> 2ndQuadrant Consultant<br /> Your PostgreSQL partner
On 03/20/2015 04:09 PM, Robert Haas wrote: > Thus far, the rule for postgresql.conf has been that pretty much > everything goes in there, and that's a defensible position. Other > reasonable options would be to ship the file with a small handful of > settings in it and leave everything else, or to ship it completely > empty of comments with only those settings that initdb sets and > nothing else. I'd be OK a coherent policy change in this area, but > just removing one or two setting seems like it will be confusing > rather than helpful. I would agree with this. I imagine there is only about a dozen, dozen and a half that need to be there by default. JD -- Command Prompt, Inc. - http://www.commandprompt.com/ 503-667-4564 PostgreSQL Support, Training, Professional Services and Development High Availability, Oracle Conversion, @cmdpromptinc Now I get it: your service is designed for a customer base that grew up with Facebook, watches Japanese seizure robot anime, and has the attention span of a gnat. I'm not that user., "Tyler Riddle"
On 03/20/2015 04:11 PM, Jim Nasby wrote: > As for why; Postgres already has a big reputation for being "hard to > use" and "hard to setup". Leaving footguns laying around that could > easily be warned about is part of the reason for that reputation. > Reality is that there are a lot of people using Postgres that are > nowhere close to being DBAs and making it easy for them to munch their > data on accident doesn't help anyone. Exactly. JD -- Command Prompt, Inc. - http://www.commandprompt.com/ 503-667-4564 PostgreSQL Support, Training, Professional Services and Development High Availability, Oracle Conversion, @cmdpromptinc Now I get it: your service is designed for a customer base that grew up with Facebook, watches Japanese seizure robot anime, and has the attention span of a gnat. I'm not that user., "Tyler Riddle"
On 22/03/15 05:42, David G. Johnston wrote: > On Sat, Mar 21, 2015 at 8:54 AM, Tom Lane <tgl@sss.pgh.pa.us > <mailto:tgl@sss.pgh.pa.us>>wrote: > > Stephen Frost <sfrost@snowman.net <mailto:sfrost@snowman.net>> writes: > > At the moment, one could look at our default postgresql.conf and the > > "turns forced synchronization on or off" and think it's > something akin > > or somehow related to synchronous_commit (which is completely > different, > > but the options are right next to each other..). > > > How about a big warning around fsync and make it more indepenent > from > > the options around it? > > Yeah, the main SGML docs are reasonably clear about the risks of > fsync, > but postgresql.conf doesn't give you any hint that it's > dangerous. Now > I'm not entirely sure that people who frob postgresql.conf without > having > read the docs can be saved from themselves, but we could do something > like this: > > # - Settings - > > #wal_level = minimal # minimal, archive, > hot_standby, or logical > # (change requires restart) > #fsync = on # turns forced > synchronization on or off > + # (fsync=off is dangerous, > read the > + # (manual before using it) > #synchronous_commit = on # synchronization level; > # off, local, > remote_write, or on > #wal_sync_method = fsync # the default is the first > option > # supported by the > operating system: > > Also, I think the short description "turns forced synchronization > on or > off" could stand improvement; it really conveys zero information. > Maybe > something like "force data to disk when committing"? > > Also, whatever we do here should be reflected into the description > strings > in guc.c. > > > " enables or disables data durability promise of ACID." ? > > David J. > > What does ACID mean??? I don't want to trip out on acid, and if I do, I don't want it hanging around. Safer to set this to off!!! I actual do know what ACID means, but some 'children' have write access to a the postgresql.conf file without adequate 'adult' supervision! Cheers, Gavin
On 03/20/2015 11:28 PM, Jaime Casanova wrote: > > I fought to remove fsync before so i understand JD concerns. and yes, > i have seen fsync=off in the field too... > > what about not removing it but not showing it in postgresql.conf? as a > side note, i wonder why trace_sort is not in postgresql.conf... That is the original proposal. I am not suggesting that it not be an option. I am suggesting that it is not in postgresql.conf by default. JD -- Command Prompt, Inc. - http://www.commandprompt.com/ 503-667-4564 PostgreSQL Support, Training, Professional Services and Development High Availability, Oracle Conversion, @cmdpromptinc Now I get it: your service is designed for a customer base that grew up with Facebook, watches Japanese seizure robot anime, and has the attention span of a gnat. I'm not that user., "Tyler Riddle"
On 03/21/2015 12:00 AM, Mark Kirkwood wrote: > > -1 > > Personally I'm against hiding *any* settings. Choosing sensible defaults > - yes! Hiding them - that reeks of secret squirrel nonsense and overpaid > Oracle dbas that knew the undocumented settings for various > capabilities. I think/hope that no open source project will try to > emulate that meme! I don't agree with this at all. On the one hand: Postgres: So many settings people have no idea where to start (unless they have background) vs Postgres: Only the settings that are "needed" for 95% of installations. JD -- Command Prompt, Inc. - http://www.commandprompt.com/ 503-667-4564 PostgreSQL Support, Training, Professional Services and Development High Availability, Oracle Conversion, @cmdpromptinc Now I get it: your service is designed for a customer base that grew up with Facebook, watches Japanese seizure robot anime, and has the attention span of a gnat. I'm not that user., "Tyler Riddle"
On 03/21/2015 12:32 PM, Gavin Flower wrote: > What does ACID mean??? > > I don't want to trip out on acid, and if I do, I don't want it hanging > around. Safer to set this to off!!! > > > I actual do know what ACID means, but some 'children' have write access > to a the postgresql.conf file without adequate 'adult' supervision! Some? I make my living babysitting, even some of our best clients have some ruby on rails developer constantly saying, "Dude, like the DB doesn't matter man... we can just (takes long sip of organic stock) horizontally partition this stuff". Sincerely, JD -- Command Prompt, Inc. - http://www.commandprompt.com/ 503-667-4564 PostgreSQL Support, Training, Professional Services and Development High Availability, Oracle Conversion, @cmdpromptinc Now I get it: your service is designed for a customer base that grew up with Facebook, watches Japanese seizure robot anime, and has the attention span of a gnat. I'm not that user., "Tyler Riddle"
On 22/03/15 08:34, Joshua D. Drake wrote: > > On 03/21/2015 12:00 AM, Mark Kirkwood wrote: > >> >> -1 >> >> Personally I'm against hiding *any* settings. Choosing sensible defaults >> - yes! Hiding them - that reeks of secret squirrel nonsense and overpaid >> Oracle dbas that knew the undocumented settings for various >> capabilities. I think/hope that no open source project will try to >> emulate that meme! > > I don't agree with this at all. On the one hand: > > Postgres: So many settings people have no idea where to start (unless > they have background) > > vs > > Postgres: Only the settings that are "needed" for 95% of installations. > > JD > > How about 2 config files? One marked adult^H^H^H^H^H power users only, or some such, with the really dangerous or unusual options? Cheers, Gavin
On 03/21/2015 12:45 PM, Gavin Flower wrote: >> > How about 2 config files? > > One marked adult^H^H^H^H^H power users only, or some such, with the > really dangerous or unusual options? > That has come up before in many threads. I don't know that we need to go down that path again. Consider, power users don't need a separate config. They can create their own or use alter system. JD -- Command Prompt, Inc. - http://www.commandprompt.com/ 503-667-4564 PostgreSQL Support, Training, Professional Services and Development High Availability, Oracle Conversion, @cmdpromptinc Now I get it: your service is designed for a customer base that grew up with Facebook, watches Japanese seizure robot anime, and has the attention span of a gnat. I'm not that user., "Tyler Riddle"
On 22/03/15 08:48, Joshua D. Drake wrote: > > On 03/21/2015 12:45 PM, Gavin Flower wrote: > >>> >> How about 2 config files? >> >> One marked adult^H^H^H^H^H power users only, or some such, with the >> really dangerous or unusual options? >> > > That has come up before in many threads. I don't know that we need to > go down that path again. Consider, power users don't need a separate > config. They can create their own or use alter system. > > JD > > How about "This file must not be changed by children, unless under competent adult supervision?" Yeah, I know, that will never happen. (Tempting as it might be!) Cheers, Gavin
On Sat, Mar 21, 2015 at 2:33 PM, Joshua D. Drake <jd@commandprompt.com> wrote: > > On 03/20/2015 11:28 PM, Jaime Casanova wrote: >> >> > >> I fought to remove fsync before so i understand JD concerns. and yes, >> i have seen fsync=off in the field too... >> >> what about not removing it but not showing it in postgresql.conf? as a >> side note, i wonder why trace_sort is not in postgresql.conf... > > > That is the original proposal. I am not suggesting that it not be an option. > I am suggesting that it is not in postgresql.conf by default. > > you're right, i misunderstood... anyway i don't feel there's a need to avoid people putting in there... just don't ship with the guc in there, if someone puts it by himself that's completely another thing -- Jaime Casanova www.2ndQuadrant.com Professional PostgreSQL: Soporte 24x7 y capacitación
why does we take so many attention to fsync issue?
but there are also table spaces in tmpfs, wal in tmpfs, disks with cache without bbu, writeback writes and fs without ordering and journal, any CLOUDS, etc etc... in our real world installations.
more over not all of these issues are usually in dba's medium, and what dba really have to do -- is to accept ugly bottom storage properties and DO properly PITR/standby.
and if we have PITR then "fsync or not fsync" in master host doesn't matter so much. and could matter providing fsync in archive host. but doing fsync in archive -- it is workaround for archive_command realization.
in conclusion -- imho, full-page writes is more sensual than fsync when we guarantee PITR.
-- misha
On 03/20/2015 09:29 PM, Michael Paquier wrote: > On Sat, Mar 21, 2015 at 2:47 AM, Peter Geoghegan <pg@heroku.com> wrote: >> On Fri, Mar 20, 2015 at 9:52 AM, Joshua D. Drake <jd@commandprompt.com> wrote: >>> There are just as many people that are running with scissors that are now >>> running (or attempting to run) our elephant in production. Does it make >>> sense to remove fsync (and possibly full_page_writes) from such a visible >>> place as postgresql.conf? >> >> -1 >> >> Anyone turning off fsync without even for a moment considering the >> consequences has only themselves to blame. I can't imagine why you'd >> want to remove full_page_writes or make it less visible either, since >> in principle it ought to be perfectly fine to turn it off in >> production once its verified as safe. > > -1 for its removal as well. It is still useful for developers to > emulate CPU-bounded loads... Coincidentally, I am just at this moment performance testing "running with scissors mode" for PostgreSQL on AWS. When intentional, this mode is useful for spinning up lots of read-only replicas which are intended mainly as cache support, something I've done at various dot-coms. So, -1 on removing the setting; it is useful to some users. Further, full_page_writes=off is supposedly safe on any copy-on-write filesystem, such as ZFS. Since that can cut fsync time by as much as 30%, -1 to remove/hide that setting either. The proposal that we make certain settings "only available via ALTER SYSTEM" also doesn't make sense; ALTER SYSTEM isn't capable of writing any settings which aren't available in postgresql.conf. Now, I have *long* been an advocate that we should ship a "stripped" PostgreSQL.conf which has only the most commonly used settings, and leave the rest of the settings in the docs and share/postgresql/postgresql.conf.advanced. Here's my example of such a file, tailored to PostgreSQL 9.3: https://github.com/pgexperts/accidentalDBA/blob/master/vagrant/setup/postgres/postgresql.conf While we likely wouldn't want to ship all of the advice in the comments in that file (the calculations, in particular, have been questioned since they were last tested with PostgreSQL 8.3), that gives you an example of what a simple/mainstream pg.conf could look like. I would further advocate that that file be broken up into segments (resources, logging, connects, etc.) and placed in conf.d/ All that being said, what *actually* ships with PostgreSQL is up to the packagers, so anything we do with pg.conf will have a limited impact unless we get them on board with the idea. -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com
"Joshua D. Drake" <jd@commandprompt.com> writes: > On 03/21/2015 12:45 PM, Gavin Flower wrote: > >>> >> How about 2 config files? >> >> One marked adult^H^H^H^H^H power users only, or some such, with the >> really dangerous or unusual options? >> > > That has come up before in many threads. I don't know that we need to > go down that path again. Consider, power users don't need a separate > config. They can create their own or use alter system. > 10 years ago I was already feeling like postgresql.conf was unwieldy and have on many occasions stripped them down only to live lines without any comments whatsoever. In fact I'd argue that due to how verbose the file is presently as shipped, it makes it that much more likely that someone looking at is isn't going to notice something as alarming as fsync=off :-) > JD > > > -- > Command Prompt, Inc. - http://www.commandprompt.com/ 503-667-4564 > PostgreSQL Support, Training, Professional Services and Development > High Availability, Oracle Conversion, @cmdpromptinc > > Now I get it: your service is designed for a customer > base that grew up with Facebook, watches Japanese seizure > robot anime, and has the attention span of a gnat. > I'm not that user., "Tyler Riddle" -- Jerry Sievers Postgres DBA/Development Consulting e: postgres.consulting@comcast.net p: 312.241.7800
On 2015-03-21 13:53:47 -0700, Josh Berkus wrote: > Coincidentally, I am just at this moment performance testing "running > with scissors mode" for PostgreSQL on AWS. When intentional, this mode > is useful for spinning up lots of read-only replicas which are intended > mainly as cache support, something I've done at various dot-coms. Which is where fsync=off barely has any effect? > So, -1 on removing the setting; it is useful to some users. Agreed on that. > Further, full_page_writes=off is supposedly safe on any copy-on-write > filesystem, such as ZFS. FWIW, I think that's a myth. One I heard various versions of by now. As long as the OSs page size (4kb nearly everywhere) is different from postgres' (8kb) you can have torn pages. Even if individual filesystem page writes are atomic. > The proposal that we make certain settings "only available via ALTER > SYSTEM" also doesn't make sense; ALTER SYSTEM isn't capable of writing > any settings which aren't available in postgresql.conf. +1. I think it's a pretty absurd sugestion. Greetings, Andres Freund -- Andres Freund http://www.2ndQuadrant.com/PostgreSQL Development, 24x7 Support, Training & Services
On 21-03-2015 17:53, Josh Berkus wrote: > Now, I have *long* been an advocate that we should ship a "stripped" > PostgreSQL.conf which has only the most commonly used settings, and > leave the rest of the settings in the docs and > share/postgresql/postgresql.conf.advanced. Here's my example of such a > file, tailored to PostgreSQL 9.3: > +1. I agree that common used settings in a postgresql.conf file is useful for newbies. How do we ship it? (i) replace postgresql.conf with newbie.conf at $PGDATA; (ii) let postgresql.conf alone and include newbie.conf at the end; (iii) install newbie.conf at share directory and let packagers decide to replace postgresql.conf with it or not; (iv) install newbie.conf at share directory and add an option in initdb to select it as postgresql.conf, say, --config=simple. As a starting point, (i) could be too radical because some DBAs are used to check that occasional parameter at postgresql.conf. (ii) will advocate the newbie configuration file. However, do we want a new configuration file? We already have two and another one could be very confusing. The option (iii) will be effective if popular distributions decided to use newbie.conf as postgresql.conf. An the last option is a flexible way to install a basic configuration (and imo is the way to satisfy those that want basic configuration file available). It also has a way to extend that option to other setups like one-file-per-section or developer-conf. The downside of the proposed newbie.conf is that we need to maintain another configuration file. During beta time, some parameters could be added/removed to/from newbie.conf. > https://github.com/pgexperts/accidentalDBA/blob/master/vagrant/setup/postgres/postgresql.conf > Your example is an good resource for newbies. I would like to see an archive section (separated from replication) and some more log options (where is log_file_prefix and log_duration?). port? A ssl section? track_function? That could have others but those are on my preference list. > While we likely wouldn't want to ship all of the advice in the comments > in that file (the calculations, in particular, have been questioned > since they were last tested with PostgreSQL 8.3), that gives you an > example of what a simple/mainstream pg.conf could look like. I would > further advocate that that file be broken up into segments (resources, > logging, connects, etc.) and placed in conf.d/ > IMHO too many files could confuse simple installations. > All that being said, what *actually* ships with PostgreSQL is up to the > packagers, so anything we do with pg.conf will have a limited impact > unless we get them on board with the idea. > In my experience, packagers tend to follow the default postgresql.conf. They don't add or remove parameters but replace values. -- Euler Taveira Timbira - http://www.timbira.com.br/ PostgreSQL: Consultoria, Desenvolvimento, Suporte24x7 e Treinamento
* David G. Johnston: > "enables or disables data durability promise of ACID." ? “fsync = on” only works if the storage stack doesn't do funny things. Depending on the system, it might not be sufficient.
On Sunday, March 22, 2015, Florian Weimer <fw@deneb.enyo.de> wrote:
Given the existence of developer settings I would add this to that list. People wanting specialized configurations where this would be disabled will learn about it elsewhere, confirm its existence in the docs, and then add it to their custom configuration. Those who do not learn elsewhere probably shouldn't be changing it in the first place.
* David G. Johnston:
> "enables or disables data durability promise of ACID." ?
“fsync = on” only works if the storage stack doesn't do funny things.
Depending on the system, it might not be sufficient.
Allows for (underlying storage not withstanding) or disables, then.
But that distinction is present no matter what so from the standpoint the alternative is no worse and at least tells the user that a key promise of RDBMS is being voluntarily waived if they disable this setting.
David J.
On 03/22/2015 06:45 AM, Andres Freund wrote: > On 2015-03-21 13:53:47 -0700, Josh Berkus wrote: >> Coincidentally, I am just at this moment performance testing "running >> with scissors mode" for PostgreSQL on AWS. When intentional, this mode >> is useful for spinning up lots of read-only replicas which are intended >> mainly as cache support, something I've done at various dot-coms. > > Which is where fsync=off barely has any effect? Well, I'll admit that I'm not testing each setting independantly. I have enough tests to run already. >> So, -1 on removing the setting; it is useful to some users. > > Agreed on that. > >> Further, full_page_writes=off is supposedly safe on any copy-on-write >> filesystem, such as ZFS. > > FWIW, I think that's a myth. One I heard various versions of by now. As > long as the OSs page size (4kb nearly everywhere) is different from > postgres' (8kb) you can have torn pages. Even if individual filesystem > page writes are atomic. ZFS's block size is larger than Linux's memory page size. That is, ZFS on Linux uses a 8kB to 128kB block size depending on which blocks you're looking at and how you have it configured. Does that make a difference at all, given that Linux's memory page size is still 4kB? FYI, the BTRFS folks are also claiming to be torn-page-proof, so it would be really nice to settle this. Not sure how to force the issue through testing though. -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com
* David G. Johnston: > On Sunday, March 22, 2015, Florian Weimer <fw@deneb.enyo.de> wrote: > >> * David G. Johnston: >> >> > "enables or disables data durability promise of ACID." ? >> >> “fsync = on” only works if the storage stack doesn't do funny things. >> Depending on the system, it might not be sufficient. >> > > Allows for (underlying storage not withstanding) or disables, then. Maybe. > But that distinction is present no matter what so from the standpoint the > alternative is no worse and at least tells the user that a key promise of > RDBMS is being voluntarily waived if they disable this setting. I don't think this will matter in the end. The existing postgresql.conf template does not suggest at all that fsync=off makes things go substantially faster. Administrators obviously get the idea from somewhere else, and they will continue to follow that advice no matter what the configuration template says.
On Sun, Mar 22, 2015 at 3:34 PM, Euler Taveira <euler@timbira.com.br> wrote: > On 21-03-2015 17:53, Josh Berkus wrote: >> Now, I have *long* been an advocate that we should ship a "stripped" >> PostgreSQL.conf which has only the most commonly used settings, and >> leave the rest of the settings in the docs and >> share/postgresql/postgresql.conf.advanced. Here's my example of such a >> file, tailored to PostgreSQL 9.3: >> > +1. I agree that common used settings in a postgresql.conf file is > useful for newbies. How do we ship it? Fwiw I disagree. I'm a fan of the idea that the default should be an empty config file. You should only have to put things in postgresql.conf if you want something unusual or specific. We're a long way from there but I would rather move towards there than keep operating under the assumption that nobody will run Postgres without first completing the rite of passage of reviewing every option in postgresql.conf to see if it's relevant to their setup. Apache used to ship with a config full of commented out options and going through and figuring out which options needed to be enabled or changed was the first step in installing Apache. It was awful. When they adopted a strict policy that the default config was empty so the only things you need in your config are options to specify what you want to serve it became so much easier. I would argue it also represents a more professional attitude where the job of the admin is to declare only what he wants to happen and how it should differ from the norm and the job of the software is to go about its business without needing to be set up for normal uses. -- greg
On 22/03/15 08:14, Jaime Casanova wrote: > > El mar 21, 2015 2:00 AM, "Mark Kirkwood" <mark.kirkwood@catalyst.net.nz > <mailto:mark.kirkwood@catalyst.net.nz>> escribió: > > > > On 21/03/15 19:28, Jaime Casanova wrote: > >> > >> what about not removing it but not showing it in postgresql.conf? as a > >> side note, i wonder why trace_sort is not in postgresql.conf... > >> other option is to make it a compile setting, that why if you want to > >> have it you need to compile and postgres' developers do that routinely > >> anyway > >> > > > > -1 > > > > Personally I'm against hiding *any* settings. Choosing sensible > defaults - yes! Hiding them - that reeks of secret squirrel nonsense and > overpaid Oracle dbas that knew the undocumented settings for various > capabilities. I think/hope that no open source project will try to > emulate that meme! > > > > That ship has already sailed. > > http://www.postgresql.org/docs/9.4/static/runtime-config-developer.html > Not really - they are documented in the official doc repo (that was the point I was making I think), and +1 for adding or improving the documentation for some of the more dangerous ones! While I'm against removing or hiding settings, I have no problem with shipping/generating a postgresql.conf that has *only* the non default settings therein, as that requires people to look at the docs where (of course) we have some sensible discussion about how to set the rest of 'em. I note that Mysql ship a pretty minimal confile files there days (5.5, 5.6) on Ubuntu, and that seems to cause no particular problem. regards Mark
On 2015-03-22 12:54:37 -0700, Josh Berkus wrote: > On 03/22/2015 06:45 AM, Andres Freund wrote: > > FWIW, I think that's a myth. One I heard various versions of by now. As > > long as the OSs page size (4kb nearly everywhere) is different from > > postgres' (8kb) you can have torn pages. Even if individual filesystem > > page writes are atomic. > > ZFS's block size is larger than Linux's memory page size. That is, ZFS > on Linux uses a 8kB to 128kB block size depending on which blocks you're > looking at and how you have it configured. Does that make a difference > at all, given that Linux's memory page size is still 4kB? No, it does not make a differencen. The page cache is still 4kb. > FYI, the BTRFS folks are also claiming to be torn-page-proof, so it > would be really nice to settle this. Not sure how to force the issue > through testing though. I don't think this applies here. Greetings, Andres Freund -- Andres Freund http://www.2ndQuadrant.com/PostgreSQL Development, 24x7 Support, Training & Services
On 3/21/15 12:25 PM, Jeff Janes wrote: > On Sat, Mar 21, 2015 at 8:54 AM, Tom Lane <tgl@sss.pgh.pa.us > <mailto:tgl@sss.pgh.pa.us>> wrote: > > Stephen Frost <sfrost@snowman.net <mailto:sfrost@snowman.net>> writes: > > At the moment, one could look at our default postgresql.conf and the > > "turns forced synchronization on or off" and think it's something akin > > or somehow related to synchronous_commit (which is completely different, > > but the options are right next to each other..). > > > How about a big warning around fsync and make it more indepenent from > > the options around it? > > Yeah, the main SGML docs are reasonably clear about the risks of fsync, > but postgresql.conf doesn't give you any hint that it's dangerous. Now > I'm not entirely sure that people who frob postgresql.conf without > having > read the docs can be saved from themselves, but we could do something > like this: > > # - Settings - > > #wal_level = minimal # minimal, archive, > hot_standby, or logical > # (change requires restart) > #fsync = on # turns forced > synchronization on or off > + # (fsync=off is dangerous, > read the > + # (manual before using it) > #synchronous_commit = on # synchronization level; > # off, local, remote_write, > or on > #wal_sync_method = fsync # the default is the first > option > # supported by the > operating system: > > Also, I think the short description "turns forced synchronization on or > off" could stand improvement; it really conveys zero information. Maybe > something like "force data to disk when committing"? > > > I agree the current description is lacking, but that proposed wording > would be a better description of synchronous_commit. It is > checkpointing and flush-WAL-before-data where fsync=off does its damage. > "Force data to disk when needed for integrity"? > > Or just don't describe what it is at all, and refer to the documentation > only. I see 3 settings that allow people to accidentally shoot themselves in the foot; fsync, wal_sync_method and full_page_writes. How about just grouping those 3 together with a bulk disclaimer along the lines of "The following 3 settings are dangerous. Use at your own risk, and read the docs first."? That would also allow us to just remove the comments about what the settings do; if you don't already know you certainly shouldn't be touching them! :) -- Jim Nasby, Data Architect, Blue Treble Consulting Data in Trouble? Get it in Treble! http://BlueTreble.com
On 3/22/15 4:50 PM, Greg Stark wrote: > On Sun, Mar 22, 2015 at 3:34 PM, Euler Taveira <euler@timbira.com.br> wrote: >> On 21-03-2015 17:53, Josh Berkus wrote: >>> Now, I have *long* been an advocate that we should ship a "stripped" >>> PostgreSQL.conf which has only the most commonly used settings, and >>> leave the rest of the settings in the docs and >>> share/postgresql/postgresql.conf.advanced. Here's my example of such a >>> file, tailored to PostgreSQL 9.3: >>> >> +1. I agree that common used settings in a postgresql.conf file is >> useful for newbies. How do we ship it? > > > Fwiw I disagree. I'm a fan of the idea that the default should be an > empty config file. You should only have to put things in > postgresql.conf if you want something unusual or specific. We're a > long way from there but I would rather move towards there than keep > operating under the assumption that nobody will run Postgres without > first completing the rite of passage of reviewing every option in > postgresql.conf to see if it's relevant to their setup. > > Apache used to ship with a config full of commented out options and > going through and figuring out which options needed to be enabled or > changed was the first step in installing Apache. It was awful. When > they adopted a strict policy that the default config was empty so the > only things you need in your config are options to specify what you > want to serve it became so much easier. I would argue it also > represents a more professional attitude where the job of the admin is > to declare only what he wants to happen and how it should differ from > the norm and the job of the software is to go about its business > without needing to be set up for normal uses. +1. Going the route of big default config files just sucks. We should either just have an empty .conf, or only write stuff that initdb has tweaked in it. -- Jim Nasby, Data Architect, Blue Treble Consulting Data in Trouble? Get it in Treble! http://BlueTreble.com
On Wed, Mar 25, 2015 at 12:45 PM, Jim Nasby <Jim.Nasby@bluetreble.com> wrote:
I see 3 settings that allow people to accidentally shoot themselves in the foot; fsync, wal_sync_method and full_page_writes.
How about just grouping those 3 together with a bulk disclaimer along the lines of "The following 3 settings are dangerous. Use at your own risk, and read the docs first."? That would also allow us to just remove the comments about what the settings do; if you don't already know you certainly shouldn't be touching them! :)
But one of these things is not like the other. Any supported (i.e. non fatal erroring) setting of wal_sync_method *should* always be safe (although may be inefficient) if the underlying kernel, RAID controller, hard drives, and fs fulfill their pledges. It is hard to document every known liar in this regard. About the best you can do, short of pull-the-plug test on a massive scale, is to run pg_fsync_test and assuming that any result inconsistent with the RPM of the spinning rust is obviously unsafe. Unfortunately that doesn't rule out the possibility that something is both unsafe and gratuitously slow.
Cheers,
Jeff
On 3/25/15 8:35 PM, Jeff Janes wrote: > On Wed, Mar 25, 2015 at 12:45 PM, Jim Nasby <Jim.Nasby@bluetreble.com > <mailto:Jim.Nasby@bluetreble.com>> wrote: > > > I see 3 settings that allow people to accidentally shoot themselves > in the foot; fsync, wal_sync_method and full_page_writes. > > How about just grouping those 3 together with a bulk disclaimer > along the lines of "The following 3 settings are dangerous. Use at > your own risk, and read the docs first."? That would also allow us > to just remove the comments about what the settings do; if you don't > already know you certainly shouldn't be touching them! :) > > > But one of these things is not like the other. Any supported (i.e. non > fatal erroring) setting of wal_sync_method *should* always be safe > (although may be inefficient) if the underlying kernel, RAID controller, > hard drives, and fs fulfill their pledges. It is hard to document every > known liar in this regard. About the best you can do, short of > pull-the-plug test on a massive scale, is to run pg_fsync_test and > assuming that any result inconsistent with the RPM of the spinning rust > is obviously unsafe. Unfortunately that doesn't rule out the possibility > that something is both unsafe and gratuitously slow. I agree, but the reason I include this setting as dangerous is you really don't know what you're getting into once you move past fsync unless you actually study it and/or do testing. To me, that makes that setting dangerous. -- Jim Nasby, Data Architect, Blue Treble Consulting Data in Trouble? Get it in Treble! http://BlueTreble.com
On Wed, Mar 25, 2015 at 3:45 PM, Jim Nasby <Jim.Nasby@bluetreble.com> wrote: > I see 3 settings that allow people to accidentally shoot themselves in the > foot; fsync, wal_sync_method and full_page_writes. Those aren't even the top three in my experience, let alone the only three. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company