IBM Support

DB2 9.7 Statement Concentrator with STMM

Technical Blog Post


Abstract

DB2 9.7 Statement Concentrator with STMM

Body

 The following blog entry was originally posted to the Process Automation Group by Bryan Tsai. I wanted to share this info with the Asset Management Group as well:
 
Self Tuning Memory Manager (STMM) is a feature introduced since DB2 9 to 
get you out of memory tuning hell. It allows DB2 instances to adjust all database shared memory (buffer pools, sort heap, lock list, package cache, catalog cache, etc) to improve the overall performance. By default, STMM is turned on. Overall, STMM works great for IBM Tivoli service management products.
 
As Rick mentioned, statement concentrator could be a huge performance booster for IBM Tivoli service management products, so we generally recommend its usage for Maximo. However, during our performance benchmark, we did find a few interesting things about STMM, when statement concentrator is turned on. The following chart shows the overall system page hit rate (throughput) under different workload (number of users). In this benchmark, we gradually added users into the system to observe its performance. Interestingly, when STMM is turned on for all shared memory area, the system's performance started to degrade when approaching near 3000 users and then kept getting worse with more users added:
image
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
One symptom we could observe was that STMM kept increasing the size of the package cache, which reached 2GB at the end of 4000-user stage. DB2 statement concentrator enables queries that are identical except for the values of literals to share the same access plan. In addition to the "shared" plan, it also put a small "stub" entry for each original query (with literals) in the package cache. When these large number of small "stub" entries fill up the package cache, it trigger STMM to increase the size of the package cache, which then quickly got filled up again and enlarged again and again ... Eventually, DB2 spent majority of its time in just managing this extra large package cache which caused the performance degradation.
 
To resolve this problem, we turned off STMM for package cache (only) and use a smaller, fixed size for it (pckcachesz). We have observed near 50% CPU decrease on DB2 with 25% improvement in overall response time.
 
Note that DB2 9.7 FP5 includes a fix to address this problem. With FP5, when both statement concentrator and STMM for package cache is turned on, the package cache size is always under 100MB for our benchmark.
 

[{"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Product":{"code":"SSLKT6","label":"IBM Maximo Asset Management"},"Component":"","Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"","Edition":"","Line of Business":{"code":"LOB59","label":"Sustainability Software"}}]

UID

ibm11134633

Page Feedback