How to setup the Self Tuning Memory Manager (STMM)

by | Apr 30, 2008

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.

Depending on workload, DB2’s default memory configuration adapts to the memory requirements of the instance without requiring explicit self-tuning of overall instance memory. For instance:

  • 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
http://www.ibm.com/developerworks/edu/dm-dw-dm-0611read2-i.html

“Understanding the advantages of DB2 9 autonomic computing features” at
http://www.ibm.com/developerworks/db2/library/techarticle/dm-0709saraswatipura/

“DB2 self-tuning memory manager log parser” at
http://www.ibm.com/developerworks/db2/library/techarticle/dm-0708naqvi/