DMS vs. SMS on RAID 5

[Joseph]

I've been thinking about the benefits or lack thereof of deploying DMS
tablespaces for RAID, say RAID 5. The point of DMS is to get reasonable
size extents of contiguous disk space so that minimal seeking is done with
sequential scans of data and sequential prefetching can pipeline the reading
of data with its processing etc.

RAID generally defeats this model of sequential scans across contiguous
placed data because they stripe data across a number of disks in a round
robin manner (so block i+1 of an extent is not even on the same disk as
block i).

I've been investigating physical database placement issues for RAID's
and SAN's. There is an IBM Redbook that deals with the matter somewhat,
where it is recommended that for a SAN, ensure that logical drives correspond
to disjoint groupings, and map each tablespace to a set of containers, one
for each logical drive grouping. The same on-line pub also discuss the
LVM facility for AIX that can be used to implement a prefetch operation
by the software that does the RAID data placement, but such a facility
apparently is lacking for Windows.

The samem pub also seems to confirm my own take that with RAID's and SAN's
the benefit of DMS over SMS is signficantly narrowed, perhaps not even
clearly an advantage at all. Has anyone measured the difference in
performance of DMS over SMS for reasonable assumptions about data and
workload characteristics when the containers are stored on a RAID or SAN?

Thoughts?

Joseph
--

[Mark A]

"Joseph" <jo****@aracnet.com> wrote in message
news:bs********@enews2.newsguy.com...

I've been thinking about the benefits or lack thereof of deploying DMS
tablespaces for RAID, say RAID 5. The point of DMS is to get reasonable
size extents of contiguous disk space so that minimal seeking is done with
sequential scans of data and sequential prefetching can pipeline the reading of data with its processing etc.

RAID generally defeats this model of sequential scans across contiguous
placed data because they stripe data across a number of disks in a round
robin manner (so block i+1 of an extent is not even on the same disk as
block i).

I've been investigating physical database placement issues for RAID's
and SAN's. There is an IBM Redbook that deals with the matter somewhat,
where it is recommended that for a SAN, ensure that logical drives correspond to disjoint groupings, and map each tablespace to a set of containers, one
for each logical drive grouping. The same on-line pub also discuss the
LVM facility for AIX that can be used to implement a prefetch operation
by the software that does the RAID data placement, but such a facility
apparently is lacking for Windows.

The samem pub also seems to confirm my own take that with RAID's and SAN's
the benefit of DMS over SMS is signficantly narrowed, perhaps not even
clearly an advantage at all. Has anyone measured the difference in
performance of DMS over SMS for reasonable assumptions about data and
workload characteristics when the containers are stored on a RAID or SAN?

Thoughts?

Joseph
--

There are two other important considerations:

1. How much memory do you have for buffer pools? If you have enough memory
(relative to the size of the database), actual disk I/O can be minimized
(after the buffer pools are loaded).

2. Is your database primarily for ad-hoc queries or more transaction
oriented? Sequential prefetch is more often used in complex queries which do
tablespace or complete index scans. For, transaction oriented databases, or
queries that typically use the B-tree index access, there is not as much (if
any) prefetch activity, and the DMS vs. SMS issue is not as important.

But you probably already knew this.

[Ian]

Joseph wrote:

I've been thinking about the benefits or lack thereof of deploying DMS
tablespaces for RAID, say RAID 5. The point of DMS is to get reasonable
size extents of contiguous disk space so that minimal seeking is done with
sequential scans of data and sequential prefetching can pipeline the reading
of data with its processing etc.
Obviously RAID 5 has some disadvantages in comparison to other RAID levels
(like RAID 1), but keep in mind its primary advantage (cost, relative to
the other RAID levels).
RAID generally defeats this model of sequential scans across contiguous
placed data because they stripe data across a number of disks in a round
robin manner (so block i+1 of an extent is not even on the same disk as
block i).

This is not entirely true. With proper configuration, read performance can
be very good. Sizing your extent size properly (i.e. setting it equal to,
or a multiple of, the stripe size), choosing an appropriate prefetch size,
and telling DB2 that it can use multiple IO Servers to fetch from a single
device will help read performance. Also, remember that a good RAID
controller will allow multiple IOs to be serviced simultaneously across the
drives within the array.


-----= Posted via Newsfeeds.Com, Uncensored Usenet News =-----
http://www.newsfeeds.com - The #1 Newsgroup Service in the World!
-----== Over 100,000 Newsgroups - 19 Different Servers! =-----

[Mark Yudkin]

Contiguity of extents is not a good argument for DMS as opposed to SMS.
Defragment the disks to achive contiguity. SMS won't "become" fragmented,
but there is no guarantee that the initial allocation won't already be
fragmented, and when you grow the SMS container, you will be adding
fragments.

For RAID-5 information, review "Designing and Choosing Table Spaces" in the
Administration Guide. Also review the description of the
DB2_STRIPED_CONTAINERS environment variable.

"Joseph" <jo****@aracnet.com> wrote in message
news:bs********@enews2.newsguy.com...

I've been thinking about the benefits or lack thereof of deploying DMS
tablespaces for RAID, say RAID 5. The point of DMS is to get reasonable
size extents of contiguous disk space so that minimal seeking is done with
sequential scans of data and sequential prefetching can pipeline the reading of data with its processing etc.

RAID generally defeats this model of sequential scans across contiguous
placed data because they stripe data across a number of disks in a round
robin manner (so block i+1 of an extent is not even on the same disk as
block i).

I've been investigating physical database placement issues for RAID's
and SAN's. There is an IBM Redbook that deals with the matter somewhat,
where it is recommended that for a SAN, ensure that logical drives correspond to disjoint groupings, and map each tablespace to a set of containers, one
for each logical drive grouping. The same on-line pub also discuss the
LVM facility for AIX that can be used to implement a prefetch operation
by the software that does the RAID data placement, but such a facility
apparently is lacking for Windows.

The samem pub also seems to confirm my own take that with RAID's and SAN's
the benefit of DMS over SMS is signficantly narrowed, perhaps not even
clearly an advantage at all. Has anyone measured the difference in
performance of DMS over SMS for reasonable assumptions about data and
workload characteristics when the containers are stored on a RAID or SAN?

Thoughts?

Joseph
--

[Harry]

Joseph wrote:

I've been thinking about the benefits or lack thereof of deploying DMS
tablespaces for RAID, say RAID 5. The point of DMS is to get reasonable
size extents of contiguous disk space so that minimal seeking is done with
sequential scans of data and sequential prefetching can pipeline the reading
of data with its processing etc.

RAID generally defeats this model of sequential scans across contiguous
placed data because they stripe data across a number of disks in a round
robin manner (so block i+1 of an extent is not even on the same disk as
block i).
As described in your cited reference, the advantage of DMS on a raw
device is that by planning the extentsize and prefetching parameters vs.
the stripe depth of the RAID group, you can get DB2 read in chunks
across all disks in the RAID group without wrapping around to odd disks.
This keeps the disk I/O to the minimum need to fullfil the request.
With SMS or DMS on a file system it is not practical to keep the files
positioned so that they line up with the start of the RAID group. If
you don't us raw devices, there isn't much difference between DMS and
SMS for performance, it then comes down to managability.

Another win with raw devices is that you don't have the OS buffering and
possibly prefetching each request in file cache when DB2 will take care
of the buffering and prefetching. OS level file caching of the database
files just causes unnecessary I/O and overhead. The ability for DB2 to
bypass OS file cache with O_DIRECT opens, is not yet widely available.
File systems like Veritas vxfs for UNIX offer this feature on a file by
file basis, but as mentioned above, you can't control the location of
the file relative to the RAID group stripe.

RAID 5 can suffer in writes, though many of the disk RAID controllers
found in SANs hide the effect of this by using cache to assemble data so
that writes to disk occur across all disks in the stripe.


The samem pub also seems to confirm my own take that with RAID's and SAN's
the benefit of DMS over SMS is signficantly narrowed, perhaps not even
clearly an advantage at all. Has anyone measured the difference in
performance of DMS over SMS for reasonable assumptions about data and
workload characteristics when the containers are stored on a RAID or SAN?
I'm just about to start some I/O tests on this very subject and will
follow up with results later.

Thoughts?

Joseph

Harry Levinson

[Sean C.]

Could any of you folks provide a title or a link to the redbook
mentioned above? I tried hunting for it, with no luck.

Thanks,
Sean

[Mark A]

"Sean C." <db*****@yahoo.com> wrote in message
news:2f**************************@posting.google.c om...

Could any of you folks provide a title or a link to the redbook
mentioned above? I tried hunting for it, with no luck.

Thanks,
Sean

Most newsreaders (or the most widely used newsreader) does show the "above"
messages easily. It would have been nice had you included it in your post.

But here is the Redbook home page and you can search from there:
http://www.redbooks.ibm.com/

[Sean C.]

Mark A,

The "above" reference I was alluding to was:
"I've been investigating physical database placement issues for RAID's
and SAN's. There is an IBM Redbook that deals with the matter
somewhat,
where it is recommended that for a SAN, ensure that logical drives
correspond
to disjoint groupings, and map each tablespace to a set of containers,
one
for each logical drive grouping. The same on-line pub also discuss
the
LVM facility for AIX that can be used to implement a prefetch
operation
by the software that does the RAID data placement, but such a facility
apparently is lacking for Windows."

I am using a browser to look at these threads through Google groups.
Are you saying that if I use a dedicated newsreader, there would be a
link visible to the redbook in question?

Thanks for the link to the redbook page, but that is where I hunted in
vain.

Sean

[Mark A]

"Sean C." <db*****@yahoo.com> wrote in message
news:2f*************************@posting.google.co m...

Mark A,

The "above" reference I was alluding to was:
"I've been investigating physical database placement issues for RAID's
and SAN's. There is an IBM Redbook that deals with the matter
somewhat,
where it is recommended that for a SAN, ensure that logical drives
correspond
to disjoint groupings, and map each tablespace to a set of containers,
one
for each logical drive grouping. The same on-line pub also discuss
the
LVM facility for AIX that can be used to implement a prefetch
operation
by the software that does the RAID data placement, but such a facility
apparently is lacking for Windows."

I am using a browser to look at these threads through Google groups.
Are you saying that if I use a dedicated newsreader, there would be a
link visible to the redbook in question?

Thanks for the link to the redbook page, but that is where I hunted in
vain.

Sean

No, I am saying that with Outlook Express, I cannot see more than one post
at a time (if the other posts in the threads have been previously read)
without changing my settings, and I don't know the subject matter you are
looking for unless you repeat the post you are responding to. Never Mind.

After searching through the Redbooks, I found this one that has some
discussion of these subjects, but not sure if this what you need:

"Database Performance Tuning on AIX"
http://publib-b.boulder.ibm.com/Redb.../sg245511.html