UNIX File buffering disable?

[Sai Hertz And Control Systems]

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 [phone] or Mobile [phone]
Website http://mypage.bluewin.ch/shinguz/PostgreSQL/
http://www.swisssign.ch
http://swisssign.net/cgi-bin/trust/import

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

[Tom Lane]

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]

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

Post by Tom Lane

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: [phone]
Fax: [phone]
Email: ***@Cranel.com
Cranel. Technology. Integrity. Focus.



---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ***@postgresql.org so that your
message can get through to the mailing list cleanly