We are running DB2 9.1 with FP 4 on AIX

This is basic AIX information:
AIX 5.3. with 4 CPU, HACMP cluster;

Memory consumption based od db2pd
Physical Memory and Swap (Megabytes):
TotalMem FreeMem AvailMem TotalSwap FreeSwap
12288 92 n/a 3584 2981

On this AIX system is installed only DB2; there is only one DB2
database

Bufferpool info:

1 IBMDEFAULTBP 8192 1008
2 ICMS_SIFARNICI 8192 1000
3 ICMS_DATA 8192 9794
4 ICMS_LOB 8192 1000
5 ICMS_INDEX 8192 1010
6 ICMS_STAT 8192 1004
4096 IBMSYSTEMBP4K 4096 16
4097 IBMSYSTEMBP8K 8192 16
4098 IBMSYSTEMBP16K 16384 16
4099 IBMSYSTEMBP32K 32768 16

Interesting bufferpools are started with "ICMS"

For that bufferpools, hit ratio is, based on db2pd poor-40% to 60%. Looking at sysibm.sysbufferpools get me npages=-2, meaning that there are automatic.

Here is also few interesting memory configuration from get db cfg:

database_page_size=8192
SELF_TUNING_MEMORY =ON
DATABASE_MEMORY = 450000
UTIL_HEAP_SZ = 308622
BUFFPAGE = 1000
DBHEAP = 3351

So, gurus, please help me with answers on this:
1) What memory locations are "inside DATABASE_MEMORY"? Are bufferpools are inside 450 000 or outside?

2) Same questions on UTIL_HEAP_SZ which is obviously set too high.

3) What is a connection between size of bufferpools and BUFFPAGE parameter? It is sound to me that "although bufferpools are automatic, high water mark is value of buffpage (in my situation, it is 1000 pages). In the same time, how is posibble that bufferpool ICMS_DATA was increased to 9794 pages?

4) Where all my memory was lost? I thought that entering DATABASE_MEMORY=450000 meaning that all my memory locations are inside 450000, like Oracle 11, but i am obviously wrong.

Thanks a lot for Your advices, Zvonimir

P.S. Of course, users are unsatisfied with "application", in fact with waiting on select stmt.
Disk subsystem is IBM Total Storage, 2 years old, i believe "good".

I just noticed very interesting thing to me;
parameter DATABASE_MEMORY is calculated in 4k pages.

Is that mean that, in DB2 9.1., database memory is possible only if
database page size is 4096?

But, again, where is my memory lost? Is it possible that high I/O
consume very high memory consumption?

Thanks a lot, again

Any help, gurus?
Thanks

The amount of bufferpool memory is the page size times the number of pages. Could you please tell us how many pages there are in your bufferpools (can't tell for sure from your report since it has no column headings).

BTW, ICMS_LOB is is a total waste of memory, since LOB's don't user bufferpools (and read or write access to them is typically 10 times slower than other data types). If you have a tablespace that you have dedicated to LOB's, just assign it to one of the other bufferpools with the same page size and drop that one bufferpool you dedicated for LOB's.

If the last number in your report of bufferpools is the number of pages, you have grossly undersized them unless you are running DB2 on an iPod. Bufferpools typically are allocated to use about 50% of the total server memory. Note that this assumes that the only major application running on the server is DB2, and you must account for all databases on that server and add up the bufferpools. However, the total of all bufferpools should not ever exceed the total size of the tables and indexes, or you are wasting memory.

I am a bit confused by the topic title and the content. Do you or don't you want to use STMM on your machine? I agree with Marcus about the LOB bufferpools, but I would give STMM control over the remaining bufferpools.
Usefull read: How to setup the Self Tuning Memory Manager (STMM) | db2ude

Of course, i want STMM.
So, next is entered:
Self tuning memory (SELF_TUNING_MEM) = ON
Size of database shared memory (4KB) (DATABASE_MEMORY) = 450000
Database memory threshold (DB_MEM_THRESH) = 10
Max storage for lock list (4KB) (LOCKLIST) = AUTOMATIC
Percent. of lock lists per application (MAXLOCKS) = AUTOMATIC
Package cache size (4KB) (PCKCACHESZ) = AUTOMATIC
Sort heap thres for shared sorts (4KB) (SHEAPTHRES_SHR) = AUTOMATIC
Sort list heap (4KB) (SORTHEAP) = AUTOMATIC

Also, You see here (in bufferpools.txt) that, although STMM is switched on, all bufferpools except one are very tiny.
In second txt file (osinfo.txt), You can see that 12 GB memory is lost.

So:
1) i turned on STMM
2) i lost all memory
3) my bufferpools are extremely tiny.

My first question is:
Im i have to turn off STMM to be able to increase bufferpools? Obviously, i can increase it for few 1000 pages because all my physical memory mysteriously was eaten by DB2.

Thanks a lot for Your answers, again

P.S. I am not AIX guru

Did you enable all your bufferpools as well? Do you notice the grow/shrink of you bufferpools in db2diag.log? Do you use db2top to monitor the size of your bufferpools.

AND, very important: are we talking about 64bit aix and 64bit DB2? (if you do not know just post the first 50 lines of db2diag.log)

You can see what's part of database shared memory here:
DB2 Database for Linux, UNIX, and Windows

database_memory is set to ~1.7 GB. The server has 12GB of memory and you mentioned that DB2 is the only application running on this server. Where did the rest of your memory go? Do you have many db2agent processes running on the server? How many of them are idle?


You can use db2mtrk/db2pd to see how much memory is being used by db2. Also, please take a look at the following technote for the latest recommended AIX VMM settings:
IBM - Recommended AIX Virtual Memory Manager settings for DB2

Wanna bet Bella? This is a 32bit db2 installation.
I've got the same here now at my client's site. When db2 is installed as part of a content manager 8.3 install, you'll get the 32 bit version. I've got a 24 Gb server and cannot allocate more than 1,2 for my bufferpools...bit frustrating.... really looking foreward to that CM8.4 upgrade...

What do you wanna bet?

zkajfez said they're running DB2 v9.1 on AIX - so must be 64-bit. I think you have v8.

DB2level told me it is 64 bit DB2 (on 64 bit AIX).

All bufferools are set to AUTOMATIC (NPAGES=-2 in SYSIBM.SYSBUFFERPOOLS)
Only one 8K bufferpools increase/decrease in size; it is bufferpool named ICMS_DATA; it's ID is 3. All other bufferpools "containing" user's data(ICMS_INDEX, ICMS_STAT and ICMS_SIFARNICI) are 1000, 1014 and 1004 pages respectively and could not increase/decrese in size

Finally, i found AUTOCONFIGURE command

This is recommendations for bufferpools in configure.txt.

Can anybode please interpret why automatic configuration
of bufferpools is not recommended in DB2 9.1

Thanks a lot

I don't know why autoconfigure made this suggestion... but Marcus_A has suggested many times on this forum not to let STMM tune the bufferpools. Here is one example here:
DB2 Memory Problems


I'd also suggest to check the number of db2agent processes you have running on this system / num_initagents and num_poolagents dbm cfg.

Extremelly valuable information, db2girl, thanks a lot