Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2010
    Location
    Australia
    Posts
    7

    Unanswered: Database Poor Performance

    Hi Folks,

    My system: DB2 v9.5_FP0 (Wintel),

    I have database with poor performance. After performing some investigation I found the following:

    The Synchronous Read Percentage (SRP) is 25.4.
    using SQL:
    SELECT SUBSTR(A.DB_NAME,1,10) as DBNAME,
    CAST(( 100.0 - ((A.POOL_ASYNC_DATA_READS + A.POOL_ASYNC_INDEX_READS + A.POOL_ASYNC_XDA_READS) * 100.0)
    / (A.POOL_DATA_P_READS + A.POOL_INDEX_P_READS + A.POOL_XDA_P_READS + 0.01))
    AS DECIMAL(5,2)) AS SRP,
    A.DBPARTITIONNUM AS PART
    FROM sysibmadm.SNAPDB A
    ORDER BY A.DBPARTITIONNUM;

    What could I do to increase the SRP?
    Bufferpool hit ratio was 71%, so I increased the size of the BP.

    Thanking you in advance.

  2. #2
    Join Date
    Oct 2007
    Posts
    246
    ur BP should be always 99% for better performace i believe u can chk the page clearners and page fetchers parameter , chgpage threshold and db2 register variable
    DB2_USE_ALTERNATE_PAGE_CLEANING
    regds
    Paul

  3. #3
    Join Date
    Jan 2009
    Location
    Zoetermeer, Holland
    Posts
    746
    Did you look at the numblockpages and blocksize in the bufferpooldefinitions? (DBI Blogs :: General :: DB2 LUW Performance: NUMBLOCKPAGES and APPR)
    and... it is a database, so (by definitions) it is hungry for memory: FEED IT!
    Last edited by dr_te_z; 12-03-10 at 04:55. Reason: previos poster already mentioned the prefechters & pagecleaners

  4. #4
    Join Date
    Sep 2010
    Location
    Australia
    Posts
    7

    and motora

    HI,

    Thanks for your replies.

    * I set NUMBLOCKPAGES to be 3% of BP(s)
    * DB2_USE_ALTERNATE_PAGE_CLEANING=ON
    * Increased NUM_IOCLEANERS by

    which has improved perfromance.

    Looking further into the problem I have noticed excessive leaf page scans & data page scans. So will look into defining/improving more precise indexes.

    Thanks.

  5. #5
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    You should install the latest fixpack. Only a masochist would use FP0 of any release. If this is Express-C, you can get 9.7.2.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

Posting Permissions

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