DB2 10.5 for Linux, UNIX, and Windows
DB2 10.5 for Linux, UNIX, and Windows
Although you can enable self-tuning memory and use the default AUTOMATIC setting for most memory-related configuration parameters, it might be useful to know the limitations of the different memory parameters and the interactions between them, in order to have more control over their settings and to understand why out-of-memory errors are still possible under certain conditions.
Before the AUTOMATIC setting was available, various operating system and DB2 tools were available that allowed you to see the amount of space used by different types memory, such as shared memory, private memory, buffer pool memory, locklists, sort memory (heaps), and so forth, but it was almost impossible to see the total amount of memory used by the DB2 database manager. If one of the heaps reached the memory limit, a statement in an application would fail with an out-of-memory error message. If you increased the memory for that heap and reran the application, you might then have received an out-of-memory error on another statement for another heap. Now, you can remove hard upper limits on individual functional memory heaps by using the default AUTOMATIC configuration parameter setting.
If required (for instance, to avoid scenarios where a poorly behaving database application requires extremely large amounts of memory), you can apply a limit on overall application memory at the database level by using the appl_memory configuration parameter. You can also apply a limit for an individual heap by changing the appropriate database configuration parameter for that heap from the AUTOMATIC setting to a fixed value. If all of the configuration parameters for all of the functional memory heaps are set to AUTOMATIC and an instance_memory limit is enforced, the only limit on application memory consumption is the instance_memory limit . If you also set instance_memory to AUTOMATIC and you are using a DB2 database product with memory usage restrictions, the DB2 database manager automatically determines an upper limit on memory consumption.
You can easily see the total amount of instance memory consumed and the current instance_memory consumption by using the db2pd -dbptnmemcommand or the ADMIN_GET_MEM_USAGE table function.
When self-tuning memory manager (STMM) is active and self-tuning of database memory is enabled (database_memory is set to AUTOMATIC), STMM checks the free memory available on the system and automatically determines how much memory to dedicate to performance heaps for optimal performance. All the performance heaps contribute to the overall database_memory size. In addition to the performance memory requirements, some memory is required to ensure the operation and integrity of the DB2 database manager. The difference between the space used by instance_memory and the space required by these two memory consumers is available for application memory (appl_memory) use. Functional memory for application programs is then allocated as needed. If an instance_memory limit is not enforced, there are no additional restrictions on how much memory a single application can allocate.
Depending on the configuration, STMM also periodically queries how much free system memory is remaining and how much free instance_memory space is remaining if there is an instance_memory limit. To prevent application failures, STMM prioritizes application requirements ahead of performance criteria. If required, it degrades performance by decreasing the amount of space available for performance heaps, thus providing enough free system memory and free instance_memory space to meet application memory requests. As applications are completed, the used memory is freed, ready to be reused by other applications or to be reclaimed for database_memory use by STMM. If performance of the database system becomes unacceptable during periods of heavy application activity, it might be useful either to apply controls on how many applications the database manager is allowed to run (by using either the connection concentrator or the new Workload Management feature of DB2 Version 9.5) or to add memory resources to the system.