From the below dbm cfg, try this first: set all your default monitor
switches OFF, particularly the health monitor; and set NUMDB to the actual
number of databases in the instance.
If this does not help sufficiently, the biggest claims on memory generally
come from database global memory - bufferpools and sort space; you're using
shared sorts because INTRA_PARALLEL=YES, so look at your BP sizes and
shared sort memory threshold (db cfg SHEAPTHRES_SHR) in case these are set
too high relative to other claims on server resources. In the case of the
bufferpools, are they pinned in memory (registry variable DB2_PINNED_BP)?
Also review whether INTRA_PARALLEL is such a good idea on a server you
share with a production database - you may be chip-hogging at the expense
of a live system.
Regards
________
Alex Levy
Sustainable Software Ltd.
http://www.sustainablesoftware.net
"sandeepjayawant"
<db2udbdba-ezmlms
hield-x26329415.x To
[Email Address Removed] "LazyDBA Discussion"
a.com> <[Email address protected]
cc
22/11/2006 09:48
Subject
memory problem
Hi Guys,
I am facing a problem. currently i have 3 dbs hosted on a aix server.
named rep , dev and prod.
whenever i connect to rep the memory usage shooots up like any thing this
happens only when i connec to rep db.
and everything is fine with other db's:
can you all suggest what parameters should be tuned...
i used vmstat 2 to check the memory usage before connecting to the db the
memory is close to six figure as soon as i connect it drops to around 3
digit/.
here is the dbm cfg
Database manager configuration release level = 0x0a00
CPU speed (millisec/instruction) (CPUSPEED) = 3.542582e-07
Communications bandwidth (MB/sec) (COMM_BANDWIDTH) = 1.000000e+02
Max number of concurrently active databases (NUMDB) = 8
Data Links support (DATALINKS) = NO
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) = /usr/java14
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) = ON
SYSADM group name (SYSADM_GROUP) = DB2GRP1
SYSCTRL group name (SYSCTRL_GROUP) =
SYSMAINT group name (SYSMAINT_GROUP) =
SYSMON group name (SYSMON_GROUP) =
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
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) = 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
Backup buffer default size (4KB) (BACKBUFSZ) = 1024
Restore buffer default size (4KB) (RESTBUFSZ) = 1024
Sort heap threshold (4KB) (SHEAPTHRES) = 20612
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
Max number of existing agents (MAXAGENTS) = 600
Agent pool size (NUM_POOLAGENTS) = 200
Initial number of agents in pool (NUM_INITAGENTS) = 0
Max number of coordinating agents (MAX_COORDAGENTS) = (MAXAGENTS -
NUM_INITAGENTS)
Max no. of concurrent coordinating agents (MAXCAGENTS) = MAX_COORDAGENTS
Max number of client connections (MAX_CONNECTIONS) = MAX_COORDAGENTS
Keep fenced process (KEEPFENCED) = NO
Number of pooled fenced processes (FENCED_POOL) = 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) = ESERVE
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) = 2
Enable intra-partition parallelism (INTRA_PARALLEL) = YES
No. of int. communication buffers(4KB)(FCM_NUM_BUFFERS) = 4096
Number of FCM request blocks (FCM_NUM_RQB) = 2048
Number of FCM connection entries (FCM_NUM_CONNECT) = AUTOMATIC
Number of FCM message anchors (FCM_NUM_ANCHORS) = AUTOMATIC
Node connection elapse time (sec) (CONN_ELAPSE) = 10
Max number of node connection retries (MAX_CONNRETRIES) = 5
Max time difference between nodes (min) (MAX_TIME_DIFF) = 60
db2start/db2stop timeout (min) (START_STOP_TIME) = 10
Regards
Sandeep
-----------------------------------------
*******************************************************************
This e-mail is confidential. It may also be legally privileged.
If you are not the addressee you may not copy, forward, disclose
or use any part of it. If you have received this message in error,
please delete it and all copies from your system and notify the
sender immediately by return e-mail.
Internet communications cannot be guaranteed to be timely,
secure, error or virus-free. The sender does not accept liability
for any errors or omissions.
*******************************************************************
"SAVE PAPER - THINK BEFORE YOU PRINT!"
---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To Subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
DB2 & UDB email list listserv db2-l LazyDBA home page