Re: Tuning, RAID5, and fragmentation

We get that here -- because the RAID5 volumes are large compared to our data the database winds up on one or two volumes. The hardware folks are fixated on throughput rather than multiplexing so I get mostly RAID5 devices. I have to prioritize my file load balancing -- I try to get TEMP and logfiles away from the rest of the data, at least, if I can get a different physical volume. They tried to tell me they could partition the volume, but if you think about it carefully, that's worse than lumping it all together (it adds blank spaces for the heads to fly over). For goodness' sake don't partition your RAID.

Since everything is on one disk, number and location of data files is less important, and file i/o loading is largely irrelevant. Pretty much all other tuning principles still count, though. Fragmentation/coalescing is probably more important (to permit read-ahead) -- as well as optimizing muti-block io. You'll find that the touted efficiency of RAID for read is mostly bunk for this Oracle, as Oracle tends to do scattered reads that would go faster if the blocks were on separate drives. I've yet to meet the RAID controller that didn't need all the read heads on the same stripe to read a block. However, the fact that you can't distribute head latency among drives move the focus to multiblock i/o (for scanning) and caching (more RAM is always better).

Consult with the hardware person to determine the stripe size for your volume and calculate the correct number (and size) of blocks to make db_file_multiblock_read_count and db_file_direct_io_count an integral multiple of the stripe, so that you cache all the data from the stripe you're reading, rather than just the first one or two layers (if you're moving the heads there, get all the data). That, and making sure the hardware guys are using enough I/O channels to saturate the controllers, is about all you can do for I/O under these conditions.

Otherwise, I just try to make sure that there's enough cache for the working set and wonder aloud why these high-speed RAIDs always seem to be the bottleneck :-).

dana mn wrote:

> Presuming a DBA is forced to use RAID5, what elements of tuning become
> irrelevant? (in the sense that if you're stuck with RAID5, warts and
> all, then trying to tune X, Y, and Z would be a waste of time /
> ineffective).
>
> Load balancing files would be one thing.. no way to put indexes and
> tables on different disks (ditto redo log file members, etc) with one
> massive RAID5 volume.
>
> What about fragmentation and coalescing? Are these still a concern for
> tablespaces located on RAID5 volumes?
>
> Has anyone written an article about Oracle and "living with RAID5"? I'm
> finding that a customer has several Oracle databases on systems with
> nothing else but RAID5 storage for everything.
>
> Thanks very much.
>
> - Dana
>
> __________________________________________________
> Do You Yahoo!?
> Get email at your own domain with Yahoo! Mail.
> http://personal.mail.yahoo.com/
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: dana mn
> INET: danamn2000_at_yahoo.com
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> 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).
Received on Mon Feb 26 2001 - 11:08:05 CST