Results 1 to 11 of 11
  1. #1
    Join Date
    Dec 2008
    Posts
    5

    Unanswered: Overall performace issues

    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?

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    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

  3. #3
    Join Date
    Dec 2008
    Posts
    5
    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.

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    What are the size of the drives? Is you system OLTP or DW? How much data are you accessing?

    Andy

  5. #5
    Join Date
    Dec 2008
    Posts
    5
    The system is OLTP. The database is about 3gb. The drives are 33gb each.

  6. #6
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    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.
    ---
    "It does not work" is not a valid problem statement.

  7. #7
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    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.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  8. #8
    Join Date
    Dec 2008
    Posts
    5
    The machine is dedicated to DB2 database server. I will try to get a copy of the config to post.

    Thanks.

  9. #9
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Have you run the DB2 Configuration Advisor to get a good baseline setup for your system?
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  10. #10
    Join Date
    Nov 2008
    Posts
    4
    this is not a matter it depend on your work and which type of data you have stored and which type software you install

  11. #11
    Join Date
    Dec 2008
    Posts
    5
    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

Posting Permissions

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