Results 1 to 15 of 15
  1. #1
    Join Date
    Nov 2007
    Posts
    265

    Unanswered: Performance Tuning DB2 -- 64 Bit 8.1 FP 18 on CentOS Linux

    Hi,

    We're trying to get a new server up with 64-BIT 8.1 FP 18 DB2 UDB on Linux ( CentOS). Here is some more info on the new server --

    4 CPUs
    RAID 10 with 4 drives
    CentOS 32GB RAM

    BUFFERPOOL SETUP --

    db2 "select varchar(bpname,20) as bpname,npages,pagesize from syscat.bufferpools"

    BPNAME NPAGES PAGESIZE
    -------------------- ----------- -----------
    IBMDEFAULTBP 4000 4096
    BKDB_BP 40000 8192
    BKDB_DATABP 40000 8192
    TEMPSP2_BP 8000 8192
    HISTBK_BP 8000 8192
    UTILBK_BP 8000 8192
    BKDBREPL_BP 12000 8192

    ( Total = Around 1 GB )

    I've been setting / updating some of the config / performance variables. Based on an earlier thread, I tried to increase the bufferpools based on this :

    'I would increase the IBMDEFAULTBP to 3000 pages to make sure the system catalog is always in memory. The remaining bufferpools (in total) should be at least 50% of the physical memory on the machine (assuming DB2 is pretty much the only thing running on that server).

    I tried to increase the NPAGES of BKDB_BP to 160000 but immediately got this error :

    SQL20189W. The buffer pool operation (CREATE/ALTER) will not take effect until the next database startup due to insufficient memory.


    The database is the only thing running on the server so am confused as to why it wouldn't let me increase it considering the total RAM = 32 GB. What can I do to speed up my queries further? Is there anything I should change in the configuration parameters below as well? Would it be a good idea to add the database_memory config parameter as well? Thanks!!
    Last edited by db2user24; 05-25-11 at 13:58.

  2. #2
    Join Date
    Nov 2007
    Posts
    265
    Here are some other performance and configuration variables that have been set --

    db2set -all
    [i] DB2_FMP_COMM_HEAPSZ=60000
    [i] DB2_HASH_JOIN=ON
    [i] DB2COMM=tcpip
    [i] DB2_PARALLEL_IO=*
    [g] DB2ADMINSERVER=db2ins1as



    [db2inst1@localhost ~]$ db2 get db cfg

    Database Configuration for Database

    Database configuration release level = 0x0a00
    Database release level = 0x0a00

    Database territory = US
    Database code page = 819
    Database code set = ISO8859-1
    Database country/region code = 1
    Database collating sequence = UNIQUE
    Alternate collating sequence (ALT_COLLATE) =
    Database page size = 4096

    Dynamic SQL Query management (DYN_QUERY_MGMT) = DISABLE

    Discovery support for this database (DISCOVER_DB) = DISABLE

    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

    Backup pending = NO

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

    Multi-page file allocation enabled = NO

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

    Data Links Token Expiry Interval (sec) (DL_EXPINT) = 60
    Data Links Write Token Init Expiry Intvl(DL_WT_IEXPINT) = 60
    Data Links Number of Copies (DL_NUM_COPIES) = 1
    Data Links Time after Drop (days) (DL_TIME_DROP) = 1
    Data Links Token in Uppercase (DL_UPPER) = NO
    Data Links Token Algorithm (DL_TOKEN) = MAC0

    Database heap (4KB) (DBHEAP) = 4800
    Size of database shared memory (4KB) (DATABASE_MEMORY) = AUTOMATIC
    Catalog cache size (4KB) (CATALOGCACHE_SZ) = 800
    Log buffer size (4KB) (LOGBUFSZ) = 256
    Utilities heap size (4KB) (UTIL_HEAP_SZ) = 10000
    Buffer pool size (pages) (BUFFPAGE) = 1000
    Extended storage segments size (4KB) (ESTORE_SEG_SZ) = 16000
    Number of extended storage segments (NUM_ESTORE_SEGS) = 0
    Max storage for lock list (4KB) (LOCKLIST) = 9600

    Max size of appl. group mem set (4KB) (APPGROUP_MEM_SZ) = 30000
    Percent of mem for appl. group heap (GROUPHEAP_RATIO) = 70
    Max appl. control heap size (4KB) (APP_CTL_HEAP_SZ) = 128

    Sort heap thres for shared sorts (4KB) (SHEAPTHRES_SHR) = (SHEAPTHRES)
    Sort list heap (4KB) (SORTHEAP) = 1024
    SQL statement heap (4KB) (STMTHEAP) = 4096
    Default application heap (4KB) (APPLHEAPSZ) = 256
    Package cache size (4KB) (PCKCACHESZ) = (MAXAPPLS*8)
    Statistics heap size (4KB) (STAT_HEAP_SZ) = 4384

    Interval for checking deadlock (ms) (DLCHKTIME) = 10000
    Percent. of lock lists per application (MAXLOCKS) = 30
    Lock timeout (sec) (LOCKTIMEOUT) = 60

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

    Track modified pages (TRACKMOD) = ON

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

    Max number of active applications (MAXAPPLS) = 1200
    Average number of active applications (AVG_APPLS) = 1
    Max DB files open per application (MAXFILOP) = 256

    Log file size (4KB) (LOGFILSIZ) = 8000
    Number of primary log files (LOGPRIMARY) = 10
    Number of secondary log files (LOGSECOND) = 5
    Changed path to log files (NEWLOGPATH) =
    Path to log files = /home/db2inst1/db2inst1/NODE0000/SQL00001/SQLOGDIR/
    Overflow log path (OVERFLOWLOGPATH) =
    Mirror log path (MIRRORLOGPATH) =
    First active log file = S0041116.LOG
    Block log on disk full (BLK_LOG_DSK_FUL) = NO
    Percent of max active 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) = 100
    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

    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

    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) = OFF
    Automatic runstats (AUTO_RUNSTATS) = OFF
    Automatic statistics profiling (AUTO_STATS_PROF) = OFF
    Automatic profile updates (AUTO_PROF_UPD) = OFF
    Automatic reorganization (AUTO_REORG) = OFF

  3. #3
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    That is a warning message. Restart the instance and see if you memory looks ok (db2mtrk). Also check "top" to see total memory used on server.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  4. #4
    Join Date
    Nov 2007
    Posts
    265
    Thanks Marcus.. will check it out. Also, do you think any of my configuration variables are set too high, for example.. DBHEAP, LOGBUFSZ, etc?


    I want to check the bufferpool hit ratio as well.. so gave this command --

    db2 update monitor switches using BUFFERPOOL on


    After running the sql query, when I give db2pd -d dbname.. all I get is this :

    Database Partition 0 -- Database DBNAME -- Active -- Up 0 days 00:16:04 -- Date 05/25/2011 18:22:58

    BufferPools:
    First Active Pool ID 1
    Max Bufferpool ID 7
    Max Bufferpool ID on Disk 7
    Num Bufferpools 11

    Address Id Name PageSz PA-NumPgs BA-NumPgs BlkSize ES NumTbsp PgsLeft CurrentSz PostAlter SuspndTSCt
    0x15CB8390 1 IBMDEFAULTBP 4096 4000 0 0 N 5 0 4000 4000 0
    0x15CB8630 2 BKDB_BP 8192 40000 0 0 N 1 0 40000 40000 0
    0x15CB88D0 3 BKDB_DATABP 8192 40000 0 0 N 1 0 40000 40000 0
    0x15CB8B70 4 TEMPSP2_BP 8192 8000 0 0 N 1 0 8000 8000 0
    0x15CB8E10 5 HISTBK_BP 8192 8000 0 0 N 1 0 8000 8000 0
    0x15CB90B0 6 UTILBK_BP 8192 8000 0 0 N 1 0 8000 8000 0
    0x15CB9350 7 BKDBREPL_BP 8192 12000 0 0 N 1 0 12000 12000 0
    0x15CAF470 4096 IBMHIDDENBP4K 4096 16 0 0 N 0 0 16 16 0
    0x15CAF710 4097 IBMHIDDENBP8K 8192 16 0 0 N 0 0 16 16 0
    0x15CAF9B0 4098 IBMHIDDENBP16K 16384 16 0 0 N 0 0 16 16 0
    0x15CAFC50 4099 IBMHIDDENBP32K 32768 16 0 0 N 0 0 16 16


    How can I get the hit ratio percent value? Is there a another command for that or some other setting that I have to enable? Thanks!!

  5. #5
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Database Heap and Log Buffer Size look OK, although Database heap is a little tricky in V8 (completely automatic in V9.7).

    Bufferpool hit ratio can be calculated by looking ratio of logical and physical reads. This is available in Database Snapshot or Dynamic SQL Snapshot.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  6. #6
    Join Date
    Nov 2007
    Posts
    265
    Thanks.. will take a look at that.. I think I'll leave DBHEAP as is, the other variables I'm curious about are --

    1. DFT_PREFETCH_SZ ( should I set this to AUTOMATIC -- currently have it set to 64)

    2. CATALOGCACHE_SZ = 800 ( Is this too high?)

    3. UTIL_HEAP_SZ = 10000 ( Is this too high?)

    4. LOCKLIST = 9600 ( Is this too high?)

    5. STMTHEAP = 4096 ( Is this too high?)

    6. (LOGFILSIZ) = 8000 ( Is this too high?)


    Also, have you ever used the database_memory config variable?
    Last edited by db2user24; 05-25-11 at 16:59.

  7. #7
    Join Date
    Nov 2007
    Posts
    265
    Another question is whether I should change the value of the buffpage configuration variable? Thanks so much for the help!

  8. #8
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by db2user24 View Post
    Thanks.. will take a look at that.. I think I'll leave DBHEAP as is, the other variables I'm curious about are --

    1. DFT_PREFETCH_SZ ( should I set this to AUTOMATIC -- currently have it set to 64)

    2. CATALOGCACHE_SZ = 800 ( Is this too high?)

    3. UTIL_HEAP_SZ = 10000 ( Is this too high?)

    4. LOCKLIST = 9600 ( Is this too high?)

    5. STMTHEAP = 4096 ( Is this too high?)

    6. (LOGFILSIZ) = 8000 ( Is this too high?)


    Also, have you ever used the database_memory config variable?
    I would increase CATALOGCACHE_SZ to about 4096. The others look OK, unless you have a problem. Given the amount of memory you have, don't worry too much about them being too high. These are number of 4K pages, so do the math, and you will see it is not that much to worry about.

    The LOGFILSIZ is file size on disk, so no memory considerations. That is a reasonable size. But number of secondary logs and LOGRETAIN=RECOVERY are questionable. Why are you not using LOGARCHMETH1?

    The database_memory config allows you to cap database memory. Probably leave it automatic, especially if only one database on that server.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  9. #9
    Join Date
    Nov 2007
    Posts
    265
    Thanks Marcus. I will change CATALOGCACHE_SZ to 4096 and leave the others as is.

    I guess I'm not sure what the values of LOGRETAIN and LOGARCHMETH1 should be. These are settings that we have on another server and with the restore, these values were also set on the new server. Also, not sure if it matters but our online backups are compressed and include logs. Then on the server where it is restored, we give the rollforward command after detaching the log files from the image and putting them in a 'logs' folder under the instance home dir --

    db2 rollforward db db2inst1 to end of logs and stop overflow log path \(/home/db2inst1/logs\)
    Last edited by db2user24; 05-25-11 at 18:59.

  10. #10
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    It has to do with archiving the log files, not a restore and rollforward. You can read about LOGARCHMETH1 and decide if you want it or not.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  11. #11
    Join Date
    Nov 2007
    Posts
    265
    thanks marcus, i will look into it... also, I was able to bump up the bufferpool size so that the total is around 16 GB ( system has 32 GB RAM)..

    I also found this as the total query execution time for data of a particular id for a whole year on the new server ( with the new bufferpool settings) --

    real 4m27.212s

    Comparatively, on another server that only has 4 GB RAM , it took half the time to execute the same query.

    On the new server, the query immediately after that with another id for the same range was much faster. Shouldn't the new server settings with much more memory cause faster execution?

  12. #12
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by db2user24 View Post
    thanks marcus, i will look into it... also, I was able to bump up the bufferpool size so that the total is around 16 GB ( system has 32 GB RAM)..

    I also found this as the total query execution time for data of a particular id for a whole year on the new server ( with the new bufferpool settings) --

    real 4m27.212s

    Comparatively, on another server that only has 4 GB RAM , it took half the time to execute the same query.

    On the new server, the query immediately after that with another id for the same range was much faster. Shouldn't the new server settings with much more memory cause faster execution?
    It is very hard to tune a database based on the little information you have provided in this thread (or is practical to provide).

    However, can you give us an estimate of the size of the database (tables and indexes)?

    If the size of your database is significantly larger than 16 GB, then you might want to consider putting your indexes in a separate tablespaces with a separate bufferpool from the data. Database tuning (especially bufferpool configuration and tuning) requires knowledge of the database design and application that is using the database.

    If the particular data that a query needs is not already in bufferpool memory, then it must be read from disk and placed in memory by DB2 before it can be used by DB2 to get the results of the query (except for certain LOB data). If you run the query the second time, then hopefully a large chunk of the data needed for the query is already in memory from the first query and should run faster. These are basic DB2 concepts.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  13. #13
    Join Date
    Nov 2007
    Posts
    265
    thanks marcus.. yes I'm aware that the initial query should definitely be slower than when running the queries when some data is in memory... I just thought since the bufferpools are much bigger in size compared to our other servers ( 4 times as much actually!) , that it would execute faster.. our database is pretty big.. around 91 GB and the query that I'm running is against a table that is atleast 70GB.. this table is in it's own tablespace. The index on all of our tables have been created with a command like this so not really sure what tablespace / bufferpool it's going to--

    GRANT INDEX ON TABLE TABLE1 TO USER DB2INST1;

    Also, another problem I'm having is with the db2look command --. when I give it to see the definition of a table I get this error --

    db2look -d dbname -t site -e -x -o site.ddl
    -- No userid was specified, db2look tries to use Environment variable USER
    -- USER is: DB2INST1
    -- The db2look utility will consider only the specified tables
    -- Creating DDL for table(s)
    -- Output is sent to file: site.ddl
    SQL0007N The character "\" following "HIJKLMNOPQRSTUVWXYZ["


    Any idea how I can fix this? Thanks!!

  14. #14
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by db2user24 View Post
    The index on all of our tables have been created with a command like this so not really sure what tablespace / bufferpool it's going to--

    GRANT INDEX ON TABLE TABLE1 TO USER DB2INST1;
    I don't understand the above. This statement allows user db2inst1 the ability to create indexes on that table. I suspect that db2inst1 is the instance owner, who already has authority to do whatever it wants to do.

    I would suggest you hire a consultant to help you tune your database. This is a complex subject and not something that can be done by someone responding in a forum. You could ask the consultant to provide you with some skills transfer on this subject as part of the work to be performed.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  15. #15
    Join Date
    Nov 2007
    Posts
    265
    thanks for the advice marcus, I agree that it is complex and needs some expertise...

    oh and sorry, I actually pasted the wrong index command in... I actually meant to put the create index statement instead --

    CREATE INDEX IDX_NAME ON TABLE1 ("TID" ASC) ;

Posting Permissions

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