Re: To RAID or not to RAID (...or how to RAID)

Rick Denoire <100.17706_at_germanynet.de> wrote in news:gecvnukn3tp03pkted9c0rqgln1vokg25i_at_4ax.com:

> SELECT greeting_name FROM greetings
> WHERE greeting_name="HELLO";
>
> I am planning to put an Oracle DB (8.1.7, Solaris 2.7) on a RAID
> System from Dell with 10 HDs in a RAID 1/0 configuration.
>
> My question is, should I make one logical device out of all disks and
> just put all tablespaces there? Or should I make groups of disks
> holding different kind of data?
>
> I have some extra disks for redologs, control files and the like.
>
> Please don't tell me to go and read the basics about using Raid with
> databases. I have already done that and have read that I should use
> Raid to take advantage of parallel operations of single spindles, but
> in the same documents, I am told to separate different kind of data.
> That is a contradiction to me except if it would involve using one
> Raid system for every type of data - kind of expensive. If all is
> going to be put together in one Raid, tables can be read in parallel,
> indexes themselves too, but the system won't read tables AND indexes
> in parallel. You will have to separete them for that.
>
> Well, separated single disks aren't a Raid system any more I guess.
>
> At present I tend to make two logical devices, one consisting of 6 and
> the other of 4 disks (effectively, 3 and 2 because of mirroring), to
> be used for data and indexes respectively. TMP, RBS etc. would go to
> extra disks.
>
> Please correct me, but to my understanding, Raid systems loose their
> advantage of parallelizing the mechanical side of things (i.e.,
> rotation of spindles and movement of heads) if I/O is going to be of
> random type; that is, Raid is more appropriate for sequencial
> operations rather than for one-block-access. So by separating tables
> and indexes into two logical devices consisting of independent
> harddisks, head jumps can occur in parallel and are reduced for every
> logical device, since Oracle demands access to indexes and tables
> (except for table scans, of course - but these should be avoided in my
> experience).
>
> Another question concerns the stripe size and arises when considering
> what happens when the DB tables are tuned to use indexes and most of
> I/O are one block operations. If the minimal data chunk that can be
> read at a time from one spindle is larger than one Oracle block, then
> no parallelism will be possible. Every block will be read entirely
> from just one harddisk, exactly the way that they are read when using
> single disks without any Raid. Correct me at this point but does not
> that mean that the stripe width (over all harddisks) should be as
> close as possible to one Oracle block? In that case, reading one block
> would force using all disks. For example, with a Raid of 4 disks and
> an Oracle block size of 8K, the stripe size on every disk should be
> 2K. This is unusual small so I need your advise here.
>
> The third question - you would guess that - refers to the optimal size
> of the Oracle parameter db_multiblock_read_count in a Raid
> environment. Since this parameter is global for the DB, it can't be
> tuned to all devices at a time. For example, a Raid of 8 disks would
> be best suited with a db_multiblock_read_count=64 (assuming a
> blocksize of 8K) - but not all devices of the DB consist of 8 disks...
> Feel free to elaborate on that.
>
> Thanks a lot
>
> Rick

There is NO "One size fits all cases" answer. This is a classic case of trade-offs;
where you can NOT get something for nothing. The only real way to get the best answer for your situation is to benchmark & replicate how YOUR system accesses its data. One other metric which you did NOT mention explicitly, but may be implicit as a part of the questions/answers that you already raised is simply Input/Output load balancing.

The over arching goal is to obtain equal activity across all available spindles regardless of what actual database operation that initiate the activity. How to best achieve this goal is left as an excerise to all the DBA who are responsible for application performance.

Let me leave folks with this to consider... I contend that you'll get more "bang for the bucket" tuning the SQL statements within an application, than trying to fine tune ANY of the disk or I/O related issues mentioned above. Received on Wed Sep 11 2002 - 21:06:04 CDT