A really brief blog this month, as I found something out while playing with buffer pool tuning (I have no hobbies!)
Buffer Pools …
There has been a long debate down the years about buffer pool management. How big should they be? How many should you have? Which parameters for which type? And so on. I have written numerous blogs, held IDUG Presentations and discussed myriad times with colleagues and customers about this topic.
Something New???
I found something new! Well, new to me anyways!!!
There was a trend a few years ago, called “big memory” where people were encouraged to recombine many data-sharing members downwards so you went from a 14 way to eight way or six way to four way. This was then backed up with more DBATs and expanded buffer pools. The argument was pretty convincing: Let Db2 do the magic!
Too Much Work!
You, as a puny human, have no chance of really knowing what is going on. So just splitting the buffer pools at the highest possible level is all you can actually reasonably do! The argument went that it is easiest to go for around 9 – 10 buffer pools.
DISPLAY GROUPBUFFERPOOL Done on a Regular Basis?
Of course you do!!!
What I found out recently, is the ominous counter in message DSNB415I titled “PREFETCH DISABLED NO READ ENGINE”. Nowadays, each Db2 subsystem/member of a datasharing system has 900 read engines but before, it was split out in 14 or eight sub-systems and is now being used in far fewer.
Zero Counter No More!
This counter was *always* zero whenever and wherever I checked, until last week… Now I see PREFETCH DISABLED NO READ ENGINE on a regular basis. There is a great blog from Robert Catterall.
In the environments I am looking at, they also have PREFETCH DISABLED NO BUFFER occurrences and well over 100 I/Os /second, so it does indeed mean trouble!
Time to Tune!
All you can do here is:
- Check your VPSEQT and see if you can nudge it up a bit.
- Throw some memory at the problem by increasing the VPSIZE and hope that the requested pages are then found in the buffer pool.
- Move smaller objects into PGSTEAL(NONE) buffer pools thus requiring no PREFETCH.
- As a last gasp, try and tune the SQL to not use PREFETCH as a method – very tricky of course!
Keep on Truckin’
But let’s be honest for a minute, if you have 900 read engines all humming along then your system is really running under pressure and you should *expect* to get this counter, I guess!
As Robert says: No need to panic!
TTFN,
Roy Boxwell