OLTP performance checklist

04 July,2011 by Jack Vamvas

This is my OLTP performance checklist baseline configuration checklist , using DB2 9.1 # 9.5 for OLTP DB2 systems.

For DB2 servers supporting OLTP , I execute a script that gathers this information. Based on application requirements , these configurations can deviate.

The SYSTEM based are dependant on infrastructure - and increasingly the systems are 64 bit - booting off SAN.

The RUNSTATS process is dependant on level of DML.

I avoid using the Health Monitor. Usually leads to a negative impact on DB2 performance - and rarely kept it on.

Configuration

Value

MODE

OS

Red Hat Linux 5  64 bit SYSTEM 
CPU 3 GHz SYSTEM 
PHYSICAL MEMORY 16 GB SYSTEM 
BOOT Local SYSTEM 
DATA \ LOG DRIVES SAN SYSTEM 
PROCESSOR 2 x Dual Core SYSTEM 
NEWLOGPATH separate SAN drive to data DB
AUTO_MAINT  OFF DB
AUTO_TBL_MAINT  OFF DB
AUTO_RUNSTATS  OFF DB
DATABASE_MEMORY  AUTOMATIC DB
SELF_TUNING_MEM  ON DB
SORTHEAP AUTOMATIC DB
SHEAPTHRES_SHR  AUTOMATIC DB
LOCKLIST AUTOMATIC DB
MAXLOCKS AUTOMATIC DB
PCKCACHESZ  8196 DB
DFT_MON_BUFPOOL  OFF INSTANCE
DFT_MON_SORT  OFF INSTANCE
DFT_MON_STMT  OFF INSTANCE
DFT_MON_TABLE  OFF INSTANCE
DFT_MON_UOW  OFF INSTANCE
HEALTH_MON  OFF INSTANCE
INSTANCE_MEMORY  AUTOMATIC INSTANCE
DB2_FMP_COMM_HEAPSZ 0 INSTANCE
MAXAGENTS 250 INSTANCE
SPEED 1000Mb\s (100 Mb\s) NETWORK
DUPLEX FULL NETWORK
PORT FIBRE (Twisted Cable) NETWORK
AUTO NEGOTIATION off NETWORK
WRITE BACK CACHE on DISK
RUNSTATS M,T,W - cron job MAINTENANCE
REORG Thursday 2a.m - cron job MAINTENANCE
FILESYSTEM CACHE Not On SYSTEM 

 

Read More

How to report a DB2 performance problem - DBA DB2

DB2 Total Sorts - overflows – improve performance - DBA DB2

10 biggest DBA concerns - DBA DB2

Author: Jack Vamvas(http://www.dba-db2.com)

Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been saved. Comments are moderated and will not appear until approved by the author. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on OLTP performance checklist

Comments are moderated, and will not appear until the author has approved them.


dba-db2.com | DB2 Performance Tuning | DBA DB2:Everything | FAQ | Contact | Copyright