Re: file system and raid performance - Mailing list pgsql-performance

Mark Mielke wrote:
> Greg Smith wrote:
> > On Fri, 15 Aug 2008, Bruce Momjian wrote:
> >> 'data=writeback' is the recommended mount method for that file
> >> system, though I see that is not mentioned in our official
> >> documentation.
> > While writeback has good performance characteristics, I don't know
> > that I'd go so far as to support making that an official
> > recommendation.  The integrity guarantees of that journaling mode are
> > pretty weak.  Sure the database itself should be fine; it's got the
> > WAL as a backup if the filesytem loses some recently written bits.
> > But I'd hate to see somebody switch to that mount option on this
> > project's recommendation only to find some other files got corrupted
> > on a power loss because of writeback's limited journalling.  ext3 has
> > plenty of problem already without picking its least safe mode, and
> > recommending writeback would need a carefully written warning to that
> > effect.
>
> To contrast - not recommending it means that most people unaware will be
> running with a less effective mode, and they will base their performance
> measurements on this less effective mode.
>
> Perhaps the documentation should only state that "With ext3,
> data=writeback is the recommended mode for PostgreSQL. PostgreSQL
> performs its own journalling of data and does not require the additional
> guarantees provided by the more conservative ext3 modes. However, if the
> file system is used for any purpose other than PostregSQL database
> storage, the data integrity requirements of these other purposes must be
> considered on their own."
>
> Personally, I use data=writeback for most purposes, but use data=journal
> for /mail and /home. In these cases, I find even the default ext3 mode
> to be fewer guarantees than I am comfortable with. :-)

I have documented this in the WAL section of the manual, which seemed
like the most logical location.

--
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +
Index: doc/src/sgml/wal.sgml
===================================================================
RCS file: /cvsroot/pgsql/doc/src/sgml/wal.sgml,v
retrieving revision 1.53
diff -c -c -r1.53 wal.sgml
*** doc/src/sgml/wal.sgml    2 May 2008 19:52:37 -0000    1.53
--- doc/src/sgml/wal.sgml    6 Dec 2008 21:32:59 -0000
***************
*** 135,140 ****
--- 135,155 ----
      roll-forward recovery, also known as REDO.)
     </para>

+    <tip>
+     <para>
+      Because <acronym>WAL</acronym> restores database file
+      contents after a crash, it is not necessary to use a
+      journaled filesystem;  in fact, journaling overhead can
+      reduce performance.  For best performance, turn off
+      <emphasis>data</emphasis> journaling as a filesystem mount
+      option, e.g. use <literal>data=writeback</> on Linux.
+      Meta-data journaling (e.g.  file creation and directory
+      modification) is still desirable for faster rebooting after
+      a crash.
+     </para>
+    </tip>
+
+
     <para>
      Using <acronym>WAL</acronym> results in a
      significantly reduced number of disk writes, because only the log