Re: disk i/o balancing

Joseph Thvedt <jthvedt_at_brookings.net> wrote in article <32F964F1.2AED_at_brookings.net>...
> We're debating different ways of optimizing disk i/o. The ground
> rules: we WILL be using some sort of redundancy for everything.
> Everything but redo logs will probably be on RAID 1 or 0+1, or RAID 0
> with LVM mirroring; redo logs will probably be duplexed by the RDBMS
> onto multiple plain old drives. Of course, tables & indexes will be
> separate.

Since you have mirroring in the picture, don't duplex them in Oracle. Let the OS/LVM handle the task for you. This will shorten the code path for the redo I/O and speed things up slightly.

> One side would like to split our tables and indexes into two tablespaces
> (and RAID sets) each. Typically, parent/child tables (and other tables
> known to be used together often) will be split, thus spreading out the
> i/o over four RAID sets (assuming indexes are used for both tables in a
> two-table join).
>
> The other side doubts that we can choose that wisely. They say that
> most queries involve some number of tables other than two; that what's a
> parent in one query is a child in another; that the applications will
> change over time to make the original choices bad ones; that this is a
> high-maintenance setup. This side advocates fewer but larger RAID/LVM
> sets, over more disks & more SCSI channels. Distribute the i/o widely
> at and below the file system level, and trickle down those advantages to
> Oracle. Break up tablespaces at the datafile level if need be.
>
> What do you think?
>

I would opt for somewhat of a compromise. Build stripes over 4-8 disks, and create a number of small file systems or logical volumes over these stripes. Use as many controllers as feasible. Keep the file systems the same size, so that relocation will be easy if it is necessary. Also, use symbolic links to address the files, and use the links in the tablespace definition. Make your stripe width a multiple of the Oracle blocksize, i.e. stripe size = db_block_size * db_file_multiblock_read_count. ex. 8k block, 16 block multiread = 128k stripe.

You might want to go to www.oracle.com to check for a white paper on this topic. Think I saw one there. Also, check old IOUW conference proceedings. There were some good presentations on this by Cary Millsap around 2 years ago.

Good luck!

-- 
================================================
Gregory A. Burris
Practice Manager, Performance and Architecture Group
Oracle Consulting Services
Troy, MI
Office: 810-614-5029
Pager: 800-467-3700 pin 6026949