Adding memory

ARWinner · #1 (**permalink**) 03-21-11, 15:29

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:

Code:

       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.

Andy

n_i · #2 (**permalink**) 03-21-11, 16:20

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.

ARWinner · #3 (**permalink**) 03-21-11, 16:28

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.

Andy

sathyaram_s · #4 (**permalink**) 03-21-11, 19:01

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 ..

Marcus_A · #5 (**permalink**) 03-22-11, 00:45

Quote:

Originally Posted by ARWinner

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

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).

dr_te_z · #6 (**permalink**) 03-22-11, 04:51

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.

Internet Services

Web Development

Digital Marketing

Consumer Tech