Results 1 to 8 of 8
  1. #1
    Join Date
    Dec 2008
    Location
    Toronto, Canada
    Posts
    399

    Unanswered: bufferpools, pctfree and other ways to improve performance

    DB2 v9.5.1 ESE on AIX v6.1
    We have all tables in one tablespace with 1 bufferpool so as I am looking for ways to improve performance I conducting a number of tests for different configurations. The first test was with tables moved to separated tablespaces. Performance improvement is only 6%. So I run same this test while collecting bufferpool stats and got this:

    BP_NAME TOTAL_LOG_READS TOTAL_PHYS_READS TOTHITRATIO DATAHITRATIO IDXHITRATIO SNAPSHOT_TIMESTAMP
    -------------- -------------------- -------------------- ----------- ------------ ----------- -------------------
    IBMDEFAULTBP 0 0 - - - 2010-03-15-12:50:34
    BP_SYSCATSPC 66297 308 99.53 99.18 99.69 2010-03-15-12:50:34
    BP_TBS_PROD 585277 8717 98.51 98.36 99.48 2010-03-15-12:50:34
    BP_TXS_16K 81311908 4945590 93.91 63.08 99.07 2010-03-15-12:50:34
    BP_16K_1 54215390 141568 99.73 99.58 99.88 2010-03-15-12:50:34
    BP_32K_1 35993208 2181549 93.93 79.01 99.92 2010-03-15-12:50:34
    BP_32K_2 197893067 180969 99.90 99.90 99.99 2010-03-15-12:50:34
    BP_32K_3 46000908 1087366 97.63 80.45 99.37 2010-03-15-12:50:34
    IBMSYSTEMBP4K 0 0 - - - 2010-03-15-12:50:34
    IBMSYSTEMBP8K 0 0 - - - 2010-03-15-12:50:34
    IBMSYSTEMBP16K 0 0 - - - 2010-03-15-12:50:34
    IBMSYSTEMBP32K 0 0 - - - 2010-03-15-12:50:34

    Id Name PageSz PA-NumPgs
    1 IBMDEFAULTBP 4096 20600
    2 BP_SYSCATSPC 4096 41200
    3 BP_TBS_PROD 4096 41200
    4 BP_TXS_16K 16384 154500
    5 BP_16K_1 16384 51500
    6 BP_32K_1 32768 51500
    7 BP_32K_2 32768 38625
    8 BP_32K_3 32768 51500
    4096 IBMSYSTEMBP4K 4096 16
    4097 IBMSYSTEMBP8K 8192 16
    4098 IBMSYSTEMBP16K 16384 16
    4099 IBMSYSTEMBP32K 32768 16



    Based on this would you say that we do not need to increase/change bufferpool sizes anymore (because hitratios are high enough already)? What changes if any can I make to improve performance? Total bufferpools size is 8GB which is half of available physical memory.

    I still have LOBs (11 tables) in the same tablespaces as tables. Would it improve performance (and how much) if I move LOBs to separate tablespaces?

    All tables have PCTFREE=0% and all indexes 10% (default) even for tables with clustered indexes. I tried to make PCTFREE=10% for 12 tables with clustered indexes (all tables were in the same TS then) and perf. improvement was 5%. Would it improve performance if I change PCTFREE of all tables to 10% as well (mixed data warehouse and OLTP environment)?

    Thanks in advance
    DB2 9.5/9.7 on Unix/AIX 6.1/Linux

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    The best way to improve system performance is to maximize the index read efficiency. Basically you want to read the minimum number of rows that you need for any statement.

    Andy

  3. #3
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    High bufferpool hit ratio is sometimes misleading ... Eg. a small non-indexed table scanned a few hundred thousand times when involved in a NL Join.

    As Andy says, first look at the indexes ...
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    I'd say performance is most frequently defined in terms of how much useful work is done per time unit. From that point of view neither BP hit ratio nor index utilization are indicative. One would need to find where most time is being spent, and try to improve that. It could be physical I/O, sorts, lock waits, query compilation, etc. Once the bottleneck is known, you can determine possible solutions and, at that point, appropriate metrics to verify them.
    ---
    "It does not work" is not a valid problem statement.

  5. #5
    Join Date
    Jan 2009
    Location
    Zoetermeer, Holland
    Posts
    746
    Quote Originally Posted by MarkhamDBA View Post
    I still have LOBs (11 tables) in the same tablespaces as tables.
    How often are those columns included in a SELECT? I'd place LOB's in a separate tablespace and enable file system caching for that tablespace. To compensate for the absence of the bufferpool.
    A better solution would be to convice your boss to upgrade to V9.7 a.s.a.p. to benefit from the inline LOB's.

  6. #6
    Join Date
    Dec 2008
    Location
    Toronto, Canada
    Posts
    399
    Quote Originally Posted by dr_te_z View Post
    How often are those columns included in a SELECT? I'd place LOB's in a separate tablespace and enable file system caching for that tablespace. To compensate for the absence of the bufferpool.
    A better solution would be to convice your boss to upgrade to V9.7 a.s.a.p. to benefit from the inline LOB's.
    does it mean that it would not be any need to move LOBs to separate TSs in v9.7?
    DB2 9.5/9.7 on Unix/AIX 6.1/Linux

  7. #7
    Join Date
    Jan 2009
    Location
    Zoetermeer, Holland
    Posts
    746
    Depends on the size. You specify the size of inline LOB's. LOB's which are bigger are stored externally and treated as usual. Smaller LOB's will be stored like normal columns, included in the buffer pools and compressed as well.

  8. #8
    Join Date
    Dec 2008
    Location
    Toronto, Canada
    Posts
    399
    thanks everyone
    DB2 9.5/9.7 on Unix/AIX 6.1/Linux

Posting Permissions

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