DB2 DPF 9.5 ESE SP3 on AIX
20 GB Mem
We have massive Waits and asynchr I/O so we gave more MEM and decreased filesystemcaching with the aim to provide a maximum of memory for bufferpools. -> RESTART -> but even after waiting for some days we still have 8gb free mem.
STMM is a very useful tool, but automatic doesn't replace intelligence. When using STMM in a DPF environment you have to be careful how you use or you run in a szenario like we did. First you need to know how it works in a dpf environment. When all Partitions are set to STMM ON, by default it takes the first data partition, optimizes it and sends the configs (parallel) to the other partitions.
If the chosen tuning partition is not representative (as was with us), you run into performance issues. We have the first partition populated with normal tables, while 4 other partitions host the distributed tables.
So we need to selectivly run stmm on those groups of partitions that are representative for its peer group.
Ill do that over the weekend ..
Here is the output of - I am not familiiar with it, could you explain what you see?
In your 1st post you claim to have "stmm activated". Please read this How to setup the Self Tuning Memory Manager (STMM) | db2ude because I'm not sure if STMM "works as designed" in you config.
I asked for the DBM config because it is essential that you have a 64bit version of DB2 installed to be able to comsume the 20 Gb available.
With the results of you db2mtrk I am not suprised that your database does not perform too wel, you bufferpools are extreme small.... I've got bigger on my laptop...
Bottomline: set your bufferpools to "AUTOMATIC" and tail you db2diag.log and notice the bufferpools getting extended. If you see that you will notice that DB2 will perform better. Allow DB2-STMM a few hours to settle in.
-> STMM.log shows that it wants to increase the BP - but somehow it doesn't..
EDIT: Pls see post below: BP have been increased by STMM
see DB CFG/ DBM CFG as attached
db CFG: shows the config -> they differ between the partitions only in the SELF_TUNING_MEM, LOG_FILESIZE,LOGPATH, LOGPRIMARY
db2 get db.txt: show actual values of DB CFG (db2 "SELECT NAME, DEFERRED_VALUE, DBPARTITIONNUM FROM SYSIBMADM.DBCFG order by name,DBPARTITIONNUM" | tr -s " " )
There is an more in depth explanation that I prefer to the one u mentioned:
After waiting some hours, BP16k increased from 6000 pages to 47000 pages using about 1GB BP mem.. :-) hopefully this will increase further over time..
PS If you have any suggestions regarding the cfgs please tell me..
Checking everything there is something strange.
[pdb_f204:/pdb_f204/db2/dump/piedw] >db2 "SELECT NAME, DEFERRED_VALUE, DBPARTITIONNUM FROM SYSIBMADM.DBCFG order by DBPARTITIONNUM,name" | tr -s " " | grep -i self
self_tuning_mem OFF 0
self_tuning_mem OFF 1
self_tuning_mem ON 2
self_tuning_mem ON 3
self_tuning_mem ON 4
self_tuning_mem ON 5
because DBPNo 0 (Coord) and 1 is a non-representative Partition.