DB2 Version 10.1 for Linux, UNIX, and Windows
DB2 Version 10.1 for Linux, UNIX, and Windows
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 specified fixed size limit. All database memory is allocated as shared memory and is retained until the database deactivates. On some Linux and UNIX operating systems, the level of allocated shared memory only counts towards virtual memory usage and does not required backing by real memory. On Windows operating systems, database memory is allocated as private memory as required, subject to any specified 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 needed. 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 the Self Tuning Memory Manager (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 behaviour of values that you can assign to database_memory are as follows.
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. 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.
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.
| 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:
|
|
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))" 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