Somewhere along the line, I associated ‘AUTOMATIC’ settings for parameters with DB2’s Self-Tuning Memory Manger (STMM). But the two are not associated. Sure, if STMM is set to ON, then some parameters set to AUTOMATIC will be tuned by the STMM, but many parameters can be set to automatic whether STMM is ON or not.
What Parameters STMM can Change
In reality, the parameters that STMM deals with are ONLY:
- DATABASE_MEMORY if AUTOMATIC
- SORTHEAP, SHEAPTHRES_SHR if AUTOMATIC, and SHEAPTHRES is 0
- BUFFERPOOLS if number of pages on CREATE/ALTER BUFFERPOOL is AUTOMATIC
- PCKCACHESZ if AUTOMATIC
- LOCKLIST, MAXLOCKS if AUTOMATIC (both must be automatic)
Now personally, I immediately set PCKCACHESZ to a fixed number on my e-commerce or OLTP systems. On an oversized system, I’ve seen the STMM allocate way too much space to my package cache by caching every query that’s only ever executed once, which in a system that should be running well-defined SQL only is not helpful for performance.
But I’ve had good luck with the others generally set at AUTOMATIC on single-instance, single-database OLTP systems.
Three Scenarios to Understand
Scenario 1: STMM is off, DATABASE_MEMORY = AUTOMATIC
SELF_TUNING_MEM: OFF
DATABASE_MEMORY: AUTOMATIC
DB2 will add up the parameters for UTIL_HEAP_SZ, PCKCACHESZ, DBHEAP, SHEAPTHRES_SHR, LOCKLIST and CATALOGCACHE_SZ. Let’s pretend that equals 10 GB. DB2 then adds 20% to that – 2 GB – as the overflow buffer. You can then manually change any of those parameters plus bufferpools, as long as that change is less than the the 2 GB (assuming other changes haven’t used your 2 GB), the change will be immediately successful. If your changes exceed 2 GB, then the change will be deferred.
Package cache overflows and Catalog cache overflows automatically take memory from the overflow buffer, but other memory parameters must be changed manually to use the overflow buffer.
The size of the overflow buffer cannot be increased because STMM is off.
Scenario 2: STMM is on, DATABASE_MEMORY = 11 GB
SELF_TUNING_MEM: ON
DATABASE_MEMORY: 2883584
DB2 will add up the parameters for UTIL_HEAP_SZ, PCKCACHESZ, DBHEAP, SHEAPTHRES_SHR, LOCKLIST and CATALOGCACHE_SZ. Let’s pretend that equals 10 GB. DB2 then subtracts that from your static value for DATABASE_MEMORY – in this case, 11-10=1GB as the overflow buffer. You can then manually change any of those parameters plus bufferpools, as long as that change is less than the the 1 GB (assuming other changes haven’t used your 1 GB), the change will be immediately successful. If your changes exceed 1 GB, then the change will be deferred.
Package cache overflows and Catalog cache overflows automatically take memory from the overflow buffer, but other memory parameters must be changed manually to use the overflow buffer.
The size of the overflow buffer cannot be increased because it is limited by the static size of DATABASE_MEMORY.
Scenario 3: STMM is on, DATABASE_MEMORY = AUTOMATIC
SELF_TUNING_MEM: ON
DATABASE_MEMORY: AUTOMATIC
DB2 will automatically tune sortheap, sortheapthres_shr (must have sheapthres=0 in DBM CFG), locklist and maxlocks, bufferpools, and package cache. If DB2 runs out of space in the overflow buffer, it will go looking for more memory in INSTANCE_MEMORY. If INSTANCE_MEMORY is set to AUTOMATIC, then DB2 will also go looking for more memory from the operating system. But realistically, DB2 is only tuning INSTANCE_MEMORY by adding space to DATABASE_MEMORY, because INSTANCE_MEMORY contains DATABASE_MEMORY. DB2 does not increase INSTANCE_MEMORY for the instance parameters.
Within INSTANCE_MEMORY, DB2 allocates space to DATABASE_MEMORY, MON_HEAP_SZ, AUDIT_BUF_SZ, FCM buffers, FCM anchors and APPL_MEMORY. DB2 does not increase INSTANCE_MEMORY for the instance parameters – INSTANCE_MEMORY is only increased to accommodate changes to DATABASE_MEMORY.
If you set a hard value for INSTANCE_MEMORY, but leave DATABASE_MEMORY at AUTOMATIC, DB2 may choose to give too much of INSTANCE_MEMORY to DATABASE_MEMORY, leaving too little memory for APPL_MEMORY, and impacting overall performance. For this reason, if hard limits are set, it is advisable to set them at the database level instead of the instance level.
Some examples of parameters that can be set to AUTOMATIC without STMM:
- DBHEAP – AUTOMATIC means it can be increased as long as there is space in DATABASE_MEMORY, or if DATABASE_MEMORY is also automatic, INSTANCE_MEMORY
- MON_HEAP_SZ – AUTOMATIC means it can be increased as long as there is space in INSTANCE_MEMORY
- STMTHEAP – AUTOMATIC means it can be increased as long as there is space in APPL_MEMORY, or INSTANCE_MEMORY if APPL_MEMORY is set to ATUOMATIC.
- APPLHEAPSZ – AUTOMATIC means it can be increased as long as there is space in APPL_MEMORY, or INSTANCE_MEMORY if APPL_MEMORY is set to ATUOMATIC.
A couple of notes: STMM requires at least 2 memory consumers to be enabled for STMM to do anything. Also, when IBM introduced STMM, IBM started using the term COMPUTED for memory areas it calculates at startup and changed AUTOMATIC to mean STMM.
Hi Ember, great article!!
What happens when you got multiple db2 instances on a single server and all of them have INSTANCE_MEMORY=AUTOMATIC & DATABASE_MEMORY=AUTOMATIC ?. Is DB2 smart enough to never cause the server swap to disk?. Does it apply in all DB2 LUW supported platforms?
Regards
Since nearly all of my production servers are single instance to single server implementations, I don’t have direct experience here. I believe IBM would tell you yes, but this is one area I have heard of significant issues in from other DBAs. I know some respected DBAs who don’t like setting instance and database memory to automatic on multi-instance implementations, so my initial reaction would be to to avoid that. It would be an interesting question to ask Scott Hayes (who runs the db2Night show).
[…] with, it is important to understand what STMM tunes and what it doesn’t. I recommend reading When is ‘AUTOMATIC’ Not STMM?. There are essentially only 5 areas that STMM can […]