Results 1 to 11 of 11
  1. #1
    Join Date
    Jan 2008

    Unanswered: DB2 v9.5 Performance on SUSE 10

    Hello DBAs,

    I have a question regarding DB2 performance. I have installed DB2 on SUSE 10 SP1 64bit and tried to restore a small database. What I noticed at the operating system is that the IO Queue depth is always at 1 and never get more than 1. My Qlogic HBA setting is at 128. When I run IOZone test, I get 128 IO Queue depth. Is the DB2 restore operation single-threaded? Or is there a way to tune DB2 to get more IO Queue depth? Please help me as this is too slow. Doesn't matter what I do (restore, create tablespace, etc..) I get only 1 outstanding IO when I run iostat -x. Any other test tools did not do this on my server so I suspect it's DB2.



  2. #2
    Join Date
    Jan 2003
    Provided Answers: 5
    What DB2 version?
    What is the configuration parameters
    of the instance?
    of the database?


  3. #3
    Join Date
    Jan 2008
    DB2 version is 9.5.
    Single container
    db2inst1@Dell6850b:~> db2set -i

  4. #4
    Join Date
    Jan 2003
    Provided Answers: 5
    What are the configuration parameters of the instance and the db?


  5. #5
    Join Date
    Jan 2008
    Hi Andy,

    I tried to copy and paste the output but it's only working.

  6. #6
    Join Date
    Jan 2008
    db2inst1@Dell6850b:~> db2 get db cfg for PRDXNGPS

    Database Configuration for Database PRDXNGPS

    Database configuration release level = 0x0c00
    Database release level = 0x0c00

    Database territory = US
    Database code page = 1208
    Database code set = UTF-8
    Database country/region code = 1
    Database collating sequence = IDENTITY
    Alternate collating sequence (ALT_COLLATE) =
    Number compatibility = OFF
    Varchar2 compatibility = OFF
    Database page size = 4096

    Dynamic SQL Query management (DYN_QUERY_MGMT) = DISABLE

    Discovery support for this database (DISCOVER_DB) = ENABLE

    Restrict access = NO
    Default query optimization class (DFT_QUERYOPT) = 5
    Degree of parallelism (DFT_DEGREE) = 1
    Continue upon arithmetic exceptions (DFT_SQLMATHWARN) = NO
    Default refresh age (DFT_REFRESH_AGE) = 0
    Default maintained table types for opt (DFT_MTTB_TYPES) = SYSTEM
    Number of frequent values retained (NUM_FREQVALUES) = 10
    Number of quantiles retained (NUM_QUANTILES) = 20

    Decimal floating point rounding mode (DECFLT_ROUNDING) = ROUND_HALF_EVEN

    Backup pending = NO

    Database is consistent = YES
    Rollforward pending = DATABASE
    Restore pending = NO

    Multi-page file allocation enabled = YES

    Log retain for recovery status = RECOVERY
    User exit for logging status = NO

    Self tuning memory (SELF_TUNING_MEM) = ON
    Size of database shared memory (4KB) (DATABASE_MEMORY) = AUTOMATIC
    Database memory threshold (DB_MEM_THRESH) = 10
    Max storage for lock list (4KB) (LOCKLIST) = AUTOMATIC
    Percent. of lock lists per application (MAXLOCKS) = AUTOMATIC
    Package cache size (4KB) (PCKCACHESZ) = AUTOMATIC
    Sort heap thres for shared sorts (4KB) (SHEAPTHRES_SHR) = AUTOMATIC
    Sort list heap (4KB) (SORTHEAP) = AUTOMATIC

    Database heap (4KB) (DBHEAP) = AUTOMATIC
    Catalog cache size (4KB) (CATALOGCACHE_SZ) = 260
    Log buffer size (4KB) (LOGBUFSZ) = 256
    Utilities heap size (4KB) (UTIL_HEAP_SZ) = 189807
    Buffer pool size (pages) (BUFFPAGE) = 1000
    SQL statement heap (4KB) (STMTHEAP) = AUTOMATIC
    Default application heap (4KB) (APPLHEAPSZ) = AUTOMATIC
    Application Memory Size (4KB) (APPL_MEMORY) = AUTOMATIC
    Statistics heap size (4KB) (STAT_HEAP_SZ) = AUTOMATIC

    Interval for checking deadlock (ms) (DLCHKTIME) = 10000
    Lock timeout (sec) (LOCKTIMEOUT) = 30

    Changed pages threshold (CHNGPGS_THRESH) = 10
    Number of asynchronous page cleaners (NUM_IOCLEANERS) = AUTOMATIC
    Number of I/O servers (NUM_IOSERVERS) = AUTOMATIC
    Index sort flag (INDEXSORT) = YES
    Sequential detect flag (SEQDETECT) = YES
    Default prefetch size (pages) (DFT_PREFETCH_SZ) = AUTOMATIC

    Track modified pages (TRACKMOD) = OFF

    Default number of containers = 1
    Default tablespace extentsize (pages) (DFT_EXTENT_SZ) = 32

    Max number of active applications (MAXAPPLS) = AUTOMATIC
    Average number of active applications (AVG_APPLS) = AUTOMATIC
    Max DB files open per application (MAXFILOP) = 30720

    Log file size (4KB) (LOGFILSIZ) = 4096
    Number of primary log files (LOGPRIMARY) = 20
    Number of secondary log files (LOGSECOND) = 235
    Changed path to log files (NEWLOGPATH) =
    Path to log files = /db2/data/db2inst1/NODE0000/SQL00001/SQLOGDIR/
    Overflow log path (OVERFLOWLOGPATH) =
    Mirror log path (MIRRORLOGPATH) =
    First active log file = S0000035.LOG
    Block log on disk full (BLK_LOG_DSK_FUL) = NO
    Percent max primary log space by transaction (MAX_LOG) = 0
    Num. of active log files for 1 active UOW(NUM_LOG_SPAN) = 0

    Group commit count (MINCOMMIT) = 1
    Percent log file reclaimed before soft chckpt (SOFTMAX) = 520
    Log retain for recovery enabled (LOGRETAIN) = RECOVERY
    User exit for logging enabled (USEREXIT) = OFF

    HADR database role = STANDARD
    HADR local host name (HADR_LOCAL_HOST) =
    HADR local service name (HADR_LOCAL_SVC) =
    HADR remote host name (HADR_REMOTE_HOST) =
    HADR remote service name (HADR_REMOTE_SVC) =
    HADR instance name of remote server (HADR_REMOTE_INST) =
    HADR timeout value (HADR_TIMEOUT) = 120
    HADR log write synchronization mode (HADR_SYNCMODE) = NEARSYNC
    HADR peer window duration (seconds) (HADR_PEER_WINDOW) = 0

    First log archive method (LOGARCHMETH1) = LOGRETAIN
    Options for logarchmeth1 (LOGARCHOPT1) =
    Second log archive method (LOGARCHMETH2) = OFF
    Options for logarchmeth2 (LOGARCHOPT2) =
    Failover log archive path (FAILARCHPATH) =
    Number of log archive retries on error (NUMARCHRETRY) = 5
    Log archive retry Delay (secs) (ARCHRETRYDELAY) = 20
    Vendor options (VENDOROPT) =

    Auto restart enabled (AUTORESTART) = ON
    Index re-creation time and redo index build (INDEXREC) = SYSTEM (RESTART)
    Log pages during index build (LOGINDEXBUILD) = OFF
    Default number of loadrec sessions (DFT_LOADREC_SES) = 1
    Number of database backups to retain (NUM_DB_BACKUPS) = 12
    Recovery history retention (days) (REC_HIS_RETENTN) = 366
    Auto deletion of recovery objects (AUTO_DEL_REC_OBJ) = OFF

    TSM management class (TSM_MGMTCLASS) =
    TSM node name (TSM_NODENAME) =
    TSM owner (TSM_OWNER) =
    TSM password (TSM_PASSWORD) =

    Automatic maintenance (AUTO_MAINT) = OFF
    Automatic database backup (AUTO_DB_BACKUP) = OFF
    Automatic table maintenance (AUTO_TBL_MAINT) = ON
    Automatic runstats (AUTO_RUNSTATS) = ON
    Automatic statement statistics (AUTO_STMT_STATS) = OFF
    Automatic statistics profiling (AUTO_STATS_PROF) = OFF
    Automatic profile updates (AUTO_PROF_UPD) = OFF
    Automatic reorganization (AUTO_REORG) = OFF

    Enable XML Character operations (ENABLE_XMLCHAR) = YES
    WLM Collection Interval (minutes) (WLM_COLLECT_INT) = 0


  7. #7
    Join Date
    Jan 2008
    db2inst1@Dell6850b:~> db2 get dbm cfg

    Database Manager Configuration

    Node type = Enterprise Server Edition with local and remote clients

    Database manager configuration release level = 0x0c00

    CPU speed (millisec/instruction) (CPUSPEED) = 2.952151e-07
    Communications bandwidth (MB/sec) (COMM_BANDWIDTH) = 1.000000e+02

    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) = OFF
    Lock (DFT_MON_LOCK) = OFF
    Sort (DFT_MON_SORT) = OFF
    Statement (DFT_MON_STMT) = OFF
    Table (DFT_MON_TABLE) = OFF
    Timestamp (DFT_MON_TIMESTAMP) = ON
    Unit of work (DFT_MON_UOW) = OFF
    Monitor health of instance and databases (HEALTH_MON) = ON

    SYSADM group name (SYSADM_GROUP) = DB2IADM1
    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
    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
    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

    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
    Initial number of agents in pool (NUM_INITAGENTS) = 0
    Max number of coordinating agents (MAX_COORDAGENTS) = AUTOMATIC
    Max number of client connections (MAX_CONNECTIONS) = AUTOMATIC

    Keep fenced process (KEEPFENCED) = YES
    Number of pooled fenced processes (FENCED_POOL) = AUTOMATIC
    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) =
    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

    Maximum Asynchronous TQs per query (FEDERATED_ASYNC) = 0

    No. of int. communication buffers(4KB)(FCM_NUM_BUFFERS) = AUTOMATIC
    No. of int. communication channels (FCM_NUM_CHANNELS) = 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

  8. #8
    Join Date
    Jan 2003
    Provided Answers: 5
    OK. How many tablespaces are in the backup and what is the restore command you are using?


  9. #9
    Join Date
    Jan 2008
    There are a total of 10 tablespaces. The command I use is
    db2 restore DB PRDXNGPS to /db2/data without rolling forward

    It doesn't matter if I restore one database or just run the import of a table. I only get one 1 IO queue depth when running iostat.


  10. #10
    Join Date
    Jan 2003
    Provided Answers: 5
    You can try setting INTRA_PARALLEL to on and using the PARALLELISM parameter on the RESTORE set to something between 2 and 10. Also try the import from multiple client at one time and see what that does.


  11. #11
    Join Date
    Jan 2008
    I have tried setting intra_parallel and use the parallelism but it did not help. Apparently, the IO queue length is always at 1 and cannot go beyond that. If I run a select statement then I get more than 1 IO queue length. I am suspecting that the import process or the restore process is single threaded operation. Thank you for your help. I really appreciate it.


Posting Permissions

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