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

On 03/20/2015 09:29 PM, Michael Paquier wrote:
> On Sat, Mar 21, 2015 at 2:47 AM, Peter Geoghegan <pg(at)heroku(dot)com> wrote:
>> On Fri, Mar 20, 2015 at 9:52 AM, Joshua D. Drake <jd(at)commandprompt(dot)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