Create tablespace slow

#11

On Sep 9, 11:37*am, Helmut Tessarek <tessa... (AT) evermeet (DOT) cx> wrote:

Quote:

On 08.09.11 22:52 , Desmodromic wrote:

One thing I am not keen on is that all containers reside in a single
2TB LUN.

This is definitely not optimal. The question now is how many disks are there
underneath? Is there only one LUN defined on this array or are there several
LUNs which are used for different databases/apps?

In any case, please set DB2_PARALLEL_IO=*

I'm not familiar with NetApp storage, but the basics are the same.
The more disks for a LUN, the better. If you can spread tablespaces by placing
containers on different LUNs on different arrays (different set of disks),
even better.

The noatime option for this LUN is not set either. Does anyone know
whether setting it will lead to a significant performance improvement?

There is a performance impact, but it won't be significant. In benchmarksyou
can get up to .1% - .8% improvement. For your workload, you will have to test
it yourself.

--
Helmut K. C. Tessarek
DB2 Performance and Development
IBM Toronto Lab

On our production system I am told the LUN is comprised of 48 physical
disks. Consequently, I have set DB2_PARALLEL_IO=*:48

These physical disks are also dedicated solely to this one DB2
database.

Typical iostat output from this system is shown below.

Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s avgrq-sz
avgqu-sz await svctm %util
data_new_SdDg-data_new_SdHv
0.00 0.00 467.80 67.00 30043.20 908.80
57.88 34.44 64.43 1.87 100.02

From this it looks like an I/O request is spending 97% of its time
queuing.

Typical CPU usage looks like that below. Again %iowait looks high.

12:02:07 PM CPU %user %nice %system %iowait
%steal %idle
12:02:12 PM all 7.83 0.00 2.12 60.52
0.00 29.53
12:02:12 PM 0 34.94 0.00 11.45 53.61
0.00 0.00
12:02:12 PM 1 10.40 0.00 1.40 84.80
0.00 3.40
12:02:12 PM 2 2.40 0.00 0.40 62.73
0.00 34.47
12:02:12 PM 3 2.20 0.00 0.40 37.60
0.00 59.80
12:02:12 PM 4 1.80 0.00 0.60 34.13
0.00 63.47
12:02:12 PM 5 5.58 0.00 0.80 59.56
0.00 34.06
12:02:12 PM 6 2.20 0.00 0.80 71.54
0.00 25.45
12:02:12 PM 7 3.60 0.00 1.20 80.20
0.00 15.00

#12

Desmodromic <davies_ms (AT) yahoo (DOT) com.au> wrote in
news:565d9a88-41f4-41f5-9ba9-b391bea43251 (AT) a10g2000prn (DOT) googlegroups.com:

Quote:

On Sep 9, 11:37*am, Helmut Tessarek <tessa... (AT) evermeet (DOT) cx> wrote:
On 08.09.11 22:52 , Desmodromic wrote:

One thing I am not keen on is that all containers reside in a
single 2TB LUN.

This is definitely not optimal. The question now is how many disks
are th
ere
underneath? Is there only one LUN defined on this array or are there
seve
ral
LUNs which are used for different databases/apps?

In any case, please set DB2_PARALLEL_IO=*

I'm not familiar with NetApp storage, but the basics are the same.
The more disks for a LUN, the better. If you can spread tablespaces
by pl
acing
containers on different LUNs on different arrays (different set of
disks)
,
even better.

The noatime option for this LUN is not set either. Does anyone know
whether setting it will lead to a significant performance
improvement?

There is a performance impact, but it won't be significant. In
benchmarks
you
can get up to .1% - .8% improvement. For your workload, you will have
to
test
it yourself.

--
Helmut K. C. Tessarek
DB2 Performance and Development
IBM Toronto Lab

On our production system I am told the LUN is comprised of 48 physical
disks. Consequently, I have set DB2_PARALLEL_IO=*:48

