Thread: Re: [HACKERS] no universally correct setting for fsync

Folks,

This is what I have to replace the current fsync entry in config.sgml.

I believe that the note about needing fsync for Warm Standby to work
correctly is true, but could someone verify it?

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

     <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 shutdown. Thus it is only advisable to turn off
        <varname>fsync</varname> if you can easily recreate
        your entire database from external data.  <varname>fsync</varname>
        must be on for WAL archiving to work correctly
        (see <xref linkend="continuous-archiving">).
       <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>

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

Josh Berkus <josh@agliodbs.com> writes:
> This is what I have to replace the current fsync entry in config.sgml.

s/unexpected shutdown/system crash/, perhaps.  The wording you have
suggests that a forced Postgres stoppage produces a problem, which it
doesn't.  It takes a failure at the OS level or below to cause a
problem.

> I believe that the note about needing fsync for Warm Standby to work
> correctly is true, but could someone verify it?

AFAIK that's nonsense.  The filesystem state that pg_standby could see
will be updated in any case; pg_standby has no direct access to the bits
on the platters, any more than Postgres does.

            regards, tom lane

On 5/7/10 5:13 PM, Tom Lane wrote:
> Josh Berkus <josh@agliodbs.com> writes:
>> This is what I have to replace the current fsync entry in config.sgml.
>
> s/unexpected shutdown/system crash/, perhaps.  The wording you have
> suggests that a forced Postgres stoppage produces a problem, which it
> doesn't.  It takes a failure at the OS level or below to cause a
> problem.

I actually meant "unexpected *system* shutdown", i.e. power-out.  A lot
of people think "crash" just means kernel dump, whereas a UPS failure or
tripped power cord is a lot more likely (except maybe on Windows).

Revised:

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


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



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