Re: Recommended stripe depth for Oracle on a Raid?

Hello,

this is eternal question but Oracle knows the answer - 1 MB

(read the Loaiza. Optimal Storage Configuration Made Easy and AUTOMATIC

STORAGE MANAGEMENT – THE NEW BEST PRACTICE by Steve Adams (!!!) and

Richard Long at http://technet.oracle.com).

By the way, as I remember, Loaiza run tests against RAID-10 of 32 or 64

disks with 4GB cache raid controller.

What is magic value - 1MB? Suppose this one is related with typical

OS max I/O size. Because Oracle historically deals with large database

in multi-user environment Oracle's tuning performance methodology rather

based on getting the maximum of total system throughtput than speed up

a single I/O request. From this it follows that:

"should set the stripe depth (the amount of data in KBytes per Disk)

according to the product

(Oracle block size X DB_FILE_MULTIBLOCK_READ_COUNT), in my case:

8x32=256K".

This approach use to avoid disk contention because every I/O request

deals with one disk thus increase the total throughtput.

On the other hand it is reasonable that you'd like to speed up a single

I/O request as you wrote:

"However, somewhere else a rule of thomb is mentioned saying that the

total amount of data per I/O (i.e. blocksize x

DB_FILE_MULTIBLOCK_READ_COUNT) should correspond to the stripe width.

The stripe width is (the stripe depth X the number of disks in the

raid group) - we have 8 disks (+1 parity +1 spare). This advice seems

more reasonable to me, because every I/O would be serviced by ALL

disks in parallel".

You are correct but it is suitable to optimize the single I/O request

for systems without I/O concurrency, e.g. for single user system,

otherwise some simultaneous I/O requests would be engaged in competition

for disks.

Unfortunately, I don't know how to combine the both approaches in the

real environment, i.e. to avoid disk contention and spread every request

on all disks.

How to accurately determine there is or no I/O concurency ?

I doubt that it should be blindly taken dictation of the first approach

in your case (with only 5 users).

Otherwise, setting of stripe depth to max I/O size (1MB) to prohibit

spread I/O on disks in any case even for night batch job.

I can propose the following procedure.

Try to play with DB_FILE_MULTIBLOCK_READ_COUNT parameter because this one

can be changed on a session or system level. Assume that

OS max I/O size is set to 1024KB and the stripe depth is 128KB.

Full stripe is 768KB=128KB*6 (should be <= OS max I/O size).

So DB_FILE_MULTIBLOCK_READ_COUNT can varies from 16 (first formula)

to 96 (second formula).

Thus you can try to find the optimal value of DB_FILE_MULTIBLOCK_READ_COUNT and determine there is or not I/O concurrency in your system.

--

SL


--
Posted via http://dbforums.com