These physical disks are also dedicated solely to this one DB2
database.

Typical iostat output from this system is shown below.

Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s avgrq-sz
avgqu-sz await svctm %util
data_new_SdDg-data_new_SdHv
0.00 0.00 467.80 67.00 30043.20 908.80
57.88 34.44 64.43 1.87 100.02

From this it looks like an I/O request is spending 97% of its time
queuing.

Typical CPU usage looks like that below. Again %iowait looks high.

12:02:07 PM CPU %user %nice %system %iowait
%steal %idle
12:02:12 PM all 7.83 0.00 2.12 60.52
0.00 29.53
12:02:12 PM 0 34.94 0.00 11.45 53.61
0.00 0.00
12:02:12 PM 1 10.40 0.00 1.40 84.80
0.00 3.40
12:02:12 PM 2 2.40 0.00 0.40 62.73
0.00 34.47
12:02:12 PM 3 2.20 0.00 0.40 37.60
0.00 59.80
12:02:12 PM 4 1.80 0.00 0.60 34.13
0.00 63.47
12:02:12 PM 5 5.58 0.00 0.80 59.56
0.00 34.06
12:02:12 PM 6 2.20 0.00 0.80 71.54
0.00 25.45
12:02:12 PM 7 3.60 0.00 1.20 80.20
0.00 15.00

In addition to the advice given already by Helmut, you may want to check
this thread on the IDUG-forum that seems related:
http://www.idug.org/p/fo/et/thread=34814

Also have a look at the recommendations in these NetApp Tech. Docs:

===
1. "DB2 9 for UNIX: Integrating with a NetApp Storage System"
http://media.netapp.com/documents/tr-3531.pdf

Chapter 9 has the following statement:
"Two of these registry variables, DB2_PARALLEL_IO and
DB2_STRIPED_CONTAINERS, should always be set when DB2 is used in
conjunction with a storage system"

===
2. "IBM DB2 on NetApp Storage: Deployment and Best Practices"
http://media.netapp.com/documents/tr-3272.pdf

Par. 11.4 discusses how to decide on some important storage related
settings, like the EXTENTSIZE and the PREFETCHSIZE

===
3. "IBM DB2 9.5 Performance and Scalability on RHEL5 with NFS and FCP
Using NetApp FAS or IBM N series Storage System"
http://media.netapp.com/documents/tr-3775.pdf

Par. 3.3 suggests to set DB2_LOGGER_NON_BUFFERED_IO to enable Direct-I/O
for the transaction logs.

===

Note: the docs also contain recommendations for Linux.

HTH

Cheers!

--
Jeroen

#13

On Sep 10, 5:27*am, TheBoss <TheB... (AT) invalid (DOT) nl> wrote:

Quote:

Desmodromic <davies... (AT) yahoo (DOT) com.au> wrote innews:565d9a88-41f4-41f5-9ba9-b391bea43251 (AT) a10g2000prn (DOT) googlegroups.com:

On Sep 9, 11:37*am, Helmut Tessarek <tessa... (AT) evermeet (DOT) cx> wrote:
On 08.09.11 22:52 , Desmodromic wrote:

One thing I am not keen on is that all containers reside in a
single 2TB LUN.

This is definitely not optimal. The question now is how many disks
are th
ere
underneath? Is there only one LUN defined on this array or are there
seve
ral
LUNs which are used for different databases/apps?

In any case, please set DB2_PARALLEL_IO=*

I'm not familiar with NetApp storage, but the basics are the same.
The more disks for a LUN, the better. If you can spread tablespaces
by pl
acing
containers on different LUNs on different arrays (different set of
disks)
,
even better.

The noatime option for this LUN is not set either. Does anyone know
whether setting it will lead to a significant performance
improvement?

There is a performance impact, but it won't be significant. In
benchmarks
*you
can get up to .1% - .8% improvement. For your workload, you will have
to
test
it yourself.

