Re: [HACKERS] Remove fsync ON/OFF as a visible option?

2015-04-02 Thread Robert Haas
On Wed, Mar 25, 2015 at 3:45 PM, Jim Nasby jim.na...@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


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


Re: [HACKERS] Remove fsync ON/OFF as a visible option?

2015-03-26 Thread Jim Nasby

On 3/25/15 8:35 PM, Jeff Janes wrote:

On Wed, Mar 25, 2015 at 12:45 PM, Jim Nasby jim.na...@bluetreble.com
mailto:jim.na...@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


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


Re: [HACKERS] Remove fsync ON/OFF as a visible option?

2015-03-25 Thread Jim Nasby

On 3/21/15 12:25 PM, Jeff Janes wrote:

On Sat, Mar 21, 2015 at 8:54 AM, Tom Lane t...@sss.pgh.pa.us
mailto:t...@sss.pgh.pa.us wrote:

Stephen Frost sfr...@snowman.net mailto:sfr...@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


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


Re: [HACKERS] Remove fsync ON/OFF as a visible option?

2015-03-25 Thread Jeff Janes
On Wed, Mar 25, 2015 at 12:45 PM, Jim Nasby jim.na...@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


Re: [HACKERS] Remove fsync ON/OFF as a visible option?

2015-03-25 Thread Jim Nasby

On 3/22/15 4:50 PM, Greg Stark wrote:

On Sun, Mar 22, 2015 at 3:34 PM, Euler Taveira eu...@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


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


Re: [HACKERS] Remove fsync ON/OFF as a visible option?

2015-03-23 Thread Andres Freund
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


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


Re: [HACKERS] Remove fsync ON/OFF as a visible option?

2015-03-22 Thread Andres Freund
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


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


Re: [HACKERS] Remove fsync ON/OFF as a visible option?

2015-03-22 Thread Euler Taveira
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, Suporte 24x7 e Treinamento


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


Re: [HACKERS] Remove fsync ON/OFF as a visible option?

2015-03-22 Thread Florian Weimer
* 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.


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


Re: [HACKERS] Remove fsync ON/OFF as a visible option?

2015-03-22 Thread Josh Berkus
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


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


Re: [HACKERS] Remove fsync ON/OFF as a visible option?

2015-03-22 Thread David G. Johnston
On Sunday, March 22, 2015, Florian Weimer f...@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.

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.

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 J.


Re: [HACKERS] Remove fsync ON/OFF as a visible option?

2015-03-22 Thread Mark Kirkwood

On 22/03/15 08:14, Jaime Casanova wrote:


El mar 21, 2015 2:00 AM, Mark Kirkwood mark.kirkw...@catalyst.net.nz
mailto:mark.kirkw...@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


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


Re: [HACKERS] Remove fsync ON/OFF as a visible option?

2015-03-21 Thread Mark Kirkwood

On 21/03/15 19:28, Jaime Casanova wrote:

On Fri, Mar 20, 2015 at 11:29 PM, Michael Paquier
michael.paqu...@gmail.com wrote:

On Sat, Mar 21, 2015 at 2:47 AM, Peter Geoghegan p...@heroku.com wrote:

On Fri, Mar 20, 2015 at 9:52 AM, Joshua D. Drake j...@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


Re: [HACKERS] Remove fsync ON/OFF as a visible option?

2015-03-21 Thread Jaime Casanova
On Fri, Mar 20, 2015 at 11:29 PM, Michael Paquier
michael.paqu...@gmail.com wrote:
 On Sat, Mar 21, 2015 at 2:47 AM, Peter Geoghegan p...@heroku.com wrote:
 On Fri, Mar 20, 2015 at 9:52 AM, Joshua D. Drake j...@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


Re: [HACKERS] Remove fsync ON/OFF as a visible option?

2015-03-21 Thread Tom Lane
Stephen Frost sfr...@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


Re: [HACKERS] Remove fsync ON/OFF as a visible option?

2015-03-21 Thread David Fetter
On Sat, Mar 21, 2015 at 11:54:00AM -0400, Tom Lane wrote:
 Stephen Frost sfr...@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 da...@fetter.org http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter  XMPP: david.fet...@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


Re: [HACKERS] Remove fsync ON/OFF as a visible option?

2015-03-21 Thread David G. Johnston
On Sat, Mar 21, 2015 at 8:54 AM, Tom Lane t...@sss.pgh.pa.us wrote:

 Stephen Frost sfr...@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.


Re: [HACKERS] Remove fsync ON/OFF as a visible option?

2015-03-21 Thread Jeff Janes
On Sat, Mar 21, 2015 at 8:54 AM, Tom Lane t...@sss.pgh.pa.us wrote:

 Stephen Frost sfr...@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


