<le*****@kommunicera.umea.se> wrote in message
news:11*********************@z14g2000cwz.googlegro ups.com...
I'm trying to put together a simple util that will help me during
performance tuning, It will grab a bunch of values from different
snapshots and the db/dbm cfg, and print out different values, and if a
certain threshold value is reached, some information of which parameter
that might be increased/decreased to improve perfomance. Simple Example
bp_hit_ratio = (1 - ( (bp data phys rd + bp inx phys rd) / (bp data
logic rd + pool index logical rd) ))
print bp_hit_ratio
if (bp_hit_ratio < 0.9)
print Low bp_hit_ratio. Concider increasing bufferpool size
For NUM_IOCLEANERS I have only seen rather vauge statements such as:
Increase NUM_IOCLEANERS if "Buffer pool data writes" is much bigger
than "Asynchronous pool data page writes." or if "Dirty page steal
cleaner triggers" / "Dirty page steal cleaner triggers + ..." is too
high.
I understand that it is impossible to exactly define "much bigger" or
"too high" but as a rule of thumb, when would you concider increasing
NUM_IOCLEANERS regarding "Buffer pool data writes" vs "Dirty page steal
cleaner triggers", 2 times,5 time,10 times,100 times or something
completely different. And the same goes for ratio between Dirty page
steal cleaners and Dirty page steal cleaners + Dirty page threshold
cleaner triggers + LSN Gap cleaner triggers.
It's difficult to get a good metric by comparing pages vs triggers (or
triggers vs triggers), because the work done each time the cleaners are
triggered may vary, and also varies by the type of trigger.
Since each type of trigger makes the page cleaners do different work, each
trigger has a different benefit to the system. Generally, from highest to
lowest benefit, the triggers are LSN Gap, dirty page steal, dirty page
thresh.
Thus, you should first focus on tuning NUM_IOCLEANERS until the number of
LSN gap triggers reaches a plateau. Note that increasing SOFTMAX will
generally reduce the number of LSN gap triggers, and vice versa.
You should find that once these triggers are under control, the other two
types of triggers will also be reduced. If you want to further reduce the
number of dirty page thresh triggers, play with CHNGPGS_THRESH.
If you're running v8 FP4 or later, and don't want to deal with tuning the
cleaners, considering our "alternate" (self-tuning) page cleaning algorithm.
It can be enabled by running "db2set DB2_USE_ALTERNATE_PAGE_CLEANING=ON".
The rule of thumb in this case is to set NUM_IOCLEANERS = number_of_cpus.
If you've got HyperThreading or SMT enabled, then any value in the range
[number_of_cpus .. number_of_cpus * 2 ] is reasonable.
--
Matt Emmerton