--
Helmut K. C. Tessarek
DB2 Performance and Development
IBM Toronto Lab

On our production system I am told the LUN is comprised of 48 physical
disks. Consequently, I have set DB2_PARALLEL_IO=*:48

These physical disks are also dedicated solely to this one DB2
database.

Typical iostat output from this system is shown below.

Device: * * * * rrqm/s * wrqm/s * r/s * w/s * rsec/s * wsec/s avgrq-sz
avgqu-sz * await *svctm *%util
data_new_SdDg-data_new_SdHv
* * * * * * * *0.00 * * 0.00 467.80 67.00 30043.20 * 908.80
57.88 * *34.44 * 64.43 * 1.87 100.02

From this it looks like an I/O request is spending 97% of its time
queuing.

Typical CPU usage looks like that below. Again %iowait looks high.

12:02:07 PM * * * CPU * * %user * * %nice * %system *%iowait
%steal * * %idle
12:02:12 PM * * * all * * *7.83 * * *0.00 * * *2.12 * * 60.52
0.00 * * 29.53
12:02:12 PM * * * * 0 * * 34.94 * * *0.00 * * 11.45 * * 53.61
0.00 * * *0.00
12:02:12 PM * * * * 1 * * 10.40 * * *0.00 * * *1.40 * * 84.80
0.00 * * *3.40
12:02:12 PM * * * * 2 * * *2.40 * * *0.00 * * *0.40 * * 62.73
0.00 * * 34.47
12:02:12 PM * * * * 3 * * *2.20 * * *0.00 * * *0.40 * * 37.60
0.00 * * 59.80
12:02:12 PM * * * * 4 * * *1.80 * * *0.00 * * *0.60 * * 34.13
0.00 * * 63.47
12:02:12 PM * * * * 5 * * *5.58 * * *0.00 * * *0.80 * * 59.56
0.00 * * 34.06
12:02:12 PM * * * * 6 * * *2.20 * * *0.00 * * *0.80 * * 71.54
0.00 * * 25.45
12:02:12 PM * * * * 7 * * *3.60 * * *0.00 * * *1.20 * * 80.20
0.00 * * 15.00

In addition to the advice given already by Helmut, you may want to check
this thread on the IDUG-forum that seems related:http://www.idug.org/p/fo/et/thread=34814

Also have a look at the recommendations in these NetApp Tech. Docs:

===
1. "DB2 9 for UNIX: Integrating with a NetApp Storage System"http://media..netapp.com/documents/tr-3531.pdf

Chapter 9 has the following statement:
"Two of these registry variables, DB2_PARALLEL_IO and
DB2_STRIPED_CONTAINERS, should always be set when DB2 is used in
conjunction with a storage system"

===
2. "IBM DB2 on NetApp Storage: Deployment and Best Practices"http://media..netapp.com/documents/tr-3272.pdf

Par. 11.4 discusses how to decide on some important storage related
settings, like the EXTENTSIZE and the PREFETCHSIZE

===
3. "IBM DB2 9.5 Performance and Scalability on RHEL5 with NFS and FCP
* *Using NetApp FAS or IBM N series Storage System"http://media.netapp.com/documents/tr-3775.pdf

Par. 3.3 suggests to set DB2_LOGGER_NON_BUFFERED_IO to enable Direct-I/O
for the transaction logs.

===

Note: the docs also contain recommendations for Linux.

HTH

Cheers!

--
Jeroen

Thanks for the links. The IDUG thread was actually posted by an ex-
colleague of mine and he was talking about exactly the same system
that I am!

I can confirm that our single data LUN is built using a single FlexVol
on a NetApp aggregate consisting of 3 RAID-DP arrays. Each RAID array
consists of 18 disks. Subtracting 2 parity disks from each array gives
us 3 x 16 disks for the LUN, hence the setting of DB2_PARALLEL_IO=*:
48.

