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

comments below

"Dino Hsu" <dino1_nospam_at_ms1.hinet.net> wrote in message news:9293htc8uu0ok2ces9lbdsk3jh8p8oabab_at_4ax.com...
> 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
>
>

i did not intend to suggest that multiple drives would free one from running backups. quite the contrary, having multiple drives can make running hot backups and exports faster and less intrusive to other database operations.

and, given a properly configured database layout on a sufficient number of drives, you can indeed "lose" a drive, and not lose 'something'... the database can remain up, operational, with only a slight performance hit. the only thing you'd lose, in fact, is the time it takes to replace the failed
drive and some performance when the mirrors are resync'd.

so both performance and availability can be improved with multiple drives. again, the configuration of the disk subsystem is determined by the exact business and application requirements for performance and availability of the database.

obviously, placing an oracle database on a single drive meets some set of performance and availability requirements, although that set is certain not to include five nines of availability.

HTH Received on Sun May 27 2001 - 22:14:36 CDT