Remove fsync ON/OFF as a visible option?

"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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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"

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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"

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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.

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

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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"?

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

El mar 21, 2015 2:00 AM, "Mark Kirkwood" <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

--
Jaime Casanova
2ndQuadrant Consultant
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"

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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"

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers