Question about tablespace and container design

[Michel Esber]

Hello,

LUW V8 FP 11 running Linux RH AS4 Update 3.

In regards to performance and IO parallelism, does it matter if I
create a tablespace with a single big container, or is it better to
create it with several smaller containers ?

Any ideas and links to the proper documentation is greatly appreciated.

Thanks in advance.

[Ian]

Michel Esber wrote:

Hello,

LUW V8 FP 11 running Linux RH AS4 Update 3.

In regards to performance and IO parallelism, does it matter if I
create a tablespace with a single big container, or is it better to
create it with several smaller containers ?

Any ideas and links to the proper documentation is greatly appreciated.

There isn't a lot of generic documentation on this - most
recommendations are specific to the type of storage your system is
using.

However: I generally prefer to have 1 container per physical storage
device (individual disk or RAID array), using DB2_PARALLEL_IO when
appropriate.

With large RAID arrays and SAN storage, you do have to be careful
because you may have multiple LUNs that reside on the same RAID array.
Putting 1 container/LUN when you have multiple LUNs from a single
RAID array can create I/O contention issues.

[Phil Sherman]

From a performance and parallelism viewpoint, multiple containers can
be of benefit if each container is located on a separate disk drive.
This allows parallel I/O when doing scans.

If you place two containers on a single disk drive, then, as data is
read from each container, a seek operation will be needed. Seeks are a
relatively slow operation and should be avoided as much as possible when
doing scans. When containers are located on separate disks, we expect to
read data from each disk drive in turn. If the system has the capacity
to do this concurrently from multiple drives, then the overall transfer
rate can be significantly improved.

Large raid5 disk subsystems, while using multiple disk drives, appear to
the operating system as a single disk. Placing multiple containers on
these doesn't provide the same benefits as separate disk drives will.
Raid5 can, with judicious matching of prefetch parameters to the
physical characteristics of the raid array, provide scan performance
enhancements similar to using multiple containers on different physical
disk drives.

Suggested reading:
Administration Guide: Planning - Chapter 5
Phil Sherman

Michel Esber wrote:

Hello,

LUW V8 FP 11 running Linux RH AS4 Update 3.

In regards to performance and IO parallelism, does it matter if I
create a tablespace with a single big container, or is it better to
create it with several smaller containers ?

Any ideas and links to the proper documentation is greatly appreciated.

Thanks in advance.

[aj]

<laughing>
It was about time for Art's annual RAID 5 rant, wasn't it?

Hi Art - hope things are going well. :)

cheers

Allen W. Jantzen (formerly of c.d.i)
Art S. Kagel wrote:

Phil Sherman wrote:
<SNIP>

>Large raid5 disk subsystems, while using multiple disk drives, appear
to the operating system as a single disk. Placing multiple containers
on these doesn't provide the same benefits as separate disk drives
will. Raid5 can, with judicious matching of prefetch parameters to the
physical characteristics of the raid array, provide scan performance
enhancements similar to using multiple containers on different
physical disk drives.

NO RAID5!!! NO RAID5!!! NO RAID5!!! NO RAID5!!! NO RAID5!!! NO
RAID5!!!

Raid5 and Relational Database Systems should never be used together!

Phil, your points are all well taken when applied to any striped RAID
format including RAID0, RAID01, RAID10, RAID3, and RAID4 (yes and RAID5
too). However, the inherent lack of data safety and performance of RAID5
make it unacceptable for RDBMSes like DB2, Informix, Oracle, etc. I
recognize that this was not your point Phil, so please don't flame me,
but I haven't had an opportunity to spread the word about the evils of
RAID5 in a while. Your posting was the opening I needed.

For details see my posting and those of others on the Anti-RAID5 web site:

www.baarf.com

Be sure to look at the members' page to review the brief mentions of the
actual problems DBAs and SAs have experienced using RAID5.

Art S. Kagel

[Phil Sherman]

This is really strange - the copy of the forum I follow from my ISP
doesn't have Art's reply to my post.

I was just a bit too lazy to go into all of the downsides of RAID5. I've
been caught by some of the effects and don't make it a practice to
recommend it:
1. A production system powered down for two days waiting for a
replacement drive in a RAID5 array. After power up, no activity allowed
until the drive was rebuilt. (I could have done a full recovery faster
than this!) Please - don't ask for details.
2. Abysmal write performance. This occurred on a system where, during
the planning process, nobody involved in the project understood the
consequences of RAID5, especially write performance issues. This problem
was compounded by neglecting to purchase a write cache.
The biggest issue I've run into is the storage appliance that provides a
"large quantity of disk space with minimal overhead using RAID5
technology" that is shared between multiple systems and is the only
place with enough disk space to support the database. It's often a
tremendous shock to the system owner when explaining that the brand new,
multi thousand (or tens of thousand) dollar storage appliance is not a
good place to locate a database.

I can only hope that someday IBM will provide additional information
about the potential downsides of RAID5 in the Planning Guide manual.
Hopefully, this will be alongside the details to tune DB2 for better
performance on that type of storage.

Thanks again to Art for his "annual rant".

Phil Sherman

aj wrote:

<laughing>
It was about time for Art's annual RAID 5 rant, wasn't it?

Hi Art - hope things are going well. :)

cheers

Allen W. Jantzen (formerly of c.d.i)
Art S. Kagel wrote:

>Phil Sherman wrote:
<SNIP>

>>Large raid5 disk subsystems, while using multiple disk drives, appear
to the operating system as a single disk. Placing multiple containers
on these doesn't provide the same benefits as separate disk drives
will. Raid5 can, with judicious matching of prefetch parameters to
the physical characteristics of the raid array, provide scan
performance enhancements similar to using multiple containers on
different physical disk drives.

NO RAID5!!! NO RAID5!!! NO RAID5!!! NO RAID5!!! NO RAID5!!! NO
RAID5!!!

Raid5 and Relational Database Systems should never be used together!

Phil, your points are all well taken when applied to any striped RAID
format including RAID0, RAID01, RAID10, RAID3, and RAID4 (yes and
RAID5 too). However, the inherent lack of data safety and performance
of RAID5 make it unacceptable for RDBMSes like DB2, Informix, Oracle,
etc. I recognize that this was not your point Phil, so please don't
flame me, but I haven't had an opportunity to spread the word about
the evils of RAID5 in a while. Your posting was the opening I needed.

For details see my posting and those of others on the Anti-RAID5 web
site:

www.baarf.com

Be sure to look at the members' page to review the brief mentions of
the actual problems DBAs and SAs have experienced using RAID5.

Art S. Kagel