Re: [HACKERS] Remove fsync ON/OFF as a visible option?

2015-03-21 Thread Joshua D. Drake


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


Re: [HACKERS] Remove fsync ON/OFF as a visible option?

2015-03-21 Thread Joshua D. Drake


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



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


Re: [HACKERS] Remove fsync ON/OFF as a visible option?

2015-03-21 Thread Gavin Flower

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


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


Re: [HACKERS] Remove fsync ON/OFF as a visible option?

2015-03-21 Thread Gavin Flower

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


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


Re: [HACKERS] Remove fsync ON/OFF as a visible option?

2015-03-21 Thread Joshua D. Drake


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



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


Re: [HACKERS] Remove fsync ON/OFF as a visible option?

2015-03-21 Thread Joshua D. Drake


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



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


Re: [HACKERS] Remove fsync ON/OFF as a visible option?

2015-03-21 Thread Jaime Casanova
El mar 21, 2015 2:00 AM, Mark Kirkwood mark.kirkw...@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


Re: [HACKERS] Remove fsync ON/OFF as a visible option?

2015-03-21 Thread Gavin Flower

On 22/03/15 05:42, David G. Johnston wrote:
On Sat, Mar 21, 2015 at 8:54 AM, Tom Lane t...@sss.pgh.pa.us 
mailto:t...@sss.pgh.pa.uswrote:


Stephen Frost sfr...@snowman.net mailto:sfr...@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


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


Re: [HACKERS] Remove fsync ON/OFF as a visible option?

2015-03-21 Thread Joshua D. Drake


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


Re: [HACKERS] Remove fsync ON/OFF as a visible option?

2015-03-21 Thread Joshua D. Drake


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



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


Re: [HACKERS] Remove fsync ON/OFF as a visible option?

2015-03-21 Thread Jaime Casanova
On Sat, Mar 21, 2015 at 2:33 PM, Joshua D. Drake j...@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


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


Re: [HACKERS] Remove fsync ON/OFF as a visible option?

2015-03-21 Thread Josh Berkus
On 03/20/2015 09:29 PM, Michael Paquier wrote:
 On Sat, Mar 21, 2015 at 2:47 AM, Peter Geoghegan p...@heroku.com wrote:
 On Fri, Mar 20, 2015 at 9:52 AM, Joshua D. Drake j...@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


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


Re: [HACKERS] Remove fsync ON/OFF as a visible option?

2015-03-21 Thread Jerry Sievers
Joshua D. Drake j...@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.consult...@comcast.net
p: 312.241.7800


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


Re: [HACKERS] Remove fsync ON/OFF as a visible option?

2015-03-20 Thread Jim Nasby

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


Re: [HACKERS] Remove fsync ON/OFF as a visible option?

2015-03-20 Thread Robert Haas
On Fri, Mar 20, 2015 at 3:26 PM, Joshua D. Drake j...@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


Re: [HACKERS] Remove fsync ON/OFF as a visible option?

2015-03-20 Thread Jim Nasby

On 3/20/15 6:09 PM, Robert Haas wrote:

On Fri, Mar 20, 2015 at 3:26 PM, Joshua D. Drake j...@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


Re: [HACKERS] Remove fsync ON/OFF as a visible option?

2015-03-20 Thread Michael Paquier
On Sat, Mar 21, 2015 at 2:47 AM, Peter Geoghegan p...@heroku.com wrote:
 On Fri, Mar 20, 2015 at 9:52 AM, Joshua D. Drake j...@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


Re: [HACKERS] Remove fsync ON/OFF as a visible option?

2015-03-20 Thread Tom Lane
Joshua D. Drake j...@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


Re: [HACKERS] Remove fsync ON/OFF as a visible option?

2015-03-20 Thread Peter Geoghegan
On Fri, Mar 20, 2015 at 9:52 AM, Joshua D. Drake j...@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


Re: [HACKERS] Remove fsync ON/OFF as a visible option?

2015-03-20 Thread Joshua D. Drake


On 03/20/2015 10:45 AM, Tom Lane wrote:

Joshua D. Drake j...@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


Re: [HACKERS] Remove fsync ON/OFF as a visible option?

2015-03-20 Thread Joshua D. Drake


On 03/20/2015 10:47 AM, Peter Geoghegan wrote:


On Fri, Mar 20, 2015 at 9:52 AM, Joshua D. Drake j...@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


Re: [HACKERS] Remove fsync ON/OFF as a visible option?

2015-03-20 Thread Greg Stark
On Fri, Mar 20, 2015 at 7:29 PM, Joshua D. Drake j...@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


Re: [HACKERS] Remove fsync ON/OFF as a visible option?

2015-03-20 Thread Stephen Frost
* Joshua D. Drake (j...@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


signature.asc
Description: Digital signature