on 08-24-2009 3:57 PM
Hi Folks - We keep getting the following message every 30 seconds on our BI QA server.
2009-08-24-07.26.17.263989-240 E78429A607 LEVEL: Warning
PID : 930014 TID : 15425 PROC : db2sysc 0
INSTANCE: db2bq1 NODE : 000 DB : BQ1
APPHDL : 0-28423 APPID: *LOCAL.DB2.090808073504
AUTHID : DB2BQ1
EDUID : 15425 EDUNAME: db2stmm (BQ1) 0
FUNCTION: DB2 UDB, Self tuning memory manager, stmmComputeMinSHEAPTHRES, probe:836
MESSAGE : Unexpected minimum value for SHEAPTHRES_SHR - value automatically
corrected - 122423 - 2448 - 338887 - 9180 - 3 - 0 - 3.000000 -
1 - 31 - 271110 - 630
I am pretty sure this will have performance impact on the database as the database manager has to keep changing the SHEAPTHRES_SHR value every 30 seconds back to the correct value. Has any one seen this before? Turning off STMM is not an option. We do not want to hard code SHEAPTHRES_SHR value as the loads on BI are not-predictable in the environment.
Pl advise.
Thanks.
If the parameter value is changed that often, then most probably there is some contention of the memory resources that are available to your database. In other words, the requirements for this memory area are high, so due to shortage of memory other memory-related parameters are continuously competing with SHEAPTHRES_SHR .
If that is the case, then you wither need to provide enough memory to the database (e.g. increase DB memory / instance memory), or set the parameters that are affected to fixed values (not too high to avoid paging) and live with resulting performance impact (due to lack of resources).
It is hard to be more precise as information provided by you is not too much.
Malte
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hello,
What version of DB2 are you running on? Assuming your are running version 9.5 (or V9.1) with stmm, please check to see if you are not exhausting the memory allocated to the INSTANCE_MEMORY configuration. This can be done by connecting to the database and running:
db2 "select * from table(admin_get_dbp_mem_usage()) as a"
You should see a "buffer" between the peak/current partition memory and the max_partition memory. There is a bug in DB2 9.5 versions prior to FP4 where it overestimates the amount of available memory and exhausts all the available memory which causes DB2 to run into several memory problems. This can be fixed (temporarily) by setting aside some reserved memory by setting the following registry parameter:
db2set DB2_MEM_TUNING_RANGE=10,20
You can remove this registry variable after upgrading to 9.5 FP4.
Thanks,
Sameer
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
sorry.. didn't get a chance to look at this forum.
I am at 9.5 fix pack 3a... about to upgrade to fix pack 4.
Temporarily, we increased instance memory to 70% of memory on the server and we don't see the SHEAPTHRES_SHR errors any more. Chances are too many memory variables were competing for the available memory.
Edited by: Pavank on Sep 9, 2009 8:55 PM
The output of the SQL statement shows:
MAX_PARTITION_MEM: Maximum allowed "instance_memory" (this will correspond with the current setting of instance_memory in bytes)
CURRENT_PARTITION_MEM: Current total memory allocation (in bytes)
PEAK_PARTITION_MEM: HWM total memory allocation (in bytes)
The bug with the STMM memory calculation was first fixed with version 9.5 FP4 so you might have to upgrade your instances to FP4. In fact, if I am not mistaken, SAP withdrew FP3 due to a hiper apar which can potentially impact SAP systems.
Also as far as the values in the output of the query and db2pd are concerned, they are not different. The output of the query is expressed in bytes whereas the output of pd is expressed in KB.
$$ db2pd -dbptnmem
Memory Limit: 36000000 KB (which is 36864000000 bytes)
Current usage: 31746944 KB (which is 32508870656 bytes)
HWM usage: 35704320 KB (which is 36561223680 bytes)
Regards,
Sameer
Hello,
What is the DB version of your databse?
Please have look at Note : 899322.
Update us on the issue if this is not helping much.
Regards,
Abhay
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
84 | |
10 | |
10 | |
9 | |
7 | |
6 | |
5 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.