04-02-11, 11:02 #1Registered User
- Join Date
- Sep 2009
Unanswered: Ideas to improve Asynchronous Write Percentage (DB2 9.5 Fix Pack4 On AIX)
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.
04-02-11, 11:52 #2Registered User
- Join Date
- May 2003
Here are some things to look at:
- 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
- Make sure all LOB's in separate tablespace with File Caching On.
- 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)
- Make sure bufferpool is sufficiently large for tablespaces used for the data and indexes being inserted. I would avoid "-2" automatic setting.
- db2set DB2_SKIPINSERTED=ON (instance restart required)
- db2set DB2_USE_ALTERNATE_PAGE_CLEANING (instance restart required)
- Eliminate any unnecessary or questionable indexes.
- 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