Unanswered: DB2 9.7.7 snapshot performance degrades after changing STMM CFG of partitioned DB
I have recently changed the STMM configuration for partitioned database (4 partitions) and taking snapshots now takes few seconds (used to be instantaneous). STMM used to be enabled on P1/2/3, but because P0 was the volatile one, I decided to enable STMM on P0 and disable it on P1/2/3. All partitions are logical ones (on the same machine) with fixed instance size. Database is obviously slower in terms of responding to snapshot requests (db2top refresh cycles goes up to 15 seconds when all partitions are being monitored, but when I choose one partition - the response seems to be alright - instantaneous).
At first I thought that package cache grows too large and slows db2top down, so I set it to 400MB on all partitions. Then I tried the same with locklist. However, the response time of db2top is still the same.
I noticed, that whenever I choose to monitor all partitions, migration/1-31 linux processes start consuming way more CPU than usually (overall system share of CPU increases) - and db2top performance degrades.
Thanks for responding db2girl, I know STMM is not recommended for a database with quite different partitions (workload, design or hardware vise). However, I am using STMM on only one partition, which is more dynamic, other 3 are pretty static, their workload is pretty much the same.
Getting back to my problem, the snapshots are still slow, but I have noticed that performance degraded on non-STMM partitions (~3 times longer response time) and STMM-enabled partition response time was better that it was before the change. I also noticed, that in non-STMM partitions almost all applications were in 'Decoupled' state, which is weird and I don't think that it was happening before the change. I don't quite understand why applications would be decoupled on partitions 1,2,3 but not on partition 0. I am not using the connection concentrator (max_cooragents=max_connections=automatic(400)).
I have checked the db2diag.log with level 4 - no useful information there..
By the way, I have installed FP7 the same day as I was doing the configuration adjustments for the database. Could it be that some processes might be in a way of db2 work? (I did not do ipclean after db2stop..)