DB2 Memory math

DBFinder · #1 (**permalink**) 08-16-10, 13:21

Hi all,

I have not much understanding of memeory used by DB2.

We have 4 production DB2 using 16 GB of physical RAM.

Which parameters and/or settings should I review for application heap size and utility heap sioze.

Actually on one DB we are having memory issues. I remember, few months ago IBM support had advised to free some memory from bufferpools for backup utility which worked like a charm. The senior DBA ( remote support ) had freed some buffrpool allocations.

I am not sure how to caculate these things. Can some one help me to start checking where the memory comes and where it goes.

One of our replication program is being set up and it is complaining of having not enough memory available

Quote:

FUNCTION: DB2 UDB, SQO Memory Management, SqloMemController::requestMemory, probe:50
MESSAGE : ZRC=0x8B0F0000=-1961951232=SQLO_NOMEM "No Memory Available"
DIA8300C A memory heap error has occurred.
DATA #1 : String, 28 bytes
Attempt to get memory failed

DB2 V 9.5.5 on WIN2K3

Regards and Thanks

DBFinder

ARWinner · #2 (**permalink**) 08-16-10, 14:30

How many servers are you using? How many DB2 instances? And how many databases? Is it 32 or 64 bit? I need a clearer picture of your environment.

Andy

DBFinder · #3 (**permalink**) 08-16-10, 14:33

On each server there is only one DB. One Partition and DB2 is 64 bit. One Instance on each server.

2 are POS dbs around 110 GB size at 100 transaction per second.

other 2 are gaming dbs 280 GB size with 240 transactions per second.

Regards

DBFinder

ARWinner · #4 (**permalink**) 08-16-10, 14:37

One thing you can try that should give you some understanding is use db2top. On each server run "db2top -d <database>". Then press "m" to see how your memory is being used.

Andy

DBFinder · #5 (**permalink**) 08-16-10, 14:46

How do we run db2top ?

Quote:

C:\Documents and Settings\db2admin>db2top
'db2top' is not recognized as an internal or external command,
operable program or batch file.

ARWinner · #6 (**permalink**) 08-16-10, 14:56

Apparently db2top cannot be run on Windows. I guess you are stuck with db2pd or use the snapshot routines:

IBM DB2 9.5 Information Center for Linux, UNIX, and Windows

Andy

DBFinder · #7 (**permalink**) 08-16-10, 20:46

But, my original ques was ,

Quote:

Which parameters and/or settings should I review for application heap size and utility heap sioze.

regards

db2girl · #8 (**permalink**) 08-16-10, 22:34

Quote:

Originally Posted by DBFinder

But, my original ques was ,

Which parameters and/or settings should I review for application heap size and utility heap sioze.

application heap size - applheapsz (appl_memory/instance_memory if applheapsz is automatic)

utility heap size - util_heap_sz

I think the memory message you pasted should also contain the heap name.

n_i · #9 (**permalink**) 08-16-10, 22:47

I think your best bet would be to enable DB2 memory self-tuning and let it do the work. In particular, it will increase the utility heap when it's needed and reallocate that memory to other pools when utilities are not running.

DBFinder · #10 (**permalink**) 08-16-10, 22:48

Bella,

Here is complete entry. Can you find for me which memory pool was out of memory.

Code:

2010-08-16-12.03.32.143000-240 I14671F988         LEVEL: Warning
PID     : 1216                 TID  : 8852        PROC : db2syscs.exe
INSTANCE: DB2                  NODE : 000         DB   : GC_PROD
APPHDL  : 0-31494              APPID: 10.90.1.21.41477.100816154811
AUTHID  : DB2ADMIN
EDUID   : 8852                 EDUNAME: db2agent (GC_PROD)
FUNCTION: DB2 UDB, SQO Memory Management, SqloMemController::requestMemory, probe:50
MESSAGE : ZRC=0x8B0F0000=-1961951232=SQLO_NOMEM "No Memory Available"
          DIA8300C A memory heap error has occurred.
