-----Message d'origine-----
De : LA RESERVE S Ext ROSI/SIPROD
Envoyé : lundi 28 février 2005 09:48
À : 'DANIEL CALLAHAN '; LazyDBA Discussion
Objet : RE: Challeging Question:Database application process slow down.


Hello Experts,

We have have a BIG problem concerning the loading of a new table and new tablespaces (In product).

We are in UDB 8.2 Aix 5.1 (befor IN 4.3 and migrated before into 5.1)

configuration of the server is :
CPU: 2 CPU
RAM: 4 GB
SWAP:4 Go de Swap
Instance : 1
DataBase Base : 1
Connected User: 1
DISKS: Raid-10 and Raid-5 (9 discs)

We try to load a table with 3800 rows.

1- Table does not have columns LOG or LONG VARCHAR Length
2- Tablespaces are DMS
3- Table has a few indexes (In other tablesapce than Datas)
4- Synthax Is : db2 "load from table.ixf of ixf savecount 100000 messages X.msg replace into TABLE;
5- Load of the table is carried out in 1 hour (too much long)
5- During the loading, the activity of the CPU reaches up to 80 % (what is not normal)
6- During the loading the activity of the discs reaches up to 90% (what is not normal)
7- Configuration UDB was optimized with the following parameters:
SHEAPTHRES: from 20000 to 50000
SORTHEAP: from 1024 to 4096 pages
APPLHEAPSZ: from 2048 to 3072 Pages
DBHEAP: 800 to 2000 pages
CATALOGCACHE_SZ: 356 pages
LOGBUFSZ: 512 To 1024 Pages
MAXGENTS: 20
NUM_INITAGENTS: 0
NUM_POOLAGENTS: 0
NUM_IOCLEANERS 4
NUM_IOSERVERS 4
DATABASE_MEMORY AUTOMATIC

DB2 registry is:
DB2_RR_TO_RS=ON
DB2_PARALLEL_IO=*
DB2_STRIPED_CONTAINERS=ON
DB2_STRIPED_CONTAINERS=ON

8- On the other hand, which is odd,
the loading of the table alone (without index) is carried out into 40s

I THINKS that the problem intervene during the creation or rebuilding of the INDEXES during the loading.
I think that the problem comes from the configuration of the AIX.

Referring to the works of Dwaine R. Snow,
I suspect a problem of "ulimit" of the machine.

Here the result of: "ulimit -a"
time(seconds) Illimité
file(blocks) Illimité
data(kbytes) Illimité
stack(kbytes) 4194304
memory(kbytes) Illimité
coredump(blocks) Illimité
nofiles(descriptors) Illimité


P/S 1 : Bizare, the same loading on a Windows Server (2 CPU, 4 GB of RAM, 4 GB of swap) spends 2 minutes (tables and index)
P/S : Even the creation of a tablespace of 1 GB produces the same effect.

You have an idea what that can be ??
Thanks In advance.

DB2 & UDB email list listserv db2-l LazyDBA home page