RE: I/O tuning... Allocating spindles to databases

Bob,

>> We are transitioning our database environment to 
>> a new SAN and I am trying to determine a good approach 
>> for allocating spindles to databases

>> I'm not losing sleep over mis-allocating a disk 
>> here or there, but I would like to make sure we 
>> don't err to any particular extreme.  

>> Any thoughts would be much appreciated...

"Any thoughts"...here goes;

You have not been "invited" to enough storage meetings and/or the SAN sales 
guys has not got a hold of you yet.
I might be a little...well a lot cynical, but unless everyone is on-board with 
your (right thinking) DBA mind non of this will happen.  Your first statement 
along made me cringe!

I have seen to big SAN implementations.
The prevailing wisdom (sales pitch) is "the SAN is huge and everything will be 
cached"
and "you will have more I/O than you can imagine".

<FORWARD 6 MONTHS LATER>

You find yourself in a detailed I/O meeting regarding the now install and 
running Black Box SAN.
I was on one site where I was seeing >20 milsec I/O Read Time from 
V$FileStat...it was if someone had two hands on the disk!

I ask one disk admin how do I know that the constant FULL TABLE SCAN of db1 is 
not effecting the performance of db2 all shoved into the same SAN.
Answer, "you don't know". :o|

You are doing some good planning, but one of the hardest things I found when 
working with a good group of SA's and disk admins was;
One; the level of physical and virtual abstraction from the actual disk to the 
datafile...arrays, LUN's, volumes, paths, etc....many levels and layers with a 
big high end SAN.
Two; the good sales guys and disk admins will tell you that you need to be 
careful when trying to do good...trying to plan.
The high ends SAN's have lots of smarts and you can end up un-doing some of the 
good stuff already built into the SAN (layout)...like stripe a stripe...I had 
to go over this stuff again and again to grasp it.

In the end for one critical database we ended up using or dedicating some SAN 
arrays to specific database...as if the disk were (physically) attached to that 
DB server...I think you said you would do that too.

Good luck and good planning you are doing.


Chris Marquez
Oracle DBA


-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx on behalf of Murching, Bob
Sent: Tue 9/13/2005 7:09 PM
To: 'oracle-l@xxxxxxxxxxxxx'
Subject: I/O tuning... Allocating spindles to databases
 
Hello!

We are transitioning our database environment to a new SAN and I am trying
to determine a good approach for allocating spindles to databases running on
various boxes.  This in turn will determine which boxes need which paths to
which LUNs.  Assumptions are fixed number of spindles available (about 100)
and that spindle allocation only takes into account performance needs, not
capacity.  We have essentially four big I/O intensive databases... call 'em
Prod1, Prod2, QA1 and Dev1, the latter two generally not in archive log mode
but heavily used by various internal groups.  Another assumption, we'd be
using some fancy LVM or ASM or what-have-you so we can move spindles around
after the transition to fine-tune... so I'm only trying to get rough ideas
here.

What we've been doing is measuring physical reads/writes (tablespace
datafiles, temp/undo, does *not* include online redo logs) per database, and
measuring amount of redo blocks generated per database.  Based on the
relative % each database consumes of the total IO load in our environment,
we allocate the appropriate percentage of spindles... so if Prod1 consumes
50% of the I/O and I have 100 disks to play with, Prod1 would get 50 disks,
divvied up into some collection of RAID groups of course.

The questions,
1. First off, am I completely off-base in my approach here?
2. If I'm taking the right approach, then how do I weigh redo blocks
generated vs. physical reads+writes when determining where to allocate disk?
2a. How do I determine how many I/Os should be allocated to archive log
destinations?  Does the log destination require fast disk vs. online redo
log locations?  Or not?
3. How best to divvy up disks into RAID groups?  Assume HW RAID-10 in the
SAN.  What's better, letting ASM logically stripe data over a triplet of 2+2
striped-mirrors, or just tossing everything into a single 6+6
striped-mirror?  The former would give me a heck of a lot more granularity
to move storage around when fine-tuning, and might be better for OLTPish
"single row lookup" type environments whereas the latter might be better
suited toward sustained "sequential-ish" access patterns.  It's just a
guess, however, and while we have production stuff running on ASM, we don't
have enough empirical data to assess whether plaiding (SAN HW stripe + ASM
SW stripe) is good, bad or merely ugly.

I'm not losing sleep over mis-allocating a disk here or there, but I would
like to make sure we don't err to any particular extreme.  Any thoughts
would be much appreciated...

Bob

Other related posts: