Results 1 to 6 of 6

Thread: Adding memory

  1. #1
    Join Date
    Jan 2003
    Provided Answers: 5

    Unanswered: Adding memory

    Our system DB2 9.5 FP 7 on RedHat linux 5.

    In an effort to increase performance on the server, management requested adding more memory to the servers. The current memory is at 32GB and they are upping it to 64GB. My question, is what would be the best way to allocate it to DB2? Right now linux is using it for cache.

    DB cfg:

           Database Configuration for Database
     Database configuration release level                    = 0x0c00
     Database release level                                  = 0x0c00
     Database territory                                      = US
     Database code page                                      = 1208
     Database code set                                       = UTF-8
     Database country/region code                            = 1
     Database collating sequence                             = IDENTITY
     Alternate collating sequence              (ALT_COLLATE) =
     Number compatibility                                    = OFF
     Varchar2 compatibility                                  = OFF
     Database page size                                      = 4096
     Dynamic SQL Query management           (DYN_QUERY_MGMT) = DISABLE
     Discovery support for this database       (DISCOVER_DB) = DISABLE
     Restrict access                                         = NO
     Default query optimization class         (DFT_QUERYOPT) = 5
     Degree of parallelism                      (DFT_DEGREE) = 1
     Continue upon arithmetic exceptions   (DFT_SQLMATHWARN) = NO
     Default refresh age                   (DFT_REFRESH_AGE) = 0
     Default maintained table types for opt (DFT_MTTB_TYPES) = SYSTEM
     Number of frequent values retained     (NUM_FREQVALUES) = 10
     Number of quantiles retained            (NUM_QUANTILES) = 20
     Decimal floating point rounding mode  (DECFLT_ROUNDING) = ROUND_HALF_EVEN
     Backup pending                                          = NO
     Database is consistent                                  = NO
     Rollforward pending                                     = NO
     Restore pending                                         = NO
     Multi-page file allocation enabled                      = YES
     Log retain for recovery status                          = RECOVERY
     User exit for logging status                            = YES
     Self tuning memory                    (SELF_TUNING_MEM) = ON
     Size of database shared memory (4KB)  (DATABASE_MEMORY) = AUTOMATIC(4322403)
     Database memory threshold               (DB_MEM_THRESH) = 100
     Max storage for lock list (4KB)              (LOCKLIST) = AUTOMATIC(524288)
     Percent. of lock lists per application       (MAXLOCKS) = AUTOMATIC(96)
     Package cache size (4KB)                   (PCKCACHESZ) = AUTOMATIC(524288)
     Sort heap thres for shared sorts (4KB) (SHEAPTHRES_SHR) = AUTOMATIC(2314037)
     Sort list heap (4KB)                         (SORTHEAP) = AUTOMATIC(462807)
     Database heap (4KB)                            (DBHEAP) = AUTOMATIC(2048)
     Catalog cache size (4KB)              (CATALOGCACHE_SZ) = 1600
     Log buffer size (4KB)                        (LOGBUFSZ) = 32
     Utilities heap size (4KB)                (UTIL_HEAP_SZ) = 7500
     Buffer pool size (pages)                     (BUFFPAGE) = 1000
     SQL statement heap (4KB)                     (STMTHEAP) = 32768
     Default application heap (4KB)             (APPLHEAPSZ) = AUTOMATIC(4096)
     Application Memory Size (4KB)             (APPL_MEMORY) = AUTOMATIC(40016)
     Statistics heap size (4KB)               (STAT_HEAP_SZ) = AUTOMATIC(8192)
     Interval for checking deadlock (ms)         (DLCHKTIME) = 10000
     Lock timeout (sec)                        (LOCKTIMEOUT) = 30
     Changed pages threshold                (CHNGPGS_THRESH) = 50
     Number of asynchronous page cleaners   (NUM_IOCLEANERS) = AUTOMATIC(15)
     Number of I/O servers                   (NUM_IOSERVERS) = AUTOMATIC(3)
     Index sort flag                             (INDEXSORT) = YES
     Sequential detect flag                      (SEQDETECT) = YES
     Default prefetch size (pages)         (DFT_PREFETCH_SZ) = AUTOMATIC
     Track modified pages                         (TRACKMOD) = ON
     Default number of containers                            = 1
     Default tablespace extentsize (pages)   (DFT_EXTENT_SZ) = 32
     Max number of active applications            (MAXAPPLS) = AUTOMATIC(590)
     Average number of active applications       (AVG_APPLS) = AUTOMATIC(1)
     Max DB files open per application            (MAXFILOP) = 61440
     Log file size (4KB)                         (LOGFILSIZ) = 10000
     Number of primary log files                (LOGPRIMARY) = 30
     Number of secondary log files               (LOGSECOND) = -1
     Changed path to log files                  (NEWLOGPATH) =
     Path to log files                                       = /atl_logs/db2insta/logs/NODE0000/
     Overflow log path                     (OVERFLOWLOGPATH) = /atl_logs/db2insta/logs/overflow/NODE0000/
     Mirror log path                         (MIRRORLOGPATH) =
     First active log file                                   = S0055514.LOG
     Block log on disk full                (BLK_LOG_DSK_FUL) = NO
     Block non logged operations            (BLOCKNONLOGGED) = NO
     Percent max primary log space by transaction  (MAX_LOG) = 0
     Num. of active log files for 1 active UOW(NUM_LOG_SPAN) = 0
     Group commit count                          (MINCOMMIT) = 1
     Percent log file reclaimed before soft chckpt (SOFTMAX) = 80
     Log retain for recovery enabled             (LOGRETAIN) = RECOVERY
     User exit for logging enabled                (USEREXIT) = OFF
     HADR database role                                      = STANDARD
     HADR local host name                  (HADR_LOCAL_HOST) =
     HADR local service name                (HADR_LOCAL_SVC) =
     HADR remote host name                (HADR_REMOTE_HOST) =
     HADR remote service name              (HADR_REMOTE_SVC) =
     HADR instance name of remote server  (HADR_REMOTE_INST) =
     HADR timeout value                       (HADR_TIMEOUT) = 120
     HADR log write synchronization mode     (HADR_SYNCMODE) = NEARSYNC
     HADR peer window duration (seconds)  (HADR_PEER_WINDOW) = 0
     First log archive method                 (LOGARCHMETH1) = DISK:/atl_logs/db2insta/archive/
     Options for logarchmeth1                  (LOGARCHOPT1) =
     Second log archive method                (LOGARCHMETH2) = OFF
     Options for logarchmeth2                  (LOGARCHOPT2) =
     Failover log archive path                (FAILARCHPATH) =
     Number of log archive retries on error   (NUMARCHRETRY) = 5
     Log archive retry Delay (secs)         (ARCHRETRYDELAY) = 20
     Vendor options                              (VENDOROPT) =
     Auto restart enabled                      (AUTORESTART) = ON
     Index re-creation time and redo index build  (INDEXREC) =
     Log pages during index build            (LOGINDEXBUILD) = OFF
     Default number of loadrec sessions    (DFT_LOADREC_SES) = 1
     Number of database backups to retain   (NUM_DB_BACKUPS) = 12
     Recovery history retention (days)     (REC_HIS_RETENTN) = 366
     Auto deletion of recovery objects    (AUTO_DEL_REC_OBJ) = OFF
     TSM management class                    (TSM_MGMTCLASS) =
     TSM node name                            (TSM_NODENAME) =
     TSM owner                                   (TSM_OWNER) =
     TSM password                             (TSM_PASSWORD) =
     Automatic maintenance                      (AUTO_MAINT) = OFF
       Automatic database backup            (AUTO_DB_BACKUP) = OFF
       Automatic table maintenance          (AUTO_TBL_MAINT) = OFF
         Automatic runstats                  (AUTO_RUNSTATS) = OFF
           Automatic statement statistics  (AUTO_STMT_STATS) = OFF
         Automatic statistics profiling    (AUTO_STATS_PROF) = OFF
           Automatic profile updates         (AUTO_PROF_UPD) = OFF
         Automatic reorganization               (AUTO_REORG) = OFF
     Enable XML Character operations        (ENABLE_XMLCHAR) = YES
     WLM Collection Interval (minutes)     (WLM_COLLECT_INT) = 0
    The bufferpools are fixed in size and use about 1GB of memory.

    Last edited by ARWinner; 03-21-11 at 16:22.

  2. #2
    Join Date
    Jun 2003
    Toronto, Canada
    Provided Answers: 1
    As indicated by your database configuration parameters, it uses about 16 GB of memory, that is, half of the actual server memory. Clearly, if the expectation is that more memory will improve the database performance, one or more memory bottlenecks have been identified, and it seems appropriate to allocate additional memory to the heaps that are starving. You might want to look at the bufferpool hit ratios and increase them where warranted.

  3. #3
    Join Date
    Jan 2003
    Provided Answers: 5
    The bufferpool hit ratio is usually above 90%, so that is not the issue. Most of the other caches are set to automatic with STMM turned on. Shouldn't DB2 start using the additional memory? I used db2pd to look at the memory before the upgrade and then again after and the amount of memory DB2 was using did not change much. The linux free command shows that the amount of cache is huge after the upgrade. I was hoping that someone had some insight as to where we could allocate the additional memory in DB2. Increasing the bufferpools is an option, but I do not think it will help much, since the hit ratios are already high, maybe just mask other issues.


  4. #4
    Join Date
    Aug 2001
    If it has not changed much, I assume db2 already has had enough memory.
    As n_i suggested, bufferpool appears to be the best place to make use of the additional memory.

    Your locklist and package cache are 2 gb each... in comaprison, bufferpool is small ..
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  5. #5
    Join Date
    May 2003
    Quote Originally Posted by ARWinner View Post
    The bufferpool hit ratio is usually above 90%, so that is not the issue.
    I don't believe that merely being "above 90%" is acceptable for most queries. I would definitely allocate at least half the server memory to bufferpools (so long as you don't exceed database size). This assumes that there are no other major applications on the same server (besides your DB2 databases).

    Quote Originally Posted by ARWinner View Post
    Most of the other caches are set to automatic with STMM turned on. Shouldn't DB2 start using the additional memory?
    I personally have not found STMM to work well. It gives up memory to quickly, and is slow to get it back when needed (especially under Linux). Make sure your Linux kernel parms are set to the recommended values in the DB2 doc (there have been changes to the recommended values in the latest fixpacks). You might want to at least hard-code instance memory to be about 85% of server memory (total for all instances).
    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
    Zoetermeer, Holland
    I've read this thread twice. Let me recap:
    - you've got a 32Gb database server with 1Gb bufferpools fixed
    - you decide to add another 32Gb although you think the bufferpools do not require anything extra
    - and now you expect .......... what?

    What gave you the idea that a bufferpool hitrate about 90% is good enough? If you do not want STMM to manage the bufferpools (suit yourself) just feed all that extra storage to your bufferpools yourself. See what happens.

Posting Permissions

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