Db2

 View Only
Expand all | Collapse all

What do I get wrong with memory information on 11.1?

  • 1.  What do I get wrong with memory information on 11.1?

    IBM Champion
    Posted Thu January 16, 2020 11:06 AM
    Hi there

    I don't know if I get something wrong or if this is a bug.
    A colleague activated a memory monitoring within check_mk on one of our newer Db2 servers and it's showing strange results.
    After investigation I see that these results are coming from Db2 itself.

    (changed some names due to security restrictions)

    We're running Redhat on an AS/400 IBMi Power partition.
    [db2inst1@... ~]$ cat /etc/redhat-release
    Red Hat Enterprise Linux Server release 7.6 (Maipo)​
    
    [db2inst1@... ~]$ uname -a
    Linux ... 3.10.0-957.el7.ppc64le #1 SMP Thu Oct 4 20:51:36 UTC 2018 ppc64le ppc64le ppc64le GNU/Linux

    Db2 is a 11.1.4.4 for PowerPC Little Endian (from 11.1 on there are only Little Endian versions for PPC except AIX)
    [db2inst1@... ~]$ db2level
    DB21085I  This instance or install (instance name, where applicable:
    "db2inst1") uses "64" bits and DB2 code release "SQL11014" with level
    identifier "0205010F".
    Informational tokens are "DB2 v11.1.4.4", "s1811091400", "DYN1811091400PPCLE",
    and Fix Pack "4".
    Product is installed at "/opt/ibm/db2/V11.1_FP4".​

    Looking into the operating system it shows 255GB memory without swap.
    [db2inst1@... ~]$ free -h
                  total        used        free      shared  buff/cache   available
    Mem:           255G        2.7G        106G        129G        146G        121G
    Swap:          4.0G          0B        4.0G
    ​
    We're running three databases within one instance.

    First strange thing is, db2pd is saying we're using more memory than we're limited to (see memory limit and current usage):
    [db2inst1@... ~]$ db2pd -dbptnmem
    
    Database Member 0 -- Active -- Up 239 days 00:52:46 -- Date 2020-01-16-11.04.38.049815
    
    Database Member Memory Controller Statistics
    
    Controller Automatic: Y
    Controller License Limit: N
    Controller Limit Enforced: N
    
    Memory Limit:         249190148 KB
    Current usage:        304772928 KB
    HWM usage:            347817920 KB
    Cached memory:         27731584 KB
    
    Individual Memory Consumers:
    
    Name             Mem Used (KB) HWM Used (KB) Cached (KB)
    ========================================================
    APPL-A                   29632        160000       23040
    APPL-B                  160000        160000      141952
    APPL-C                  160000        160000      153984
    DBMS-db2inst1           175552        175552        2048
    FMP_RESOURCES            22528         22528       20992
    PRIVATE                 104128        165824           0
    DB-C                 147150528     172275840    12492032
    DB-B                  62881792      63157376     6236864
    DB-A                  94088768      94088768     8660672
    ​


    Additionally the sum of all three database memory is higher than the instance memory:

    [db2inst1@... ~]$ db2 get dbm cfg show detail| grep -i instance_memory
     Global instance memory (% or 4KB)     (INSTANCE_MEMORY) = AUTOMATIC(62297537)        AUTOMATIC(62297537)
    
    [db2inst1@... ~]$ db2 connect to A>/dev/null;db2 get db cfg for A show detail | grep -i database_memory
     Size of database shared memory (4KB)  (DATABASE_MEMORY) = AUTOMATIC(23522246)        AUTOMATIC(23522246)
    
    [db2inst1@... ~]$ db2 connect to B>/dev/null;db2 get db cfg for B show detail | grep -i database_memory
     Size of database shared memory (4KB)  (DATABASE_MEMORY) = AUTOMATIC(15720512)        AUTOMATIC(15626168)
    
    [db2inst1@... ~]$ db2 connect to C>/dev/null;db2 get db cfg for C show detail | grep -i database_memory
     Size of database shared memory (4KB)  (DATABASE_MEMORY) = AUTOMATIC(37694304)        AUTOMATIC(37694304)
    

    We've got a second system with Db2 10.5.0.8 (Big Endian) on a similar operating system which is showing plausible memory values.
    Do I get something wrong here?
    Is db2pd buggy (maybe only in the little endian version for power)?

    If no one could explain that to me I might raise a PMR.

    ------------------------------
    ----------------
    Sebastian Zok
    Db2 Database Administrator
    ------------------------------

    #Db2


  • 2.  RE: What do I get wrong with memory information on 11.1?

    Posted Fri January 17, 2020 03:39 PM
    Here's an answer straight from IBM support Q&A. Cheers!

    https://www.ibm.com/support/pages/db2-instance-memory-vs-system-memory
    Instance Memory is a high-level controller of DB2 dynamic configuration, and DB2's system memory usage will be some subset of this



    ------------------------------
    Hung Tam Nguyen
    ------------------------------



  • 3.  RE: What do I get wrong with memory information on 11.1?

    IBM Champion
    Posted Mon January 20, 2020 01:51 AM
    I know what instance and system memory is but that doesn't answer my questions.

    ------------------------------
    Sebastian Zok
    ------------------------------



  • 4.  RE: What do I get wrong with memory information on 11.1?

    Posted Mon January 20, 2020 10:57 AM
    In other words "tools reporting instance memory usage (such as db2pd -dbptnmem) tend to state a higher value than operating system tools."

    ------------------------------
    Hung Tam Nguyen
    ------------------------------



  • 5.  RE: What do I get wrong with memory information on 11.1?

    IBM Champion
    Posted Tue January 21, 2020 01:55 AM
    I'm totally fine with the difference "free" and db2pd are showing but why is db2pd in itself showing inconsistent data?
    How could the current usage be higher than the limit?
    Memory Limit:         249190148 KB
    Current usage:        304772928 KB​
    Secondly how could the sum of all database memory within an instance be higher than the instance memory itself.
    By definition and documentation the database memory is taken from the instance memory.

    ------------------------------
    Sebastian Zok
    ------------------------------



  • 6.  RE: What do I get wrong with memory information on 11.1?

    Posted Tue January 21, 2020 11:27 AM
    I see what you mean, can you run this search on database C and post the results?

    db2 get db cfg show detail | grep AUTOM

    ------------------------------
    Hung Tam Nguyen
    ------------------------------



  • 7.  RE: What do I get wrong with memory information on 11.1?

    IBM Champion
    Posted Wed January 22, 2020 02:12 AM
    Here you go ... what are your thoughts?
    [db2inst1]$ db2 get db cfg for C show detail | grep AUTOM
     Size of database shared memory (4KB)  (DATABASE_MEMORY) = AUTOMATIC(37694304)        AUTOMATIC(37694304)
     Package cache size (4KB)                   (PCKCACHESZ) = AUTOMATIC(8192)            AUTOMATIC(8192)
     Database heap (4KB)                            (DBHEAP) = AUTOMATIC(10193)           AUTOMATIC(10193)
     Utilities heap size (4KB)                (UTIL_HEAP_SZ) = AUTOMATIC(3129074)         AUTOMATIC(3129074)
     Default application heap (4KB)             (APPLHEAPSZ) = AUTOMATIC(256)             AUTOMATIC(256)
     Application Memory Size (4KB)             (APPL_MEMORY) = AUTOMATIC(40000)           AUTOMATIC(40000)
     Statistics heap size (4KB)               (STAT_HEAP_SZ) = AUTOMATIC(4384)            AUTOMATIC(4384)
     Number of asynchronous page cleaners   (NUM_IOCLEANERS) = AUTOMATIC(1)               AUTOMATIC(1)
     Number of I/O servers                   (NUM_IOSERVERS) = AUTOMATIC(12)              AUTOMATIC(12)
     Default prefetch size (pages)         (DFT_PREFETCH_SZ) = AUTOMATIC                  AUTOMATIC
     Max number of active applications            (MAXAPPLS) = AUTOMATIC(40)              AUTOMATIC(40)
     Average number of active applications       (AVG_APPLS) = AUTOMATIC(1)               AUTOMATIC(1)
     HADR spool log data limit (4KB)      (HADR_SPOOL_LIMIT) = AUTOMATIC(0)               AUTOMATIC(0)
     Target agent load per CPU core    (WLM_AGENT_LOAD_TRGT) = AUTOMATIC(11)              AUTOMATIC(11)​


    The real strange thing is that we have another system I've build this one from, running on Db2 10.5 which is showing completly reasonable numbers.
    Our monitoring is taking the "db2pd -dbptnmem" (standard implementation in check_mk) and is going crazy on the 11.1 system as its measuring -39% free memory

    ------------------------------
    Sebastian Zok
    ------------------------------



  • 8.  RE: What do I get wrong with memory information on 11.1?

    Posted Wed January 22, 2020 11:03 AM
    Memory limit as shown in db2pd is just a calculated value, it's not enforced in this case since there are no restrictions at the license level and INSTANCE_MEMORY is set to automatic. It just calculates to about 95% of physical RAM here.

    DATABASE_MEMORY total was then allowed to be set to a higher value, but that's not the actually memory committed since there's only 256GB of physical RAM obviously.

    First safe thing I would try is to update DATABASE_MEMORY to AUTOMATIC just to make sure it was not set to AUTOMATIC but with a minimum value sometime in the past. (ie 37694304 AUTOMATIC)

    db2 update db cfg using DATABASE_MEMORY AUTOMATIC

    I was thinking since both INSTANCE_MEMORY and DATABASE_MEMORY are automatic and SELF_TUNING_MEM is probably ON as well, there must be some memory pool over-allocated somewhere.  Infocenter says DATABASE_MEMORY includes bufferpools, the database heap, the locklist, the utility heap, the package cache, the catalog cache, the shared sort heap, and an additional minimum overflow area of 7% in this case.  

    As it turns out, LOCKLIST and MAXLOCKS are not set to automatic on database C.  Is the LOCKLIST there very large?

    This command among others can be used to find what contributes to the big DATABASE_MEMORY total on C:
    db2pd -db C -memsets -mempools


    ------------------------------
    Hung Tam Nguyen
    ------------------------------



  • 9.  RE: What do I get wrong with memory information on 11.1?

    Posted Thu January 23, 2020 02:54 AM
    My experience was that the command "db2pd" is king.
    All DB2 processes "think" that the all amount of available memory was that showed from the command.
    From that value and the effective value of memory available/used ... there is operating system policies.
    If you want to control the effective use of memory ... pay maximum attention to every parameter expecially that with also the AUTOMATIC setting. And also, STMM was good but, for my production DB's, it arrive always too late ... and let some setting to strange values (unwanted 99,9% cases).

    As of I have seen from the precedent post, I see that you have an huge memory consumer UTIL_HEAP_SZ and I think that is due for some strange reason (or this setting was really wanted from you ?!?).
    In my Production Database (rare use of LOAD/UNLOAD and similar that really benefit from this setting) the UTIL_HEAP_SZ was set to a very low value (1000 AUTOMATIC).


    ------------------------------
    Adriano Mari
    ------------------------------



  • 10.  RE: What do I get wrong with memory information on 11.1?

    Posted Fri January 24, 2020 02:59 AM
    Hello, 

    If you want to retrieve memory information you can have a look at the MON_GET monitor functions that come with Db2. They have an SQL interface and are very easy to use and to interpret.

    MON_GET_MEMORY_SET and MON_GET_MEMORY_POOL.


    ------------------------------
    Guido Verbraak
    IBM Data & AI Lab Services
    ------------------------------