Helpful folks,

I am trying to find any doc or research or studies comparing the pros
and cons of architecting a DB2 disk subsystem to physically segragate
index access and data access on separate spindles, as opposed to
putting both index and data on one RAID group and assuming the
striping will provide adequate segregation.
Can anyone point me in a good direction for finding such info?

Thanks in advance
Sean

"Scav" <dba_fla@yahoo.com> wrote in message
news:287e99f5-c08f-463b-9b30-f71845589970@e23g2000prf.googlegroups.com...
> Helpful folks,
>
> I am trying to find any doc or research or studies comparing the pros
> and cons of architecting a DB2 disk subsystem to physically segragate
> index access and data access on separate spindles, as opposed to
> putting both index and data on one RAID group and assuming the
> striping will provide adequate segregation.
> Can anyone point me in a good direction for finding such info?
>
> Thanks in advance
> Sean

How big is your database, and how much memory does your database server have
for use in bufferpools. If your bufferpool hit ratio is high, it won't make
any difference.

Scav wrote:
> Helpful folks,
>
> I am trying to find any doc or research or studies comparing the pros
> and cons of architecting a DB2 disk subsystem to physically segragate
> index access and data access on separate spindles, as opposed to
> putting both index and data on one RAID group and assuming the
> striping will provide adequate segregation.
> Can anyone point me in a good direction for finding such info?
>
> Thanks in advance
> Sean

You need to start with the performance guides and build your
understanding of the pro's and con's of each storage mechanism. Once you
have this understanding, you'll need to apply it to your expected load
and then verify it against the actual load placed on the disk subsystem.

Separate disks isolate physical arm movement of data and indexes. Raid
cannot do this because the array is (usually) treated as a single disk.
The simplest isolation; a separate single drive for indexes and data
will allow twice the number of arm movements per second than placing
both components of the data base on a single drive.

Raid-5 type arrays need to be examined very very carefully. The normal
write procedure for this mechanism requires reading data from all of the
disks in the array and always writing data to a minimum of two disks.
When the database manager is in a "wait for write operation to complete"
state, the potential delays for the multiple reads and writes can be
significant. Hardware is available for raid arrays that may alleviate
this problem.

Phil Sherman

Thanks for the detailed replies. Here is some background.

DB2 V8 running on Windows 2000 Ent server, 4g of memory
Fiber attached Clariion shelf, 15 physical drives
130g OLTP database

5 RAID groups, each of 3 drives, each group configured as RAID5
3 of these RAID groups are dedicated to data, 2 to indexes.
The largest most transaction-heavy tables have their containers spread
across the 3 data RAID groups and their indexes containsers spread
across the 2 index RAID groups.

A new storage administrator wants to reconfigure the shelf as having a
single RAID group for data and a single RAID group for indexes, both
with RAID5. I am not convinced that this will provide any additional
benefit to performance, and may make things worse. The current config
performs extremely well and has been tuned over a period of years.

I was hoping to find some doc which would shed light on the pros and
cons of this scenario.

Thanks again for the feedback
Sean

On reflection, I should have titled this post: RAID5 striping vs
"container" striping

And now this Strorage Admin wants to put the transaction logs on the
same RAID groups as the data and index.
Surely this is still a major bad move? Regardless of how versatile
RAID configurations have become, this has to be a detriment to
performance, yes?

Again, is there any doc or white papers that compare these kind of
issues?

Scav wrote:
> And now this Strorage Admin wants to put the transaction logs on the
> same RAID groups as the data and index.
> Surely this is still a major bad move? Regardless of how versatile
> RAID configurations have become, this has to be a detriment to
> performance, yes?
>
> Again, is there any doc or white papers that compare these kind of
> issues?

Scav,

If your system is performing adequately, my question to the new storage
admin would be: Why change something that's already functioning well?
What advantage will the application users gain from the exercise?

Perhaps the storage admin is looking to reclaim spindles (and therefore
space) for other use.

It's very difficult to write a general white paper for this kind of
question, because each system is unique. The specific disk requirements
are a function of how effective your bufferpools / sort heap are at
eliminating disk I/O. In your case -- if the rate of physical I/O to
each existing RAID group is low enough, combining data/index/temp/logs
may have no effect on end-user performance.

Scav wrote:
> Helpful folks,
>
> I am trying to find any doc or research or studies comparing the pros
> and cons of architecting a DB2 disk subsystem to physically segragate
> index access and data access on separate spindles, as opposed to
> putting both index and data on one RAID group and assuming the
> striping will provide adequate segregation.
> Can anyone point me in a good direction for finding such info?
>
> Thanks in advance
> Sean

Perhaps you should also read some of the articles from BAARF:

http://www.miracleas.com/BAARF/BAARF2.html

Jan M. Nelken

On Dec 13, 5:19 am, "Jan M. Nelken" <Unknown.U...@Invalid.Domain>
wrote:
> Scav wrote:
> > Helpful folks,
>
> > I am trying to find any doc or research or studies comparing the pros
> > and cons of architecting a DB2 disk subsystem to physically segragate
> > index access and data access on separate spindles, as opposed to
> > putting both index and data on one RAID group and assuming the
> > striping will provide adequate segregation.
> > Can anyone point me in a good direction for finding such info?
>
> > Thanks in advance
> > Sean
>
> Perhaps you should also read some of the articles from BAARF:
>
> http://www.miracleas.com/BAARF/BAARF2.html
>
> Jan M. Nelken


Excellent reference, Jan. I especially recommend Art Kagel's article.

On the other hand, if things work OK, why change? I recommend to read
EMC's papers on tuning CLARiiONs for DB2 (ask your EMC partner to
provide them, if you can't get them on Internet), both you and your
storage admin. In my opinion, for OLTP system, at least logs should
be on RAID 1 or RAID 10 LUN.

For RAID 5 LUN's, if I remember well, EMC guys recommend 4+1 and 8+1
configurations, if you HAVE to use RAID 5 for OLTP. I think that with
RAID 5 CLARiiONs do have some performance improvements to avoid read
before write, when you write full stripe at once. I am not sure if you
can achieve that in OLTP system.

Darko Krstic