Received: from maia.hub.org (maia-5.hub.org [200.46.204.29])
	by mail.postgresql.org (Postfix) with ESMTP id D6E2B63229F
	for <pgsql-hackers-postgresql.org@mail.postgresql.org>;
	Mon, 10 May 2010 17:35:52 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.29]) (amavisd-maia, port 10024)
	with ESMTP id 36361-02
	for <pgsql-hackers-postgresql.org@mail.postgresql.org>;
	Mon, 10 May 2010 20:35:45 +0000 (UTC)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from glacier.frostconsultingllc.com (glacier.frostconsultingllc.com
	[69.36.227.170])
	by mail.postgresql.org (Postfix) with ESMTP id D2895632880
	for <pgsql-hackers@postgresql.org>;
	Mon, 10 May 2010 17:35:44 -0300 (ADT)
Received: from dsl081-245-111.sfo1.dsl.speakeasy.net ([64.81.245.111]
	helo=Sidney-Stratton.local)
	by glacier.frostconsultingllc.com with esmtpsa
	(TLSv1:CAMELLIA256-SHA:256) (Exim 4.69)
	(envelope-from <josh@agliodbs.com>)
	id 1OBZhN-0005Yj-2S; Mon, 10 May 2010 13:35:37 -0700
Message-ID: <4BE86E14.1030707@agliodbs.com>
Date: Mon, 10 May 2010 13:35:32 -0700
From: Josh Berkus <josh@agliodbs.com>
User-Agent: Mozilla/5.0 (Macintosh; U; Intel Mac OS X 10.5; en-US;
	rv:1.9.1b3pre) Gecko/20090223 Thunderbird/3.0b2
MIME-Version: 1.0
To: =?UTF-8?B?Q8OpZHJpYyBWaWxsZW1haW4=?=
 <cedric.villemain.debian@gmail.com>
CC: Bernd Helmle <mailings@oopsware.de>,
	Kevin Grittner <Kevin.Grittner@wicourts.gov>,
	Andrew Dunstan <andrew@dunslane.net>, pgsql-hackers@postgresql.org
Subject: Re: no universally correct setting for fsync
References: <4BE3D3930200002500031382@gw.wicourts.gov>
	<4BE425F7.30804@dunslane.net>
	<4BE3E205020000250003138F@gw.wicourts.gov>
	<A1EBC6E5100210B51612365E@amenophis>
	<AANLkTin_XJNc3uxB5730VUbeovj2QtHV3JZc5tJT9Zup@mail.gmail.com>
In-Reply-To: <AANLkTin_XJNc3uxB5730VUbeovj2QtHV3JZc5tJT9Zup@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-1.9 tagged_above=-5 required=5 tests=BAYES_00=-1.9
X-Spam-Level: 
X-Archive-Number: 201005/481
X-Sequence-Number: 162144

All,

Updated docs based on tracking this discussion.  fsync through full page
writes recorded below.

============


     <varlistentry id="guc-fsync" xreflabel="fsync">
      <indexterm>
       <primary><varname>fsync</> configuration parameter</primary>
      </indexterm>
      <term><varname>fsync</varname> (<type>boolean</type>)</term>
      <listitem>
       <para>
        If this parameter is on, the <productname>PostgreSQL</> server
        will try to make sure that updates are physically written to
        disk, by issuing <function>fsync()</> system calls or various
        equivalent methods (see <xref linkend="guc-wal-sync-method">).
        This ensures that the database cluster can recover to a
        consistent state after an operating system or hardware crash.
       </para>

       <para>
        While turning off <varname>fsync</varname> is often a performance
        benefit, this can result in unrecoverable data corruption in the
event
        of an unexpected system shutdown or crash.  Thus it is only
advisable
        to turn off  <varname>fsync</varname> if you can easily recreate
        your entire database from external data.
       </para>

       <para>
	Examples of safe times to turn off <varname>fsync</varname> would be
        when initially loading a new database from a backup file, on a
database which is
	only used for processing statistics on an hourly basis and is then
deleted,
        or on a reporting read-only clone of your database which gets
recreated very
        night and is not used for failover.  High quality hardware alone
is not a
        sufficient justification for turning off <varname>fsync</varname>.
       </para>

       <para>
        In many situations, turning off <xref
linkend="guc-synchronous-commit">
        for noncritical transactions can provide much of the potential
        performance benefit of turning off <varname>fsync</varname>, without
        the attendant risks of data corruption.
       </para>

       <para>
        <varname>fsync</varname> can only be set in the
<filename>postgresql.conf</>
        file or on the server command line.
        If you turn this parameter off, also consider turning off
        <xref linkend="guc-full-page-writes">.
       </para>
      </listitem>
     </varlistentry>

     <varlistentry id="guc-synchronous-commit"
xreflabel="synchronous_commit">
      <term><varname>synchronous_commit</varname>
(<type>boolean</type>)</term>
      <indexterm>
       <primary><varname>synchronous_commit</> configuration
