Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2009
    Posts
    4

    Question Unanswered: DB2 with DPF/STMM doesn't use memory

    Dear all,

    we are wondering about the following issue:

    DB2 DPF 9.5 ESE SP3 on AIX
    20 GB Mem
    12 CPU
    STMM activated.

    We have massive Waits and asynchr I/O so we gave more MEM and decreased filesystemcaching with the aim to provide a maximum of memory for bufferpools. -> RESTART -> but even after waiting for some days we still have 8gb free mem.

    MB Used 11861.3MB
    MB Free 8618.6MB

    Why doesn't the db use the mem?

    DB2TOP shows:
    Database PDEDW BufferPool 65.32% 4.2G 4.2G 0.39% 1.0T 54

    Any ideas?

    Thanks

    Mala

  2. #2
    Join Date
    Jan 2009
    Location
    Zoetermeer, Holland
    Posts
    746
    Please post your dbm & db cfs's and the result of "db2mtrk -i -d"

  3. #3
    Join Date
    Sep 2009
    Posts
    4
    Hi,

    thanks for your reply. Maybe I found the reason:

    STMM is a very useful tool, but automatic doesn't replace intelligence. When using STMM in a DPF environment you have to be careful how you use or you run in a szenario like we did. First you need to know how it works in a dpf environment. When all Partitions are set to STMM ON, by default it takes the first data partition, optimizes it and sends the configs (parallel) to the other partitions.


    db2 => CALL SYSPROC.ADMIN_CMD( 'get stmm tuning dbpartitionnum' )


    Result set 1
    --------------

    USER_PREFERRED_NUMBER CURRENT_NUMBER
    --------------------- --------------
    1 1

    1 record(s) selected.

    Return Status = 0


    If the chosen tuning partition is not representative (as was with us), you run into performance issues. We have the first partition populated with normal tables, while 4 other partitions host the distributed tables.

    So we need to selectivly run stmm on those groups of partitions that are representative for its peer group.

    Ill do that over the weekend ..

    Here is the output of - I am not familiiar with it, could you explain what you see?

    db2mtrk -i -d

    Tracking Memory on: 2009/09/04 at 11:28:14

    Memory for instance

    other monh fcmbp
    131.0M 320.0K 79.1M

    Memory for database: PDEDW

    utilh pckcacheh other catcacheh bph (5) bph (4)
    64.0K 11.4M 192.0K 2.2M 31.8M 16.2M

    bph (3) bph (2) bph (1) bph (S32K) bph (S16K) bph (S8K)
    8.2M 631.5M 31.8M 832.0K 576.0K 448.0K

    bph (S4K) shsorth lockh dbh apph (11057)apph (10776)
    384.0K 384.0K 124.9M 35.3M 64.0K 64.0K

    apph (10775)apph (5399) apph (4601) apph (723) apph (100) apph (65587)
    64.0K 64.0K 64.0K 192.0K 384.0K 64.0K

    apph (55) apph (54) apph (53) appshrh
    64.0K 64.0K 64.0K 2.0M



    Many regards
    Mala

  4. #4
    Join Date
    Jan 2009
    Location
    Zoetermeer, Holland
    Posts
    746
    In your 1st post you claim to have "stmm activated". Please read this How to setup the Self Tuning Memory Manager (STMM) | db2ude because I'm not sure if STMM "works as designed" in you config.
    I asked for the DBM config because it is essential that you have a 64bit version of DB2 installed to be able to comsume the 20 Gb available.
    With the results of you db2mtrk I am not suprised that your database does not perform too wel, you bufferpools are extreme small.... I've got bigger on my laptop...

    Bottomline: set your bufferpools to "AUTOMATIC" and tail you db2diag.log and notice the bufferpools getting extended. If you see that you will notice that DB2 will perform better. Allow DB2-STMM a few hours to settle in.

  5. #5
    Join Date
    Sep 2009
    Posts
    4
    Thx for your answer,

    yes its 64 bit. We just changed the "to tune DB" to the right partition.

    Regarding the bufferpools - they sum to 4.2 GB:

    (DB2top)
    Database PDEDW BufferPool 63.94% 4.2G 4.2G 0.39% 1.0T 54

    After we switched the "Partition to be optimised" and some waiting, the Bufferpools haven't been updated so far. the Nfy file show index rebuilts but no change in BP. We'll wait for some more minutes.

    BP Settings:

    db2 "SELECT BPNAME, NPAGES, PAGESIZE FROM SYSCAT.BUFFERPOOLS"

    BPNAME NPAGES PAGESIZE
    -------------------------------------------------------------------------------------------------------------------------------- ----------- -----------
    IBMDEFAULTBP -2 32768
    BP_4K -2 4096
    BP_8K -2 8192
    BP_16K -2 16384
    BP_32K -2 32768

    BUT:

    *** stmmCostBenefitRecord ***
    Type: BUFFER POOL ( BP_16K )
    PageSize: 16384
    Saved Misses: 11
    Benefit:
    -> Simulation size: 1024
    -> Total seconds saved: 0 (+ 13129182 ns)
    -> Normalized seconds/page: 1.06769E-07
    Cost:
    -> Simulation size: 1024
    -> Total seconds saved: 0 (+ 13129182 ns)
    -> Normalized seconds/page: 1.06769E-07
    Current Size: 1004
    Minimum Size: 1000
    Potential Increase Amount: 502
    Potential Increase Amount From OS: 502
    Potential Decrease Amount: 0
    Pages Available For OS: 0
    Interval Time: 30.0215

    -> STMM.log shows that it wants to increase the BP - but somehow it doesn't..

    EDIT: Pls see post below: BP have been increased by STMM



    see DB CFG/ DBM CFG as attached

    piedw_PDEDW_DBM_CFG.txt
    db CFG: shows the config -> they differ between the partitions only in the SELF_TUNING_MEM, LOG_FILESIZE,LOGPATH, LOGPRIMARY
    db2 get db.txt: show actual values of DB CFG (db2 "SELECT NAME, DEFERRED_VALUE, DBPARTITIONNUM FROM SYSIBMADM.DBCFG order by name,DBPARTITIONNUM" | tr -s " " )


    There is an more in depth explanation that I prefer to the one u mentioned:

    ftp://ftp.software.ibm.com/software/...apers/stmm.pdf


    Many regards
    Mala
    Attached Files Attached Files
    Last edited by Malapha; 09-04-09 at 16:10.

  6. #6
    Join Date
    Sep 2009
    Posts
    4
    After waiting some hours, BP16k increased from 6000 pages to 47000 pages using about 1GB BP mem.. :-) hopefully this will increase further over time..

    Many regards

    Mala

    PS If you have any suggestions regarding the cfgs please tell me..

    EDIT:

    Checking everything there is something strange.

    We set:

    [pdb_f204:/pdb_f204/db2/dump/piedw] >db2 "SELECT NAME, DEFERRED_VALUE, DBPARTITIONNUM FROM SYSIBMADM.DBCFG order by DBPARTITIONNUM,name" | tr -s " " | grep -i self
    self_tuning_mem OFF 0
    self_tuning_mem OFF 1
    self_tuning_mem ON 2
    self_tuning_mem ON 3
    self_tuning_mem ON 4
    self_tuning_mem ON 5


    because DBPNo 0 (Coord) and 1 is a non-representative Partition.

    We set:
    db2 => CALL SYSPROC.ADMIN_CMD( 'get stmm tuning dbpartitionnum' )


    Result set 1
    --------------

    USER_PREFERRED_NUMBER CURRENT_NUMBER
    --------------------- --------------
    2 2

    1 record(s) selected.

    Return Status = 0


    But I find (hours after the CFG-Change):

    2009-09-04-20.26.02.401215+120 I248742833A501 LEVEL: Info
    PID : 2146392 TID : 33488 PROC : db2sysc 1
    INSTANCE: piedw NODE : 001 DB : PDEDW
    APPHDL : 2-2166 APPID: *N2.DB2.090904110026
    AUTHID : PIEDW
    EDUID : 33488 EDUNAME: db2agntp (PDEDW) 1
    FUNCTION: DB2 UDB, buffer pool services, sqlbAlterBufferPoolAct, probe:90
    MESSAGE : Altering bufferpool "BP_16K" From: "5082" <automatic> To: "7623"
    <automatic>

    2009-09-04-20.26.02.421245+120 I248743335A501 LEVEL: Info
    PID : 1785982 TID : 37940 PROC : db2sysc 0
    INSTANCE: piedw NODE : 000 DB : PDEDW
    APPHDL : 2-2166 APPID: *N2.DB2.090904110026
    AUTHID : PIEDW
    EDUID : 37940 EDUNAME: db2agntp (PDEDW) 0
    FUNCTION: DB2 UDB, buffer pool services, sqlbAlterBufferPoolAct, probe:90
    MESSAGE : Altering bufferpool "BP_16K" From: "5082" <automatic> To: "7623"
    <automatic>

    So STMM changed the config to the Partitions I wanted fixed..

    Is setting SELF_TUNING_MEM to off not sufficiant? I thought it overruled the other automatics like bufferpools...

    Thanks alot
    Mala
    Last edited by Malapha; 09-04-09 at 16:07.

Posting Permissions

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