Load performance on AIX w/ SAN

[rdudejr]

Hi all,

I hardly ever make a post unless I am having a very purplexing issue,
so this one should be good...

I am trying to do a load against a database on an AIX server into a
DB2 v9.1 database, using SAN for storage. The table has a few CLOBs
(smallish clobs but we are storing XML data in non-native format).
Here is the load command I am using:

db2 "load from loadset1 of del modified by chardel| coldel& insert
into testschema.test table nonrecoverable data buffer 240000
disk_parallelis m 32"

Now I am loading about 440 rows/second, which to me is abysmally
slow. The tablespaces I am loading into have 8 containers and I
believe there are at least 30 disks on the SAN that the data
eventually lives on. So needless to say there should be all the I/O
power available to load this data.

My data file I am loading from lives on the same filesystem (and
therefore same logical volume), so I am aware that IO headseek issues
could be the problem. However, the way I understand SAN is that all
the disks are working together, so SAN takes care of the data
allocation so that headseek does not become a problem.

I guess what I am asking is a twofold question...firs t (and the most
appropriate for this forum), is my load command most appopriate for
what I am trying to do?

Second, does the output of topas below indicate that the disks are
really only working at 20% on average? Or is it a lie since I am
running on SAN? Anyone with simular experience please help!

If what is below is indeed correct, why is the load not using all the
disk and/or CPU?
Topas Monitor for host: server1 EVENTS/QUEUES FILE/TTY
Mon Nov 10 16:09:09 2008 Interval: 2 Cswitch 8071
Readch 1520.1K
Syscall 5308
Writech 1976.3K
CPU User% Kern% Wait% Idle% Reads 168
Rawin 0
cpu1 69.5 6.5 0.0 24.0 Writes 889
Ttyout 624
cpu2 1.0 4.0 7.5 87.5 Forks 0
Igets 0
cpu4 0.5 4.0 21.5 74.0 Execs 1
Namei 24
cpu0 0.5 3.0 0.0 96.5 Runqueue 1.0
Dirblk 0
cpu5 0.0 3.0 25.5 71.5 Waitqueue 0.0
cpu3 0.0 2.0 15.5 82.5
cpu6 0.0 2.0 15.0 83.0
PAGING MEMORY
cpu7 0.0 5.5 12.5 82.0
Faults 1032 Real,MB 24576

Steals 390 % Comp 39.9
Network KBPS I-Pack O-Pack KB-In KB-Out PgspIn 0 %
Noncomp 13.3
en6 0.8 1.0 1.5 0.1 0.7 PgspOut 0 %
Client 2.2
en5 0.0 0.0 0.0 0.0 0.0 PageIn 1267
lo0 0.0 0.0 0.0 0.0 0.0 PageOut 888
PAGING SPACE
Sios 2155
Size,MB 12032
Disk Busy% KBPS TPS KB-Read KB-Writ %
Used 0.0
hdisk17 18.0 944.0 236.0 518.0 426.0 NFS (calls/sec) %
Free 100.0
hdisk27 14.5 1.0K 237.0 456.0 584.0 ServerV2 0
hdisk42 14.0 780.0 195.0 384.0 396.0 ClientV2 0
Press:
hdisk22 13.5 876.0 219.0 424.0 452.0 ServerV3 0 "h"
for help
hdisk12 12.0 892.0 211.5 414.0 478.0 ClientV3 0 "q"
to quit
hdisk37 11.0 900.0 214.0 392.0 508.0
hdisk7 11.0 950.0 237.5 480.0 470.0
hdisk32 6.0 874.0 218.5 464.0 410.0
hdisk29 2.0 512.0 2.0 512.0 0.0
hdisk24 2.0 512.0 2.0 512.0 0.0
hdisk9 1.5 256.0 1.0 256.0 0.0
hdisk19 1.0 256.0 1.0 256.0 0.0
hdisk16 0.5 6.0 1.5 0.0 6.0
hdisk15 0.0 0.0 0.0 0.0 0.0
hdisk14 0.0 0.0 0.0 0.0 0.0
hdisk11 0.0 2.0 0.5 0.0 2.0
hdisk1 0.0 0.0 0.0 0.0 0.0
hdisk20 0.0 0.0 0.0 0.0 0.0

