IBM Support

Understanding and Tuning Application Group Memory

Question & Answer


Question

Understanding and Tuning Application Group Memory

Answer


APPLICATION CONTROL MEMORY OVERVIEW:
===========================================

Application Group - A set of server-side resources (eg. db2agent processes, shared memory) servicing a collection of application sessions or connections. The primary resource dedicated to a single application group is a large memory area called the Application Control Shared Memory Set. The maximum number of applications or connections in an Application Group is (APPGROUP_MEM_SZ / APP_CTL_HEAP_SZ). The connections are assigned to the most recent Application Group created, which has the capacity to service it.

Application Group Shared Memory - A large shared memory area containing one Application Control Heap per connection and a large memory pool shared by all connections called the Application Group Shared Heap. The size of the Application Group Shared Memory is based on the APPGROUP_MEM_SZ database configuration parameter. The actual size of the allocated shared memory segment on UNIX, as viewed by the ipcs -am command, will be slightly larger than that shown by the APPGROUP_MEM_SZ parameter due to the addition of a small amount of overhead. Application Group Shared Memory is freed when the last connection serviced within the Application Group terminates. This memory area can also be referred to as the Application Control Memory Set or Application Global Memory. Application Group Shared Memory replaces the smaller Application Control shared memory segments which existed on version 7 but were allocated on a per application basis.

Application Control Heap - The Application Control Heap is allocated from Application Group Shared Memory and contains information about a specific connection. Memory for table queues is also allocated from the Application Control Heap. The approximate maximum for the Application Control Heap is ( ( 100 - GROUPHEAP_RATIO ) / 100 * APP_CTL_HEAP_SZ ). The Application Control Heap is not freed when a connection terminates, but is reused by a subsequent connection.

Application Group Shared Heap - The Application Group Shared Heap is a large memory area allocated from Application Group Shared Memory. Much of this memory is managed as a cache of SQL sections which can be reused by any applications in the group and is known as the Shared Workspace. The Shared Workspace used by an application group can be monitored via application snapshots. The maximum size of the Application Group Shared Heap is ( GROUPHEAP_RATIO ) * APPGROUP_MEM_SZ. The Application Group Shared Heap is freed when the Application Control Shared Memory is freed and which is when the last connection in an Application Group terminates.

Application Groups, Application Group Shared Heap, and Application Group Shared Memory exist only in environments where shared memory is required by DB2 to do the work for an application which is basically when multiple agents may be involved in servicing an application. These environments include Concentrator, DPF (multi-partition databases), and SMP-enabled (INTRA_PARALLEL enabled). While Application Groups do not exist in a single partition, non-concentrator, non-SMP-enabled environment, the Application Control Heap does exist as a private memory area, and APP_CTL_HEAP_SZ is the maximum size in this case.


There are 3 parameters which control Application Group Shared Memory configuration:

APPGROUP_MEM_SZ - the size of Application Group Shared Memory.

GROUPHEAP_RATIO - the percentage of Application Group Shared Memory that can be used by the Application Group Shared Heap

APP_CTL_HEAP_SZ - a value used in calculating how many applications can be serviced within an Application Group. It is also used to derive the maximum size of the Application Control Heap


One way of picturing Application Group Shared memory is the following:

APPLICATION CONTROL SHARED MEMORY SEGMENT
( 40000 4K pages by default on multi-partitioned databases )
/-----------------------------------------------------------/
/ /
/ Application Group Shared Heap /
/ (shared by all applications in this /
/ Application Group) /
/ maximum is GROUPHEAP_RATIO * APPGROUP_MEM_SZ /
/ by default, 112M on EEE systems /
/ /
/-----------------------------------------------------------/
/ Application Control Heap (1 per app) /
/-----------------------------------------------------------/
/ Application Control Heap /
/-----------------------------------------------------------/
/ Application Control Heap /
/-----------------------------------------------------------/
/ ... /
/-----------------------------------------------------------/

TUNING METHODS:
================================

Application Control Heap Out of Memory Errors (SQL0973):

While the default configuration should suffice in most environments, the common exception is the sizing of the Application Control Heap.
The Application Control Heap needs to be big enough for DB2. Even though the amount of Application Control Heap used by DB2 is always fixed and is not affected by the size of Application Control Heap, the execution of DB2 will fail if Application Control Heap is not big enough.

