DB2 10.5 for Linux, UNIX, and Windows

CF self-tuning memory parameter configuration

In a DB2® pureScale® instance, several configuration parameters and registry variables work together to control memory allocation for the cluster caching facility, also known as the CF.

In DB2 Version 10.5 Fix Pack 5 and later fix packs, when explicitly enabled, cluster caching facility (CF) self-tuning memory optimizes memory distribution and avoids out of memory conditions. Unless explicitly enabled, CF memory parameter configuration functions as before.

In a DB2 pureScale instance, CF self-tuning memory automatically and continuously monitors the usage of memory and optimizes the distribution of memory. CF self-tuning memory monitors database memory parameter cf_db_mem_sz, and these CF memory consumer parameters:

Group buffer pool (cf_gbp_sz),
Lock manager (cf_lock_sz), and
Shared communication area (cf_sca_sz).

CF memory is dynamically distributed between these CF memory consumer parameters. In addition, in multiple database environments, CF memory can be dynamically distributed between databases based on their need for memory.

CF self-tuning memory is set at the instance level, and is explicitly enabled (that is, turned on) by setting registry variable DB2_DATABASE_CF_MEMORY to AUTO.

db2set DB2_DATABASE_CF_MEMORY=AUTO

Setting DB2_DATABASE_CF_MEMORY to AUTO turns on CF self-tuning memory for all databases that have the CF memory consumer parameters (cf_gbp_sz, cf_lock_sz, cf_sca_sz) set to AUTOMATIC. However, for databases that have the CF memory consumer parameters set to fixed values, CF self-tuning memory remains off.

Note:

In DB2 Version 10.5 Fix Pack 5 and later fix packs, if you are applying an online fix pack, you cannot set registry variable DB2_DATABASE_CF_MEMORY until after the instance is committed to the new fix pack level.
High availability disaster recovery (HADR) in a DB2 pureScale environment can use CF self-tuning memory. However, CF memory tuning occurs on the primary site only. If the registry variable is set on the standby site, the registry variable takes effect when the standby site becomes the primary site.

CF self-tuning memory can be used at different levels:

To dynamically adjust the amount of CF memory a database is assigned, set cf_db_mem_sz to AUTOMATIC.
In this case, depending on workload, CF memory is available for use between all databases that also have cf_db_mem_sz set to AUTOMATIC, and database memory within one database.

Database memory is also dynamically distributed between the CF memory consumer parameters (cf_gbp_sz, cf_lock_sz, cf_sca_sz).
To dynamically distribute the CF memory for a database among the memory consumer parameters, set cf_db_mem_sz to a fix value.
In this case, memory is not dynamically distributed between databases, but memory is dynamically distributed between the CF memory consumer parameters (cf_gbp_sz, cf_lock_sz, cf_sca_sz).

If you have more than one database, while it is optimal for all databases to use CF self-tuning memory, it is possible to have only a subset of the databases in the instance use CF self-tuning memory. That is, one or more databases has cf_db_mem_sz set to a fixed value. When using a fixed value, the databases do not need to have the same fixed value. Also, in this case, to avoid the possibility of insufficient free CF memory, the order of database activation is important. Activate the databases with a fixed value first.

Before DB2 Version 10.5 Fix Pack 5, the value of database memory parameter cf_db_mem_sz was static. In DB2 Version 10.5 Fix Pack 5 and later fix packs, when cf_db_mem_sz is set to AUTOMATIC, CF self-tuning memory limits the maximum CF database memory that is based on database manager configuration parameter numdb. Set numdb as close as possible to the expected number of active databases. For example, in the case of a single database environment, the value of numdb determines how much CF memory is used by the single database. If numdb is set to 1, all CF memory is used by the single database. However, if numdb is set to a value greater than 1, a small amount of memory remains unusable for that single database.

In a multiple database environment, CF memory is configured automatically based on workload and available memory. When a new database is activated, databases that are already active automatically give up CF memory for the newly activated database until a workload-based distribution of CF memory is reached.

CF self-tuning memory calculates an initial starting size for a database on the first database activation. If this is a newly created database, an initial starting size is calculated for the database. For a new database, the starting size is based on:

The maximum number of concurrently active databases that are specified by the numdb configuration parameter, and
CF memory configuration parameter cf_mem_sz.

If this is an existing database, the starting size is based on the last configured values.

Changes resulting from the CF self-tuning operations are recorded in memory tuning log files that are in the db2dump/stmmlog directory. The log provides useful diagnostic information, including CF memory consumer parameter sizes.

When cf_db_mem_sz is set to AUTOMATIC, CF self-tuning memory ensures that the database uses a reasonable portion of the CF memory and attempts to activate the database with the parameter values that the CF memory consumer parameters were set to. CF self-tuning memory monitors the memory consumer parameters, determines the new values, and dynamically resizes the affected configuration parameters. Manual intervention is not required. These small but frequent changes are required to satisfy changing CF memory needs. If you have more than one database, in the case of CF memory contention, CF self-tuning memory makes changes towards a workload-based distribution for the active databases.