DATA #1 : String, 28 bytes
Attempt to get memory failed
DATA #2 : unsigned integer, 8 bytes
4259840
DATA #3 : unsigned integer, 8 bytes
0
DATA #4 : String, 7 bytes
PRIVATE
DATA #5 : unsigned integer, 8 bytes
3166568448
DATA #6 : unsigned integer, 8 bytes
132841472
DATA #7 : unsigned integer, 8 bytes
14749073408
DATA #8 : unsigned integer, 8 bytes
14749331456
DATA #9 : unsigned integer, 8 bytes
135331840

regards

DBFinder · #11 (**permalink**) 08-16-10, 22:51

Quote:

Originally Posted by n_i

I think your best bet would be to enable DB2 memory self-tuning and let it do the work. In particular, it will increase the utility heap when it's needed and reallocate that memory to other pools when utilities are not running.

I cannot do it. Very sensetive database. High activity gaming DB.

Even tried to tune some online backup command, always went wrong. That's why I need to understand more about memory.

regards

db2girl · #12 (**permalink**) 08-16-10, 22:54

Unfortunately, utility heap is not tuned by STMM, but it's a soft limit and can grow into overflow if there is still room

db2girl · #13 (**permalink**) 08-16-10, 23:06

Quote:

Originally Posted by DBFinder

Bella,

Here is complete entry. Can you find for me which memory pool was out of memory.

Code:

2010-08-16-12.03.32.143000-240 I14671F988         LEVEL: Warning
PID     : 1216                 TID  : 8852        PROC : db2syscs.exe
INSTANCE: DB2                  NODE : 000         DB   : GC_PROD
APPHDL  : 0-31494              APPID: 10.90.1.21.41477.100816154811
AUTHID  : DB2ADMIN
EDUID   : 8852                 EDUNAME: db2agent (GC_PROD)
FUNCTION: DB2 UDB, SQO Memory Management, SqloMemController::requestMemory, probe:50
MESSAGE : ZRC=0x8B0F0000=-1961951232=SQLO_NOMEM "No Memory Available"
          DIA8300C A memory heap error has occurred.
DATA #1 : String, 28 bytes
Attempt to get memory failed
DATA #2 : unsigned integer, 8 bytes
4259840
DATA #3 : unsigned integer, 8 bytes
0
DATA #4 : String, 7 bytes
PRIVATE
DATA #5 : unsigned integer, 8 bytes
3166568448
DATA #6 : unsigned integer, 8 bytes
132841472
DATA #7 : unsigned integer, 8 bytes
14749073408
DATA #8 : unsigned integer, 8 bytes
14749331456
DATA #9 : unsigned integer, 8 bytes
135331840

regards

I think what this message is saying is that:

"PRIVATE" heap requested 4259840 bytes. It's currently using 14749073408 and the maximum allowed is 14749331456

14749331456 - 14749073408 = 258048 which is less than what it's trying to allocate.

As far as I know, this "PRIVATE" heap is not something you can configure (except for private sorts). I'll have to look into this further...

I think it's not a bad idea to open a pmr (not sure if it has to go to the replication group or db2, can start with db2)

DBFinder · #14 (**permalink**) 08-16-10, 23:10

Surprisingly 'PRIVATE' is not known via sysibmadm views

Code:

POOL_ID        MEMORY_SIZE         

-------------- --------------------
APPLICATION                65667072
APPL_SHARED                33816576
BP                       9389080576
CAT_CACHE                   4325376
DATABASE                  100597760
LOCK_MGR                  282001408
OTHER                        196608
PACKAGE_CACHE             111083520
UTILITY                      851968
-----------------------------------
 TOTAL                   9987031040

db2girl · #15 (**permalink**) 08-16-10, 23:32

Quote:

Originally Posted by db2girl

I think what this message is saying is that:

"PRIVATE" heap requested 4259840 bytes. It's currently using 14749073408 and the maximum allowed is 14749331456

14749331456 - 14749073408 = 258048 which is less than what it's trying to allocate.

It can't be using this much, I may not be reading the numbers correctly... Perhaps, the large number is your instance_memory setting

Try:
db2pd -dbptnmem

Internet Services

Web Development

Digital Marketing

Consumer Tech