While much of the memory allocated from the Application Control Heap on Version 7 has been moved to the new Application Group Shared Heap on Version 8, the default memory may still be insufficient in some situations - for example in the case of complex SQL statements which require a large number of tables queues. In this case, the Application Control Heap can be increased, for example in 100 page increments.

Increasing the APP_CTL_HEAP_SZ setting alone, while increasing the Application Control Heap limit, also reduces the number of applications in each Application Group. While it is the simplest approach , it results in much greater Application Group Memory requirements as a whole, not just for the application which requires the larger Application Control Heap. Below is a more precise method which increases the Application Control Heap limit while minimizing any increase in overall memory requirements. We do this by keeping the number of applications in each Application Group and the Application Group Shared Heap limit constant .

We refer to the Application Control Heap as ACTLHeap , the number of Applications per Application Group as AppsInGroup, and the Application Group Shared Heap as AGSHeap.
The desired change in the Application Control Heap is ACTLHeap(delta) .

preliminary calculations:
ACTLHeap(old) = ( 100 - GROUPHEAP_RATIO ) / 100 * APP_CTL_HEAP_SZ
AppsInGroup = APPGROUP_MEM_SZ / APP_CTL_HEAP_SZ
AGSHeap = ( GROUPHEAP_RATIO / 100 ) * APPGROUP_MEM_SZ

the element we want to change :
ACTLHeap(new) = ACTLHeap(old) + ACTLHeap(delta)

now to set the parameters :
APPGROUP_MEM_SZ(new) = AGSHeap + ( AppsInGroup * ACTLHeap(new) )
APP_CTL_HEAP_SZ(new) = APPGROUP_MEM_SZ(new) / AppsInGroup
GROUPHEAP_RATIO(new) = ( AGSHeap / APPGROUP_MEM_SZ(new)) * 100

Example:
Let's assume we have the default configuration for a Multi-Partitioned Database and wish to increase the Application Control Heap limit by 100 4K pages .
The default configuration is:
APPGROUP_MEM_SZ = 40000
APP_CTL_HEAP_SZ = 512
GROUPHEAP_RATIO = 70

We will use 4K pages as the units in the calculations. ACTLHeap(delta) = 100.
ACTLHeap(old) = ( 100 - 70 ) / 100 * 512 = 154 ( round up )
AppsInGroup = 40000 / 512 = 78 ( round down )
AGSHeap = ( 70 / 100 ) * 40000 = 28000

determine the new Application Control Heap maximum:
ACTLHeap(new) = 154 + 100 = 254

now to set the parameters:
APPGROUP_MEM_SZ(new) = 28000 + ( 78 * 254 ) = 47812
APP_CTL_HEAP_SZ(new) = 47812 / 78 = 613 ( round up )
GROUPHEAP_RATIO(new) = ( 28000 / 47812 ) * 100 = 58 ( round down )

NOTE: if this is for a 32-bit system, and the resulting APPGROUP_MEM_SZ value is greater than 64000 pages, this will need to be reduced. See "Adjusting the size of Application Group Memory" below.

Tuning the number of applications per Application Group:

This is an important consideration when migrating to version 8 from a previous version of DB2. In some cases, the APP_CTL_HEAP_SZ value inherited from version 7 may result in a signficant increase in memory consumption on version 8. This occurs when the value for APP_CTL_HEAP_SZ is relatively large, and results in a small number of applications per application group.

Again, the number of applications per group is APPGROUP_MEM_SZ / APP_CTL_HEAP_SZ. If we have fewer applications per group, we need more Application Groups, and consequently more Application Group Shared Memory.

In this scenario, the safest approach to tuning down Application Group memory usage on version 8 is to preserve the Application Control Heap maximum while increasing the number of applications per Application Group, i.e. decreasing the number of Application Groups which will be required for a given number of applications.

Below are the calculations for modifying the Application Group Memory parameters. We need to know the current maximum of the Application Control Heap (ACTLHeap) which we want to keep constant. We assume that we have a target for the number of applications per Application Group (AppsInGroup) as well as the limit for the Application Group Shared Heap (AGSHeap).
ACTLHeap = ( 100 - GROUPHEAP_RATIO ) / 100 * APP_CTL_HEAP_SZ
AGSHeap = ( known target value )
AppsInGroup(new) = ( known target value )

