Thread: UNIX File buffering disable?

UNIX File buffering disable?

From
Oli Sennhauser
Date:
Hello

I have heard that UNIX I/O buffer sometimes causes problems during
system crash because it keeps files which, application thinks, are
already written to disk. And then this changes go lost during crash.
Therefore I heard a recommendation, that dba's should disabled UNIX file
buffering on mountpoints where rdbms are running.

What about this? Any hints? What about PostgreSQL? Do we bypass this OS
buffering (fsync?) or should a dba do anything else?

How do I disable this I/O buffering?

Thanks for comments
Oli

-------------------------------------------------------

Oli Sennhauser
Database-Engineer (Oracle & PostgreSQL)
Rebenweg 6
CH - 8610 Uster / Switzerland

Phone (+41) 1 940 24 82 or Mobile (+41) 79 450 49 14
e-Mail oli.sennhauser@bluewin.ch
Website http://mypage.bluewin.ch/shinguz/PostgreSQL/

Secure (signed/encrypted) e-Mail with a Free Personal SwissSign ID: http://www.swisssign.ch

Import the SwissSign Root Certificate: http://swisssign.net/cgi-bin/trust/import


Attachment

Re: UNIX File buffering disable?

From
Sai Hertz And Control Systems
Date:
Hello ,

Hmmm
This concernes me to  does anyone has answers for this please .
and Oli  if you get  this answer from some other posts please forward it
here also.

Regards,
Vishal Kashyap

> Hello
>
> I have heard that UNIX I/O buffer sometimes causes problems during
> system crash because it keeps files which, application thinks, are
> already written to disk. And then this changes go lost during crash.
> Therefore I heard a recommendation, that dba's should disabled UNIX
> file buffering on mountpoints where rdbms are running.
>
> What about this? Any hints? What about PostgreSQL? Do we bypass this
> OS buffering (fsync?) or should a dba do anything else?
>
> How do I disable this I/O buffering?
>
> Thanks for comments
> Oli
>
> -------------------------------------------------------
>
> Oli Sennhauser
> Database-Engineer (Oracle & PostgreSQL)
> Rebenweg 6
> CH - 8610 Uster / Switzerland
>
> Phone (+41) 1 940 24 82 or Mobile (+41) 79 450 49 14
> e-Mail oli.sennhauser@bluewin.ch
> Website http://mypage.bluewin.ch/shinguz/PostgreSQL/
>
> Secure (signed/encrypted) e-Mail with a Free Personal SwissSign ID:
> http://www.swisssign.ch
>
> Import the SwissSign Root Certificate:
> http://swisssign.net/cgi-bin/trust/import
>


Re: UNIX File buffering disable?

From
Tom Lane
Date:
Oli Sennhauser <oli.sennhauser@bluewin.ch> writes:
> Therefore I heard a recommendation, that dba's should disabled UNIX file
> buffering on mountpoints where rdbms are running.

Was this from anyone who actually knows what they're talking about?
You can't (AFAIK), and you don't need to.

            regards, tom lane

Re: UNIX File buffering disable?

From
Greg Spiegelberg
Date:
Yes, databases like Oracle and DB2 can benefit from the file system
option you're referring too but it depends first on the nature of
the database (OLTP v. warehouse), the operating system, the file
system in use and even the hardware.  I have managed many varieties.

One case was DB2 warehouse running on Solaris 7 using the native UFS
on a direct attached UDWIS RAID5.  The mount option is "forcedirectio"
which in that case helped us big time.  Another was a Solaris 8,
Oracle, and UFS on the same type of RAID5 setup which was alse helped
by the "forcedirectio".  That's for all file systems containing data
files, logs, etc.  The Oracle logs should always been on a file system
mounted with forcedirectio.  I have seen a Oracle OLTP database
though that was hindered by that option (noforcedirectio is the default)
though you wouldn't think that would be the case.

I have used PostgreSQL on Solaris 9 with a LUN on a 2Gbps SAN using
the native UFS file system and the best config I came up with was
having a file system just for the pg_xlogs mounted "forcedirectio" and
the rest with the typical "logging"/journaling.

Those are just a few cases I remember off the top of my head.  Best
you can do it test it on your own using your app/query/testsuite
with and without it.

mount -o remount,forcedirectio,nologging /path/to/postgres
  and
mount -o remount,noforcedirectio,logging /path/to/postgres

I recommend turning logging off with forcedirectio.  I have witnessed
"issues" in the past with both turned on at the same time.

Linux?  When we used EXT3, we used the mount option "sync" when loading
massive amounts of data but typically ran with the default "async".
JFS is our filesystem of choice now with the default mount options.

There are too many other file systems to comment on and I'm sure
there's plenty of filesystem threads available on the archive regarding
it.

Point is, there are too many combinations of platforms, hardware, RAID,
OS, filesystems, mount options, databases and applications for one right
answer.  This is possibly the answer I've been seeking as to why
mainframe shops typically had a "DASD" admin whose soul purpose was to
watch spindles.  Go fig.

Greg


Tom Lane wrote:
 > Oli Sennhauser <oli.sennhauser@bluewin.ch> writes:
 >
 >>Therefore I heard a recommendation, that dba's should disabled UNIX file
 >>buffering on mountpoints where rdbms are running.
 >
 >
 > Was this from anyone who actually knows what they're talking about?
 > You can't (AFAIK), and you don't need to.
 >
 >             regards, tom lane
 >
 > ---------------------------(end of broadcast)---------------------------
 > TIP 6: Have you searched our list archives?
 >
 >                http://archives.postgresql.org


--
Greg Spiegelberg
  Sr. Product Development Engineer
  Cranel, Incorporated.
  Phone: 614.318.4314
  Fax:   614.431.8388
  Email: gspiegelberg@Cranel.com
Cranel. Technology. Integrity. Focus.