Re: Reasonable layout for DSS

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 henryk_at_email.uky.edu wrote:

>
> Does this layout seem reasonable as a starting point for a Windows
> 2003/Oracle 9.2 DSS database?
>
> Volume 0 Mirrored 33 GB OS (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 GB Oracle Home / Archive Logs / UNDO
> Volume 2 Mirrored 33 GB Redo / 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 GB Data1 / Index2
> Volume 4 RAID 5 409 GB Data2 / 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
• volumes 2,3,4 (RAID1 = 132 Gb apiece): redo, undo, table tablespaces, and busy index tablespaces

Yes, it can be argued that archived redo logfiles are "busy", but their impact on database performance is not as direct as the impact of the online redo logfiles...

>
> Keith H.

Just some ideas -- hope it helps...

-Tim

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Tim Gorman
  INET: tim_at_sagelogix.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).