Self-tuning memory

A memory-tuning feature simplifies the task of memory configuration by automatically setting values for several memory configuration parameters. When enabled, the memory tuner dynamically distributes available memory resources among the following memory consumers: buffer pools, locking memory, package cache, and sort memory.

The tuner works within the memory limits that are defined by the database_memory configuration parameter. The value of this parameter can be automatically tuned as well. When self-tuning is enabled (when the value of database_memory has been set to AUTOMATIC), the tuner determines the overall memory requirements for the database and increases or decreases the amount of memory allocated for database shared memory, depending on current database requirements. For example, if current database requirements are high and there is sufficient free memory on the system, more memory is allocated for database shared memory. If the database memory requirements decrease, or if the amount of free memory on the system becomes too low, some database shared memory is released. If large pages or pinned memory are enabled, the STMM will not tune the overall database memory configuration, and you need to assign a specific amount of memory to database memory by setting the DATABASE_MEMORY configuration parameter to a specific value. For more information, see database_memory, DB2_LARGE_PAGE_MEM in Performance variables, and Enabling large page support (AIX).

If the database_memory configuration parameter is not set to AUTOMATIC, the database uses the amount of memory that has been specified for this parameter, distributing it across the memory consumers as required. You can specify the amount of memory in one of two ways: by setting database_memory to some numeric value or by setting it to COMPUTED. In the latter case, the total amount of memory is based on the sum of the initial values of the database memory heaps at database startup time.

You can also enable the memory consumers for self tuning as follows:
  • For buffer pools, use the ALTER BUFFERPOOL or the CREATE BUFFERPOOL statement (specifying the AUTOMATIC keyword)
  • For locking memory, use the locklist or the maxlocks database configuration parameter (specifying a value of AUTOMATIC)
  • For the package cache, use the pckcachesz database configuration parameter (specifying a value of AUTOMATIC)
  • For sort memory, use the sheapthres_shr or the sortheap database configuration parameter (specifying a value of AUTOMATIC)

Changes resulting from self-tuning operations are recorded in memory tuning log files that are located in the stmmlog subdirectory. These log files contain summaries of the resource demands from each memory consumer during specific tuning intervals, which are determined by timestamps in the log entries.

If little memory is available, the performance benefits of self tuning will be limited. Because tuning decisions are based on database workload, workloads with rapidly changing memory requirements limit the effectiveness of the self-tuning memory manager (STMM). If the memory characteristics of your workload are constantly changing, the STMM will tune less frequently and under shifting target conditions. In this scenario, the STMM will not achieve absolute convergence, but will try instead to maintain a memory configuration that is tuned to the current workload.

When changes are being made to CF memory configuration by the self-tuning memory manager (STMM), depending on the workload, the transaction times may vary due to CF workers acquiring locks to perform the resize operations.

More details about this can be found here: Db2 pureScale transaction throughput fluctuates when CF self-tuning memory configures CF structure sizes.