Keith,
First of all, please remember that, despite the popular perception that
because data warehouses are read only (they aren't, by the way!), they do
not place great demands on the I/O subsystem.
The real facts are that data warehouses, because much of their I/O activity
is uncacheable due to the greater percentage of full table scans and bulk
data loads, often present far more demand on the I/O subsystem than any OLTP
or operational system. Not sure whether your S/A realizes that or not...
Further comments inline...
on 1/16/04 10:44 AM, Henry, Keith at [EMAIL PROTECTED] wrote:
Does this layout seem reasonable as a starting point for a Windows
2003/Oracle 9.2 DSS database?
Volume 0 Mirrored 33 GBOS (Windows 2003)
Not being a Windows administrator, I have no idea how much space Windows
requires for it's root volume(s), but on UNIX it would be done a lot
differently. Does the Windows OS really require 33Gb? Of course that
probably includes swap space (which on UNIX is usually allocated to separate
volumes). Anyway, no advise to offer here...
Volume 1 Mirrored 33 GBOracle Home / Archive Logs / UNDO
Volume 2 Mirrored 33 GBRedo / Temp / System
The important thing I attempt to do is:
* isolate data structures that generate high volumes of I/O from
one another first
* backfill the rest of the volume with data structures that
essentially take up space but do not generate much volume
of I/O
So, by this principle, I think you have a good distribution of data
structures amongst these two volumes.
However, since you've obviously had to fight for your RAID-1 volumes, it is
very much of a waste to place the ORACLE_HOME and the SYSTEM tablespace on
them. There are each very low-volume data structures and they would each be
quite happy on the lower-performing RAID-5 volumes...
I'd replace each with busier data structures that are currently earmarked
for the two RAID-5 volumes...
Volume 3 RAID 5 409 GBData1 / Index2
Volume 4 RAID 5 409 GBData2 / Index1
This is fine, but please remember as a sidenote that the admonition to
segregate table tablespaces from their related indexe tablespaces is pure
bunk. Indexed scan operations are purely sequential (except for FAST full
scans) -- there is little or no opportunity for parallelism of I/O because
the I/O requests themselves are sequential in nature (as the wait-event name
of db file sequential read suggests), and not parallel.
Separating tables and indexes to different tablespaces has lots of excellent
justification and is highly recoommended, but separating table tablespaces
from their associated indexe tablespaces is completely unnecessary. No harm
can come of it, but no benefit either.
It's possible I could get the sa to split out another set of Mirrored
drives from one of the RAID 5 volumes, but it was like pulling teeth to
get any non-RAID 5 volumes.
Obviously RAID-5 and RAID-1 represent the traditional tradeoff of
performance versus cost. Both provide necessary fault-tolerance to the
failure of a single drive. RAID-5 presents more usable space but has a
clear impact on performance due to synchronization, most noticeably on
writes. RAID-1 does not diminish the basic throughput of the drives but
presents less usable space.
The questions have to be: what is the minimum necessary space for this
application and would be better performance of RAID-1 be an acceptable
tradeoff?
The configuration you describe presents 66 Gb of RAID-1 space and 818 Gb of
RAID-5 space, totalling 884 Gb total (exclusing volume 0).
Assuming that your storage is filled with 33G drives (an assumption based on
the size of the RAID-1 volumes), that means that each RAID-5 LUN could
represent 13- or 14-drive sets (i.e. 12x33 is pretty close to 409, leaving
1-2 drives for parity). So, following this tenuous chain of assumptions,
that implies that you actually have 32 spindles in your configuration (i.e.
two each from volumes 1 and 2 and 14 each from volumes 3 and 4).
This means that you could potentially create four RAID-1 sets/volumes/LUNs
of 8 drives apiece, yielding about 528Gb total usable space. Is this
sufficient for this application, or not?
In terms of space, 884 Gb is always preferable to 528 Gb, but in terms of
performance, RAID-1 in four 8-drive LUNs will undoubtedly perform much
better.
Another option represents one of the many middle grounds between the two
extremes of 528 Gb (all RAID-1) and the present proposal of 884 Gb: three
eight-drive LUNs of RAID-1 (i.e. 3 x 4 x 33 = 396 Gb) plus one eight-drive
LUN of RAID-5 (i.e. 231 Gb), for a total of 627 Gb.
Then, the plan would be to put the data structures with lower volumes of I/O
activity (especially less intense write activity) on the single RAID-5
volume and the volumes with the higher I/O activity on the RAID-1 volumes:
- volume 1 (RAID-5 = 321 Gb): ORACLE_HOME, archived logs, SYSTEM, and
certain index tablespaces
-
