OLTP performance checklist

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	1000Mbs (100 Mbs)	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

Author: Jack Vamvas