Our tablespace extent sizes are typically 192KB. This is based upon
(what we believed was) a 4KB strip size and a RAID stripe size of 48 x
4KB = 192KB.

From what I understand in the TR-3272 document, the tablespace extent
size should be a multiple of 256KB.

I am still slightly confused about this Tetris size of 256KB. Is that
the RAID strip or stripe size? If it's the strip size then our stripe
size would be 16 x 256KB = 4096KB. In fact, since the aggregate
consists of 3 RAID arrays, wouldn't our RAID stripe size be 3 x 4096KB
= 12288KB? That's 12MB, which sounds excessive to me.

Also, are there any recommendations regarding the number of disks per
RAID array and the number of RAID arrays per aggregate? Should RAID
arrays span multiple disk trays or be confined to disks within the
same tray?

Any advice will be much appreciated.

#14

Desmodromic <davies_ms (AT) yahoo (DOT) com.au> wrote in
news:37acbf57-e52e-45e7-b633-54951697457f (AT) s2g2000prm (DOT) googlegroups.com:

Quote:

On Sep 10, 5:27*am, TheBoss <TheB... (AT) invalid (DOT) nl> wrote:
Desmodromic <davies... (AT) yahoo (DOT) com.au> wrote
innews:565d9a88-41f4-41f5-9ba9
-b391bea43251 (AT) a10g2000prn (DOT) googlegroups.com:

On Sep 9, 11:37*am, Helmut Tessarek <tessa... (AT) evermeet (DOT) cx> wrote:
On 08.09.11 22:52 , Desmodromic wrote:

One thing I am not keen on is that all containers reside in a
single 2TB LUN.

This is definitely not optimal. The question now is how many disks
are th
ere
underneath? Is there only one LUN defined on this array or are
there seve
ral
LUNs which are used for different databases/apps?

In any case, please set DB2_PARALLEL_IO=*

I'm not familiar with NetApp storage, but the basics are the same.
The more disks for a LUN, the better. If you can spread
tablespaces by pl
acing
containers on different LUNs on different arrays (different set of
disks)
,
even better.

The noatime option for this LUN is not set either. Does anyone
know whether setting it will lead to a significant performance
improvement?

There is a performance impact, but it won't be significant. In
benchmarks
*you
can get up to .1% - .8% improvement. For your workload, you will
have to
test
it yourself.

--
Helmut K. C. Tessarek
DB2 Performance and Development
IBM Toronto Lab

On our production system I am told the LUN is comprised of 48
physical disks. Consequently, I have set DB2_PARALLEL_IO=*:48

These physical disks are also dedicated solely to this one DB2
database.

Typical iostat output from this system is shown below.

Device: * * * * rrqm/s * wrqm/s * r/s * w/s * rsec/s
* wsec/s avgrq-sz
avgqu-sz * await *svctm *%util
data_new_SdDg-data_new_SdHv
* * * * * * * *0.00 * * 0.00 467.80 67.00 30043.20
* 908.80
57.88 * *34.44 * 64.43 * 1.87 100.02

From this it looks like an I/O request is spending 97% of its time
queuing.

Typical CPU usage looks like that below. Again %iowait looks high.

12:02:07 PM * * * CPU * * %user * * %nice * %system *
%iowait
%steal * * %idle
12:02:12 PM * * * all * * *7.83 * * *0.00 * * *
2.12 * * 60.52
0.00 * * 29.53
12:02:12 PM * * * * 0 * * 34.94 * * *0.00 * * 11.
45 * * 53.61
0.00 * * *0.00
12:02:12 PM * * * * 1 * * 10.40 * * *0.00 * * *
1.40 * * 84.80
0.00 * * *3.40
12:02:12 PM * * * * 2 * * *2.40 * * *0.00 * *
*0.40 * * 62.73
0.00 * * 34.47
12:02:12 PM * * * * 3 * * *2.20 * * *0.00 * *
*0.40 * * 37.60
0.00 * * 59.80
12:02:12 PM * * * * 4 * * *1.80 * * *0.00 * *
*0.60 * * 34.13
0.00 * * 63.47
12:02:12 PM * * * * 5 * * *5.58 * * *0.00 * *
*0.80 * * 59.56
0.00 * * 34.06
12:02:12 PM * * * * 6 * * *2.20 * * *0.00 * *
*0.80 * * 71.54
0.00 * * 25.45
12:02:12 PM * * * * 7 * * *3.60 * * *0.00 * *
*1.20 * * 80.20
0.00 * * 15.00

