467,134 Members | 944 Online
Bytes | Developer Community
Ask Question

Home New Posts Topics Members FAQ

Post your question to a community of 467,134 developers. It's quick & easy.

Prefetchers

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
Nov 12 '05 #1
  • viewed: 2467
Share:
2 Replies
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
Nov 12 '05 #2
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.
Nov 12 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

4 posts views Thread by Erik Hendrix | last post: by
5 posts views Thread by Evan Smith | last post: by
12 posts views Thread by Nobody | last post: by
1 post views Thread by mairhtin o'feannag | last post: by
reply views Thread by DB2 News Group | last post: by
4 posts views Thread by Joachim Klassen | last post: by
4 posts views Thread by natG | last post: by
2 posts views Thread by db2admin | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.