[LUW] Linux and DB2 memory use

Gregory Palgrave

[LUW] Linux and DB2 memory use
Hi Folks,

Hoping someone can explain this to me. We are running DB2 ESE 9.5 on RHEL5. I've done some searching but found no clear answer to my puzzle.

Our Linux admin is saying DB2 is not using much real memory, and perhaps that is a problem for performance.

This is based on the free -m output which shows on the second line buffers/cache used of less than 3 GB on a 32 GB server:

$ free -m
total used free shared buffers cached
Mem: 32183 29528 2655 0 595 26402
-/+ buffers/cache: 2530 29653
Swap: 8189 3 8185

Db2top, on the other hand is showing us that we have over 16 GB of memory allocated to DB2:

Type Level Pool Total Size WaterMark Max Size Pool(s)
Memory Memory Percent Current High Percent Maximum # of
----------- ---------- -------------- ------- ------- --------- ------- ------- -------
Instance DB2INST Monitor 0.01% 2.1M 2.4M 566.67% 384.0K 1
Instance DB2INST FCMBP 0.01% 832.0K 832.0K 100.00% 832.0K 1
Instance DB2INST Other 0.28% 43.4M 65.5M 118.00% 36.8M 1
Database MYDB2 Applications 0.07% 10.7M 19.2M 0.21% 4.9G 157
Database MYDB2 Database 1.51% 236.7M 1.5G 14.77% 1.5G 1
Database MYDB2 Lock Mgr 0.42% 65.5M 65.5M 99.90% 65.6M 1
Database MYDB2 Utility 0.00% 320.0K 97.8M 0.16% 195.3M 1
Database MYDB2 Package Cache 0.51% 80.6M 108.6M 127.57% 63.2M 1
Database MYDB2 Catalog Cache 0.06% 9.4M 9.4M 96.18% 9.8M 1
Database MYDB2 Other 0.00% 192.0K 192.0K 0.94% 20.0M 1
Database MYDB2 BufferPool 96.48% 14.7G 14.7G 100.00% 14.7G 38
Database MYDB2 ApplShrHeap 0.25% 38.8M 101.1M 49.76% 78.1M 1
Application MYDB2 Applications 0.07% 10.7M 19.2M 0.21% 4.9G 157
Application MYDB2 Other 0.34% 53.2M 80.1M 0.00% 4.8T 157

Db2mtrk is telling us the same tale:

$ db2mtrk -d -v|tail
Application Heap (2908) is of size 65536 bytes
Application Heap (38402) is of size 65536 bytes
Application Heap (41447) is of size 327680 bytes
Application Heap (3398) is of size 65536 bytes
Application Heap (3397) is of size 65536 bytes
Application Heap (3396) is of size 65536 bytes
Application Heap (3395) is of size 65536 bytes
Applications Shared Heap is of size 42467328 bytes
Total: 16354443264 bytes

And top output is showing similar numbers:

Mem: 32956200k total, 31375336k used, 1580864k free, 669244k buffers
Swap: 8385920k total, 3752k used, 8382168k free, 28051736k cached

PID USER PR NI VIRT SHR S %CPU %MEM TIME+ P COMMAND
10400 db2inst 24 0 27.9g 16g S 78.0 54.1 13209:15 2 db2sysc


So which one is correct - and why?
If Linux 'free' is correct - what is happening to my bufferpools?
If db2 is correct, how do I explain it to my sysadmin?

If you can point me at a web page or explain it in terms a poor DBA can understand, I'd be grateful.

Cheers
Greg

_____________________________________________________________________
* IDUG EMEA * Prague, Czech Republic * 14-18 November 2011 * http://IDUG.ORG/EMEA *
* If you are going to attend only one conference this year, this is it! *
_____________________________________________________________________
http://www.IDUG.org/mentor
Mentoring should be a rewarding experience for everyone...
IDUG is offering up to 80% off when you both come to the conference!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv

Philip Gunning

Re: [LUW] Linux and DB2 memory use
(in response to Gregory Palgrave)
You are using most of the 16GB, 14.7GB for a bufferpool, probably the
IBMDEFAULTBP bufferpool. The db2mtrk and db2top output is correct. PG



From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Gregory Palgrave
Sent: Thursday, December 16, 2010 2:54 AM
To: [login to unmask email]
Subject: [DB2-L] [LUW] Linux and DB2 memory use



Hi Folks,



Hoping someone can explain this to me. We are running DB2 ESE 9.5 on RHEL5.
I've done some searching but found no clear answer to my puzzle.



Our Linux admin is saying DB2 is not using much real memory, and perhaps
that is a problem for performance.



This is based on the free -m output which shows on the second line
buffers/cache used of less than 3 GB on a 32 GB server:



$ free -m

total used free shared buffers cached

Mem: 32183 29528 2655 0 595 26402

-/+ buffers/cache: 2530 29653

Swap: 8189 3 8185



Db2top, on the other hand is showing us that we have over 16 GB of memory
allocated to DB2:



Type Level Pool Total Size WaterMark Max
Size Pool(s)

