DB2 10.5 for Linux, UNIX, and Windows

database_memory - Database shared memory size configuration parameter

The database_memory configuration parameter specifies the size of the database memory set. The database memory size counts towards any instance memory limit in effect. The setting must be large enough to accommodate the following configurable memory pools: bufferpools, the database heap, the locklist, the utility heap, the package cache, the catalog cache, the shared sort heap, and an additional minimum overflow area of 5%.
Configuration type
Database
Applies to
  • Database server with local and remote clients
  • Database server with local clients
  • Partitioned database server with local and remote clients
Parameter type
  • Configurable Online (requires a database connection)
    Note: Requires a database connection. Dynamic changes are limited for pinned memory and large page configurations.
  • Configurable by member in a DB2® pureScale® environment and in partitioned database environments
Default [range]
AUTOMATIC [AUTOMATIC, COMPUTED, 0 - 4 294 967 295]
Unit of measure
Pages (4 KB)
When allocated or comitted
On Linux and UNIX operating systems
The initial size is allocated on database activation. Additional memory is allocated as required.
On Windows operating systems
Memory is allocated as required.
Memory for bufferpools, the locklist, and basic infrastructure is committed during database activation. Additional memory is committed as required.
When freed
All database memory is freed when a database is deactivated. The Self Tuning Memory Manager (STMM) releases memory back to the operating system as it decreases the database memory size. Additional releasing of memory is subject to the db_mem_thresh configuration parameter.

On UNIX operating systems, after allocating the initial database memory size on database activation, DB2 allocates additional memory as needed to support dynamic requirements. Additional memory allocation is subject to any fixed size limit. All database memory is allocated as shared memory and is retained until the database deactivates. The total allocated shared memory counts only towards virtual memory usage. While this does not require backing by real memory, virtual memory does require backing by swap or paging space on some operating systems. On Windows operating systems, database memory is allocated as private memory as required, subject to any fixed size limit. Allocations no longer in use might be freed dynamically or retained for reuse. All outstanding memory allocations are freed when the database deactivates. For details about operating system support, see the Operating System Support section.

Committed memory is memory that is backed by the operating system. With the exception of pinned memory, which includes large page configurations, allocated memory is committed as required by memory pools. Committed memory no longer required by memory pools is either cached to improve performance or released (decommitted) back to the operating system. The action taken is subject to the db_mem_thresh configuration parameter. Memory is also released or decommitted as necessary when the database_memory size is reduced, such as by STMM. All committed memory is released when the database deactivates.

The database memory size counts towards instance memory usage. The database memory overflow area is equivalent to cached instance memory for the database memory consumer. Database memory overflow can be dynamically reduced as necessary to accommodate the requirements of other memory areas used by the database instance. This is done automatically when an instance memory limit is in effect.

The configured sizes of the underlying memory pools are reserved from the database memory size. The remaining database memory is considered overflow memory. Memory pools are normally allowed to use any available overflow, also known as unreserved memory.

The Self Tuning Memory Manager (STMM) is a feature that you can use to automatically configure key memory areas. You can enable STMM to tune the overall database memory size as well as the following areas within database memory:
  • Bufferpools
  • The locklist
  • The shared sort heap (If SHEAPTHRES is 0)
  • The package cache
The size of the overflow area is dependent on the size of database memory. Large database memory sizes require less overflow and smaller sizes require more overflow, up to a maximum of 10%. The overflow amount is calculated during database activation and on an ongoing basis as part of STMM overflow tuning.
Table 1. Comparison of database memory size and overflow
Database memory size Overflow target
64 GB or less 10%
64 - 96 GB 9%
96 - 156 GB 8%
156 - 266 GB 7%
266 - 493 GB 6%
493 GB or more 5%

The behaviour of values that you can assign to database_memory are as follows.

AUTOMATIC
When database_memory is set to AUTOMATIC, the initial database memory size is calculated based on the underlying configuration requirements. This includes:
  • Bufferpools
  • The database heap
  • The locklist
  • The utility heap
  • The package cache
  • The catalog cache
  • The shared sort heap, if it is enabled
  • The overflow area

The AUTOMATIC setting allows database memory to grow beyond its initial size if there are unforeseen requirements beyond what the overflow provides. Dynamic configuration changes made manually or by STMM to individual memory pools also adjust the database memory size by a corresponding amount.

If STMM is enabled (The value of SELF_TUNING_MEM is ON), STMM controls the overall database memory size. STMM takes into account the underlying configuration requirements, including overflow, and the performance benefits of acquiring additional available memory. Depending on the instance_memory setting, STMM tunes database memory to avoid shortages of system and instance memory. A percentage of memory, that you can control with the DB2_MEM_TUNING_RANGE variable, is left available to satisfy volatile requirements. When a database is activated, if there is insufficient system or instance memory to support the starting configuration, any memory areas tuned by STMM are reduced to accommodate existing memory constraints. This action is subject to enforced minimum sizes.