When cf_db_mem_sz is set to a fixed value, you specify the amount of memory that a database can take from CF memory. CF self-tuning memory works within the CF memory that is limited by this database memory parameter.

To see the actual values of the CF memory configuration parameters, run the GET DB CFG SHOW DETAIL command. For example:

       Database Configuration for Database

 Description                                   Parameter   Current Value              Delayed Value
 ------------------------------------------------------------------------------------------------------
...
 CF Resource Configuration:

 CF self-tuning memory                                   = ON 
 CF database memory size (4KB)            (CF_DB_MEM_SZ) = AUTOMATIC(8383744)         AUTOMATIC(8383744)
 Group buffer pool size (4KB)                (CF_GBP_SZ) = AUTOMATIC(6589696)         AUTOMATIC(6589696)
 Global lock memory size (4KB)              (CF_LOCK_SZ) = AUTOMATIC(1257728)         AUTOMATIC(1257728)
 Shared communication area size (4KB)        (CF_SCA_SZ) = AUTOMATIC(135245)          AUTOMATIC(135245)
 Smart array size (4KB)                     (CF_LIST_SZ) = AUTOMATIC(315571)          AUTOMATIC(315571)

Parameter combinations are summarized in this table:

Table 1. Parameter combinations
CF memory consumer parameters (cf_gbp_sz, cf_lock_sz, cf_sca_sz)	CF database memory parameter cf_db_mem_sz	CF self-tuning memory (on or off
AUTOMATIC	AUTOMATIC	Set to or remains ON for the database. cf_db_mem_sz and the three memory consumer parameters are tuned.
AUTOMATIC	Fixed value	Set to or remains ON for the database. cf_db_mem_sz is not tuned. The three memory consumer parameters are tuned.
Fixed value	Fixed value	Turned off for the database. There is no CF self-tuning memory for this database.

When explicitly disabled by changing the registry variable DB2_DATABASE_CF_MEMORY from AUTO to a numeric value, CF memory tuning is turned off for all databases that are activated. However, the CF memory usage by the database and the CF memory consumer parameters is not adjusted immediately for these databases. The current CF memory sizes remain at the current level. A database manager restart is required.

CF self-tuning memory in a multiple database environment with cf_db_mem_sz set to AUTOMATIC

If you are running in an environment with one database with database manager configuration parameter numdb set to 2 (or higher), when CF self-tuning memory is turned on, that database can use almost all CF memory. (Some memory is reserved for additional database activation.) Later when another database is added, after that database is started, the already active database automatically gives up CF memory for the newly activated database until needed, or until a workload-based distribution of CF memory is reached.

For example:

DB2_DATABASE_CF_MEMORY is set to AUTO
numdb is set to 3, indicating a maximum number of three active databases in the instance
cf_db_mem_sz is set to AUTOMATIC
CF memory consumer parameters (cf_gbp_sz, cf_lock_sz, or cf_sca_sz) are set to AUTOMATIC

When the first database is activated, since only one database is activated, that database can use almost all CF memory. Later, when another database is activated, the two databases dynamically distribute the CF memory. (Again, some memory is reserved for additional database activation.) When another database is activated, all of the CF memory can be used by the databases since memory is no longer reserved for additional database activation because the numdb value (3) is already met.

In addition, in this case, the databases can dynamically distribute memory that is based on their need for CF memory. This results in moving memory from the database with lower need to the database with higher need. When all databases have equal demand for CF memory, each database has its one third share of CF instance memory.

CF self-tuning memory in a multiple database environment with cf_db_mem_sz set to a fixed value, and memory consumer parameters set to AUTOMATIC

If you are running in an environment with two databases, when CF self-tuning memory is turned on, each database consumes the percentage of CF memory as specified by the fixed values.

For example:

DB2_DATABASE_CF_MEMORY is set to AUTO
numdb is set to 2, indicating a maximum number of two active databases in the instance
cf_db_mem_sz is set to a fixed value:
- The first database has cf_db_mem_sz set to a fixed value that is 70% of cf_mem_sz
- The second database has cf_db_mem_sz set to a fixed value that is 30% of cf_mem_sz
CF memory consumer parameters (cf_gbp_sz, cf_lock_sz, or cf_sca_sz) are set to AUTOMATIC

When the first database is activated, it consumes 70% of CF memory. Later, when the second database is activated, it consumes 30% of CF memory. The two databases do not trade memory with each other. However, for each database, its CF memory is dynamically distributed between the CF memory consumer parameters that are associated with the database (cf_gbp_sz, cf_lock_sz, or cf_sca_sz).