In addition to the advice given already by Helmut, you may want to
check this thread on the IDUG-forum that seems
related:http://www.idug.org/p/fo
/et/thread=34814

Also have a look at the recommendations in these NetApp Tech. Docs:

==
1. "DB2 9 for UNIX: Integrating with a NetApp Storage
System"http://media
.netapp.com/documents/tr-3531.pdf

Chapter 9 has the following statement:
"Two of these registry variables, DB2_PARALLEL_IO and
DB2_STRIPED_CONTAINERS, should always be set when DB2 is used in
conjunction with a storage system"

==
2. "IBM DB2 on NetApp Storage: Deployment and Best
Practices"http://media
.netapp.com/documents/tr-3272.pdf

Par. 11.4 discusses how to decide on some important storage related
settings, like the EXTENTSIZE and the PREFETCHSIZE

==
3. "IBM DB2 9.5 Performance and Scalability on RHEL5 with NFS and FCP
* *Using NetApp FAS or IBM N series Storage System"http://media.netap
p.com/documents/tr-3775.pdf

Par. 3.3 suggests to set DB2_LOGGER_NON_BUFFERED_IO to enable
Direct-I/O for the transaction logs.

==

Note: the docs also contain recommendations for Linux.

HTH

Cheers!

--
Jeroen

Thanks for the links. The IDUG thread was actually posted by an ex-
colleague of mine and he was talking about exactly the same system
that I am!

I can confirm that our single data LUN is built using a single FlexVol
on a NetApp aggregate consisting of 3 RAID-DP arrays. Each RAID array
consists of 18 disks. Subtracting 2 parity disks from each array gives
us 3 x 16 disks for the LUN, hence the setting of DB2_PARALLEL_IO=*:
48.

Our tablespace extent sizes are typically 192KB. This is based upon
(what we believed was) a 4KB strip size and a RAID stripe size of 48 x
4KB = 192KB.

From what I understand in the TR-3272 document, the tablespace extent
size should be a multiple of 256KB.

I am still slightly confused about this Tetris size of 256KB. Is that
the RAID strip or stripe size? If it's the strip size then our stripe
size would be 16 x 256KB = 4096KB. In fact, since the aggregate
consists of 3 RAID arrays, wouldn't our RAID stripe size be 3 x 4096KB
= 12288KB? That's 12MB, which sounds excessive to me.

Also, are there any recommendations regarding the number of disks per
RAID array and the number of RAID arrays per aggregate? Should RAID
arrays span multiple disk trays or be confined to disks within the
same tray?

Any advice will be much appreciated.

My experience on this subject isn't enough to offer sound advice.
If you have a service contract, your best bet would to open a service
request at either IBM or NetApp for this.
Alternatively you could try to contact (via social media?) well known
DB2 Champion Roger E. Sanders, who used to work for NetApp...

Cheers!

--
Jeroen

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Create tablespace slow

comp.databases.ibm-db2 comp.databases.ibm-db2

Re: Create tablespace slow - 09-08-2011 , 11:07 PM

Re: Create tablespace slow - 09-09-2011 , 04:27 PM

Re: Create tablespace slow - 09-12-2011 , 06:45 AM

Re: Create tablespace slow - 09-14-2011 , 05:01 PM