Re: When many disks are involved in a physical database layout

On Sun, 27 May 2001 16:36:08 -0500, "Spencer" <spencerp_at_swbell.net> wrote:

>
>"Dino Hsu" <dino1_at_ms1.hinet.net> wrote in message
>news:gu22ht04frm33j5989rqt0kvssn6u2om3e_at_4ax.com...
>> Dear all,
>>
>> In Ch4 Physical Database Layouts of Kevin Loney's book, an ideal
>> 22-disk model is presented as:
>>
>> Disk Contents
>> 1 Oracle software
>> 2 SYSTEM tablespace
>> 3 RBS tablespace
>> 4 DATA tablespace
>> 5 INDEXES tablespace
>> 6 TEMP tablespace
>> 7 TOOLS tablespace
>> 8 Online Redo log 1
>> 9 Online Redo log 2
>> 10 Online Redo log 3
>> 11 Control file 1
>> 12 Control file 2
>> 13 Control file 3
>> 14 Application software
>> 15 RBS_2
>> 16 DATA_2
>> 17 INDEXES_2
>> 18 TEMP_[USER]
>> 19 TOOLS_I
>> 20 USERS
>> 21 Archived redo log destination disk
>> 22 Export dump file desination disk
>>
>> He then tries in successive iterations to reduce the number of disks
>> to 17-disk, 15-disk, 12-disk, 9-disk and 7-disk solutions.
>>
>> The goals for the disk layout, he defines, are as follows:
>> 1.The database must be recoverable.
>> 2.The online redo log files must be mirrored via the system or the
>> database.
>> 3.The database file I/O weights must be estimated.
>> 4.Contention between DBWR, LGWR, and ARCH must be minimized.
>> 5.Contention between disks for DBWR must be minimized.
>> 6.The performance goals of the system must be defined.
>> 7.The disk hardware options must be known.
>> 8.The disk mirroring architecture must be known.
>> 9.Disks must be dedicated to the database.
>>
>> I think the author is trying to cover all kinds of situations, if
>> there are less than 7 disks involved, more compromises have to be
>> made, and this model would become less useful. Unfortunately, the
>> current databases we have all reside on only one disk; control files,
>> data files, online redo log files,... everything lives on the same
>> disk.
>
>one disk ? that represents quite a compromise. how would you
>recover the database if you were to lose the one disk ?
>
>> It seems (I might be wrong) that on Windows NT there seldom are
>> more than 5 disks, and when there are, they could be combined into one
>> by RAID. From the practical point of view, I have questions:
>> 1.Do you usually use 7 or more 'dedicated' disks for an Oracle
>> database?
>
>for now, yes, we use 'dedicated' disks. the number of drives and
>controllers is dependent on performance requirements. ideally,
>we would have a robust, enterprise-class disk storage subsystem.
>
>> 2.Do you prefer the disks to be RAID'ed or not?
>
>drive mirroring protects the availability of the database in the
>event that a drive, cable or controller fails. if by RAID, you are
>referring to a RAID-5 setup, then, no, i do NOT prefer RAID.
>
>if by RAID you are referring to some combination of mirroring
>and striping and, then, yes, to mirroring (for high availability),
>and, yes, to striping (to distribute i/o for higher performance.)
>
>> 3.Do you DBA's get involvied in the matter of server purchase so that
>> hardware spec. are good for the physical layout?
>
>of course. i cannot conceive of a situation in which i would place
>a production database onto a machine with only a single drive.
>
>> 4.The issue of file sizes is not really taken into account in the
>> decisions about which tablespaces should be placed on which disks by
>> the author. He focuses on reducing and balancing the contentions most
>> of the time. This might imply there are a lot of wasted disk space on
>> disks where data are small, and because disks must be dedicated to the
>> database (rule 9), they will not be used to accomodate other files.
>>
>
>it depends on the database performance requirements. the point is that
>the performance of an oracle database is often constrainted by disk i/o.
>
>using a larger number of drives, you can better distribute the i/o than you
>can with a smaller number of drives.
>
>any unused space (or, to use your term, "wasted space") on a disk does
>not cause a database performance problem.
>
>> Thanks, Dino
>>
>
>

The physical layout issue can be addressed in two mutually diagonal dimensions: recoverability and performance.

In a single-drive layout, the major compromise should be in the performance aspect, because no muliple drives can be used to distribute I/O requests, thus reducing I/O contentions. Therefore, a single-drive solution is possible only when the performance is not a critical issue (data are small, memory and disk space are big enough, CPU is quick enough, etc.) In the recoverability aspect, when you lose one disk, you lose everthing in a single-drive layout, but you still lose 'something' in a multi-drive layout. A database cannot be up without that 'something', especially when it is DATA or online redo logs that are lost. Therefore, a multi-drive layout doesn't free you from the duty of doing backups.

Will this explain the difference between a single-drive and a multi-drive layout?

Dino Received on Sun May 27 2001 - 20:29:30 CDT