In Version 9.5 Fix Pack 6/6a and 7, and Version 9.7 Fix Pack
3/3a, 2MB of private memory is leaked on every LOAD operation if
history file filtering (of load) is enabled.
History file filtering of load is enabled via the following
undocumented registry setting:
DB2_HISTORY_FILTER=L
Note that the filter does not apply to Loads with COPY YES,
memory will not be leaked when that option is used.
This memory leak can result in excessive memory usage on systems
with a high frequency of LOAD operations. Symptoms can include
performance problems from paging and other related symptoms.
The memory leak is not necessarily permanent. The memory is
associated with the private memory of an agent, which may
terminate at some point - and free the leaked private memory.
This freeing/termination depends on agent pooling configuration
and activity.
The main workaround is to turn off history file filtering for
load:
db2set DB2_HISTORY_FILTER=
(this setting is dynamic and does not require recycling the
instance).
A second workaround may be to reduce the size of the agent pool,
thereby causing agents to terminate more frequently and free up
associated private memory areas. The agent pool can also be
flushed at specific times by setting NUM_POOLAGENTS to a low
number such as 1 or 0 for some period (until memory usage is
reduced by agents being reused and terminating). Note that "0"
disables time-based buffering, so this setting could incur a
greater performance impact. If using this approach, ensure that
the CLP session is attached to the instance first, otherwise any
change will not be dynamic. For example:
db2 attach to <instance>
db2 update dbm cfg using num_poolagents 0
<wait a short interval, monitoring memory usage>
db2 update dbm cfg using num_poolagents <former value,>
db2 detach
For AUTOMATIC settings, ensure both the underlying value and
AUTOMATIC setting are returned to the former values.
eg. if the prior setting was AUTOMATIC (50), return to this
setting with the following command :
db2 update dbm cfg using NUM_POOLAGENTS 50 AUTOMATIC
It is not possible to monitor for this leak specifically,
however, if history file filtering of load is being used, the
"Other" memory area for an agent will grow. Private memory
usage for the db2sysc/db2syscs.exe process will also grow, which
can be observed with Operating System monitoring tools.
Monitoring of "Other" memory can be performed with the
snapagent_memory_pool view:
db2 "select AGENT_PID, POOL_ID, POOL_CUR_SIZE from
sysibmadm.snapagent_memory_pool where POOL_ID = 'OTHER'"
AGENT_PID POOL_ID POOL_CUR_SIZE
-------------------- -------------- --------------------
23762 OTHER 43712512
2829 OTHER 196608
...
Or with "db2 get snapshot for applications on <db>"
Agent process/thread ID = 23762
Database partition number = 0
Agent Lock timeout (seconds) = -1
Memory usage for agent:
Memory Pool Type = Other Memory
Current size (bytes) = 43712512
High water mark (bytes) = 43712512
Configured size (bytes) = 67108859904
This Agent/EDU will have previously been used as Load subagent
(db2lload) and will have had db2diag.log entries similar to:
2010-12-23-21.32.38.871842-360 XXX LEVEL: Warning
PID : 660710 TID : 23762 PROC : db2sysc
0
INSTANCE: db2inst1 NODE : 000 DB : SAMPLE
APPHDL : 136-48566 APPID: XXX
AUTHID : DBADMIN
EDUID : 23762 EDUNAME: db2lload 0
FUNCTION: DB2 UDB, database utilities, DIAG_NOTE, probe:0
DATA #1 : String, 78 bytes
LOADID: 76125.2010-12-23-21.32.36.151654.136 (6098;4)
Load SA is running. 0, 0 |
