Re: UNIX File buffering disable?

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(dot)sennhauser(at)bluewin(dot)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(at)Cranel(dot)com
Cranel. Technology. Integrity. Focus.