Re: Need advise about expanding storage

Rick Denoire wrote:

> Hello
>
> I am running an Oracle DB (8.1.7, Unix). All Oracle data is contained
> in three independent devices. It happens that three devices are to few
> to cope with the numerous I/O that Oracle does to different datafiles
> and that could be done in parallel.
>
> The question ist: How should be a Raid system configured to be used
> with the DB? It is a StoreEdge A1000 (SCSI) with 12 HD that is able to
> be setup in different Raid flavours.
>
> I first thought that just making one device out of it by striping data
> over all 11 harddisks (1 is for parity) would be fine, since that
> would accelerate access a lot. But since Oracle will be trying to
> access different files more or less at the same time, having one
> device would force serializing access to it. OK, access will be fast,
> but can only happen one after the other.
>
> Another possibility would be to make one device (or LUN) out of each
> harddisk, so access to each particular harddisk won't be so fast, but
> it can be done in parallel, since each harddisk behaves independently.
>
> Perhaps a kind of compromise between these two extreme cases would be
> the optimal solution, that is, building several groups of harddisk
> which use stripes but behave as independent devices.
>
> Well, having three copies of the controlfile, five redolog groups with
> two members each and running in archivelog mode demands a minimum
> number of 6 devices, without considering the access requirements for
> the datafiles containing the real data.
>
> One hint, however: The datafile in the index tablespace shows a much
> larger number of read/write access compared with other datafiles, and
> the main datafile containing most objects is mainly being read, but
> seldom written to (data mining application).
>
> Need some advise, since I am not that experienced with performance
> issues due to the physical topology of the database.
>
> Thanks a lot
> Rick
>

First You must separate seqential write devices from random read/write. The logwriter is a seq. writer of many small chunks into several larger files (redolog) I would set two disks in mirror for this - and only for the redologs. Make them raw devises or 512 byte formatet. (Solaris/Oracle writes down to 512 bytes on redologs - solaris default pagesize is 8K as far as I remember)
Then the random i/o. Load balancing can best be obtained by creating 5 mirrors (of the remaining 10 drives) and stripe them together with a stipe size of 64K. Or whatever your db_file_multiblock_read_count (dmrc) can be. Test by alter session set dmrc to 16384 or higher, and then show parameter dmrc or query v_$parameter(2). You may need some space for the archived redologs on a separate (system) mirrored drive. Doesnt matter where - but not on the redolog drives!

I am aware that there is as many oppinions on this matter as there are dba's. But I have had fast, god, rock solid systems created this way. In case you have real high DML frequency - you need more drives, but for what you got - this solution is optimal - given the spary input.

/Svend Jensen Received on Fri Jun 07 2002 - 13:24:11 CDT