db2 udb on windows - db2mtrk

[dunleav1]

I have a db2 9 FP3a installation with one instance and one database
installed.
I have 4G of physical RAM and I have enabled the /3G switch in
boot.ini.
I am using STTM.
If I run db2mtrk the database never consumes more than 1.2G - why?

Here's the db2mtrk output:
Tracking Memory on: 2007/11/19 at 15:13:43

Memory for instance

Database Monitor Heap is of size 1245184 bytes
Other Memory is of size 10223616 bytes
Total: 11468800 bytes

Memory for database: B001

Backup/Restore/Util Heap is of size 65536 bytes
Package Cache is of size 15794176 bytes
Catalog Cache Heap is of size 1114112 bytes
Buffer Pool Heap (1) is of size 826081280 bytes
Buffer Pool Heap (System 32k buffer pool) is of size 720896 bytes
Buffer Pool Heap (System 16k buffer pool) is of size 458752 bytes
Buffer Pool Heap (System 8k buffer pool) is of size 327680 bytes
Buffer Pool Heap (System 4k buffer pool) is of size 262144 bytes
Shared Sort Heap is of size 589824 bytes
Lock Manager Heap is of size 407371776 bytes
Database Heap is of size 5636096 bytes
Other Memory is of size 131072 bytes
Total: 1258553344 bytes

[ajstorm]

Do you mean that you are running STMM? (you wrote STTM below)

If in fact you are running STMM, there could be several reasons why
your database is not consuming more memory:

1) You may have set SELF_TUNING_MEM to ON but might not have set
DATABASE_MEMORY to AUTOMATIC. Without having DATABASE_MEMORY set to
AUTOMATIC, the total memory used by the database will not grow.

2) The workload that you're running might not require any more
memory. This can happen in cases where the workload isn't driving the
database hard enough or if the database is small.

3) You may have started running STMM a short time ago and its memory
tuning has not yet completed.

4) You may have other applications running on the machine (not to
mention the OS) which are consuming memory that is therefore
unavailable for the database. Before the database is able to increase
its memory usage it must first ensure that there is free memory
available on the box. If there is no free memory available, the
database will not grow its memory consumption.

Do any of these situations seem likely?

Thanks,
Adam

On Nov 19, 3:15 pm, dunleav1 <[e-mail]:

I have a db2 9 FP3a installation with one instance and one database
installed.
I have 4G of physical RAM and I have enabled the /3G switch in
boot.ini.
I am using STTM.
If I run db2mtrk the database never consumes more than 1.2G - why?

Here's the db2mtrk output:
Tracking Memory on: 2007/11/19 at 15:13:43

Memory for instance

Database Monitor Heap is of size 1245184 bytes
Other Memory is of size 10223616 bytes
Total: 11468800 bytes

Memory for database: B001

Backup/Restore/Util Heap is of size 65536 bytes
Package Cache is of size 15794176 bytes
Catalog Cache Heap is of size 1114112 bytes
Buffer Pool Heap (1) is of size 826081280 bytes
Buffer Pool Heap (System 32k buffer pool) is of size 720896 bytes
Buffer Pool Heap (System 16k buffer pool) is of size 458752 bytes
Buffer Pool Heap (System 8k buffer pool) is of size 327680 bytes
Buffer Pool Heap (System 4k buffer pool) is of size 262144 bytes
Shared Sort Heap is of size 589824 bytes
Lock Manager Heap is of size 407371776 bytes
Database Heap is of size 5636096 bytes
Other Memory is of size 131072 bytes
Total: 1258553344 bytes

[Liam Finnie]

On Nov 19, 3:15 pm, dunleav1 <[e-mail]:

I have a db2 9 FP3a installation with one instance and one database
installed.
I have 4G of physical RAM and I have enabled the /3G switch in
boot.ini.
I am using STTM.
If I run db2mtrk the database never consumes more than 1.2G - why?

Here's the db2mtrk output:
Tracking Memory on: 2007/11/19 at 15:13:43

Memory for instance

