Thanks to all who replied! I got exactly what I wanted, a fairly wide
spectrum of opinions, with the reasoning behind them.
I usually hate it when people quote lots of messages instead of doing a
real summary, but this time, for the sake of the archive, I'm going to
do it myself. Seeing everyone's answers in their own words is worth the
space, this time, IMHO.
I also plan to pose this question on our application vendor's
Oracle-specific list. If that generates any good new ideas, I'll post a
follow-up here.
My original question:
>
> Hi folks,
>
> We'll be moving our Oracle 7.3.3.6 database from VMS to DU4.0D
> soon, and are debating what file systems to use for the Oracle
> database files.
>
> I'd really appreciate it if some of you who have done this already
> would share your wisdom. I checked the archives, and found one
> summary of a general question on Oracle tuning on DU. I'm hoping
> for more specifics here -- which way did you decide and why?
>
> We've got lots of experience with DU 4.0A and earlier (not yet with
> 4.0D), and lots with Oracle, but none with Oracle on DU or on any
> other Unix.
>
> The system will be a 4100 starting with 2GB memory, 2 500MHz CPUs,
> and an RA7000 disk array with a single HSZ70 (128MB cache) and 18
> 9GB 7200 RPM UltraSCSI disks. We'll have execellent power
> conditioning, but no UPS. (BTW, this is our first RA7000, and
> we're just now starting to read about how to configure and manage
> it.) We haven't yet determined which exact Oracle version we'll
> be using, but we know it'll be 7.something, since our application
> vendor hasn't blessed Oracle 8 yet.
>
> The application is our integrated Student Records system (Banner),
> which I guess you'd say is more of a traditional OLTP system than
> anything else, though we also have some heavy reporting
> requirements. Mainly, this box will be dedicated database server,
> but it will have a small number of local interactive users running
> SQL*Forms and SQL*Plus.
>
> We want to design our tablespaces and file systems so as to make
> hot backup feasible.
>
> The current thought is to use RAID 0+1 (striping + mirroring) on
> the HSZ70 to provide good performance and redundancy. (Any other
> suggestions?)
>
> The question is, for the Oracle data files, what file systems
> should we use on top of the RAID sets, if any.
>
> Here's what I've been able to gather so far.
>
> Raw disk partitions
> -------------------
>
> Pro: Minor performance improvement in some cases due to lack
> of file system overhead.
>
> Con: Must use extra cost Oracle backup utility.
> No way to expand a partition on the fly.
> No filesystem read-ahead, so sequential reads may be
> slower.
>
> Advfs file system (with advfs utilities add-on product)
> -------------------------------------------------------
>
> Pro: Dynamic resizing
> No fsck at boot.
> Good performance.
> Can use clone file system to backup.
> Very easy to manage (we've got lot's of experience with it).
>
> Con: History of instability. ADVFS has been working great for us
> on DU4.0a + patches, but we remember awful problems in the
> past, and have heard there are new problems with DU4.0D.
>
> UFS
> ---
>
> Pro: Very stable. Has been around a long time, and seems to
> have few bugs.
> Good performance.
>
> Con: Fsck required after powerfail, making reboots take much
> longer (Is there any way to mitigate this -- say by tuning
> for fewer inodes?).
> Cannot add space dynamically.
>
> Thanks in advance for your help. I'll summarize.
>
> --
> -- Phil Rand <prand_at_spu.edu>, aka <postmaster_at_spu.edu>
> -- http://www.spu.edu/users/prand (206) 281-2428
> -- Computer and Information Systems
> -- Seattle Pacific University
> -- 3307 3rd Ave. W., Seattle, WA 98119
> ------------------------------------------------------------------------
> "One person CAN change the world, but most of the time,
> you probably shouldn't." -- Marge Simpson
Here are the replies, slightly edited:
///////////////////////////////////////////////////////////////
Subject: Re: Oracle on DU4.0D - ufs vs. advfs vs. raw
Date: Tue, 17 Nov 1998 12:55:45 -0800
From: Russ_Fish_at_idx.com
To: Phil Rand <prand_at_paul.spu.edu>
Hi Phil--
I'm a (relatively new) Unix sysadmin/Oracle DBA at IDX Systems Corp. in
downtown Seattle. I'm going to Boston tomorrow to set up a server very
similar to the one you describe below. I'm going with hardware RAID 0+1
on the drives, then using AdvFS as the file system. We've run Oracle
7.3.4.0 on 4.0D (no patches) for most of a year now, and haven't had any
significant problems, so as you note, we've got plenty of experience
with it and it's treated us right so far. There was a nice thread on the
advantages of UFS a little while ago, but it didn't seem compelling to
me.
--Russ
/////////////////////////////////////////////////////////////
Subject: RE: Oracle on DU4.0D - ufs vs. advfs vs. raw
Date: Tue, 17 Nov 1998 16:48:04 -0500
From: "Goebel, Greg" <ggoebel_at_grci.com>
To: Phil Rand <prand_at_paul.spu.edu>
Phil,
I recommend the UFS or Advfs options because they is tried and true (you
are very familiar with Advfs from comments below). Why add extra
configuration/maintenance overhead and problems to a system that will
most likely perform very well with a meat and potato's setup.
Some recommendations are:
1> Put the temporary tablespace, and any other temporary type
tablespaces on non-RAID drives. These are generally write intensive and
don't need the protection because they can be re-created with no lose of
data. Maybe redo logs as well. These would also be good candidates for
RAW partitions if you really want to do it. Also, you don't need any
special Oracle Backup tools to handle RAW partitions. There are
numerous back scripts available to copy and modify to meet your needs.
You would use "dd" to backup RAW partitions.
2> Make redo logs larger and increase the associated init.ora parms.
For
write operations to redo logs/log switches the whole database pauses
until complete.
3> For an OLTP system I've found RAID 5 to be very acceptable
performance wise. It does tablespace striping for you and you don't
loose as much disk space. RAID 0+1 is fine as well.
4> Start planning on going to Oracle 8. I just received my letter from
Oracle stating that support for 7.x will end Dec. 31, 1999. Unless
enough people complain.
That's all. Good luck with the migration.
Greg.
/////////////////////////////////////////////////////////////////
Subject: Re: Oracle on DU4.0D - ufs vs. advfs vs. raw
Date: Wed, 18 Nov 1998 10:19:29 +1000
From: alf_at_northpower.com.au
To: prand_at_paul.spu.edu
CC: alpha-osf-managers_at_ornl.gov
Well, we have Oracle 8.0.4 running on a couple of alphas (all DU4.0D
with 2nd patch set). I'm using ADVFS as I get better use of diskspace,
and also I can backup the Oracle database online with clonefset :) .
I haven't had too many problems except for when a drive died (It wasn't
in a RAIDSET ) and when I tried to vdump a clone of one of my oracle
DBs. The latter caused an ADVFS panic and would not fix itself. I had to
use salvage (which is not supported) but it worked. The reason for the
panic, I think, was that I was just using 512 (default) for the number
of pages in the file_domain log. When I rebuilt the domain I made the
log size 1024.
Alf....
/////////////////////////////////////////////////////////////////
Subject: RE: Oracle on DU4.0D - ufs vs. advfs vs. raw
Date: Tue, 17 Nov 1998 18:28:24 -0500
From: Matthew Huff <mhuff_at_colltech.com>
To: "'Phil Rand'" <prand_at_paul.spu.edu>
I'd stick with UFS. The fsck issue is really moot. Oracle neither uses
large numbers of files nor does it make file system transactions (unless
you have auto-extend on). Therefore a transaction based file systems
buys you little. Raid 0+1 is the best solution, but rather costly. I
typically run Raid 5 for the data and Raid 0+1 for the redo logs.
I'd only use raw file systems if I had a known performance problem or
knew that you need every last write performance enhancements.
Get a UPS.
Upgrade to 7.3.4.x if possible.
Look at a separate controller/disks for the redo logs if you have large
numbers of transactions/sec.
//////////////////////////////////////////////////////////////////
Subject: Re: Oracle on DU4.0D - ufs vs. advfs vs. raw
Date: 17 Nov 98 16:04:27 -0800
From: "Vipin Gokhale, Compaq SBU, Oracle Corporation"
<VGOKHALE_at_us.oracle.com>
To: prand_at_paul.spu.edu
1. You can use 'dd' to backup data files - no extra backup software
needed.
2. You can move back and forth between raw devices and filesystems if
there's a need to.
3. In my opinion, one advantage with raw devices is that it's
relatively predictable when it comes to performance tuning (database
performance, that is). With filesystem caches, statistics about i/os is
somewhat incorrect (Oracle can not tell for sure if a physical I/O
happened as a result of the I/O it issued).
4. You're limited to 8 partitions per disk (physical or logical)
device. Some find it annoying from administrative purposes (i.e. like
the ability to create as many files as they want on a filesystem). You
certainly can resize disks if you're using LSM etc, and you can resize
Oracle data files once you've resized the device underneath.
5. Oracle already caches most frequently used data to the extent
possible. Any caching filesystem will do becomes redundant (except when
the application/database is badly tuned). Any prefetching by the
filesystem pretty much gets in the way (again, unless
database/application is badly tuned or designed).
6. Should you decide to use parallel server configuration in a cluster
environment, you'd need to put the database on raw devices (atleast for
now; and even when cluster filesystem is available in V5.0 of Digital
UNIX for performance reasons).
I've found this issue of choosing filesystem or raw devices to be a
matter of personal preferences of database administrators in most cases.
FWIW, all published TPC benchmarks for Oracle on Digital UNIX are with
raw devices.
Hope this helps.
- Vipin
These are my personal views - I do not speak for my employer.
///////////////////////////////////////////////////////////////////
Subject: Re: Oracle on DU4.0D - ufs vs. advfs vs. raw
Date: Tue, 17 Nov 1998 18:32:38 -0800
From: Tom Webster <webster_at_ssdpdc.lgb.cal.boeing.com>
To: prand_at_paul.spu.edu
Phil,
Here is my $0.02 worth,
> We'll be moving our Oracle 7.3.3.6 database from VMS to DU4.0D
> soon, and are debating what file systems to use for the Oracle
> database files.
[....]
> The system will be a 4100 starting with 2GB memory, 2 500MHz
> CPUs, and an RA7000 disk array with a single HSZ70 (128MB cache)
> and 18 9GB 7200 RPM UltraSCSI disks. We'll have execellent power
> conditioning, but no UPS. (BTW, this is our first RA7000, and
> we're just now starting to read about how to configure and manage
> it.) We haven't yet determined which exact Oracle version we'll
> be using, but we know it'll be 7.something, since our application
> vendor hasn't blessed Oracle 8 yet.
[....]
> We want to design our tablespaces and file systems so as to make
> hot backup feasible.
This kind of locks you into a solution that uses either EBU/RMON and a
Oracle Backup Utility backend (for NSR and others) and/or AdvFS clones.
> The current thought is to use RAID 0+1 (striping + mirroring) on
> the HSZ70 to provide good performance and redundancy. (Any other
> suggestions?)
This may be overkill unless you REALLY need the speed. This is also a
long running argument -- your DBA will point to your Oracle consultant
(or to an Oracle document) that will tell you that the "best"
performance can be reached by using 0+1, which may be a true statement.
You should also be able to get performance about as good as non-mirrored
disks for some applications out of RAID5.
We are actually dead-locked, I won't/can't commit enough disks to go to
0+1, and the DBA won't look at a mixed RAID solution. If your DBA is a
little more cooperative, take a look at "RAID Performance Issues" by
Lehr and Schultz in the December 1997 SysAdmin magazine. They have a
long discussion about how to break-up database systems into more
sensable RAID configurations. Unfortunately, they don't come right out
and give any specific recomendations (other than to benchmark it and see
how it does). I haven't been able to get anything out of Oracle other
than the blanket statement about 0+1, but haven't tried too hard either.
It would be nice to have a two tiered redundancy structure from Oracle.
Then they could say that for best performance, use 0+1 for everything,
but for reasonable performance you can use RAID5 for a, b, and c and you
should use RAID1 or RAID0+1 for d,e, and f.
If you come up with any good answers, let me know so I can pitch them at
my DBA before we start failing disks. :->
> The question is, for the Oracle data files, what file systems
> should we use on top of the RAID sets, if any.
>
> Here's what I've been able to gather so far.
>
> Raw disk partitions
> -------------------
>
> Pro: Minor performance improvement in some cases due to lack
> of file system overhead.
>
> Con: Must use extra cost Oracle backup utility.
> No way to expand a partition on the fly.
> No filesystem read-ahead, so sequential reads may be
> slower.
If you will be looking at a cluster environment later, you will have to
encapsulate the raw partitions in LSM, just a tip.
Oracle will handle file/disk caching on its own with raw partitions.
Oracle also partitions out datafiles in chunks, so you can generally
spread the data around on multiple volumes.
Don't forget to add the raw partitions to your AdvFS exclude list, so
you or your JR Admin don't add that nice "free" partition to /home in a
moment of confusion.
>From what I've heard from folks I trust, it's a lot of management head-ache for a 2-3% improvement. If you really need the performance, it can be worth it though.
If you want to use the paralle querry, you will have to use raw
partitions.
> Advfs file system (with advfs utilities add-on product)
> -------------------------------------------------------
>
> Pro: Dynamic resizing
> No fsck at boot.
> Good performance.
> Can use clone file system to backup.
> Very easy to manage (we've got lot's of experience with it).
>
> Con: History of instability. ADVFS has been working great for us
> on DU4.0a + patches, but we remember awful problems in the
> past, and have heard there are new problems with DU4.0D.
I really do like AdvFS. For most things I use it to the exclusion of
UFS.
DU4.0D and the latest patch kit appears to be stable, other than some
lingering defrag concerns (Oracle data files don't need to be defragged
anyways). Can't say that I have a lot experince with 4.0D and Oracle
other than in a test environment.
On the con side, if your AdvFS sets get hosed, the tools to fix it are
pretty light-weight. DEC is said to be working on it.
> UFS
> ---
>
> Pro: Very stable. Has been around a long time, and seems to
> have few bugs.
> Good performance.
>
> Con: Fsck required after powerfail, making reboots take much
> longer (Is there any way to mitigate this -- say by tuning
> for fewer inodes?).
> Cannot add space dynamically.
Tools for reparing hosed UFS volumes are more mature than for AdvFS.
Adding space with Oracle data files may not be a big deal since, they
can be spread around in chunks.
Another word of advice, buy a UPS, really, buy one.
Tom
--
+-----------------------------------+---------------------------------+
| Tom Webster | "Funny, I've never seen it |
| SysAdmin MDA-SSD ISS-IS-HB-S&O | do THAT before...." |
| webster_at_ssdpdc.lgb.cal.boeing.com | - Any user support person |
+-----------------------------------+---------------------------------+
| Unless clearly stated otherwise, all opinions are my own. |
+---------------------------------------------------------------------+
////////////////////////////////////////////////////////////////
Subject: Re: Oracle on DU4.0D - ufs vs. advfs vs. raw
Date: Wed, 18 Nov 1998 08:22:16 -0500
From: Trevor Stott <stott_at_dathomir.sheridanc.on.ca>
To: Phil Rand <prand_at_paul.spu.edu>
Hi Phil,
Im not a DBA so I wont pretend to know what I'm talking about in
that area but from what I understand if you need to resize your database
it requires that you rebuild it anyways so why is adding space
dynamically a concern? We've tried advfs and dumped it due to poor
performance and reliability. There are summaries out that show ufs as
being faster in all respects and definately more stable. If your
concerned about fscks put a battery backup and some cache on your RAID
controller the system will still fsck but it wont find any problems.
-------------------------------------------------------------------
Trevor Stott
Trevor.Stott_at_sheridanc.on.ca
Information Technology
Sheridan College Phone: (905) 845-9430 ext. 2148
Oakville, Ontario, Canada Fax: (905) 815-4011
-------------------------------------------------------------------
"Every time I think I know where it's at, they move it."
//////////////////////////////////////////////////////////////////
Subject: Re: Oracle on DU4.0D - ufs vs. advfs vs. raw
Date: Wed, 18 Nov 1998 13:40:38 +0100
From: matthias.reiter_at_de.pwcglobal.com
To: Phil_Rand_ <prand_at_paul.spu.edu>
Hello Phil,
here are my recommendations:
Use several diskpools.
- Each diskpool consists of one or more disks, possibly with Raid 0, 1,
5.
- Seperate diskpools are very important to ensure the ability of ORACLE
to recover from a media crash without RAID 1 or 5.
- Never put archived redologs together with tablespaces in the same
diskpool.
- Never put original and mirrored online redologs in the same diskpool.
- Never substitute oracle-redolog-software-mirroring by hardware
- - This will allow to recover from a dual diskfailure in one
diskpool and gives best availability and ability to recover
Put each of the following areas on a seperate diskpool:
1. UNIX, swap
2. Archived redologs, swap, other data
3. original online redologs, software, less frequently accesed data
4. mirrored online redologs, software, staging area
5. tablespaces with data
6. tablespaces with indices
Use 2 disks and mirroing for 1 to 4.
Use 5 disks and RAID-5 for 5 and 6.
Use advfs for 1 to 4.
Use advfs for 5 and 6 if the database will grow in a unkown manner
or there is need to create and delete tablespaces
or there is need to reorganise indices/tables often
Use rawdisks (on RAID-5) for 5 and 6 if the database has a stable
size and structure.
Advfs gives quick recovery in case of systemcrash or powerfailure.
- Reduce the ubc-minpercent to 5 and the ubc-maxpercent to 10
- Use only one domain in each diskpool 2 to 6
- Use multipe fsets with few tablespacefiles in each fset
- Use many fsets, one for each logical area: oracle-sw, staging
ares, redos, tmp, /usr/local, /home
- - This allows easy moving of fsets to a new domain/diskpool
- - I do this with a script very handy ( checks, umount, move,
fstab, mount)
Rawdisk is a bit faster.
- It does not use the UBC
- Use only one partition per diskpool
UFS may make your files unuseable because it is not able to to a
deterministic recovery of the files. It just brings its own metadata in
a defined state.
If you use mirroring there is no need for striping, since the reads are
spread over both disks. So put the disks of a mirrored pair on
different SCSI-channels.
Oracle writes with respect to transactions synchronous to redologs and
asynchronous to tablespaces. Writing to a mirrored diskpool is a bit
faster than writing to RAID-5. So mirroring is good for online-redologs
and RAID-5 is good for tablespaces.
Do not use writecache (even with battery use with caution)
- Online redologs are the heart of consistency in transactions
- A single point of failure (controller) would corrupt the database
because the controller may reorder the data written to the redologs
- Use writecache only if the memory is ECC-protected
- Since tablespacefiles and archived redologs are written asynchronuos
there is no need for a writecache.
- You may think about enabling writecache on the disk because it is
seperate for each disk.
Do not use clonefset for ORACLE-backup. It makes no sense for
ORACLE-databases.
- This gives no real offline backup.
- It will need extra diskspace for the changed datablocks and may
freeze your complete system if advfs eats up all space because
your backup is not fast enough.
- ORACLE WILL need archived redologs to perform a recovery after
restoring such a backup.
- The information of your application which is external to the
database is not in sync with such a backup.
Use Oracles feature of online backup.
- It will not reduce the performance of the database
- ORACLE just freezes the number of the last redolog in the
tablespacefile
- ORACLE will continue to write into the tablespacefiles, there
can be nostuck
- ORACLE can do a clean recovery from this backup
- It is a reliable part of permanent avalability
Bye
Matthias Reiter
System- and Databaseadministrator ( ORACLE, SAP R/3, DU)
//////////////////////////////////////////// end.
Received on Wed Nov 25 1998 - 01:14:44 NZDT