Dear folks,

My company got a time critical DB2 system which got some complaints on
SQL delay recently. After investigation, it is found that the delay
probably caused by log switching (i.e the timestamps match for the
delays happened). However, the db2diag.log shows the log switch took
only a few ms to complete... It is believed that delay caused by new
log creation before the switching (i.e. the delay time did improve
when we reduced the log size to 100M though our customer is still
experiencing delay up to 10s occasionally). Though we think change the
disks may help, however, this could not be happened in a short
time....So, I need to look for some tunings in DB2 first. I am facing
the questions below and please shed some light on them for me if
possible.

How could I measure the new log creation time in DB2?
Would it be possible to create new logs before the DB2 need them?
Any other DB2 tunings could help on the situation?

Thanks in advance!

Henry

On Sep 11, 9:50*am, Guest <busilogi... (AT) gmail (DOT) com> wrote:
Hi,

if the SOFTMAX param is 100, when a log switch happens, a LSN GAP
cleaner is triggered, and dirty pages in the bufferpool are written to
disk. If you don't have enough iocleaners, writing is done
synchronously, and this can cause performance loss. Monitor if writing
is done asynchronously and LSN GAP cleaner triggers.

Another problem can be if you have small log files (LOGFILSIZ). This
cause that dirty pages cleaning is done many times.

Ensure that:

LOGFILSIZ or SOFTMAX are big enough to trigger dirty page cleaning
correctly
NUM_IOCLEANER=CPUs

If you have a big LOGFILSIZ may be opposite. Many data must be written
to disk. To avoid this low the SOFTMAX, or low CHNGPGS_THRESH to
ensure that the ammount of data to write is not too big

Ricardo Marcos Alonso
www.blogdb2.com

On 2011-09-12 19:18, rmarcosa wrote:
[...]
In addition to what Ricardo said, you might also consider turning on
DB2_USE_ALTERNATE_PAGE_CLEANING registry variable. Instead of waiting to
be triggered, cleaners write pages proactively.

/Lennart

On 12.09.11 13:18 , rmarcosa wrote:
The term 'CPUs' is a little bit confusing these days, so let me elaborate:

NUM_IOCLEANERS should be the number of cores. Ignore the SMT grade.

example: 2 sockets, 4 cores per socket, SMT 4

The OS will show you 2*4*4=32 CPUs.
NUM_IOCLEANERS should be set to 8 though.

--
Helmut K. C. Tessarek
DB2 Performance and Development
IBM Toronto Lab