Self-tuning memory was first introduced in IBM ® DB2 ® 9.1 and simplifies the task of memory configuration by automatically setting optimal values for most memory configuration parameters, including buffer pools, package cache, locking memory, sort heap, and total database shared memory. When the self tuning memory manager (STMM) is enabled, the memory tuner dynamically distributes the available memory among the various memory consumers.
STMM modes of operation
STMM works in four different modes
- Mode 1: DATABASE_MEMORY= AUTOMATIC
In this case, the required memory is taken from and returned to the OS when required. The total amount of memory used by DB2 can grow over time and is limited only by the operating system’s memory availability.
Mode 2: DATABASE_MEMORY= <NUMERIC VALUE> AUTOMATIC
In this case, memory tuning still occurs, but the total memory used by the database would start from the NUMERIC value and can grow over time. It is only limited by the operating system’s available memory.
Mode 3: DATABASE_MEMORY=<NUMERIC VALUE>
DB2 will allocate this amount of memory during startup and this memory setting is static. It can not take or give memory to the operating system on demand.
Mode 4: DATABASE_MEMORY=COMPUTED
The database memory is computed based on the sum of initial values of the database memory heaps during the database startup. In this case DATABASE_MEMORY is not enabled for self tuning.
Activating self-tuning memory
UPDATE DATABASE CONFIGURATION USING DATABASE_MEMORY AUTOMATIC UPDATE DATABASE CONFIGURATION USING SELF_TUNING_MEM ON IMMEDIATE UPDATE DATABASE CONFIGURATION USING SORTHEAP AUTOMATIC UPDATE DATABASE CONFIGURATION USING SHEAPTHRES_SHR AUTOMATIC UPDATE DATABASE CONFIGURATION USING LOCKLIST AUTOMATIC UPDATE DATABASE CONFIGURATION USING MAXLOCKS AUTOMATIC UPDATE DATABASE CONFIGURATION USING PCKCACHESZ 8196 AUTOMATIC IMMEDIATE ALTER BUFFERPOOL BPname1 IMMEDIATE SIZE 1000 AUTOMATIC ALTER BUFFERPOOL BPname2 IMMEDIATE SIZE 1000 AUTOMATIC
Determining which memory consumers are enabled for self tuning
GET DB CFG SHOW DETAIL Description Parameter Current Value Delayed Value ----------------------------------------------------------------------------------------------- Self tuning memory (SELF_TUNING_MEM) = ON (Active) ON Size of database shared memory (4KB) (DATABASE_MEMORY) = AUTOMATIC(37200) AUTOMATIC(37200) Max storage for lock list (4KB) (LOCKLIST) = AUTOMATIC(7456) AUTOMATIC(7456) Percent. of lock lists per application (MAXLOCKS) = AUTOMATIC(98) AUTOMATIC(98) Package cache size (4KB) (PCKCACHESZ) = AUTOMATIC(8196) AUTOMATIC(5600) Sort heap thres for shared sorts (4KB) (SHEAPTHRES_SHR) = AUTOMATIC(5000) AUTOMATIC(5000) Sort list heap (4KB) (SORTHEAP) = AUTOMATIC(256) AUTOMATIC(256)
Verifying which bufferpools are enabled for self tuning
SELECT BPNAME, NPAGES, PAGESIZE FROM SYSCAT.BUFFERPOOLS BPNAM NPAGES PAGESIZE ----------------------------------------------- IBMDEFAULTBP -2 4096 BPname1 -2 8192 BPname2 -2 32768
A bufferpool is enabled for self tuning, when NPAGES has a value of -2
Controlling DB2 Memory consumption for an Instance
The instance_memory configuration parameter specifies the maximum amount of memory that can be allocated for a database partition.
When instance_memory is set to AUTOMATIC, a fixed upper bound on total memory consumption for the instance is set at instance startup (db2start). Actual memory consumption by DB2 varies depending on the workload. When STMM is enabled to perform database_memory tuning (by default for new databases), during run-time, STMM dynamically updates the size of performance-critical heaps within the database shared memory set
according to the free physical memory on the system, while ensuring that there is sufficient free instance_memory available for functional memory requirements.
- For heavily-used instances, STMM increases the size of performance-critical heaps as needed. More functional memory is consumed, as there are more database agents servicing applications and consuming functional memory. If there is enough free instance_memory but very little free physical memory on the system, STMM starts decreasing the size of performance-critical heaps ensure that the system does not start paging. As functional memory requirements drop, free physical memory on the system should increase, and STMM will start increasing the performance-critical heaps again.
- For less heavily-used instances, there is less functional memory consumed by the instance, and if there is insufficient free physical memory left on the system, STMM shrinks performance-critical heaps.
If instance_memory is set to a specific value, and at least one active database has an AUTOMATIC value for database_memory, and STMM is enabled for that database, then STMM increases the database_memory size such that DB2 uses almost the entire amount of memory specified by instance_memory, ensuring only that enough free instance_memory is available
for functional memory requests. In this scenario, STMM does not monitor free physical memory on the machine, therefore, instance_memory must be configured properly to ensure that paging will not occur.
Disabling self tuning memory
Self tuning can be disabled for the entire database by setting self_tuning_mem to OFF. When self_tuning_mem is set to OFF, the memory configuration parameters and buffer pools that are set to AUTOMATIC remain AUTOMATIC and the memory areas remain at their current size.
UPDATE DATABASE CONFIGURATION USING SELF_TUNING_MEM OFF IMMEDIATE
Self tuning can also be effectively deactivated for the entire database if only a single memory consumer is enabled for self tuning. This is because memory cannot be redistributed when only one memory area is enabled. For example, to disable self tuning of the sortheap configuration parameter, you could enter the following:
UPDATE DATABASE CONFIGURATION USING SORTHEAP MANUAL
To disable self tuning of the sortheap configuration parameter and change the current value of sortheap to 2000 at the same time, enter the following:
UPDATE DATABASE CONFIGURATION USING SORTHEAP 2000
More detailed information
More details can be found on these very helpful article:
“DB2 9 self-tuning memory management” at
“Understanding the advantages of DB2 9 autonomic computing features” at
“DB2 self-tuning memory manager log parser” at