Fixed Value
You can assign a specific value to the database_memory configuration parameter. However, the specified value should be large enough to support the minimum configuration requirements. This includes:
  • Bufferpools
  • The database heap
  • The locklist
  • The utility heap
  • The package cache
  • The catalog cache
  • The shared sort heap, if it is enabled
  • A 5% minimum overflow area

If the assigned value is too small, the higher minimum size required to support the configuration is allocated. Allocated database memory cannot exceed the fixed setting or higher minimum size. However, the database memory setting can still be increased dynamically or changed to AUTOMATIC. Dynamic configuration increases to memory pools only succeed if sufficient overflow is available.

If STMM is enabled (The value of SELF_TUNING_MEM is ON), only the underlying memory pools and overflow are tuned. At the time of database activation, if a fixed setting exists that is too small to support the current configuration, the individual areas tuned by STMM are reduced to accommodate existing memory constraints. This action is subject to enforced minimum sizes. It is recommended to leave at least half of a fixed database_memory configuration available for STMM tuning. For example, if manually setting the size of the bufferpool areas, avoid having the bufferpools consume more than half of a fixed database_memory setting. Doing so might constrain STMM tuning capabilities, resulting in suboptimal performance and symptoms related to constrained memory resources.

COMPUTED
COMPUTED is a legacy setting. Its behaviour is similar to AUTOMATIC when STMM is not enabled. When STMM is enabled, the behaviour of COMPUTED is similar to that of a fixed value.

Operating system support

Table 2. Operating system support
Operating System Available support
AIX Uses medium (64K) pages by default, which can benefit performance. AIX supports pinned memory and large/huge (16MB/16GB) pages.1
HP-UX Allocated shared memory requires backing by virtual swap. HP-UX supports pinned memory.1
Linux Allocated shared memory counts towards the virtual shared memory limit (shmall). Linux supports pinned memory and large (2MB) pages.1
Solaris Allocated shared memory requires backing by virtual swap and counts towards any virtual memory limits. Solaris supports pinned ISM memory and large pages.2
Windows Supports large (2MB) pages.1
Note:
  1. The use of memory pinning (DB2_PINNED_BP) and large/huge pages (DB2_LARGE_PAGE_MEM) limits the ability to release memory dynamically. STMM performs limited tuning in this type of environment, and does not attempt to adjust the overall database memory size. It is recommended to configure a fixed database_memory value when using STMM under these configurations, which allows STMM to make optimal use of a consistent database memory sizing.
  2. When database_memory is set to AUTOMATIC on the Solaris operating system, the database manager uses pageable memory for database shared memory. This allows database memory configuration adjustments to be fully dynamic, and STMM tunes the overall database memory size. On SPARC architectures, the database manager attempts to use 64 KB memory pages if available. Otherwise 8 KB memory pages are used. On x64 architectures, the database manager uses 4KB memory pages. When using the fixed or COMPUTED options for database memory, ISM (pinned) memory is allocated. In this case Solaris chooses the largest appropriate page size, which might improve performance. However, dynamic database memory configuration changes is limited, and STMM does not attempt to tune the overall database memory size. Changes between AUTOMATIC and the other database_memory options cannot be performed dynamically on Solaris operating systems.

Monitoring

The database memory set can be monitored through the MON_GET_MEMORY_SET and MON_GET_MEMORY_POOL routines. For example, the following command:
db2 "select member, substr(db_name,1,10)as db_name, substr(memory_set_type,1,10) as set_type, 
memory_set_size, memory_set_committed, memory_set_used, memory_set_used_hwm 
from table(mon_get_memory_set('DATABASE','',-1))" 
Returns the following information:
MEMBER DB_NAME    SET_TYPE   MEMORY_SET_SIZE      MEMORY_SET_COMMITTED MEMORY_SET_USED      MEMORY_SET_USED_HWM 
------ ---------- ---------- -------------------- -------------------- -------------------- -------------------- 
     0 SAMPLE     DATABASE                 154927                68616                67829                68616 
     0 TEST       DATABASE                 238092               123404               123404               123404 

  2 record(s) selected. 

In this case, the database memory set is using 154927KB of instance_memory (MEMORY_SET_SIZE) and 68616KB of system memory (MEMORY_SET_COMMITTED), of which 67829KB (MEMORY_SET_USED) is assigned to memory pools.

You can also monitor database memory using the db2pd utility:
db2pd -db  <database_name> -memsets -mempools, db2pd -dbptnmem