Database Monitor Heap is of size 1245184 bytes
Other Memory is of size 10223616 bytes
Total: 11468800 bytes

Memory for database: B001

Backup/Restore/Util Heap is of size 65536 bytes
Package Cache is of size 15794176 bytes
Catalog Cache Heap is of size 1114112 bytes
Buffer Pool Heap (1) is of size 826081280 bytes
Buffer Pool Heap (System 32k buffer pool) is of size 720896 bytes
Buffer Pool Heap (System 16k buffer pool) is of size 458752 bytes
Buffer Pool Heap (System 8k buffer pool) is of size 327680 bytes
Buffer Pool Heap (System 4k buffer pool) is of size 262144 bytes
Shared Sort Heap is of size 589824 bytes
Lock Manager Heap is of size 407371776 bytes
Database Heap is of size 5636096 bytes
Other Memory is of size 131072 bytes
Total: 1258553344 bytes

Hello,

You are likely configured to use more than 1.2GB of memory - db2mtrk
only shows how much memory has been currently allocated. Under
database memory, the used utility heap is only about 64K, and the used
shared sort heap is only about 590K. If you look at the in-memory
settings for the two associated db config parms ('db2 get db cfg show
detail', look for util_heap_sz and sheapthres_shr), you should see how
large they are allowed to grow. If, for instance, your utility heap
is much larger than you think you will need, then you can decrease the
UTIL_HEAP_SZ configuration parameter (that one isn't tuned by STMM).
STMM will tune sort heap memory (as long as sheapthres_shr is set to
AUTOMATIC) according to the sort workload on your instance - if you
were to take a db2mtrk snapshot while there were many active sorts,
you would see a much larger sort heap usage (and correspondingly, see
that DB2 is using more memory).

Another thing to check is that database_memory is set to AUTOMATIC
(the default on Windows) - if not, then set it to AUTOMATIC so that
STMM can tune that.

Finally, there are other address space consumers that db2mtrk can't
report on - for instance, loaded DLLs, global variables, etc. You're
also missing private memory - 'db2mtrk -p' - that can contribute
significantly to DB2's overall memory usage if you have a large number
of agents.

Cheers,
Liam.

[dunleav1]

Thanks for the input.
Yes, it's STMM.
DATABASE_MEMORY AUTOMATIC
SELF_TUNING_MEM ON

There is a 4G of RAM on the windows bow and I have the /3G switch set
in the boot.ini.
If I look at the task manager there is 1.7G of free memory.
There are no other programs running on the box consuming memory.
Here is the output of db2mtrk -i -d -v -p:
Tracking Memory on: 2007/11/28 at 09:39:36
Memory for instance
Database Monitor Heap is of size 327680 bytes
Other Memory is of size 9961472 bytes
Total: 10289152 bytes
Memory for database: B001
Backup/Restore/Util Heap is of size 65536 bytes
Package Cache is of size 1572864 bytes
Catalog Cache Heap is of size 983040 bytes
Buffer Pool Heap (1) is of size 1082327040 bytes
Buffer Pool Heap (System 32k buffer pool) is of size 720896 bytes
Buffer Pool Heap (System 16k buffer pool) is of size 458752 bytes
Buffer Pool Heap (System 8k buffer pool) is of size 327680 bytes
Buffer Pool Heap (System 4k buffer pool) is of size 262144 bytes
Shared Sort Heap is of size 262144 bytes
Lock Manager Heap is of size 194445312 bytes
Database Heap is of size 5439488 bytes
Other Memory is of size 131072 bytes
Total: 1286995968 bytes
Memory for agent 5876
Other Memory is of size 65536 bytes
Application Control Heap is of size 65536 bytes
Application Heap is of size 196608 bytes
Total: 327680 bytes
Memory for agent 4844
Other Memory is of size 65536 bytes
Application Control Heap is of size 65536 bytes
Application Heap is of size 65536 bytes
Total: 196608 bytes
Memory for agent 1604
Other Memory is of size 196608 bytes
Application Heap is of size 655360 bytes
Application Control Heap is of size 65536 bytes
Total: 917504 bytes
Memory for agent 4972
Application Heap is of size 65536 bytes
Other Memory is of size 65536 bytes
Application Control Heap is of size 65536 bytes
Total: 196608 bytes
Memory for agent 2788
Other Memory is of size 65536 bytes
Application Heap is of size 65536 bytes
Application Control Heap is of size 65536 bytes
Total: 196608 bytes
Memory for agent 664
Other Memory is of size 458752 bytes
Application Heap is of size 65536 bytes
Application Control Heap is of size 65536 bytes
Total: 589824 bytes
Memory for agent 2248
Other Memory is of size 589824 bytes
Application Heap is of size 917504 bytes
Application Control Heap is of size 65536 bytes
Total: 1572864 bytes

[ajstorm]

It's looking like this is going to require some more detailed
analysis. Can you please send me your db2diag.log file and your
stmmlogs (located in an stmmlog directory in the same place you'd find
the db2diag.log file)?

Thanks,
Adam

On Nov 28, 9:44 am, dunleav1 <[e-mail]:

Thanks for the input.
Yes, it's STMM.
DATABASE_MEMORY AUTOMATIC
SELF_TUNING_MEM ON

There is a 4G of RAM on the windows bow and I have the /3G switch set
in the boot.ini.
If I look at the task manager there is 1.7G of free memory.
There are no other programs running on the box consuming memory.

Here is the output of db2mtrk -i -d -v -p:
Tracking Memory on: 2007/11/28 at 09:39:36
Memory for instance
Database Monitor Heap is of size 327680 bytes
Other Memory is of size 9961472 bytes
Total: 10289152 bytes
Memory for database: B001
Backup/Restore/Util Heap is of size 65536 bytes
Package Cache is of size 1572864 bytes
Catalog Cache Heap is of size 983040 bytes
Buffer Pool Heap (1) is of size 1082327040 bytes
Buffer Pool Heap (System 32k buffer pool) is of size 720896 bytes
Buffer Pool Heap (System 16k buffer pool) is of size 458752 bytes
Buffer Pool Heap (System 8k buffer pool) is of size 327680 bytes
Buffer Pool Heap (System 4k buffer pool) is of size 262144 bytes
Shared Sort Heap is of size 262144 bytes
Lock Manager Heap is of size 194445312 bytes
Database Heap is of size 5439488 bytes
Other Memory is of size 131072 bytes
Total: 1286995968 bytes
Memory for agent 5876
Other Memory is of size 65536 bytes
Application Control Heap is of size 65536 bytes
Application Heap is of size 196608 bytes
Total: 327680 bytes
Memory for agent 4844
Other Memory is of size 65536 bytes
Application Control Heap is of size 65536 bytes
Application Heap is of size 65536 bytes
Total: 196608 bytes
Memory for agent 1604
Other Memory is of size 196608 bytes
Application Heap is of size 655360 bytes
Application Control Heap is of size 65536 bytes
Total: 917504 bytes
Memory for agent 4972
Application Heap is of size 65536 bytes
Other Memory is of size 65536 bytes
Application Control Heap is of size 65536 bytes
Total: 196608 bytes
Memory for agent 2788
Other Memory is of size 65536 bytes
Application Heap is of size 65536 bytes
Application Control Heap is of size 65536 bytes
Total: 196608 bytes
Memory for agent 664
Other Memory is of size 458752 bytes
Application Heap is of size 65536 bytes
Application Control Heap is of size 65536 bytes
Total: 589824 bytes
Memory for agent 2248
Other Memory is of size 589824 bytes
Application Heap is of size 917504 bytes
Application Control Heap is of size 65536 bytes
Total: 1572864 bytes

[dunleav1]

I'll send an email with the requested files.

[dunleav1]

On Dec 6, 2:14 pm, dunleav1 <[e-mail]:

I'll send an email with the requested files.

Weird, it looks like db2 is consuming more than 1.2G now. I'll keep an
eye on this and post an update if it happens again.