I used to think that if I could just get enough details into a spreadsheet, I could tell exactly how much memory DB2 would be using at any point in time. I gave up on the spreadsheet idea long ago, though when I was working with 32-bit systems and their limit of ~2GB for the most critical memory areas, I did use a simplified spreadsheet when adjusting to make sure I could keep it under the 2GB.
Thankfully, 64-bit databases mean that I am less likely to have to rob bufferpools to give memory to sorts or vice-versa. Also, I use STMM and automatic settings for many areas on my one-database->one-instance->one-server systems.
But even with these advances it is best to understand DB2’s memory model and have the real-world commands to figure out what is going on.
DB2’s Memory Model
There is a lot of good material on this, so I am not going to go into a full description here. I suggest this developerWorks article as a great read on this: http://www.ibm.com/developerworks/data/library/techarticle/dm-0406qi/
The way I think of it, there are basically three types of memory areas – ones that are allocated at the instance level, ones that are allocated at the database level, and ones that are allocated at the application level.
Instance level memory areas include:
- AUDIT_BUF_SZ
- MON_HEAP_SZ
- FCM areas
Database level memory areas include:
- Size of bufferpools
- LOCKLIST
- DBHEAP
- UTIL_HEAP_SZ
- PCKCACHESZ
- SHEAPTHRES_SHR
- CATALOGCACHE_SZ
Application level memory areas include:
- APPLHEAPSZ
- STMTHEAP
- STAT_HEAP_SZ
- Private Sort Heaps (SHEAPTHRES and SORTHEAP)
- AGENT_STACK_SZ
- ASLHEAPSZ
- RQRIOBLK
Why there is no exact way to say “DB2 should be using X memory at any given time”?
It took me a while to understand this. There are two big reasons you cannot just add up parameters as configured in DBM and DB configurations and say exactly how much memory DB2 should be using at any one time.
- Some memory areas are allocated only as applications connect or agents are started up, and the number of connected applications can change from second to second
- Different memory is allocated at different times. Some areas are allocated in full on instance start or database activation or application connection. Some areas start at a minimum size and are incremented by db2 up to a configured maximum
If you work really hard at it, you can come up with an accurate range of memory allocation that DB2 should fall into at any given time based on your configuration settings, but estimating an exact number accurately is very difficult, especially for a live system with a variable number of end users.
Understanding an Individual Memory Area
Sometimes you need to approach memory from this perspective – understanding how much space the buffer pools are taking, or how much space the Package Cache is using for example.
I really do love the Info Center. I still have a complete three foot long book shelf of db2 8.1 reference books that has been replaced by the info center. Everything that was there is somewhere in the Info Center. Most memory areas correspond to a specific configuration parameter. You can look that configuration parameter up in the information center, you can figure out when it is allocated, whether it is allocated at an exact value, or if it is allocated at a minimum and then allocated up to a maximum. I have blog entries on some areas too.
Take the package cache as an example. The associated parameter is PCKCACHESZ (the one I most frequently misspell). In the Info Center, PCKCACHESZ is detailed here: http://www.ibm.com/support/knowledgecenter/SSEPGG_9.7.0/com.ibm.db2.luw.admin.config.doc/doc/r0000266.html
If you look at that page there is a section titled “When allocated” – that is how you can tell when this memory area is allocated.
If you read through the text, you will see details like this:
“The limit specified by the pckcachesz parameter is a soft limit. This limit can be exceeded, if required, if memory is still available in the database shared set. You can use the pkg_cache_size_top monitor element to determine the largest that the package cache has grown, and the pkg_cache_num_overflows monitor element to determine how many times the limit specified by the pckcachesz parameter has been exceeded.”
From reading the Info Center entry, you can learn that the area is allocated in full on database activation, but this particular memory area can exceed the value you set for your parameter.
To contrast, look at the info center entry for LOCKLIST: http://www.ibm.com/support/knowledgecenter/SSEPGG_9.7.0/com.ibm.db2.luw.admin.config.doc/doc/r0000267.html
If you read the text carfully, you will see that the lock list is an area that is allocated in full on the first database connection. BUT your lock list will absolutely not get larger than the configured value for LOCKLIST – to the point of degrading performance and finally returning an error if it gets that far.
These are very different behaviors for memory areas that are both allocated at the database level.
How to Tell How Much Memory DB2 is Using
For the most part, I will leave looking at DB2 memory from the OS side to SAs and when needed, googling. I have used top before on linux/UNIX, but it just reports one (generally large) number for all of DB2. That is not terribly helpful, and the main situation I have used it in was when someone was using it and called me and said “What is this db2sysc that is using over 50% of the memory?”, and I had to compare it back to what memory DB2 thought it was using.
top
top looks something like this:
$ top top - 21:56:00 up 97 days, 8:28, 1 user, load average: 0.11, 0.04, 0.01 Tasks: 206 total, 1 running, 203 sleeping, 0 stopped, 2 zombie Cpu(s): 0.0%us, 0.3%sy, 0.0%ni, 99.3%id, 0.3%wa, 0.0%hi, 0.0%si, 0.0%st Mem: 8060476k total, 7696276k used, 364200k free, 7152k buffers Swap: 6291448k total, 75748k used, 6215700k free, 6725432k cached PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND 16756 db2inst1 20 0 17220 1344 968 R 0.3 0.0 0:00.16 top 24131 db2inst1 20 0 6294m 4.1g 3.7g S 0.3 53.6 2869:02 db2sysc
The nice thing about top is that it does show changes over time, but it can also consume system resources.
db2mtrk
When we want to dig further into memory than the one big number, I have found db2mtrk as a reliable method. It is used like this:
$ db2mtrk -i Tracking Memory on: 2012/11/15 at 22:33:30 Memory for instance other fcmbp monh 17.0M 832.0K 384.0K $ db2mtrk -d Tracking Memory on: 2012/11/15 at 22:33:38 Memory for database: SAMPLE utilh pckcacheh other catcacheh bph (4) bph (3) 128.0K 44.9M 192.0K 21.9M 106.4M 217.9M bph (2) bph (1) bph (S32K) bph (S16K) bph (S8K) bph (S4K) 135.8M 2.1G 832.0K 576.0K 448.0K 384.0K lockh dbh apph (52487)apph (52484)apph (52482)apph (52427) 567.2M 34.9M 128.0K 128.0K 256.0K 192.0K apph (52426)apph (52424)apph (52423)apph (52422)apph (52421)apph (45849) 192.0K 128.0K 64.0K 192.0K 192.0K 64.0K apph (45848)apph (45847)apph (45846)apph (45845)apph (45844)appshrh 64.0K 576.0K 64.0K 64.0K 64.0K 6.7M
Adding up all of these numbers will give you a pretty accurate number as far as total memory usage. There are a few other useful option on db2mtrk – “db2mtrk -h” provides both a usage diagram and meanings of the abbreviations used for the various heaps.
db2top
I am a big fan of db2top for watching a database in real time. The memory screen can be useful here, and shows similar memory areas:
[/]23:28:40,refresh=60secs(0.004) Memory Linux,part=[1/1],DB2INST1:SAMPLEDB
[d=Y,a=N,e=N,p=ALL] [qp=off]
┌──────────────┬────────────┬────────────┬────────────┬───────────┐
Self Tuning......: On │ │ 25%│ 50%│ 75%│ 100%│ Sort Heap........: 0
Sort HWM.........: 117.6M │Memory hwm% │--------------------------------------------------│ Private Mem......: 109.8M
Lock List........: 198.0K │Sort Heap% │ │ Private Sort.....: 0
Shared Sort......: 0 │Mem Skew% │ │ Shared Sort HWM..: 0
Private Work HWM.: 0 │Pool Skew% │ │ PkgCache HWM.....: 156.0M
Catalog Cache HWM: 15.1M └──────────────┴──────────────────────────────────────────────────┘ Shared Work HWM..: 0
Memory Memory Percent Current High Percent Maximum # of
Type Level Pool Total Size WaterMark Max Size Pool(s)
----------- ---------- -------------------- ------- ------------ ------------ ------- ------------ -------
Instance DB2INST1 Monitor 0.01% 384.0K 1.1M 100.00% 384.0K 1
Instance DB2INST1 FCMBP 0.02% 832.0K 832.0K 100.00% 832.0K 1
Instance DB2INST1 Other 0.51% 17.1M 20.3M 40.44% 42.5M 1
Database WC036D01 Applications 0.07% 2.5M 9.6M 13.89% 18.0M 18
Database WC036D01 Database 1.04% 34.9M 34.9M 84.83% 41.1M 1
Database WC036D01 Lock Mgr 16.88% 567.2M 595.6M 100.24% 565.8M 1
Database WC036D01 Utility 0.00% 128.0K 66.6M 0.09% 139.4M 1
Database WC036D01 Package Cache 1.11% 37.2M 198.3M 116.41% 32.0M 1
Database WC036D01 Catalog Cache 0.65% 21.8M 21.8M 136.72% 16.0M 1
Database WC036D01 Other 0.01% 192.0K 192.0K 0.94% 20.0M 1
Database WC036D01 BufferPool 79.24% 2.6G 3.1G 100.00% 2.6G 8
Database WC036D01 ApplShrHeap 0.20% 6.6M 28.0M 8.56% 78.1M 1
Application WC036D01 Applications 0.07% 2.5M 9.6M 13.89% 18.0M 18
Application WC036D01 Other 0.19% 6.2M 12.1M 0.00% 138.3G 18
Quit: q, Help: h Total memory 3.2G db2top 2.0
Notice in the very middle at the bottom, it gives you a total number that matches up well with the other methods described here.
db2pd
db2pd also lets us look at these memory areas in detail:
$ db2pd -mempools Database Partition 0 -- Active -- Up 43 days 10:01:38 -- Date 2012-11-15-22.37.17.338066 Memory Pools: Address MemSet PoolName Id Overhead LogSz LogUpBnd LogHWM PhySz PhyUpBnd PhyHWM Bnd BlkCnt CfgParm 0x00000002000012B8 DBMS fcm 74 0 0 706833 0 0 720896 0 Ovf 0 n/a 0x0000000200001170 DBMS fcmsess 77 65376 1401568 1687552 1401568 1572864 1703936 1572864 Ovf 3 n/a 0x0000000200001028 DBMS fcmchan 79 65376 259584 507904 259584 393216 524288 393216 Ovf 3 n/a 0x0000000200000EE0 DBMS fcmbp 13 65376 656896 925696 656896 851968 983040 851968 Ovf 3 n/a 0x0000000200000D98 DBMS fcmctl 73 126624 1513973 3597992 1513973 1703936 3604480 1703936 Ovf 11 n/a 0x0000000200000C50 DBMS monh 11 122496 187124 368640 720892 393216 393216 1179648 Ovf 29 MON_HEAP_SZ 0x0000000200000B08 DBMS resynch 62 42816 153720 2752512 153720 262144 2752512 262144 Ovf 2 n/a 0x00000002000009C0 DBMS apmh 70 4512 2498164 8257536 2521972 3080192 8257536 3080192 Ovf 121 n/a 0x0000000200000878 DBMS kerh 52 32 1816944 4128768 1824648 2031616 4128768 2031616 Ovf 195 n/a 0x0000000200000730 DBMS bsuh 71 0 444750 15335424 2838531 1048576 15335424 4718592 Ovf 64 n/a 0x00000002000005E8 DBMS sqlch 50 0 2618755 2686976 2618755 2686976 2686976 2686976 Ovf 208 n/a 0x00000002000004A0 DBMS krcbh 69 0 147400 196608 147840 196608 196608 196608 Ovf 15 n/a 0x0000000200000358 DBMS eduah 72 44864 4608024 4608064 4608024 4653056 4653056 4653056 Ovf 1 n/a 0x0000000210000358 FMP undefh 59 56000 860300 22971520 860300 917504 23003136 917504 Phy 7 n/a $ db2pd -db sampledb -mempools Database Partition 0 -- Database SAMPLEDB -- Active -- Up 4 days 02:22:50 -- Date 2012-11-15-22.39.41.223822 Memory Pools: Address MemSet PoolName Id Overhead LogSz LogUpBnd LogHWM PhySz PhyUpBnd PhyHWM Bnd BlkCnt CfgParm 0x00007FFF4A0A0408 SAMPLEDB utilh 5 0 3840 146210816 69378188 131072 146210816 69861376 Ovf 16 UTIL_HEAP_SZ 0x00007FFF4A0A0178 SAMPLEDB pckcacheh 7 2772288 32405296 Unlimited 163654502 47120384 Unlimited 208011264 Ovf 4329 PCKCACHESZ 0x00007FFF4A0A0030 SAMPLEDB xmlcacheh 93 50880 145552 20971520 145552 196608 20971520 196608 Ovf 1 n/a 0x00007FFE9ED11BB0 SAMPLEDB catcacheh 8 90464 16768378 Unlimited 16777210 22937600 Unlimited 22937600 Ovf 4400 CATALOGCACHE_SZ 0x00007FFE9ED117D8 SAMPLEDB bph 16 40800 111484416 Unlimited 111484416 111607808 Unlimited 111607808 Ovf 846 n/a 0x00007FFE9ED11548 SAMPLEDB bph 16 81600 228203472 Unlimited 228203472 228458496 Unlimited 228458496 Ovf 1725 n/a 0x00007FFE9ED112B8 SAMPLEDB bph 16 81600 142117728 Unlimited 146656864 142344192 Unlimited 146931712 Ovf 1065 n/a 0x00007FFE9ED11028 SAMPLEDB bph 16 2121600 2302992752 Unlimited 2875827056 2308177920 Unlimited 2881814528 Ovf 16914 n/a 0x00007FFE9ED10D98 SAMPLEDB bph 16 0 783104 Unlimited 783104 851968 Unlimited 851968 Ovf 5 n/a 0x00007FFE9ED10B08 SAMPLEDB bph 16 0 520960 Unlimited 520960 589824 Unlimited 589824 Ovf 3 n/a 0x00007FFE9ED10878 SAMPLEDB bph 16 0 389888 Unlimited 389888 458752 Unlimited 458752 Ovf 2 n/a 0x00007FFE9ED105E8 SAMPLEDB bph 16 0 324352 Unlimited 324352 393216 Unlimited 393216 Ovf 2 n/a 0x00007FFE9ED104A0 SAMPLEDB lockh 4 0 594782848 624623616 624536192 594804736 624623616 624558080 Ovf 1 LOCKLIST 0x00007FFE9ED10358 SAMPLEDB dbh 2 1883264 32304071 43188224 33257585 36634624 43188224 36634624 Ovf 25517 DBHEAP 0x00007FFFB63B1920 Appl apph 1 0 20375 1048576 71576 131072 1048576 131072 Phy 50 APPLHEAPSZ 0x00007FFFB66F3C68 Appl apph 1 0 176492 1048576 14066624 262144 1048576 14221312 Phy 219 APPLHEAPSZ 0x00007FFFB63B0D98 Appl apph 1 0 10935 1048576 10935 65536 1048576 65536 Phy 18 APPLHEAPSZ 0x00007FFFB63B0C50 Appl apph 1 0 10935 1048576 10935 65536 1048576 65536 Phy 18 APPLHEAPSZ 0x00007FFFB63B0B08 Appl apph 1 0 358615 1048576 402159 589824 1048576 655360 Phy 4364 APPLHEAPSZ 0x00007FFFB63B09C0 Appl apph 1 0 10935 1048576 10935 65536 1048576 65536 Phy 18 APPLHEAPSZ 0x00007FFFB63B0878 Appl apph 1 0 10935 1048576 12919 65536 1048576 65536 Phy 18 APPLHEAPSZ 0x00007FFFB63B0730 Appl apph 1 0 10935 1048576 10935 65536 1048576 65536 Phy 18 APPLHEAPSZ 0x00007FFFB63B0358 Appl appshrh 20 133568 4077928 81920000 27070923 6881280 81920000 29425664 Phy 1657 application shared
There are interesting looking columns here for things like high water marks and upper bounds. See those “Unlimited” – well, they are not fully unlimited. They are limited by the amount of physical and virtual memory on the server, and possibly by things that provide upper bounds on a composite basis like DATABASE_MEMORY or INSTANCE_MEMORY.
For a slightly different way of looking at it, you can also look at it from a memory sets point of view with db2pd:
$ db2pd -db sampledb -memsets Database Partition 0 -- Database SAMPLEDB -- Active -- Up 4 days 02:30:10 -- Date 2012-11-15-22.47.01.149499 Memory Sets: Name Address Id Size(Kb) Key DBP Type Unrsv(Kb) Used(Kb) HWM(Kb) Cmt(Kb) Uncmt(Kb) SAMPLEDB 0x00007FFE9ED10000 639729668 4428736 0x0 0 1 865920 3413120 4005824 3845824 160000 Seg0 0x00007FFE7ED10000 811827205 0 0x0 0 1 0 0 0 0 0 Seg1 0x00007FFE6DF5D000 811859975 0 0x0 0 1 0 0 0 0 0 AppCtl 0x00007FFFB63B0000 639696899 16512 0x0 0 12 0 8640 165248 16192 320 Seg0 0x00007FFE8ED10000 770605065 0 0x0 0 12 0 0 0 0 0 App52482 n/a 821788678 128 0x0 0 4 0 128 0 128 0 $ db2pd -memsets Database Partition 0 -- Active -- Up 43 days 10:11:39 -- Date 2012-11-15-22.47.18.517271 Memory Sets: Name Address Id Size(Kb) Key DBP Type Unrsv(Kb) Used(Kb) HWM(Kb) Cmt(Kb) Uncmt(Kb) DBMS 0x0000000200000000 1497006081 48192 0x7F9F7661 0 0 6208 18624 22144 22144 26048 FMP 0x0000000210000000 1497038850 22592 0x0 0 0 2 0 960 22592 0 Trace 0x0000000000000000 1496973312 39251 0x7F9F7674 0 -1 0 39251 0 39251 0
Man, I love MON_GET…
The MON_GET table functions are by far one of my favorite recent features. I hear they come from Informix inspiration, and they are inspiring, containing data we have never had SQL access to before and getting it in a lightweight way. MON_GET_MEMORY_SETS and MON_GET_MEMORY_POOLS should correspond to the db2pd way of looking at things.
$ db2 "select substr(HOST_NAME,1,25) as HOST_NAME, substr(DB_NAME,1,10) as DBNAME, MEMORY_SET_TYPE, MEMORY_SET_ID, MEMORY_SET_SIZE, MEMORY_SET_COMMITTED, MEMORY_SET_USED, MEMORY_SET_USED_HWM from table(MON_GET_MEMORY_SET(null,null,-2)) as t with ur" HOST_NAME DBNAME MEMORY_SET_TYPE MEMORY_SET_ID MEMORY_SET_SIZE MEMORY_SET_COMMITTED MEMORY_SET_USED MEMORY_SET_USED_HWM ------------------------- ---------- -------------------------------- -------------------- -------------------- -------------------- -------------------- -------------------- xxxxxxxxxxxxxxxxxxxxx.xxx - DBMS 0 49348 22675 19136 22675 xxxxxxxxxxxxxxxxxxxxx.xxx - FMP 2 23134 23134 983 983 xxxxxxxxxxxxxxxxxxxxx.xxx - PRIVATE 9 115212 115212 61276 173015 xxxxxxxxxxxxxxxxxxxxx.xxx SAMPLEDB DATABASE 1 4535025 3938123 3493789 4101963 xxxxxxxxxxxxxxxxxxxxx.xxx SAMPLEDB APPLICATION 12 170065 16580 9371 169213 5 record(s) selected. [db2inst1@435753-svd36db01r ~]$ db2 "select substr(HOST_NAME,1,25) as HOST_NAME, substr(DB_NAME,1,10) as DBNAME, MEMORY_SET_TYPE, MEMORY_POOL_TYPE, MEMORY_POOL_ID, APPLICATION_HANDLE, EDU_ID, MEMORY_POOL_USED, MEMORY_POOL_USED_HWM from table(MON_GET_MEMORY_POOL(null,null,-2)) as t with ur" HOST_NAME DBNAME MEMORY_SET_TYPE MEMORY_POOL_TYPE MEMORY_POOL_ID APPLICATION_HANDLE EDU_ID MEMORY_POOL_USED MEMORY_POOL_USED_HWM ------------------------- ---------- -------------------------------- -------------------------------- -------------------- -------------------- -------------------- -------------------- -------------------- xxxxxxxxxxxxxxxxxxxxx.xxx - DBMS FCM_LOCAL 74 - - 0 0 xxxxxxxxxxxxxxxxxxxxx.xxx - DBMS FCM_SESSION 77 - - 1572864 1572864 xxxxxxxxxxxxxxxxxxxxx.xxx - DBMS FCM_CHANNEL 79 - - 393216 393216 xxxxxxxxxxxxxxxxxxxxx.xxx - DBMS FCMBP 13 - - 851968 851968 xxxxxxxxxxxxxxxxxxxxx.xxx - DBMS FCM_CONTROL 73 - - 1703936 1703936 xxxxxxxxxxxxxxxxxxxxx.xxx - DBMS MONITOR 11 - - 393216 1179648 xxxxxxxxxxxxxxxxxxxxx.xxx - DBMS RESYNC 62 - - 262144 262144 xxxxxxxxxxxxxxxxxxxxx.xxx - DBMS APM 70 - - 3080192 3080192 xxxxxxxxxxxxxxxxxxxxx.xxx - DBMS KERNEL 52 - - 2031616 2031616 xxxxxxxxxxxxxxxxxxxxx.xxx - DBMS BSU 71 - - 1376256 4718592 xxxxxxxxxxxxxxxxxxxxx.xxx - DBMS SQL_COMPILER 50 - - 2686976 2686976 xxxxxxxxxxxxxxxxxxxxx.xxx - DBMS KERNEL_CONTROL 69 - - 196608 196608 xxxxxxxxxxxxxxxxxxxxx.xxx - DBMS EDU 72 - - 4653056 4653056 xxxxxxxxxxxxxxxxxxxxx.xxx - FMP MISC 59 - - 917504 917504 xxxxxxxxxxxxxxxxxxxxx.xxx - PRIVATE PRIVATE 88 - 1429 0 0 xxxxxxxxxxxxxxxxxxxxx.xxx - PRIVATE PRIVATE 88 - 1480 196608 196608 xxxxxxxxxxxxxxxxxxxxx.xxx - PRIVATE 90 - 1379 0 0 xxxxxxxxxxxxxxxxxxxxx.xxx - PRIVATE 90 - 1379 0 0 xxxxxxxxxxxxxxxxxxxxx.xxx - PRIVATE PRIVATE 88 - 1379 393216 1572864 xxxxxxxxxxxxxxxxxxxxx.xxx - PRIVATE PRIVATE 88 - 1451 327680 327680 xxxxxxxxxxxxxxxxxxxxx.xxx - PRIVATE PRIVATE 88 - 1524 524288 524288 xxxxxxxxxxxxxxxxxxxxx.xxx - PRIVATE PRIVATE 88 - 1403 327680 393216 xxxxxxxxxxxxxxxxxxxxx.xxx - PRIVATE PRIVATE 88 - 1444 327680 720896 xxxxxxxxxxxxxxxxxxxxx.xxx - PRIVATE PRIVATE 88 - 1423 393216 1507328 xxxxxxxxxxxxxxxxxxxxx.xxx - PRIVATE PRIVATE 88 - 1521 262144 524288 xxxxxxxxxxxxxxxxxxxxx.xxx - PRIVATE PRIVATE 88 - 1502 524288 851968 xxxxxxxxxxxxxxxxxxxxx.xxx - PRIVATE PRIVATE 88 - 1504 655360 851968 xxxxxxxxxxxxxxxxxxxxx.xxx - PRIVATE PRIVATE 88 - 1503 393216 589824 xxxxxxxxxxxxxxxxxxxxx.xxx - PRIVATE PRIVATE 88 - 1536 458752 851968 xxxxxxxxxxxxxxxxxxxxx.xxx - PRIVATE PRIVATE 88 - 1418 524288 851968 xxxxxxxxxxxxxxxxxxxxx.xxx - PRIVATE PRIVATE 88 - 1412 393216 851968 xxxxxxxxxxxxxxxxxxxxx.xxx - PRIVATE PRIVATE 88 - 1519 458752 1572864 xxxxxxxxxxxxxxxxxxxxx.xxx - PRIVATE PRIVATE 88 - 1535 458752 655360 xxxxxxxxxxxxxxxxxxxxx.xxx - PRIVATE PRIVATE 88 - 1408 720896 3211264 xxxxxxxxxxxxxxxxxxxxx.xxx - PRIVATE PRIVATE 88 - 1449 327680 524288 xxxxxxxxxxxxxxxxxxxxx.xxx - PRIVATE PRIVATE 88 - 1526 262144 655360 xxxxxxxxxxxxxxxxxxxxx.xxx - PRIVATE PRIVATE 88 - 1410 458752 851968 xxxxxxxxxxxxxxxxxxxxx.xxx - PRIVATE PRIVATE 88 - 1525 393216 1507328 xxxxxxxxxxxxxxxxxxxxx.xxx - PRIVATE PRIVATE 88 - 1401 524288 1507328 xxxxxxxxxxxxxxxxxxxxx.xxx - PRIVATE PRIVATE 88 - 1446 458752 917504 xxxxxxxxxxxxxxxxxxxxx.xxx - PRIVATE PRIVATE 88 - 1532 589824 851968 xxxxxxxxxxxxxxxxxxxxx.xxx - PRIVATE PRIVATE 88 - 1409 196608 196608 xxxxxxxxxxxxxxxxxxxxx.xxx - PRIVATE PRIVATE 88 - 1400 393216 524288 xxxxxxxxxxxxxxxxxxxxx.xxx - PRIVATE PRIVATE 88 - 1437 589824 589824 xxxxxxxxxxxxxxxxxxxxx.xxx - PRIVATE PRIVATE 88 - 1283 393216 1507328 xxxxxxxxxxxxxxxxxxxxx.xxx - PRIVATE PRIVATE 88 - 1529 589824 589824 xxxxxxxxxxxxxxxxxxxxx.xxx - PRIVATE PRIVATE 88 - 1405 917504 3407872 xxxxxxxxxxxxxxxxxxxxx.xxx - PRIVATE PRIVATE 88 - 1447 655360 1572864 xxxxxxxxxxxxxxxxxxxxx.xxx - PRIVATE PRIVATE 88 - 1433 655360 1572864 xxxxxxxxxxxxxxxxxxxxx.xxx - PRIVATE PRIVATE 88 - 1428 720896 1179648 xxxxxxxxxxxxxxxxxxxxx.xxx - PRIVATE PRIVATE 88 - 1520 786432 1179648 xxxxxxxxxxxxxxxxxxxxx.xxx - PRIVATE PRIVATE 88 - 1443 589824 720896 xxxxxxxxxxxxxxxxxxxxx.xxx - PRIVATE PRIVATE 88 - 1430 655360 917504 xxxxxxxxxxxxxxxxxxxxx.xxx - PRIVATE PRIVATE 88 - 1422 655360 917504 xxxxxxxxxxxxxxxxxxxxx.xxx - PRIVATE PRIVATE 88 - 1445 458752 1507328 xxxxxxxxxxxxxxxxxxxxx.xxx - PRIVATE PRIVATE 88 - 1498 655360 655360 xxxxxxxxxxxxxxxxxxxxx.xxx - PRIVATE PRIVATE 88 - 1450 655360 655360 xxxxxxxxxxxxxxxxxxxxx.xxx - PRIVATE PRIVATE 88 - 1534 393216 1572864 xxxxxxxxxxxxxxxxxxxxx.xxx - PRIVATE PRIVATE 88 - 1424 196608 262144 xxxxxxxxxxxxxxxxxxxxx.xxx - PRIVATE PRIVATE 88 - 1538 786432 1572864 xxxxxxxxxxxxxxxxxxxxx.xxx - PRIVATE PRIVATE 88 - 1416 589824 589824 xxxxxxxxxxxxxxxxxxxxx.xxx - PRIVATE PRIVATE 88 - 1442 655360 720896 xxxxxxxxxxxxxxxxxxxxx.xxx - PRIVATE PRIVATE 88 - 1518 589824 720896 xxxxxxxxxxxxxxxxxxxxx.xxx - PRIVATE PRIVATE 88 - 1537 458752 720896 xxxxxxxxxxxxxxxxxxxxx.xxx - PRIVATE PRIVATE 88 - 1539 196608 196608 xxxxxxxxxxxxxxxxxxxxx.xxx - PRIVATE PRIVATE 88 - 1533 327680 720896 xxxxxxxxxxxxxxxxxxxxx.xxx - PRIVATE PRIVATE 88 - 1523 589824 589824 xxxxxxxxxxxxxxxxxxxxx.xxx - PRIVATE PRIVATE 88 - 1505 131072 131072 xxxxxxxxxxxxxxxxxxxxx.xxx - PRIVATE PRIVATE 88 - 1506 196608 4521984 xxxxxxxxxxxxxxxxxxxxx.xxx - PRIVATE PRIVATE 88 - 1528 655360 655360 xxxxxxxxxxxxxxxxxxxxx.xxx - PRIVATE PRIVATE 88 - 1527 589824 720896 xxxxxxxxxxxxxxxxxxxxx.xxx - PRIVATE PRIVATE 88 - 1522 589824 720896 xxxxxxxxxxxxxxxxxxxxx.xxx - PRIVATE PRIVATE 88 - 1417 655360 720896 xxxxxxxxxxxxxxxxxxxxx.xxx - PRIVATE PRIVATE 88 - 1420 524288 589824 xxxxxxxxxxxxxxxxxxxxx.xxx - PRIVATE PRIVATE 88 - 1508 65536 2228224 xxxxxxxxxxxxxxxxxxxxx.xxx - PRIVATE PRIVATE 88 - 1517 262144 262144 xxxxxxxxxxxxxxxxxxxxx.xxx - PRIVATE PRIVATE 88 - 1516 262144 262144 xxxxxxxxxxxxxxxxxxxxx.xxx - PRIVATE PRIVATE 88 - 1515 262144 262144 xxxxxxxxxxxxxxxxxxxxx.xxx - PRIVATE PRIVATE 88 - 1514 262144 262144 xxxxxxxxxxxxxxxxxxxxx.xxx - PRIVATE PRIVATE 88 - 1513 262144 262144 xxxxxxxxxxxxxxxxxxxxx.xxx - PRIVATE PRIVATE 88 - 1512 327680 458752 xxxxxxxxxxxxxxxxxxxxx.xxx - PRIVATE PRIVATE 88 - 1511 262144 262144 xxxxxxxxxxxxxxxxxxxxx.xxx - PRIVATE PRIVATE 88 - 1510 65536 65536 xxxxxxxxxxxxxxxxxxxxx.xxx - PRIVATE PRIVATE 88 - 1507 393216 393216 xxxxxxxxxxxxxxxxxxxxx.xxx - PRIVATE PRIVATE 88 - 1435 327680 327680 xxxxxxxxxxxxxxxxxxxxx.xxx - PRIVATE PRIVATE 88 - 1421 196608 196608 xxxxxxxxxxxxxxxxxxxxx.xxx - PRIVATE PRIVATE 88 - 1501 196608 196608 xxxxxxxxxxxxxxxxxxxxx.xxx - PRIVATE PRIVATE 88 - 1399 196608 524288 xxxxxxxxxxxxxxxxxxxxx.xxx - PRIVATE PRIVATE 88 - 1439 196608 196608 xxxxxxxxxxxxxxxxxxxxx.xxx - PRIVATE PRIVATE 88 - 1499 196608 196608 xxxxxxxxxxxxxxxxxxxxx.xxx - PRIVATE PRIVATE 88 - 1397 786432 3276800 xxxxxxxxxxxxxxxxxxxxx.xxx - PRIVATE PRIVATE 88 - 1414 655360 720896 xxxxxxxxxxxxxxxxxxxxx.xxx - PRIVATE PRIVATE 88 - 1432 589824 655360 xxxxxxxxxxxxxxxxxxxxx.xxx - PRIVATE PRIVATE 88 - 1415 589824 851968 xxxxxxxxxxxxxxxxxxxxx.xxx - PRIVATE PRIVATE 88 - 1427 458752 655360 xxxxxxxxxxxxxxxxxxxxx.xxx - PRIVATE PRIVATE 88 - 1169 589824 655360 xxxxxxxxxxxxxxxxxxxxx.xxx - PRIVATE PRIVATE 88 - 1452 655360 655360 xxxxxxxxxxxxxxxxxxxxx.xxx - PRIVATE PRIVATE 88 - 1500 524288 589824 xxxxxxxxxxxxxxxxxxxxx.xxx - PRIVATE PRIVATE 88 - 1426 720896 1048576 xxxxxxxxxxxxxxxxxxxxx.xxx - PRIVATE PRIVATE 88 - 16 0 0 xxxxxxxxxxxxxxxxxxxxx.xxx - PRIVATE PRIVATE 88 - 14 65536 65536 xxxxxxxxxxxxxxxxxxxxx.xxx - PRIVATE PRIVATE 88 - 13 65536 65536 xxxxxxxxxxxxxxxxxxxxx.xxx - PRIVATE PRIVATE 88 - 0 65536 65536 xxxxxxxxxxxxxxxxxxxxx.xxx - PRIVATE USER_DATA 95 - 0 8650752 11337728 xxxxxxxxxxxxxxxxxxxxx.xxx - PRIVATE PERSISTENT_PRIVATE 86 - 0 15400960 36503552 xxxxxxxxxxxxxxxxxxxxx.xxx SAMPLEDB DATABASE UTILITY 5 - - 131072 69861376 xxxxxxxxxxxxxxxxxxxxx.xxx SAMPLEDB DATABASE PACKAGE_CACHE 7 - - 39059456 208011264 xxxxxxxxxxxxxxxxxxxxx.xxx SAMPLEDB DATABASE XMLCACHE 93 - - 196608 196608 xxxxxxxxxxxxxxxxxxxxx.xxx SAMPLEDB DATABASE CAT_CACHE 8 - - 22937600 22937600 xxxxxxxxxxxxxxxxxxxxx.xxx SAMPLEDB DATABASE BP 16 - - 111607808 111607808 xxxxxxxxxxxxxxxxxxxxx.xxx SAMPLEDB DATABASE BP 16 - - 228458496 228458496 xxxxxxxxxxxxxxxxxxxxx.xxx SAMPLEDB DATABASE BP 16 - - 142344192 146931712 xxxxxxxxxxxxxxxxxxxxx.xxx SAMPLEDB DATABASE BP 16 - - 2308177920 2881814528 xxxxxxxxxxxxxxxxxxxxx.xxx SAMPLEDB DATABASE BP 16 - - 851968 851968 xxxxxxxxxxxxxxxxxxxxx.xxx SAMPLEDB DATABASE BP 16 - - 589824 589824 xxxxxxxxxxxxxxxxxxxxx.xxx SAMPLEDB DATABASE BP 16 - - 458752 458752 xxxxxxxxxxxxxxxxxxxxx.xxx SAMPLEDB DATABASE BP 16 - - 393216 393216 xxxxxxxxxxxxxxxxxxxxx.xxx SAMPLEDB DATABASE LOCK_MGR 4 - - 594804736 624558080 xxxxxxxxxxxxxxxxxxxxx.xxx SAMPLEDB DATABASE DATABASE 2 - - 36634624 36634624 xxxxxxxxxxxxxxxxxxxxx.xxx SAMPLEDB APPLICATION APPLICATION 1 52543 - 65536 65536 xxxxxxxxxxxxxxxxxxxxx.xxx SAMPLEDB APPLICATION APPLICATION 1 52542 - 65536 65536 xxxxxxxxxxxxxxxxxxxxx.xxx SAMPLEDB APPLICATION APPLICATION 1 52540 - 262144 7274496 xxxxxxxxxxxxxxxxxxxxx.xxx SAMPLEDB APPLICATION APPLICATION 1 52537 - 196608 262144 xxxxxxxxxxxxxxxxxxxxx.xxx SAMPLEDB APPLICATION APPLICATION 1 52532 - 196608 196608 xxxxxxxxxxxxxxxxxxxxx.xxx SAMPLEDB APPLICATION APPLICATION 1 52531 - 196608 327680 xxxxxxxxxxxxxxxxxxxxx.xxx SAMPLEDB APPLICATION APPLICATION 1 52530 - 131072 131072 xxxxxxxxxxxxxxxxxxxxx.xxx SAMPLEDB APPLICATION APPLICATION 1 52527 - 131072 131072 xxxxxxxxxxxxxxxxxxxxx.xxx SAMPLEDB APPLICATION APPLICATION 1 52526 - 65536 131072 xxxxxxxxxxxxxxxxxxxxx.xxx SAMPLEDB APPLICATION APPLICATION 1 52525 - 196608 196608 xxxxxxxxxxxxxxxxxxxxx.xxx SAMPLEDB APPLICATION APPLICATION 1 52487 - 131072 131072 xxxxxxxxxxxxxxxxxxxxx.xxx SAMPLEDB APPLICATION APPLICATION 1 45849 - 65536 65536 xxxxxxxxxxxxxxxxxxxxx.xxx SAMPLEDB APPLICATION APPLICATION 1 45848 - 65536 65536 xxxxxxxxxxxxxxxxxxxxx.xxx SAMPLEDB APPLICATION APPLICATION 1 45847 - 589824 655360 xxxxxxxxxxxxxxxxxxxxx.xxx SAMPLEDB APPLICATION APPLICATION 1 45846 - 65536 65536 xxxxxxxxxxxxxxxxxxxxx.xxx SAMPLEDB APPLICATION APPLICATION 1 45845 - 65536 65536 xxxxxxxxxxxxxxxxxxxxx.xxx SAMPLEDB APPLICATION APPLICATION 1 45844 - 65536 65536 xxxxxxxxxxxxxxxxxxxxx.xxx SAMPLEDB APPLICATION APPL_SHARED 20 - - 7340032 29425664 137 record(s) selected.
Now that is pretty long, but think of the potential for easy addition and averaging and such.
I think you are going to get the best possible answer to “how much memory is DB2 using” from DB2’s perspective by using:
$ db2 "select SUM(MEMORY_POOL_USED) as TOT_MEMORY_USED from table(MON_GET_MEMORY_POOL(null,null,-2)) as t with ur"
TOT_MEMORY_USED
--------------------
3579117568
1 record(s) selected.
Thanks a lot, it was really very helpful, as I was faing stat_hp_sz issue
Ember ur my best tutor, one more superb tutorial..
DB2 10.1 application heap size memory leak. The table functioned mentioned on this website helped us track the rogue connections and force them off. Thank a lot !
Thanks Ember. That helped me to understand the Issues with my applheapsz that is being used by the application.
You are the perfect tutor.
Thanks for all the right up and set of useful sample commands. I’d like to add to your list for instance level memory usage try:
$ db2pd -dbptnmem
Cheers,
Allan
Hi,
I was trying to understand IO Block Size for Blocking Cursors or Row Blocking when returning a huge result set.
I am facing limitation of 10 MB for Block Size in Agent Private Memory(ASLHEAPSZ set to 2500).
If i set it to 3000, it will Block but only 400 KB.
Is there any limit or should i set some thing else.
Please note it is only a question for learning, not a real time scenario.
Thanks,
Harish Pathangay
What is the best approach to find out application handle which is causing high swap in Linux System ?
Hi Ember
im new to 10.5, but im trying to determine if there are any differences between how 9.5 and 10.5 fp7 uses instance_memory setting. I do have more than one intance in my machines and sometimes its blurry to see how can i limit the amount of memory i could set to a given instance and their “child” databases
i have been using this doc as reference to the matter
http://scn.sap.com/docs/DOC-14275
Thanks so much for your time
hi, how can we check list of 10 applications which is consuming more CPU in db2
It is often hard to look at from the application level without a tool such as DSM or DBI. If you’re talking about from the connection level, the MON_GET_CONNECTION table function will allow you to see the CPU time used by each connection.