now to set the parameters:
APPGROUP_MEM_SZ(new) = AGSHeap(new) + ( AppsInGroup(new) * ACTLHeap )
APP_CTL_HEAP_SZ(new) = APPGROUP_MEM_SZ(new) / AppsInGroup(new)
GROUPHEAP_RATIO(new) = ( AGSHeap(new) / APPGROUP_MEM_SZ(new)) * 100

Example:
We assume the default GROUPHEAP_RATIO and APPGROUP_MEM_SZ values for a multiple-partitioned database, and an inherited APP_CTL_HEAP_SZ maximum of 4096. This gives us the following settings:
APPGROUP_MEM_SZ 40000
APP_CTL_HEAP_SZ 4096
GROUPHEAP_RATIO 70

There are 40000/4096 = 9 applications per Application Group, and the Application Group Shared Heap maximum is 70 / 100 * 40000 = 28000.

The default number of applications per Application Group (AppsInGroup) for a multiple-partitioned database is 40000 / 512 , or 78 , and the default Application Group Shared Heap is ( 70 / 100 ) * 40000 = 28000 i.e. ( ( GROUPHEAP_RATIO / 100 ) * APPGROUP_MEM_SZ ), so we assume this is the target.
ACTLHeap = ( 100 - 70 ) / 100 * 4096 = 1229
AGSHeap(new) = 28000
AppsInGroup(new) = 78

now to set the parameters :
APPGROUP_MEM_SZ(new) = 28000 + ( 78 * 1229 ) = 123862
APP_CTL_HEAP_SZ(new) = 123862 / 78 = 1588 (round up)
GROUPHEAP_RATIO(new) = ( 28000 / 123862 ) * 100 = 21 (round down)

To summarize:
OLD NEW
APPGROUP_MEM_SZ 40000 128000
GROUPHEAP_RATIO 70 22
APP_CTL_HEAP_SZ 4096 1600

The memory savings here are significant. Given 78 applications, the OLD settings result in 78 / 9 = 9 Application Groups - for a total of 9 * 40000 * 4096 = ~1.4GB of memory. With the NEW settings, only one Application Group is required, or 128000 * 4096 = ~512MB of memory.

NOTE: if this is for a 32-bit system, and the resulting APPGROUP_MEM_SZ value is greater than 64000 pages, this will need to be reduced. See "Adjusting the size of Application Group Memory" below.


Tuning the Application Group Shared Heap:

The Application Group Shared Heap is primarily a performance-related tuning parameter. While this heap contains a variey of DB2 "runtime" requirements, the majority of memory is taken up by the "Shared Workspace", which is managed as a cache. The Shared Workspace contains SQL sections and memory allocations required to support the execution of those sections. Being a cache, once a section is no longer in use by an application, it is kept around for reuse by another application or until the space is needed for other requirements, eg. the memory must be freed to make room for a different SQL section. If the Application Group Shared Heap is too large, we may waste memory by caching rarely-used sections for a long period of time. If too small, DB2 may end up having to consume additional CPU resources copying sections into the Shared Workspace, which requires freeing memory in order to be able to insert the sections as well as section initialization. This activity can be monitored via Application Snapshots. Hit ratios, i.e. the rate at which sections are referenced vs. inserted, can be determined similar to monitoring other cache hit ratios, eg. the Package Cache.

In most cases, the default Application Group Shared Heap maximum is sufficient. If it is desired to fine-tune the Application Group Shared Heap sizing while keeping the other key characteristics of Application Group Memory constant, the following method can be used.

In this case, we assume known values for the Application Control Heap maximum and the number of applications per Application Group.
The desired change in the size of the Application Group Shared Heap is AGSHeap(delta).

some preliminary calculations:
AGSHeap(old) = ( GROUPHEAP_RATIO / 100 ) * APPGROUP_MEM_SZ
AGSHeap(new) = AGSHeap(old) + AGSHeap(delta)
AppsInGroup = APPGROUP_MEM_SZ / APP_CTL_HEAP_SZ
ACTLHeap = ( 100 - GROUPHEAP_RATIO ) / 100 * APP_CTL_HEAP_SZ

now to set the parameters:
APPGROUP_MEM_SZ(new) = AGSHeap(new) + ( AppsInGroup * ACTLHeap )
APP_CTL_HEAP_SZ(new) = APPGROUP_MEM_SZ(new) / AppsInGroup
GROUPHEAP_RATIO(new) = ( AGSHeap(new) / APPGROUP_MEM_SZ(new)) * 100

Example:
Let's assume we have the default configuration for a Multi-Partitioned Database and wish to increase the Application Group Shared Heap by 10000 pages
The default configuration is:
APPGROUP_MEM_SZ = 40000
APP_CTL_HEAP_SZ = 512
GROUPHEAP_RATIO = 70

Here are the calculations with an AGSHeap(delta) of 10000 pages
AGSHeap(old) = ( 70 / 100 ) * 40000 = 28000
AGSHeap(new) = 28000 + 10000 + 38000
AppsInGroup = 40000 / 512 = 78 (round down)
ACTLHeap = ( 100 - 70 ) / 100 * 512 = 154

now to set the parameters:
APPGROUP_MEM_SZ(new) = 38000 + ( 78 * 154 ) = 50012
APP_CTL_HEAP_SZ(new) = 50012 / 78 = 641 (round down)
GROUPHEAP_RATIO(new) = ( 38000 / 50012) * 100 = 75 (round down)

NOTE: if this is for a 32-bit system, and the resulting APPGROUP_MEM_SZ value is greater than 64000 pages, this will need to be reduced. See "Adjusting the size of Application Group Memory" below.

Adjusting the size of Application Group Shared Memory:

This may be required when tuning for 32-bit DB2, where the size of Application Group Memory must not exceed 256MB.
While by default the Application Group Memory will not exceed 160MB, it may be necessary to shrink the size of this memory after performing one of the above tuning methods.
Note that there may still be some benefit in terms of efficiency of memory usage to increase Application Group Shared Memory above the default , i.e. it is still useful to follow the methods above, and this also provides information on tuning possibilities upon migration to 64-bit DB2.
If the 256MB limit is exceeded , an SQL1042 will be returned on connect with messages in the db2diag.log including "Failed to allocate App Ctl Shr Mem Set ..."

In most cases it is reasonable to shrink the Application Group Shared Heap at the same ratio as the Application Group Memory, and this simplifies the tuning.
This method leaves the maximum Application Control Heap intact and reduces the number of applications per group in tandem with the decrease in the size of the Application Group Shared Heap.
APP_CTL_HEAP_SZ and GROUPHEAP_RATIO
will remain unchanged.

In this case, we assume that the Application Group Memory size ( APPGROUP_MEM_SZ ) is larger than the maximum, which we will specify as 64000 pages.
(this is not quite 256MB , but the actual allocated size will be larger than 64000 due to overhead - we need to leave some room ).

The following is based on the recommendations in the example taken from "Tuning the number of applications per Application Group":

APPGROUP_MEM_SZ 128000
GROUPHEAP_RATIO 22
APP_CTL_HEAP_SZ 1600

From the example, note that the Application Group Shared Heap is 28000, The Application Control Heap maximum is 1229, and the number of applications per group is 78.
After decreasing APPGROUP_MEM_SZ to 64000, we have:

APPGROUP_MEM_SZ 64000
GROUPHEAP_RATIO 22
APP_CTL_HEAP_SZ 1600

The effect of this is to reduce the Application Group Shared Heap to 14000 and the number of applications per group to 39.

Notes:
1. If using DB2's Memory Tracker utility to monitor the Application Control Heap or Application Group Shared Heap memory usage, it should be noted that currently ( version 8.1 ), 50% of the memory in the Application Group Shared Heap and the first Application Control Heap allocated in a group is used/committed.
2. DB2's Memory Tracker reports memory usage in terms of the actual memory used. The configured maximums are limits that do not include overhead due to DB2's memory management structures or fragmentation. Consequently , it is possible for the memory tracker to report used memory which is higher than the configured maximum.
3. Some padding is added to heaps as a base allocation requirement. In the case of the Application Control Heap, this value is 40 pages. This value does not impact the above calculations.
4. Default values for APP_CTL_HEAP_SZ and APPGROUP_MEM_SZ differ across environments and platforms. Please consult DB2 documentation.

[{"Product":{"code":"SSEPGG","label":"Db2 for Linux, UNIX and Windows"},"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Component":"Operating System \/ Hardware - Memory Management","Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"9.1;8","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
16 June 2018

UID

swg21179841