Not sure if this is the proper place to ask this question so I appologize in advance. I am running DB2 on Windows 2003 x64. Of late we have been running into some performance issues. While monitoring the server I noticed that one of the performance monitor values, Avg. Disk Read Queue Length appears to be greater than is generally recommended (max of 2 per disk). The write queue length is minimal. The server has 2 RAID 5 arrays with 3 disks in each. The log is on the C drive and the database is on the D drive. The server has 4GB of RAM with 1.6 showing as available. It has a XEON 3.0 processor.

Any ideas or suggestions as to what else I can look at? Would this parameter show that more RAM would help this? I there a DB2 parameter I should look at?

You are only using 60% of memory, so that does not look like it is an issue. To me it looks like you system is I/O bound. You are using the C drive, which I assume also has the OS on it, so you have contention there. What type of disks, how are they attached, size, speed?

Andy

The 2 arrays have three 15k RPM SAS disks attached to a PERC5/i RAID controller. The C drive has the log and that is not the array showing the high read queue. It is the D drive that has the db that I am seeing the high read queue.

What are the size of the drives? Is you system OLTP or DW? How much data are you accessing?

Andy

The system is OLTP. The database is about 3gb. The drives are 33gb each.

I'm not sure if all these hardware details are relevant at this point. I would start with making sure it is DB2 that saturates the I/O channel. What else may be running on the server at the same time?

If DB2 is indeed the suspect then it's a standard tuning exercise. Take baseline snapshots during quiet time, then take another set while under load, compare, find the bottleneck, make changes, repeat quantum satis.

Most of the time, sudden performance problems can be traced back to specific SQL statements that are taking too long. They may have a poor access path (complete table scans, etc), or they may be involved in a lockwait situations. The first step is to identify the SQL that is running slow using a Snapshot for Dynamic SQL (assuming the SQL is dynamic). You will need to use a different method is the SQL is static (embeded in a C program, or Stored Proc, etc), but all SQL from java is dynamic.

You can also post your db cfg to see if there any glaring issues there, along with your bufferpool setup.

The machine is dedicated to DB2 database server. I will try to get a copy of the config to post.

Thanks.

Have you run the DB2 Configuration Advisor to get a good baseline setup for your system?

this is not a matter it depend on your work and which type of data you have stored and which type software you install

We have run the DB2 Configuration Advisor. Here is the DB2 config file.

Thanks for all the responses. I guess one big question can be would more memory help with the read queue parameter? I know it could not hurt to add another 4gb but would not want to just throw memory at it if it is not the issue.


;Use BINARY file transfer

[FILE_DESCRIPTION]
APPLICATION=DB2/NT64 9.1.4
FILE_CONTENT=DB2 CCA Exported Data Sources
FILE_TYPE=CommonServer
FILE_FORMAT_VERSION=2.0
Platform=23
DB2SYSTEM=MBDB3
Instance=DB2

[REGISTRY_LOCAL]
DB2ACCOUNTNAME=MBDB3\db2admin
DB2INSTOWNER=MBDB3
DB2PORTRANGE=60000:60003
DB2NTNOCACHE=ON
DB2COMM=TCPIP
DB2_PARALLEL_IO=1,2,3,5,6,7

[DBM_CONFIG]
NODETYPE=4
RELEASE=0xb00
DIAGLEVEL=3
RQRIOBLK=32767
DOS_RQRIOBLK=4096
AUTHENTICATION=0
DIR_CACHE=1
DISCOVER=2
TP_MON_NAME=
SYSADM_GROUP=
SYSCTRL_GROUP=
SYSMAINT_GROUP=
SYSMON_GROUP=
TM_DATABASE=1ST_CONN
DFT_ACCOUNT_STR=
DISCOVER_COMM=
CATALOG_NOAUTH=0
NOTIFYLEVEL=3
DFT_CLIENT_ADPT=0
DIR_TYPE=0
DIR_OBJ_NAME=
ROUTE_OBJ_NAME=
DFT_CLIENT_COMM=
JAVA_HEAP_SZ=1024
FEDERATED=0
USE_SNA_AUTH=0
FED_NOAUTH=0
UTIL_IMPACT_LIM=10
GROUP_PLUGIN=
CLNT_PW_PLUGIN=
CLNT_KRB_PLUGIN=IBMkrb5
LOCAL_GSSPLUGIN=
MAX_QUERYDEGREE=2
AUDIT_BUF_SZ=0
INTRA_PARALLEL=0
NUMDB=8
INITFENCED_JVM=0
INDEXREC=2
SHEAPTHRES=0
BACKBUFSZ=1024
RESTBUFSZ=1024
MAXAGENTS=400
MAXCAGENTS=-1
ASLHEAPSZ=15
FENCED_POOL=-1
KEEPFENCED=1
AGENTPRI=-1
DFT_MON_UOW=OFF
DFT_MON_STMT=OFF
DFT_MON_TABLE=ON
DFT_MON_BUFPOOL=ON
DFT_MON_LOCK=OFF
DFT_MON_SORT=OFF
DFT_MON_TIMESTAMP=ON
MIN_PRIV_MEM=32
PRIV_MEM_THRESH=20000
CPUSPEED=1.417033e-007
QUERY_HEAP_SZ=1000
MAXTOTFILOP=16000
AGENT_STACK_SZ=16
MON_HEAP_SZ=219
RESYNC_INTERVAL=180
UDF_MEM_SZ=256
SPM_LOG_FILE_SZ=256
SPM_MAX_RESYNC=20
TRUST_ALLCLNTS=YES
SS_LOGON=1
NUM_INITFENCED=0
SPM_NAME=MBDB3
NUM_INITAGENTS=0
MAX_COORDAGENTS=-1
NUM_POOLAGENTS=400
FCM_NUM_BUFFERS=4096
MAX_LOGICAGENTS=-1
TRUST_CLNTAUTH=CLIENT
DISCOVER_INST=1
MAX_CONNECTIONS=-1
INSTANCE_MEMORY=17280
HEALTH_MON=ON
SRV_PLUGIN_MODE=1
SRVCON_PW_PLUGIN=
SRVCON_GSSPLUGIN_LIST=
SRVCON_AUTH=255
COMM_BANDWIDTH=1.000000e+002
CONN_ELAPSE=10
MAX_CONNRETRIES=5
MAX_TIME_DIFF=60
START_STOP_TIME=10
FEDERATED_ASYNC=0

[ADMINNODE>MBODB3]
DB2SYSTEM=MBDB3
Comment=Local workstation
ServerType=DB2NT
Nodetype=U
Protocol=LOCAL
instance_name=DB2DAS00

[INST>DB2]
instance_name=DB2
NodeType=4
ServerType=DB2NT
Authentication=SERVER
DB2COMM=TCPIP

[DB>!LOCAL:MBPROD]
Dir_entry_type=INDIRECT
Drive=D:
DBName=MBPROD

[DB>!LOCAL:TOOLSDB]
Dir_entry_type=INDIRECT
Drive=C:
DBName=TOOLSDB