Re: no universally correct setting for fsync

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
arephysically 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
consistentstate after an operating system or hardware crash.      </para>
 
      <para>       While turning off <varname>fsync</varname> is often a performance       benefit, this can result in
unrecoverabledata 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
externaldata.      </para>
 
      <para>Examples of safe times to turn off <varname>fsync</varname> would be       when initially loading a new
databasefrom a backup file, on a
 
database which isonly 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
performancebenefit 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
considerturning 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
forWAL 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-committedtransactions 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
exactcertainty 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
determinedby the setting in effect when it       commits.  It is therefore possible, and useful, to have some
transactionscommit synchronously and others asynchronously.       For example, to make a single multi-statement
transactioncommit       asynchronously when the default is the opposite, issue <command>SET       LOCAL
synchronous_commitTO 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
outat 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
choicesare available on all platforms.       The default is the first method in the above list that is supported
bythe 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
modificationof that page after a checkpoint.       This is needed because       a page write that is in process during
anoperating system crash
 
might       be only partially completed, leading to an on-disk page       that contains a mix of old and new data.  The
row-levelchange data       normally stored in WAL will not be enough to completely restore       such a page during
post-crashrecovery.  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
thecost 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
datacorruption, 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
commandline.       The default is <literal>on</>.      </para>     </listitem>    </varlistentry>
 




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