The following command to create a 14.6 GB tablespace took 19 minutes
to run. This strikes me as very slow. What are your experiences?

CREATE LARGE TABLESPACE "CREC_2011Q4_IDX" IN DATABASE PARTITION GROUP
IBMDEFAULTGROUP PAGESIZE 8192 MANAGED BY DATABASE USING (FILE '<LUN>/
CREC_2011Q4_IDX.01' 1914624) EXTENTSIZE 24 PREFETCHSIZE AUTOMATIC
BUFFERPOOL BP_IX_8K_CALL_REC AUTORESIZE YES INCREASESIZE 615168 K
MAXSIZE NONE NO FILE SYSTEM CACHING DROPPED TABLE RECOVERY ON;

The system is DB2 LUW 9.5 FP7 on Red Hat ELS 5.5. The underlying disk
storage is a NetApp filer.

I thought I would then see how dd performs with a file of the same
size being written to the same LUN using the command below:

dd if=/dev/zero of=<LUN>/emptyfile bs=8k count=1914624

Performance was better than the CREATE TABLESPACE command but not
great, taking an average of about 6 minutes over 3 runs.

I think I may have answered my own question here. This iostat output
was take during the dd command runs:

Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s avgrq-sz
avgqu-sz await svctm %util
sdb 0.00 18066.12 13.59 161.38 183.73 145819.98
834.43 148.21 834.62 5.96 104.20

Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s avgrq-sz
avgqu-sz await svctm %util
sdb 0.00 30114.54 18.07 239.10 224.75 243627.50
948.21 296.96 1133.86 7.64 196.48

Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s avgrq-sz
avgqu-sz await svctm %util
sdb 0.00 20727.59 14.90 164.26 168.57 167606.98
936.45 168.60 921.69 6.50 116.43

The await, svctm and %util values do not strike me as being great.

Interestingly, I ran a very similar CREATE TABLESPACE command on a
laptop running DB2 9.7 FP4 and Windows XP. The 14GB tablespace was
created in less than a second.

Can anyone shed any light on this?

I welcome your comments.

Thanks in advance.

On Sep 7, 7:58*am, Desmodromic <davies... (AT) yahoo (DOT) com.au> wrote:
Could you take out "NO FILE SYSTEM CACHING" from the command, then
take a try?

Arthur

What type of filesystem are you using? ext3/ext4/xfs/jfs ?

On 07.09.11 7:58 , Desmodromic wrote:

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

On Sep 7, 11:50*pm, Helmut Tessarek <tessa... (AT) evermeet (DOT) cx> wrote:
The filesystem is ext3.

On Sep 7, 10:48*pm, Arthurccc <yaxiongc... (AT) gmail (DOT) com> wrote:
I ran the same CREATE TABLESPACE command without the NO FILE SYSTEM
CACHING option. It took 13 minutes rather than 19 minutes. What is the
rationale behind not using that option? I normally use it to prevent
double buffering.

On Sep 8, 7:32*am, Desmodromic <davies... (AT) yahoo (DOT) com.au> wrote:
This is not a good situation. During CREATE, DROP and RESIZE of
tablespaces, applications are not able to connect or disconnect from
the database. This is making any TABLESPACE administration an offline
task. Auto-extend also results in the same issue.

How about to specify "INITIALSIZE ... K"(48K or more)?

I thought that DB2 might formatting the tablespace upto INCREASESIZE
615168 K.

Hi,

since your dd command is also very slow I assume that there coulbd be
a config problem between the Linux-box and the Netapp-Filer. Something
like buffersizes for communication or so - I'm not a specialist for
these things, but I would have a look at this:
1) CFG of linux for connection to NetApp
2) CFG for NetApp for conn. to linux
3) the equipment for the communication itself (FC? ethernet? firewall?
switches?)

"NONE NO FILE SYSTEM CACHING" is OK for me, we use it also - and the
duration for TS creation is not significantly faster...

Is this the first/only tablespace you use on this NetApp-Filer on this
box?
Do you have other boxes connected to NettApp having a much better
speed?

Good luck - Stefan

On Sep 8, 8:58*pm, "stefan.albert" <stefan.alb... (AT) spb (DOT) de> wrote:
All tablespaces are affected and all of our DB2 systems - Dev, UAT and
Prod - seem to suffer from the same I/O issues. All run Red Hat ELS
with NetApp storage. So, I don't have any DB2 systems running well
with NetApp storage here.

We are thin on Linux/Storage experts as well, which doesn't help.

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

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

Thanks.

On 08.09.11 22:52 , Desmodromic wrote:
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.

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