Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Jan 2002
    Posts
    165

    Unanswered: Automatic memory and poor hit ratio

    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".
    Last edited by zkajfez; 12-11-09 at 04:17.

  2. #2
    Join Date
    Jan 2002
    Posts
    165
    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

  3. #3
    Join Date
    Jan 2002
    Posts
    165
    Any help, gurus?
    Thanks

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    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).
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  5. #5
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    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.
    Last edited by Marcus_A; 12-13-09 at 15:25.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  6. #6
    Join Date
    Jan 2009
    Location
    Zoetermeer, Holland
    Posts
    746
    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

  7. #7
    Join Date
    Jan 2002
    Posts
    165
    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
    Attached Files Attached Files

  8. #8
    Join Date
    Jan 2009
    Location
    Zoetermeer, Holland
    Posts
    746
    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)

  9. #9
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    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

  10. #10
    Join Date
    Jan 2009
    Location
    Zoetermeer, Holland
    Posts
    746
    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...

  11. #11
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    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.

  12. #12
    Join Date
    Jan 2002
    Posts
    165
    Quote Originally Posted by dr_te_z View Post
    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)
    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

  13. #13
    Join Date
    Jan 2002
    Posts
    165
    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
    Attached Files Attached Files

  14. #14
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    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:
    http://www.dbforums.com/db2/1651206-...roblems-2.html


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

  15. #15
    Join Date
    Jan 2002
    Posts
    165
    Extremelly valuable information, db2girl, thanks a lot

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •