Prefetchers

[John]

OK, there are lots of recommendations out there for number of
prefetchers to configure. But, there isn't any guidance on how to tell
if I have too many or not enough? What metrics can I use to figure
this out....Thx, Flick

[Fred]

If you have too few prefetchers (NUM_IOSERVERS is too low) you will
see relatively high values for "Time waited for prefetch (ms)" and
slow performance for queries that scan lots of rows. Prefetchers don't
do much when they're not being used, so there is little risk
associated with specifying too many prefetchers.

In addition to defining enough prefetchers, it is important to
identify the larger tables that are likely to be scanned and make sure
they are placed in separate tablespaces. When multiple tablespaces are
involved in a biq query that scans, there is a good chance that
multiple prefetchers will be used to pull in all the extents needed by
the query.

Good luck,

Fred

[PM \(pm3iinc-nospam\) CGO]

If i may add,
you'll also see high Time waited for prefetch (ms)" when you're too
agressively prefetching.
e.g. prefetch queue(s) are full, you're asking too many io's per sec (there
the wait).
Also if you prefetch more than the bp size you use, you compete for bp
space.
You should see something like stolen pages or forced writes, cleaners
triggered and waits, etc.

If you want to investigate more, i suggest you read a bit on DB2BPVARS.
http://publib.boulder.ibm.com/infoce...n/r0005665.htm

A good and simple way to measure the io performance is the old ms/io.
You start low and increase and see what it does on a tablescan or your
workload.
There would be a relation with the number of io's and the size you're
asking. (containers setup, extent and prefetch values, etc.)

In the end, every environment is different. (io capacity of adapters used,
etc.)

A general rule of thumb is NUM_IOSERVERS = nb cpu + a few extra.