Memory Memory Percent Current High Percent
Maximum # of

----------- ---------- -------------- ------- ------- --------- -------
------- -------

Instance DB2INST Monitor 0.01% 2.1M 2.4M 566.67%
384.0K 1

Instance DB2INST FCMBP 0.01% 832.0K 832.0K 100.00%
832.0K 1

Instance DB2INST Other 0.28% 43.4M 65.5M 118.00%
36.8M 1

Database MYDB2 Applications 0.07% 10.7M 19.2M 0.21%
4.9G 157

Database MYDB2 Database 1.51% 236.7M 1.5G 14.77%
1.5G 1

Database MYDB2 Lock Mgr 0.42% 65.5M 65.5M 99.90%
65.6M 1

Database MYDB2 Utility 0.00% 320.0K 97.8M 0.16%
195.3M 1

Database MYDB2 Package Cache 0.51% 80.6M 108.6M 127.57%
63.2M 1

Database MYDB2 Catalog Cache 0.06% 9.4M 9.4M 96.18%
9.8M 1

Database MYDB2 Other 0.00% 192.0K 192.0K 0.94%
20.0M 1

Database MYDB2 BufferPool 96.48% 14.7G 14.7G 100.00%
14.7G 38

Database MYDB2 ApplShrHeap 0.25% 38.8M 101.1M 49.76%
78.1M 1

Application MYDB2 Applications 0.07% 10.7M 19.2M 0.21%
4.9G 157

Application MYDB2 Other 0.34% 53.2M 80.1M 0.00%
4.8T 157



Db2mtrk is telling us the same tale:



$ db2mtrk -d -v|tail

Application Heap (2908) is of size 65536 bytes

Application Heap (38402) is of size 65536 bytes

Application Heap (41447) is of size 327680 bytes

Application Heap (3398) is of size 65536 bytes

Application Heap (3397) is of size 65536 bytes

Application Heap (3396) is of size 65536 bytes

Application Heap (3395) is of size 65536 bytes

Applications Shared Heap is of size 42467328 bytes

Total: 16354443264 bytes



And top output is showing similar numbers:



Mem: 32956200k total, 31375336k used, 1580864k free, 669244k buffers

Swap: 8385920k total, 3752k used, 8382168k free, 28051736k cached



PID USER PR NI VIRT SHR S %CPU %MEM TIME+ P COMMAND

10400 db2inst 24 0 27.9g 16g S 78.0 54.1 13209:15 2 db2sysc





So which one is correct - and why?

If Linux 'free' is correct - what is happening to my bufferpools?

If db2 is correct, how do I explain it to my sysadmin?



If you can point me at a web page or explain it in terms a poor DBA can
understand, I'd be grateful.



Cheers

Greg



_____

< http://www.idug.org > Independent, not-for-profit, User Run - the IDUG
difference!

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are
not already an IDUG member, please < http://www.idug.org/register > register
here.


_____________________________________________________________________
* IDUG EMEA * Prague, Czech Republic * 14-18 November 2011 * http://IDUG.ORG/EMEA *
* If you are going to attend only one conference this year, this is it! *
_____________________________________________________________________
http://www.IDUG.org/mentor
Mentoring should be a rewarding experience for everyone...
IDUG is offering up to 80% off when you both come to the conference!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv

Gregory Palgrave

Re: [LUW] Linux and DB2 memory use
(in response to Philip Gunning)
Thanks Phil,

Feedback from IBM is:

DB2 on AIX, Linux and Windows does not pre-allocate all of the memory required by the configuration at database or instance startup.
It does check with the O/S to reserve the memory, but does not pre-allocate it.

So it seems that even though we *think* we have 14.7 GB of bufferpools (split over a number of BPs), it may not actually be allocated until DB2 wants it.

I monitored the free -m output over the weekend, and there was very little movement in the -/+ buffers/cache over 3 days with large batch, runstats, full table exports run during that time.

Used Free
AVG 2,456 29,726
MIN 2,264 29,483
MAX 2,700 29,919

Given the workload on our system, I cannot understand why we are not making more use of the memory on the server.