parameter</primary>
      </indexterm>
      <listitem>
       <para>
        Specifies whether transaction commit will wait for WAL records
        to be written to disk before the command returns a <quote>success</>
        indication to the client.  The default, and safe, setting is
        <literal>on</>.  When <literal>off</>, there can be a delay between
        when success is reported to the client and when the transaction is
        really guaranteed to be safe against a server crash.  (The maximum
        delay is three times <xref linkend="guc-wal-writer-delay">.)  Unlike
        <xref linkend="guc-fsync">, setting this parameter to
<literal>off</>
        does not create any risk of database inconsistency: a crash might
        result in some recent allegedly-committed transactions being
lost, but
        the database state will be just the same as if those
transactions had
        been aborted cleanly.  So, turning
<varname>synchronous_commit</> off
        can be a useful alternative when performance is more important than
        exact certainty about the durability of a transaction.  For more
        discussion see <xref linkend="wal-async-commit">.
       </para>
       <para>
        This parameter can be changed at any time; the behavior for any
        one transaction is determined by the setting in effect when it
        commits.  It is therefore possible, and useful, to have some
        transactions commit synchronously and others asynchronously.
        For example, to make a single multi-statement transaction commit
        asynchronously when the default is the opposite, issue <command>SET
        LOCAL synchronous_commit TO OFF</> within the transaction.
       </para>
      </listitem>
     </varlistentry>

     <varlistentry id="guc-wal-sync-method" xreflabel="wal_sync_method">
      <term><varname>wal_sync_method</varname> (<type>enum</type>)</term>
      <indexterm>
       <primary><varname>wal_sync_method</> configuration
parameter</primary>
      </indexterm>
      <listitem>
       <para>
        Method used for forcing WAL updates out to disk.
        If <varname>fsync</varname> is off then this setting is irrelevant,
        since WAL file updates will not be forced out at all.
        Possible values are:
       </para>
       <itemizedlist>
        <listitem>
        <para>
         <literal>open_datasync</> (write WAL files with
<function>open()</> option <symbol>O_DSYNC</>)
        </para>
        </listitem>
        <listitem>
        <para>
         <literal>fdatasync</> (call <function>fdatasync()</> at each
commit)
        </para>
        </listitem>
        <listitem>
        <para>
         <literal>fsync_writethrough</> (call <function>fsync()</> at
each commit, forcing write-through of any disk write cache)
        </para>
        </listitem>
        <listitem>
        <para>
         <literal>fsync</> (call <function>fsync()</> at each commit)
        </para>
        </listitem>
        <listitem>
        <para>
         <literal>open_sync</> (write WAL files with <function>open()</>
option <symbol>O_SYNC</>)
        </para>
        </listitem>
       </itemizedlist>
       <para>
        Not all of these choices are available on all platforms.
        The default is the first method in the above list that is supported
        by the platform.
        The <literal>open_</>* options also use <literal>O_DIRECT</> if
available.
        The utility <filename>src/tools/fsync</> in the PostgreSQL
source tree
        can do performance testing of various fsync methods.
        This parameter can only be set in the <filename>postgresql.conf</>
        file or on the server command line.
       </para>
      </listitem>
     </varlistentry>

     <varlistentry id="guc-full-page-writes" xreflabel="full_page_writes">
      <indexterm>
       <primary><varname>full_page_writes</> configuration
parameter</primary>
      </indexterm>
      <term><varname>full_page_writes</varname>
(<type>boolean</type>)</term>
      <listitem>
       <para>
        When this parameter is on, the <productname>PostgreSQL</> server
        writes the entire content of each disk page to WAL during the
        first modification of that page after a checkpoint.
        This is needed because
        a page write that is in process during an operating system crash
might
        be only partially completed, leading to an on-disk page
        that contains a mix of old and new data.  The row-level change data
        normally stored in WAL will not be enough to completely restore
        such a page during post-crash recovery.  Storing the full page image
        guarantees that the page can be correctly restored, but at the price
        of increasing the amount of data that must be written to WAL.
        (Because WAL replay always starts from a checkpoint, it is
sufficient
        to do this during the first change of each page after a checkpoint.
        Therefore, one way to reduce the cost of full-page writes is to
        increase the checkpoint interval parameters.)
       </para>

       <para>
        Turning this parameter off speeds normal operation, but
        might lead to either unrecoverable data corruption, or silent
        data corruption, after a system failure. The risks are similar
to turning off
        <varname>fsync</varname>, though smaller, and it should be
turned off
        only based on the same circumstances recommended for that parameter.
       </para>

       <para>
        Turning off this parameter does not affect use of
        WAL archiving for point-in-time recovery (PITR)
        (see <xref linkend="continuous-archiving">).
       </para>

       <para>
        This parameter can only be set in the <filename>postgresql.conf</>
        file or on the server command line.
        The default is <literal>on</>.
       </para>
      </listitem>
     </varlistentry>




-- 
                                  -- Josh Berkus
                                     PostgreSQL Experts Inc.
                                     http://www.pgexperts.com