Name PID CPU% PgSp Owner
db2sysc 1839138 10.7 0.6 tvpi01
db2sysc 574364 0.1 0.5 tvpi01
db2sysc 1188050 0.1 0.6 tvpi01
db2sysc 663620 0.1 0.7 tvpi01
db2sysc 1225124 0.1 0.6 tvpi01
db2sysc 290888 0.1 0.7 tvpi01
db2sysc 1552616 0.1 0.6 tvpi01
db2sysc 671780 0.1 0.6 tvpi01
db2sysc 1339656 0.1 0.6 tvpi01
db2sysc 1511780 0.1 0.6 tvpi01
db2sysc 1323334 0.1 0.6 tvpi01
db2sysc 1679524 0.1 0.6 tvpi01
topas 1564806 0.1 3.5 tvpi01
db2sysc 983414 0.1 0.6 tvpi01
db2sysc 143704 0.1 0.7 tvpi01
db2sysc 1216702 0.1 0.6 tvpi01
db2sysc 975286 0.0 0.6 tvpi01
db2sysc 872862 0.0 0.7 tvpi01
db2sysc 962762 0.0 0.6 tvpi01
db2sysc 1180054 0.0 0.6 tvpi01

[rdudejr]

We turn off filesystem caching on all our tablespaces, regardless of
if they have LOBs or not (as we do not want giant lobs in memory).

Here is the link about parallelism not supported with LOBs:

http://publib.boulder.ibm.com/infoce...c/c0004605.htm

read halfway down under "CPU_PARALLELIS M"

We do not use LPARs here, the only shared resource is Disk on the
SAN. I tried the import and it was even slower. I did strip out the
LOBs and create the table SANs lob loaded data SANs lob, and I was
able to load 3 million records in less than a minute...more of the
performance I was expecting.

So I guess all of this data leads me to this question...what is the
best way to load a LOB into a table, seeing as DB2's LOAD utility does
an abysmal job of dealing with LOBs?????

[rdudejr]

Noone has any more good ideas on this?

And yes I know this is a shameless bump but I am getting pretty
desperate/frustrated with DB2's behaviour at this point in regards to
LOBs.

On Nov 12, 2:50*pm, rdudejr <rdud...@gmail. comwrote:

We turn off filesystem caching on all our tablespaces, regardless of
if they have LOBs or not (as we do not want giant lobs in memory).

Here is the link about parallelism not supported with LOBs:

http://publib.boulder.ibm.com/infoce...x.jsp?topic=/c....

read halfway down under "CPU_PARALLELIS M"

We do not use LPARs here, the only shared resource is Disk on the
SAN. *I tried the import and it was even slower. *I did strip out the
LOBs and create the table SANs lob loaded data SANs lob, and I was
able to load 3 million records in less than a minute...more of the
performance I was expecting.

So I guess all of this data leads me to this question...what is the
best way to load a LOB into a table, seeing as DB2's LOAD utility does
an abysmal job of dealing with LOBs?????

[rdudejr]

Noone has any more good ideas on this?

And yes I know this is a shameless bump but I am getting pretty
desperate/frustrated with DB2's behaviour at this point in regards to
LOBs.

[Ian]

rdudejr wrote:

Noone has any more good ideas on this?

And yes I know this is a shameless bump but I am getting pretty
desperate/frustrated with DB2's behaviour at this point in regards to
LOBs.

You're not going to get much help if you can't give any useful
information. No DDL, no sample data, etc.

What process do you see consuming the most CPU when you're running the
load? Is it db2lfrm (i.e. the process that formats the actual data
pages)? Or is it something else?

Do you get warnings or errors from your load process (i.e. does DB2
complain about your data?)

Are your lobs inline in the data file, or are they external (i.e. lob
locators in the actual import file)?