Geoff writes:
I read your latest blog on db2mag.com with interest. We have a buffer pool with a very low SRP (5-10%) and poor hit ratio (60%). This is not surprising due to the nature of the application that uses this buffer pool, which is the DB2 SQL Replication process. It is to be expected that the majority of accesses would be asynchronous I/O.
I was planning to increase the size of this pool to try and improve the hit ratio, and also to introduce the use of block pages which have not previously been used. I was going to allocate NUMBLOCKPAGES to be about 50% of the bufferpool size, until I saw your recommendation of setting this to about 3% of the bufferpool size.
I would be grateful if you could provide a bit more detail on why you suggest 3% ? It doesn't seem to me that this would be enough to make a significant difference in the case where the majority of the access is asynchronous.
Any help appreciated, thanks.
The Performance Science Projects
Well Geoff, glad you asked. When bufferpool block I/O was introduced in V8, I performed a number of performance tests with queries that scanned a 1,000,000+ row table. A 64MB bufferpool was used. The number of NUMBLOCKPAGES was varied from 0%, to 5%, to 50%, and finally to 90% of the bufferpool size in four separate tests. The results showed query times of 45 seconds, 37 seconds, 36 seconds, and 35 seconds respectively, so there is a rapid point of diminishing returns. More surprisingly, CPU time varied from 21, to 19, to 18, and back up to 20 seconds respectively, so there is actually a CPU penalty for over-allocating NUMBLOCKPAGES. Further testing showed that the "sweet spot" seems to be around 3-5% of the bufferpool size. If there is any sorting going on in the bufferpool, allocations of NUMBLOCKPAGES greater than 10% were actually detrimental to query throughput.
With such a low SRP, this bufferpool and the tablespaces assigned to it, should be optimized for asynchronous prefetch I/O. Setting NUMBLOCKPAGES to 3% of the bufferpool size will be a great start. The next thing we need to look at is the APPR, or Asynchronous Pages read Per Request - this is a measure of prefetch effectiveness.
Asynchronous Pages per Request
APPR = (Asynchronous pool data page reads + Asynchronous pool index page reads) / (Asynchronous data read requests + Asynchronous index read requests)
APPR can be computed for the database (use 'dbsnap2.txt'), each bufferpool (use 'bpsnap2.txt'), and each tablespace (use 'tssnap2.txt').
Generally, the value should be 10 or higher, with extra credit given for values closer to 16. However, if APPR is less than ten, and especially if it is in the low single digits, then your prefetch I/O is struggling and inefficient. This can occur when a bufferpool is too small, or too congested, for the prefetch demands being placed upon it.
So, long story short, after you set NUMBLOCKPAGES to 3% of the bufferpool size (NUMBLOCKPAGES should be a multiple of the bufferpool extentsize which should match the tablespace extentsize), compute the APPR for your bufferpool and tablespaces. If APPR is >= 10, the bufferpool size is big enough and you can probably obtain better performance by allocating the additional memory elsewhere. If APPR is < 10, gradually increase the bufferpool size until an APPR of 10 or higher is reached.
Thanks for your question.
The Shameless Marketing Moment
Brother-Panther™ for DB2 LUW automatically computes the APPR (and many more key metrics) for your databases, bufferpools, and tablespaces. Each Database receives a Database Score, like a Credit Score, which provides a single metric that summarizes the overall health and efficiency of each of your databases.
Database Score reports explain why points are subtracted and provide recommendations for improvement. All of your databases and their performance metrics are presented in a sortable grid so you can quickly and easily triage which databases need the most tuning help.
Brother-Panther is optimized for DPF and multi-partition databases, meaning you can easily compare performance attributes of multiple partitions to detect load imbalance and skew.
Are you using DB2 9 pureXML? If so, Brother-Panther provides the industry's first metrics for measuring pureXML performance!
Would you like to be able to trust but verify DB2 9 autonomic tuning? Brother-Panther provides performance trend charts with correlated change events so that you can readily see the performance impacts of configuration changes, registry variable changes, bufferpool changes, and index changes!
BE A HERO. Take Back Control of Performance, SLAs, and On Demand CPU Upgrades. Get your FREE trial of Brother-Panther today!
What time is it?
DBI is working with international customers around the globe. With so many time zones on this planet, it can be a challenge to figure out meeting times. To help make appointments and keep track of when you are at work, and when you might be at the DBA pub, I use www.timeanddate.com. DBI is located in Austin, Texas, USA. If you would like to automatically sync the clock on your computer with the atomic clock (this will help you be on time for meetings), check out the free Atomic Clock tools from www.worldtimeserver.com.
Austin, Texas, USA |
Until next time,
Cheers,
Scott
Scott Hayes
President & CEO, DBI
IBM GOLD Consultant
www.Database-Performance.info
www.Database-Auditing.info