Still investigating (STMM, I'm looking at *you*), but if anyone has some more bright ideas let me know :)

Cheers,
Greg



From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Phil Gunning
Sent: Friday, 17 December 2010 9:24 AM
To: [login to unmask email]
Subject: Re: [DB2-L] [LUW] Linux and DB2 memory use

You are using most of the 16GB, 14.7GB for a bufferpool, probably the IBMDEFAULTBP bufferpool. The db2mtrk and db2top output is correct. PG

From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Gregory Palgrave
Sent: Thursday, December 16, 2010 2:54 AM
To: [login to unmask email]
Subject: [DB2-L] [LUW] Linux and DB2 memory use

Hi Folks,

Hoping someone can explain this to me. We are running DB2 ESE 9.5 on RHEL5. I've done some searching but found no clear answer to my puzzle.

Our Linux admin is saying DB2 is not using much real memory, and perhaps that is a problem for performance.

This is based on the free -m output which shows on the second line buffers/cache used of less than 3 GB on a 32 GB server:

$ free -m
total used free shared buffers cached
Mem: 32183 29528 2655 0 595 26402
-/+ buffers/cache: 2530 29653
Swap: 8189 3 8185

Db2top, on the other hand is showing us that we have over 16 GB of memory allocated to DB2:

Type Level Pool Total Size WaterMark Max Size Pool(s)
Memory Memory Percent Current High Percent Maximum # of
----------- ---------- -------------- ------- ------- --------- ------- ------- -------
Instance DB2INST Monitor 0.01% 2.1M 2.4M 566.67% 384.0K 1
Instance DB2INST FCMBP 0.01% 832.0K 832.0K 100.00% 832.0K 1
Instance DB2INST Other 0.28% 43.4M 65.5M 118.00% 36.8M 1
Database MYDB2 Applications 0.07% 10.7M 19.2M 0.21% 4.9G 157
Database MYDB2 Database 1.51% 236.7M 1.5G 14.77% 1.5G 1
Database MYDB2 Lock Mgr 0.42% 65.5M 65.5M 99.90% 65.6M 1
Database MYDB2 Utility 0.00% 320.0K 97.8M 0.16% 195.3M 1
Database MYDB2 Package Cache 0.51% 80.6M 108.6M 127.57% 63.2M 1
Database MYDB2 Catalog Cache 0.06% 9.4M 9.4M 96.18% 9.8M 1
Database MYDB2 Other 0.00% 192.0K 192.0K 0.94% 20.0M 1
Database MYDB2 BufferPool 96.48% 14.7G 14.7G 100.00% 14.7G 38
Database MYDB2 ApplShrHeap 0.25% 38.8M 101.1M 49.76% 78.1M 1
Application MYDB2 Applications 0.07% 10.7M 19.2M 0.21% 4.9G 157
Application MYDB2 Other 0.34% 53.2M 80.1M 0.00% 4.8T 157

Db2mtrk is telling us the same tale:

$ db2mtrk -d -v|tail
Application Heap (2908) is of size 65536 bytes
Application Heap (38402) is of size 65536 bytes
Application Heap (41447) is of size 327680 bytes
Application Heap (3398) is of size 65536 bytes
Application Heap (3397) is of size 65536 bytes
Application Heap (3396) is of size 65536 bytes
Application Heap (3395) is of size 65536 bytes
Applications Shared Heap is of size 42467328 bytes
Total: 16354443264 bytes

And top output is showing similar numbers:

Mem: 32956200k total, 31375336k used, 1580864k free, 669244k buffers
Swap: 8385920k total, 3752k used, 8382168k free, 28051736k cached

PID USER PR NI VIRT SHR S %CPU %MEM TIME+ P COMMAND
10400 db2inst 24 0 27.9g 16g S 78.0 54.1 13209:15 2 db2sysc


So which one is correct - and why?
If Linux 'free' is correct - what is happening to my bufferpools?
If db2 is correct, how do I explain it to my sysadmin?

If you can point me at a web page or explain it in terms a poor DBA can understand, I'd be grateful.

Cheers
Greg

________________________________

[ http://www.idug.org/images/banners/idug_2011.gif ] < http://www.idug.org >

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please register here. < http://www.idug.org/register >

________________________________

[ http://www.idug.org/images/banners/idug_2011.gif ] < http://www.idug.org >

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please register here. < http://www.idug.org/register >

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* If you are going to attend only one conference this year, this is it! *
_____________________________________________________________________
http://www.IDUG.org/mentor
How can you expand your staff or do succession planning in this economy?
Mentoring is a proven, economical, way to train the next generation of DB2 Users!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv

Philip Gunning

Re: [LUW] Linux and DB2 memory use
(in response to Gregory Palgrave)
It is allocated at first connect, so if you have a connection or have
activated the database they are fully allocated..PG



From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Gregory Palgrave
Sent: Monday, December 20, 2010 7:35 PM
To: [login to unmask email]
Subject: Re: [DB2-L] [LUW] Linux and DB2 memory use



Thanks Phil,



Feedback from IBM is:



DB2 on AIX, Linux and Windows does not pre-allocate all of the memory
required by the configuration at database or instance startup.

It does check with the O/S to reserve the memory, but does not pre-allocate
it.



So it seems that even though we *think* we have 14.7 GB of bufferpools
(split over a number of BPs), it may not actually be allocated until DB2
wants it.



I monitored the free -m output over the weekend, and there was very little
movement in the -/+ buffers/cache over 3 days with large batch, runstats,
full table exports run during that time.



Used Free

AVG 2,456 29,726

MIN 2,264 29,483

MAX 2,700 29,919



Given the workload on our system, I cannot understand why we are not making
more use of the memory on the server.



Still investigating (STMM, I'm looking at *you*), but if anyone has some
more bright ideas let me know J



Cheers,

Greg







From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Phil Gunning
Sent: Friday, 17 December 2010 9:24 AM
To: [login to unmask email]
Subject: Re: [DB2-L] [LUW] Linux and DB2 memory use



You are using most of the 16GB, 14.7GB for a bufferpool, probably the
IBMDEFAULTBP bufferpool. The db2mtrk and db2top output is correct. PG



From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Gregory Palgrave
Sent: Thursday, December 16, 2010 2:54 AM
To: [login to unmask email]
Subject: [DB2-L] [LUW] Linux and DB2 memory use



Hi Folks,



Hoping someone can explain this to me. We are running DB2 ESE 9.5 on RHEL5.
I've done some searching but found no clear answer to my puzzle.



Our Linux admin is saying DB2 is not using much real memory, and perhaps
that is a problem for performance.



This is based on the free -m output which shows on the second line
buffers/cache used of less than 3 GB on a 32 GB server:



$ free -m

total used free shared buffers cached

Mem: 32183 29528 2655 0 595 26402

-/+ buffers/cache: 2530 29653

Swap: 8189 3 8185



Db2top, on the other hand is showing us that we have over 16 GB of memory
allocated to DB2:



Type Level Pool Total Size WaterMark Max
Size Pool(s)

Memory Memory Percent Current High Percent
Maximum # of

----------- ---------- -------------- ------- ------- --------- -------
------- -------

Instance DB2INST Monitor 0.01% 2.1M 2.4M 566.67%
384.0K 1

Instance DB2INST FCMBP 0.01% 832.0K 832.0K 100.00%
832.0K 1

Instance DB2INST Other 0.28% 43.4M 65.5M 118.00%
36.8M 1

Database MYDB2 Applications 0.07% 10.7M 19.2M 0.21%
4.9G 157

Database MYDB2 Database 1.51% 236.7M 1.5G 14.77%
1.5G 1

Database MYDB2 Lock Mgr 0.42% 65.5M 65.5M 99.90%
65.6M 1

Database MYDB2 Utility 0.00% 320.0K 97.8M 0.16%
195.3M 1

Database MYDB2 Package Cache 0.51% 80.6M 108.6M 127.57%
63.2M 1

Database MYDB2 Catalog Cache 0.06% 9.4M 9.4M 96.18%
9.8M 1

Database MYDB2 Other 0.00% 192.0K 192.0K 0.94%
20.0M 1

Database MYDB2 BufferPool 96.48% 14.7G 14.7G 100.00%
14.7G 38

Database MYDB2 ApplShrHeap 0.25% 38.8M 101.1M 49.76%
78.1M 1

Application MYDB2 Applications 0.07% 10.7M 19.2M 0.21%
4.9G 157

Application MYDB2 Other 0.34% 53.2M 80.1M 0.00%
4.8T 157



Db2mtrk is telling us the same tale:



$ db2mtrk -d -v|tail

Application Heap (2908) is of size 65536 bytes

Application Heap (38402) is of size 65536 bytes

Application Heap (41447) is of size 327680 bytes

Application Heap (3398) is of size 65536 bytes

Application Heap (3397) is of size 65536 bytes

Application Heap (3396) is of size 65536 bytes

Application Heap (3395) is of size 65536 bytes

Applications Shared Heap is of size 42467328 bytes

Total: 16354443264 bytes



And top output is showing similar numbers:



Mem: 32956200k total, 31375336k used, 1580864k free, 669244k buffers

Swap: 8385920k total, 3752k used, 8382168k free, 28051736k cached



PID USER PR NI VIRT SHR S %CPU %MEM TIME+ P COMMAND

10400 db2inst 24 0 27.9g 16g S 78.0 54.1 13209:15 2 db2sysc





So which one is correct - and why?

If Linux 'free' is correct - what is happening to my bufferpools?

If db2 is correct, how do I explain it to my sysadmin?



If you can point me at a web page or explain it in terms a poor DBA can
understand, I'd be grateful.



Cheers

Greg



_____

< http://www.idug.org > Independent, not-for-profit, User Run - the IDUG
difference!

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are
not already an IDUG member, please < http://www.idug.org/register > register
here.



_____

< http://www.idug.org > Independent, not-for-profit, User Run - the IDUG
difference!

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are
not already an IDUG member, please < http://www.idug.org/register > register
here.



_____

>>> Error in line 8 of site.mailtpl: incorrect operand type, leftover data:
" = 3" <<< -> .BB &DAYSEQ(3) = 2 or &DAYSEQ(3) = 3 <-


_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* If you are going to attend only one conference this year, this is it! *
_____________________________________________________________________
http://www.IDUG.org/mentor
How can you expand your staff or do succession planning in this economy?
Mentoring is a proven, economical, way to train the next generation of DB2 Users!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv

Gregory Palgrave

Re: [LUW] Linux and DB2 memory use
(in response to Philip Gunning)
Thanks, but this is a live Prod database so there are plenty of connections and the bufferpools do not appear to be fully allocated/active even under heavy load - we still have a disagreement between the db2 reporting tools and Linux over how much RAM is in use.

The memory may be 'reserved', but it does not seem to be getting used. I'm still puzzled.


From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Phil Gunning
Sent: Tuesday, 21 December 2010 9:05 AM
To: [login to unmask email]
Subject: Re: [DB2-L] [LUW] Linux and DB2 memory use

It is allocated at first connect, so if you have a connection or have activated the database they are fully allocated....PG

From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Gregory Palgrave
Sent: Monday, December 20, 2010 7:35 PM
To: [login to unmask email]
Subject: Re: [DB2-L] [LUW] Linux and DB2 memory use

Thanks Phil,

Feedback from IBM is:

DB2 on AIX, Linux and Windows does not pre-allocate all of the memory required by the configuration at database or instance startup.
It does check with the O/S to reserve the memory, but does not pre-allocate it.

So it seems that even though we *think* we have 14.7 GB of bufferpools (split over a number of BPs), it may not actually be allocated until DB2 wants it.

I monitored the free -m output over the weekend, and there was very little movement in the -/+ buffers/cache over 3 days with large batch, runstats, full table exports run during that time.

Used Free
AVG 2,456 29,726
MIN 2,264 29,483
MAX 2,700 29,919

Given the workload on our system, I cannot understand why we are not making more use of the memory on the server.

Still investigating (STMM, I'm looking at *you*), but if anyone has some more bright ideas let me know :)

Cheers,
Greg



From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Phil Gunning
Sent: Friday, 17 December 2010 9:24 AM
To: [login to unmask email]
Subject: Re: [DB2-L] [LUW] Linux and DB2 memory use

You are using most of the 16GB, 14.7GB for a bufferpool, probably the IBMDEFAULTBP bufferpool. The db2mtrk and db2top output is correct. PG

From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Gregory Palgrave
Sent: Thursday, December 16, 2010 2:54 AM
To: [login to unmask email]
Subject: [DB2-L] [LUW] Linux and DB2 memory use

Hi Folks,

Hoping someone can explain this to me. We are running DB2 ESE 9.5 on RHEL5. I've done some searching but found no clear answer to my puzzle.

Our Linux admin is saying DB2 is not using much real memory, and perhaps that is a problem for performance.

This is based on the free -m output which shows on the second line buffers/cache used of less than 3 GB on a 32 GB server:

$ free -m
total used free shared buffers cached
Mem: 32183 29528 2655 0 595 26402
-/+ buffers/cache: 2530 29653
Swap: 8189 3 8185

Db2top, on the other hand is showing us that we have over 16 GB of memory allocated to DB2:

Type Level Pool Total Size WaterMark Max Size Pool(s)
Memory Memory Percent Current High Percent Maximum # of
----------- ---------- -------------- ------- ------- --------- ------- ------- -------
Instance DB2INST Monitor 0.01% 2.1M 2.4M 566.67% 384.0K 1
Instance DB2INST FCMBP 0.01% 832.0K 832.0K 100.00% 832.0K 1
Instance DB2INST Other 0.28% 43.4M 65.5M 118.00% 36.8M 1
Database MYDB2 Applications 0.07% 10.7M 19.2M 0.21% 4.9G 157
Database MYDB2 Database 1.51% 236.7M 1.5G 14.77% 1.5G 1
Database MYDB2 Lock Mgr 0.42% 65.5M 65.5M 99.90% 65.6M 1
Database MYDB2 Utility 0.00% 320.0K 97.8M 0.16% 195.3M 1
Database MYDB2 Package Cache 0.51% 80.6M 108.6M 127.57% 63.2M 1
Database MYDB2 Catalog Cache 0.06% 9.4M 9.4M 96.18% 9.8M 1
Database MYDB2 Other 0.00% 192.0K 192.0K 0.94% 20.0M 1
Database MYDB2 BufferPool 96.48% 14.7G 14.7G 100.00% 14.7G 38
Database MYDB2 ApplShrHeap 0.25% 38.8M 101.1M 49.76% 78.1M 1
Application MYDB2 Applications 0.07% 10.7M 19.2M 0.21% 4.9G 157
Application MYDB2 Other 0.34% 53.2M 80.1M 0.00% 4.8T 157

Db2mtrk is telling us the same tale:

$ db2mtrk -d -v|tail
Application Heap (2908) is of size 65536 bytes
Application Heap (38402) is of size 65536 bytes
Application Heap (41447) is of size 327680 bytes
Application Heap (3398) is of size 65536 bytes
Application Heap (3397) is of size 65536 bytes
Application Heap (3396) is of size 65536 bytes
Application Heap (3395) is of size 65536 bytes
Applications Shared Heap is of size 42467328 bytes
Total: 16354443264 bytes

And top output is showing similar numbers:

Mem: 32956200k total, 31375336k used, 1580864k free, 669244k buffers
Swap: 8385920k total, 3752k used, 8382168k free, 28051736k cached

PID USER PR NI VIRT SHR S %CPU %MEM TIME+ P COMMAND
10400 db2inst 24 0 27.9g 16g S 78.0 54.1 13209:15 2 db2sysc


So which one is correct - and why?
If Linux 'free' is correct - what is happening to my bufferpools?
If db2 is correct, how do I explain it to my sysadmin?

If you can point me at a web page or explain it in terms a poor DBA can understand, I'd be grateful.

Cheers
Greg

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* If you are going to attend only one conference this year, this is it! *
_____________________________________________________________________
http://www.IDUG.org/mentor
How can you expand your staff or do succession planning in this economy?
Mentoring is a proven, economical, way to train the next generation of DB2 Users!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv

Philip Gunning

Re: [LUW] Linux and DB2 memory use
(in response to Gregory Palgrave)
You didn't say if you had specified values for your bufferpools or if you are letting stmm control them. If so that might explain the inconsistency. Believe me if you allocate a 14GB bufferpool and have the supporting memory, you will get it. You should be concerned with physical pages read per minute to see if they , the pools are adequately sized to support your IO rate, and not so much proving it is used...seems to me to be waste of time, I've never run into this question at a client on having to prove bps are used, a simple bufferpool snapshot will show that.. Good Luck. PG
Sent via BlackBerry by AT&T

-----Original Message-----
From: Gregory Palgrave <[login to unmask email]>
Sender: IDUG DB2-L <[login to unmask email]>
Date: Tue, 21 Dec 2010 12:18:09
To: <[login to unmask email]>
Reply-To: IDUG DB2-L <[login to unmask email]>
Subject: Re: [DB2-L] [LUW] Linux and DB2 memory use

Thanks, but this is a live Prod database so there are plenty of connections and the bufferpools do not appear to be fully allocated/active even under heavy load - we still have a disagreement between the db2 reporting tools and Linux over how much RAM is in use.

The memory may be 'reserved', but it does not seem to be getting used. I'm still puzzled.


From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Phil Gunning
Sent: Tuesday, 21 December 2010 9:05 AM
To: [login to unmask email]
Subject: Re: [DB2-L] [LUW] Linux and DB2 memory use

It is allocated at first connect, so if you have a connection or have activated the database they are fully allocated....PG

From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Gregory Palgrave
Sent: Monday, December 20, 2010 7:35 PM
To: [login to unmask email]
Subject: Re: [DB2-L] [LUW] Linux and DB2 memory use

Thanks Phil,

Feedback from IBM is:

DB2 on AIX, Linux and Windows does not pre-allocate all of the memory required by the configuration at database or instance startup.
It does check with the O/S to reserve the memory, but does not pre-allocate it.

So it seems that even though we *think* we have 14.7 GB of bufferpools (split over a number of BPs), it may not actually be allocated until DB2 wants it.

I monitored the free -m output over the weekend, and there was very little movement in the -/+ buffers/cache over 3 days with large batch, runstats, full table exports run during that time.

Used Free
AVG 2,456 29,726
MIN 2,264 29,483
MAX 2,700 29,919

Given the workload on our system, I cannot understand why we are not making more use of the memory on the server.

Still investigating (STMM, I'm looking at *you*), but if anyone has some more bright ideas let me know :)

Cheers,
Greg



From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Phil Gunning
Sent: Friday, 17 December 2010 9:24 AM
To: [login to unmask email]
Subject: Re: [DB2-L] [LUW] Linux and DB2 memory use

You are using most of the 16GB, 14.7GB for a bufferpool, probably the IBMDEFAULTBP bufferpool. The db2mtrk and db2top output is correct. PG

From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Gregory Palgrave
Sent: Thursday, December 16, 2010 2:54 AM
To: [login to unmask email]
Subject: [DB2-L] [LUW] Linux and DB2 memory use

Hi Folks,

Hoping someone can explain this to me. We are running DB2 ESE 9.5 on RHEL5. I've done some searching but found no clear answer to my puzzle.

Our Linux admin is saying DB2 is not using much real memory, and perhaps that is a problem for performance.

This is based on the free -m output which shows on the second line buffers/cache used of less than 3 GB on a 32 GB server:

$ free -m
total used free shared buffers cached
Mem: 32183 29528 2655 0 595 26402
-/+ buffers/cache: 2530 29653
Swap: 8189 3 8185

Db2top, on the other hand is showing us that we have over 16 GB of memory allocated to DB2:

Type Level Pool Total Size WaterMark Max Size Pool(s)
Memory Memory Percent Current High Percent Maximum # of
----------- ---------- -------------- ------- ------- --------- ------- ------- -------
Instance DB2INST Monitor 0.01% 2.1M 2.4M 566.67% 384.0K 1
Instance DB2INST FCMBP 0.01% 832.0K 832.0K 100.00% 832.0K 1
Instance DB2INST Other 0.28% 43.4M 65.5M 118.00% 36.8M 1
Database MYDB2 Applications 0.07% 10.7M 19.2M 0.21% 4.9G 157
Database MYDB2 Database 1.51% 236.7M 1.5G 14.77% 1.5G 1
Database MYDB2 Lock Mgr 0.42% 65.5M 65.5M 99.90% 65.6M 1
Database MYDB2 Utility 0.00% 320.0K 97.8M 0.16% 195.3M 1
Database MYDB2 Package Cache 0.51% 80.6M 108.6M 127.57% 63.2M 1
Database MYDB2 Catalog Cache 0.06% 9.4M 9.4M 96.18% 9.8M 1
Database MYDB2 Other 0.00% 192.0K 192.0K 0.94% 20.0M 1
Database MYDB2 BufferPool 96.48% 14.7G 14.7G 100.00% 14.7G 38
Database MYDB2 ApplShrHeap 0.25% 38.8M 101.1M 49.76% 78.1M 1
Application MYDB2 Applications 0.07% 10.7M 19.2M 0.21% 4.9G 157
Application MYDB2 Other 0.34% 53.2M 80.1M 0.00% 4.8T 157

Db2mtrk is telling us the same tale:

$ db2mtrk -d -v|tail
Application Heap (2908) is of size 65536 bytes
Application Heap (38402) is of size 65536 bytes
Application Heap (41447) is of size 327680 bytes
Application Heap (3398) is of size 65536 bytes
Application Heap (3397) is of size 65536 bytes
Application Heap (3396) is of size 65536 bytes
Application Heap (3395) is of size 65536 bytes
Applications Shared Heap is of size 42467328 bytes
Total: 16354443264 bytes

And top output is showing similar numbers:

Mem: 32956200k total, 31375336k used, 1580864k free, 669244k buffers
Swap: 8385920k total, 3752k used, 8382168k free, 28051736k cached

PID USER PR NI VIRT SHR S %CPU %MEM TIME+ P COMMAND
10400 db2inst 24 0 27.9g 16g S 78.0 54.1 13209:15 2 db2sysc


So which one is correct - and why?
If Linux 'free' is correct - what is happening to my bufferpools?
If db2 is correct, how do I explain it to my sysadmin?

If you can point me at a web page or explain it in terms a poor DBA can understand, I'd be grateful.

Cheers
Greg

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* If you are going to attend only one conference this year, this is it! *
_____________________________________________________________________
http://www.IDUG.org/mentor
How can you expand your staff or do succession planning in this economy?
Mentoring is a proven, economical, way to train the next generation of DB2 Users!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv


_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* If you are going to attend only one conference this year, this is it! *
_____________________________________________________________________
http://www.IDUG.org/mentor
How can you expand your staff or do succession planning in this economy?
Mentoring is a proven, economical, way to train the next generation of DB2 Users!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv

Gregory Palgrave

Re: [LUW] Linux and DB2 memory use
(in response to Philip Gunning)
We do have all our BPs set to specific sizes - we have STMM on for some other memory settings, but not for the BPs as it did not work well with our mixed workload. Possibly this mix of some stmm on and some off is doing something strange?

The reason we are trying to check this is because we have had some occasional IO-related issues over the last few weeks, and the sysadmins are pointing (with a hint of glee) at DB2 saying it isn't using much memory, ergo, it's a DB2 configuration problem...hence the need to prove memory is really being used.

The BP snapshots show the 'current size' as expected, it's just not (apparently) reflected in the real used memory on Linux.

$ vmstat -a -S M
procs -----------memory---------- ---swap-- -----io---- --system-- -----cpu------
r b swpd free inact active si so bi bo in cs us sy id wa st
3 3 3 238 8322 22753 0 0 2745 147 2 2 8 3 54 36 0

So 22Gb of active Virtual memory, which would be about right for the instance, db + buffers. Is it just the interpretation of how Linux is reporting the memory usage in the free command?

Cheers
Greg



From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Phil Gunning
Sent: Tuesday, 21 December 2010 12:30 PM
To: [login to unmask email]
Subject: Re: [DB2-L] [LUW] Linux and DB2 memory use

You didn't say if you had specified values for your bufferpools or if you are letting stmm control them. If so that might explain the inconsistency. Believe me if you allocate a 14GB bufferpool and have the supporting memory, you will get it. You should be concerned with physical pages read per minute to see if they , the pools are adequately sized to support your IO rate, and not so much proving it is used...seems to me to be waste of time, I've never run into this question at a client on having to prove bps are used, a simple bufferpool snapshot will show that.. Good Luck. PG

Sent via BlackBerry by AT&T

________________________________
From: Gregory Palgrave <[login to unmask email]>
Sender: IDUG DB2-L <[login to unmask email]>
Date: Tue, 21 Dec 2010 12:18:09 +0800
To: <[login to unmask email]>
ReplyTo: IDUG DB2-L <[login to unmask email]>
Subject: Re: [DB2-L] [LUW] Linux and DB2 memory use

Thanks, but this is a live Prod database so there are plenty of connections and the bufferpools do not appear to be fully allocated/active even under heavy load - we still have a disagreement between the db2 reporting tools and Linux over how much RAM is in use.

The memory may be 'reserved', but it does not seem to be getting used. I'm still puzzled.


From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Phil Gunning
Sent: Tuesday, 21 December 2010 9:05 AM
To: [login to unmask email]
Subject: Re: [DB2-L] [LUW] Linux and DB2 memory use

It is allocated at first connect, so if you have a connection or have activated the database they are fully allocated....PG

From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Gregory Palgrave
Sent: Monday, December 20, 2010 7:35 PM
To: [login to unmask email]
Subject: Re: [DB2-L] [LUW] Linux and DB2 memory use

Thanks Phil,

Feedback from IBM is:

DB2 on AIX, Linux and Windows does not pre-allocate all of the memory required by the configuration at database or instance startup.
It does check with the O/S to reserve the memory, but does not pre-allocate it.

So it seems that even though we *think* we have 14.7 GB of bufferpools (split over a number of BPs), it may not actually be allocated until DB2 wants it.

I monitored the free -m output over the weekend, and there was very little movement in the -/+ buffers/cache over 3 days with large batch, runstats, full table exports run during that time.

Used Free
AVG 2,456 29,726
MIN 2,264 29,483
MAX 2,700 29,919

Given the workload on our system, I cannot understand why we are not making more use of the memory on the server.

Still investigating (STMM, I'm looking at *you*), but if anyone has some more bright ideas let me know :)

Cheers,
Greg



From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Phil Gunning
Sent: Friday, 17 December 2010 9:24 AM
To: [login to unmask email]
Subject: Re: [DB2-L] [LUW] Linux and DB2 memory use

You are using most of the 16GB, 14.7GB for a bufferpool, probably the IBMDEFAULTBP bufferpool. The db2mtrk and db2top output is correct. PG

From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Gregory Palgrave
Sent: Thursday, December 16, 2010 2:54 AM
To: [login to unmask email]
Subject: [DB2-L] [LUW] Linux and DB2 memory use

Hi Folks,

Hoping someone can explain this to me. We are running DB2 ESE 9.5 on RHEL5. I've done some searching but found no clear answer to my puzzle.

Our Linux admin is saying DB2 is not using much real memory, and perhaps that is a problem for performance.

This is based on the free -m output which shows on the second line buffers/cache used of less than 3 GB on a 32 GB server:

$ free -m
total used free shared buffers cached
Mem: 32183 29528 2655 0 595 26402
-/+ buffers/cache: 2530 29653
Swap: 8189 3 8185

Db2top, on the other hand is showing us that we have over 16 GB of memory allocated to DB2:

Type Level Pool Total Size WaterMark Max Size Pool(s)
Memory Memory Percent Current High Percent Maximum # of
----------- ---------- -------------- ------- ------- --------- ------- ------- -------
Instance DB2INST Monitor 0.01% 2.1M 2.4M 566.67% 384.0K 1
Instance DB2INST FCMBP 0.01% 832.0K 832.0K 100.00% 832.0K 1
Instance DB2INST Other 0.28% 43.4M 65.5M 118.00% 36.8M 1
Database MYDB2 Applications 0.07% 10.7M 19.2M 0.21% 4.9G 157
Database MYDB2 Database 1.51% 236.7M 1.5G 14.77% 1.5G 1
Database MYDB2 Lock Mgr 0.42% 65.5M 65.5M 99.90% 65.6M 1
Database MYDB2 Utility 0.00% 320.0K 97.8M 0.16% 195.3M 1
Database MYDB2 Package Cache 0.51% 80.6M 108.6M 127.57% 63.2M 1
Database MYDB2 Catalog Cache 0.06% 9.4M 9.4M 96.18% 9.8M 1
Database MYDB2 Other 0.00% 192.0K 192.0K 0.94% 20.0M 1
Database MYDB2 BufferPool 96.48% 14.7G 14.7G 100.00% 14.7G 38
Database MYDB2 ApplShrHeap 0.25% 38.8M 101.1M 49.76% 78.1M 1
Application MYDB2 Applications 0.07% 10.7M 19.2M 0.21% 4.9G 157
Application MYDB2 Other 0.34% 53.2M 80.1M 0.00% 4.8T 157

Db2mtrk is telling us the same tale:

$ db2mtrk -d -v|tail
Application Heap (2908) is of size 65536 bytes
Application Heap (38402) is of size 65536 bytes
Application Heap (41447) is of size 327680 bytes
Application Heap (3398) is of size 65536 bytes
Application Heap (3397) is of size 65536 bytes
Application Heap (3396) is of size 65536 bytes
Application Heap (3395) is of size 65536 bytes
Applications Shared Heap is of size 42467328 bytes
Total: 16354443264 bytes

And top output is showing similar numbers:

Mem: 32956200k total, 31375336k used, 1580864k free, 669244k buffers
Swap: 8385920k total, 3752k used, 8382168k free, 28051736k cached

PID USER PR NI VIRT SHR S %CPU %MEM TIME+ P COMMAND
10400 db2inst 24 0 27.9g 16g S 78.0 54.1 13209:15 2 db2sysc


So which one is correct - and why?
If Linux 'free' is correct - what is happening to my bufferpools?
If db2 is correct, how do I explain it to my sysadmin?

If you can point me at a web page or explain it in terms a poor DBA can understand, I'd be grateful.

Cheers
Greg

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* Your only source for independent, unbiased, and trusted DB2 information. *
_____________________________________________________________________
http://www.IDUG.org/mentor
Mentoring should be a rewarding experience for everyone...
IDUG is offering up to 80% off when you both come to the conference!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv