Results 1 to 11 of 11
  1. #1
    Join Date
    Oct 2004

    Unanswered: Performance problem

    DB2 V9.5 FP7 on RHEL 6.0 VM Image

    We have 4 databases on this server which are copied from each other. Anotherwords, Structually they are the same. Same application running against all of them but the performance on one database is 5 times worst than the others. The only difference is, the problem database size is 3 GB and the others 1.8 GB, 1.2 GB and 450 MB.

    The database has one bufferpool defined for the only user tablespace with SIZE 500 PAGESIZE 8192. Would adding another bufferpool ease the problem ?

    It is a VM image with 4GB memory. Attached is the db cfg settings. Please let me know if you have any ideas...........Thank you.
    Attached Files Attached Files
    Last edited by mdx34; 04-04-12 at 17:11.

  2. #2
    Join Date
    Jan 2003
    Provided Answers: 5
    Making the bufferpool larger would be better. It is only about 4MB in size-a very small (<1%) percentage of the DB size.

    Is this a 32 or 64 bit instance?
    Are all 4 databases running under the same instance with 4GB RAM?


  3. #3
    Join Date
    Oct 2004
    Thank you Andy. Yes, all 4 databases running under the same instance with 4GB RAM. It is a 64 bit instance.

  4. #4
    Join Date
    Jan 2003
    Provided Answers: 5
    How much free ram is there right now.

    4 GB is awful tight, especially for 4 databases. See if they can give the VM more. Then increase the bufferpool size.


  5. #5
    Join Date
    Oct 2004
    Only 2 of the 4 databases are in use and the one with the problem is the most used.

    Here is the result of free -m (711 MB Free)

    [15:32:00] db2inst1@trtprod01 ~ $free -m

    total used free shared buffers cached
    Mem: 3962 3251 711 0 181 2131

    -/+ buffers/cache: 938 3023
    Swap: 4095 0 4095

    [15:32:00] db2inst1@trtprod01 ~ $


  6. #6
    Join Date
    Jan 2003
    Provided Answers: 5
    Have them give more memory to the VM, then increase you bufferpool size.


  7. #7
    Join Date
    Jun 2003
    Toronto, Canada
    Provided Answers: 1
    In this situation I would also set INSTANCE_MEMORY to something like 80% of available physical memory, then set DATABASE_MEMORY, bufferpools, and whatever else makes sense to automatic. Despite the STMM shortcomings, I think DB2 will be better (and faster) at figuring out the optimal memory configuration than a human.

  8. #8
    Join Date
    Oct 2004
    Thanks............I don't think it is a server memory issue. We were having this problem when the server memory was 2GB. We increased it to 4GB but still having the problem. There is always free memory on the server. This is not very heavily used production system. It is a production test system.

    We have collected db2pd, db2top, monitoring results but nothing looks apparent. Below is DBM CFG....................

    Database Manager Configuration

    Node type = Database Server with local and remote clients

    Database manager configuration release level = 0x0c00

    CPU speed (millisec/instruction) (CPUSPEED) = 3.778754e-07

    Max number of concurrently active databases (NUMDB) = 8
    Federated Database System Support (FEDERATED) = NO
    Transaction processor monitor name (TP_MON_NAME) =

    Default charge-back account (DFT_ACCOUNT_STR) =

    Java Development Kit installation path (JDK_PATH) = /home/db2inst1/sqllib/java/jdk64

    Diagnostic error capture level (DIAGLEVEL) = 3
    Notify Level (NOTIFYLEVEL) = 3
    Diagnostic data directory path (DIAGPATH) = /home/db2inst1/sqllib/db2dump

    Default database monitor switches
    Buffer pool (DFT_MON_BUFPOOL) = ON
    Lock (DFT_MON_LOCK) = ON
    Sort (DFT_MON_SORT) = ON
    Statement (DFT_MON_STMT) = ON
    Table (DFT_MON_TABLE) = ON
    Timestamp (DFT_MON_TIMESTAMP) = ON
    Unit of work (DFT_MON_UOW) = ON
    Monitor health of instance and databases (HEALTH_MON) = OFF

    SYSADM group name (SYSADM_GROUP) = DB2IADM1
    SYSCTRL group name (SYSCTRL_GROUP) =
    SYSMAINT group name (SYSMAINT_GROUP) =
    SYSMON group name (SYSMON_GROUP) = DB2IADM1

    Client Userid-Password Plugin (CLNT_PW_PLUGIN) =
    Client Kerberos Plugin (CLNT_KRB_PLUGIN) =
    Group Plugin (GROUP_PLUGIN) =
    GSS Plugin for Local Authorization (LOCAL_GSSPLUGIN) =
    Server Plugin Mode (SRV_PLUGIN_MODE) = UNFENCED
    Server List of GSS Plugins (SRVCON_GSSPLUGIN_LIST) =
    Server Userid-Password Plugin (SRVCON_PW_PLUGIN) =
    Server Connection Authentication (SRVCON_AUTH) = NOT_SPECIFIED
    Cluster manager (CLUSTER_MGR) =

    Database manager authentication (AUTHENTICATION) = SERVER
    Cataloging allowed without authority (CATALOG_NOAUTH) = NO
    Trust all clients (TRUST_ALLCLNTS) = YES
    Trusted client authentication (TRUST_CLNTAUTH) = CLIENT
    Bypass federated authentication (FED_NOAUTH) = NO

    Default database path (DFTDBPATH) = /home/db2inst1

    Database monitor heap size (4KB) (MON_HEAP_SZ) = AUTOMATIC(90)
    Java Virtual Machine heap size (4KB) (JAVA_HEAP_SZ) = 2048
    Audit buffer size (4KB) (AUDIT_BUF_SZ) = 0
    Size of instance shared memory (4KB) (INSTANCE_MEMORY) = AUTOMATIC(809905)
    Backup buffer default size (4KB) (BACKBUFSZ) = 1024
    Restore buffer default size (4KB) (RESTBUFSZ) = 1024

    Agent stack size (AGENT_STACK_SZ) = 1024
    Sort heap threshold (4KB) (SHEAPTHRES) = 0

    Directory cache support (DIR_CACHE) = YES

    Application support layer heap size (4KB) (ASLHEAPSZ) = 15
    Max requester I/O block size (bytes) (RQRIOBLK) = 32767
    Query heap size (4KB) (QUERY_HEAP_SZ) = 1000

    Workload impact by throttled utilities(UTIL_IMPACT_LIM) = 10

    Priority of agents (AGENTPRI) = SYSTEM
    Agent pool size (NUM_POOLAGENTS) = AUTOMATIC(100)
    Initial number of agents in pool (NUM_INITAGENTS) = 0
    Max number of coordinating agents (MAX_COORDAGENTS) = AUTOMATIC(200)
    Max number of client connections (MAX_CONNECTIONS) = AUTOMATIC(MAX_COORDAGENTS)

    Keep fenced process (KEEPFENCED) = YES
    Number of pooled fenced processes (FENCED_POOL) = AUTOMATIC(MAX_COORDAGENTS)
    Initial number of fenced processes (NUM_INITFENCED) = 0

    Index re-creation time and redo index build (INDEXREC) = RESTART

    Transaction manager database name (TM_DATABASE) = 1ST_CONN
    Transaction resync interval (sec) (RESYNC_INTERVAL) = 180

    SPM name (SPM_NAME) = trtprod0
    SPM log size (SPM_LOG_FILE_SZ) = 256
    SPM resync agent limit (SPM_MAX_RESYNC) = 20
    SPM log path (SPM_LOG_PATH) =

    TCP/IP Service name (SVCENAME) = db2c_db2inst1
    Discovery mode (DISCOVER) = SEARCH
    Discover server instance (DISCOVER_INST) = ENABLE

    Maximum query degree of parallelism (MAX_QUERYDEGREE) = ANY
    Enable intra-partition parallelism (INTRA_PARALLEL) = NO

    No. of int. communication buffers(4KB)(FCM_NUM_BUFFERS) = AUTOMATIC(1024)
    No. of int. communication channels (FCM_NUM_CHANNELS) = AUTOMATIC(512)
    db2start/db2stop timeout (min) (START_STOP_TIME) = 10
    Last edited by mdx34; 04-05-12 at 09:51.

  9. #9
    Join Date
    Aug 2001
    You have increased server memory. Did you increase the bufferpool size "SIZE 500" . As Andy mentioned above, this is small.

    When you say you monitored with db2pd, db2top etc, what did you look for ?
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  10. #10
    Join Date
    Nov 2011
    Are the sqls running agaist them are the same?If they are,
    pick up one of them to see whether each sql's access plan of the 4 database is same or not.

  11. #11
    Join Date
    Oct 2004
    "pick up one of them to see whether each sql's access plan of the 4 database is same or not"

    Yes. I'll do that next. Thanks.

Posting Permissions

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