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.
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.
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
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
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