Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion Groups
Database Servers
DB2InformixIngresMS SQLOraclePervasive.SQLPostgreSQLProgressSybase
Desktop Databases
FileMakerFoxProMS AccessParadox
General
General DB TopicsDatabase Theory
Related Topics
Java Development.NET DevelopmentVB DevelopmentMore Topics ...

Database Forum / DB2 Topics / December 2005

Tip: Looking for answers? Try searching our database.

how to determine the NUM_IOCLEANERS?

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
lennart@kommunicera.umea.se - 15 Dec 2005 20:31 GMT
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.

Thanx in advance
/Lennart
Matt Emmerton - 15 Dec 2005 21:22 GMT
> 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
[quoted text clipped - 22 lines]
> 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
lennart@kommunicera.umea.se - 16 Dec 2005 07:39 GMT
[...]

> 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
[quoted text clipped - 19 lines]
> If you've got HyperThreading or SMT enabled, then any value in the range
> [number_of_cpus .. number_of_cpus * 2 ] is reasonable.

Thanx a lot Matt. I realize it is not that simple to determine this,
and that is probably why every article I have seen uses words like
"much bigger" etc. Anyhow, your explanation above gave me some ideas
and since I'm, on FP9 I will certainly try
DB2_USE_ALTERNATE_PAGE_CLEANING. Once again thanx a lot

/Lennart
 

> --
> Matt Emmerton
lennart@kommunicera.umea.se - 17 Dec 2005 08:17 GMT
[...]

> 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.

Just checking, inorder to use DB2_USE_ALTERNATE_PAGE_CLEANING=ON on
Linux I have to have a 2.6 kernel?

/Lennart

[...]
Mark A - 17 Dec 2005 15:44 GMT
> Just checking, inorder to use DB2_USE_ALTERNATE_PAGE_CLEANING=ON on
> Linux I have to have a 2.6 kernel?
>
> /Lennart

No, it works with all flavors of DB2 for LUW.
lennart@kommunicera.umea.se - 17 Dec 2005 19:56 GMT
> > Just checking, inorder to use DB2_USE_ALTERNATE_PAGE_CLEANING=ON on
> > Linux I have to have a 2.6 kernel?
> >
> > /Lennart
>
> No, it works with all flavors of DB2 for LUW.

Thanx, then I'll start playing with it right away

/Lennart
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2009 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.