Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2009
    Posts
    12

    Red face Unanswered: Ideas to improve Asynchronous Write Percentage (DB2 9.5 Fix Pack4 On AIX)

    Hi EveryBody,
    Recently I have been asked to review an application batch. The batch is a fairly simple one which runs a series of Insert Statements. After running db2top in background mode for bufferpools, I found that the Average Execute Time is almost 10 times the Average CPU time. There are no lock escalations. After narrowing the suspects to I/O, I ran a snapshot on the db. The snapshot shows the that the Asynchronous Write Percentage (AWP) hovering between 25%-35%.
    My first question is:-
    1. Is the low AWP the sole cause of the high Execution time? If No what are the other suspects.
    2. If I need to increase the AWP are there any guidelines. I know some of the parameters affecting this are hardware dependent, so I am not expecting a definitive answer .

    All advice is welcome.

    Ashwin

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Here are some things to look at:

    1. Check the following in "get db cfg"
      • log file size at least 10000
      • log buffer size 256. If you have LOB data, then 512
      • number of page cleaners set to automatic
    2. Make sure all LOB's in separate tablespace with File Caching On.
    3. Setup multiple disk mount points (with no overlapping spindles between them) and use each of them when defining automatic storage paths (or for containers on non-automatic storage tablespace definitions)
    4. Make sure bufferpool is sufficiently large for tablespaces used for the data and indexes being inserted. I would avoid "-2" automatic setting.
    5. db2set DB2_SKIPINSERTED=ON (instance restart required)
    6. db2set DB2_USE_ALTERNATE_PAGE_CLEANING (instance restart required)
    7. Eliminate any unnecessary or questionable indexes.
    8. If you have LOB data, and using DB2 9.7, use 32K page size for table and define INLINE LENGTH (as large as possible) for LOB columns.
    Last edited by Marcus_A; 04-02-11 at 20:17.
    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
  •