database_memory - Database shared memory size configuration parameter
- 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
- Configurable Online (requires a database connection)
- Default [range]
- AUTOMATIC [AUTOMATIC, COMPUTED, 0 - 4 294 967 295]
- Unit of measure
- Pages (4 KB)
- When allocated or committed
- 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.
- 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.
- Bufferpools
- The locklist
- The shared sort heap (If
SHEAPTHRES
is 0) - The package cache
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 behavior 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 behavior is similar to AUTOMATIC when STMM is not enabled. When STMM is enabled, the behavior of COMPUTED is similar to that of a fixed value.
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 |
Linux | Allocated shared memory counts towards the virtual shared memory limit (shmall). Linux supports pinned memory and large (2MB) pages.1 |
Windows | Supports large (2MB) pages.1 |
Note:
|
Monitoring
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.
db2pd -db <database_name> -memsets -mempools, db2pd -dbptnmem