473,325 Members | 2,308 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,325 software developers and data experts.

Question about tablespace and container design

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.

Jul 25 '06 #1
4 1776
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.
Jul 25 '06 #2
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.
Jul 25 '06 #3
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
Jul 26 '06 #4
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
Jul 27 '06 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: maricel | last post by:
Which one is better in terms of producing optimum performance: a) Single tablespace that contain multiple containers, say 5 containers of 100MB each container or b)Single tablespace that that...
8
by: Amy DBA | last post by:
OK, I'm stumped now. I need help restoring a tablespace that has been marked offline. Here's a little background on the problem: 1) There was a hardware failure that caused the tablespace to...
4
by: duoduo | last post by:
I want to know one thing, what is best design for tablespace containers, of course, it is DB2 EE 7.2 on AIX 4.3.3. For example, I have RAID 0+1 storage (8 disks) on EMC Symmetrix. Now I need to...
4
by: mairhtin o'feannag | last post by:
Hello, I have a tablespace striped across three drives, call them 1,2,3, just to be clever. :) I allocated a lot more space (DMS) than I should have, since I didn't know a way to estimate the...
1
by: jane | last post by:
HI, I have one performance questions... is # of tables in one tablespaces matter for performance point of view? why?
2
by: causacn | last post by:
Hi, the column "picture" in table emp_photo from sample db is defined as blob(102400) As a Blob type, the actual data should be stored separately from the base table. However, when i look at...
0
by: Lan W via DBMonster.com | last post by:
I tried to restore DB2 UDB two databases into two different instances on AIX server. the raw devices size of rShortSpace0 and rShortSpace1 on the two instances are the same. # lsvg -l datavg...
0
by: zhif | last post by:
I tried to test this process on my personal laptop. Could you help me to take a look where is the problem as below? 1. I created a source database, name: db100 <-- db2 create db db100...
3
by: Michel Esber | last post by:
Hi all, Db2 v8 FP15 LUW . create table T (ID varchar (24), ABC timestamp) There is an index for (ID, ABC), allowing reverse Scans. My application needs to determine MIN and MAX(ABC) for a...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.