Optimize DB 2 Buffer Pool performance with the

Optimize DB 2 Buffer Pool performance with the new Performance Wizard and Buffer Pool Tool for DB 2 Joel Goldstein Responsive Systems joel@responsivesystems. com Session Code: F 09 May 13, 2010 8: 30 am Platform: z/OS

Presentation Objectives • Buffer Pool Tool Software Objectives & Architecture • Components and Functions • Mainframe and Workstation • Utilities • Using Buffer Pool Tool • Analyzing sets of data • Finding the problems and opportunities • Illustrate before/after tuning performance • Performance Wizard – reduces your work, improves your productivity • Summary © Responsive Systems 2010 www. responsivesystems. com 2

Software Objectives • Provide the ability to predict the effect of buffer pool changes • Simple changes – size & thresholds • Moving objects into other existing, or new, pools • Predict the I/O rate/sec – the only measurable metric • Hit Ratios are interesting, but useless as performance metrics • Provide a reliable technique for grouping objects into multiple pools • Ramos and Samos ( and then working set size) • Is the Industry Proven technique!! • Show the performance projections, and let you make the intelligent decisions based upon your resources 3 © Responsive Systems 2010 www. responsivesystems. com

Software Objectives - 2 • • • Show you how to tune Show you the effect of changes Show you the rational behind tuning changes Show you both system and application performance issues Learn how things work – your system & your applications Optimize system and application performance, and memory utilization • Memory isn’t unlimited yet, and certainly not at most installations • Some large client systems are already past 16 Gigabytes of pool allocations 4 © Responsive Systems 2010 www. responsivesystems. com

NOT - Software Objectives • Be a black box, and present only recommendations • You can see the reasons/rationale behind any tuning changes you make – tuning should be a learning process • Change the system dynamically from snapshot statistics • All changes determined and implemented by the analyst • By the time active monitoring detects, and reacts, to a significant performance change, the system has already changed again… • Too much overhead to do it properly…. . • Not possible to predict from Statistics…. Averages of averages… you need a buffer manager and IO trace • Run/Monitor all the time • Continuous CPU consumption/overhead • Not necessary when tuning peak periods, or problem periods 5 © Responsive Systems 2010 www. responsivesystems. com

Overall System 6 © Responsive Systems 2010 www. responsivesystems. com

Collecting Data • What are your busiest or problem times? • This is when you want to collect data • At least twice, to determine workload consistency • Depending upon system size/volume, collector should run up to one hour – really big systems, 5 -15 Mins • Full Buffer Manager and IO trace (how big is BIG? ) • Small snapshots, at intervals, lack statistical validity • National Bureau of Standards sampling techniques • Can collect up to 16 Gigabytes of data • Low overhead process, 3 -5% - only during the collection 7 © Responsive Systems 2010 www. responsivesystems. com

Collected Data… Now what 1 ? • Run Statistics PCSTAT • Statistical analysis • Reports • PC file • Run Simulation(s) PCSIM Base Simulation • Shows performance at varying pool sizes • Reports • PC file – Wkset sizes, as well as I/O rate projections • Base simulation – one size the same as the current size • Consolidated Reporting CONSREP • Does both of the above automatically • Avoids having to download multiple files to the workstation 8 © Responsive Systems 2010 www. responsivesystems. com

Collected Data… Now what 2 ? • In most cases we go to the workstation now • We need the detail print reports in rare cases, since almost all the data is available on the workstation. • We’ll look at some data and analysis from several systems • There is no absolute analysis path, it all depends on the performance data 9 © Responsive Systems 2010 www. responsivesystems. com

Large System – Buffer Pools 160 M GP/hr BP 2 has 2/3 of the entire system I/O rate 10 © Responsive Systems 2010

BP 2 – with the high I/O rate Default graph at pool level 11 © Responsive Systems 2010 www. responsivesystems. com

How much do you think SP Getpages cost? © Responsive Systems 2010 www. responsivesystems. com 35 Mins Elapsed 12

© Responsive Systems 2010 www. responsivesystems. com 13

Same System – 7 Mos. Later 198 M GP/hr Getpage rate is UP 20% added 2 more pools IO rate is 1100 per second Lower 14 © Responsive Systems 2010 www. responsivesystems. com

System Configuration – for a different system Performance Fallacy – just use a lot of memory, you don’t need multiple pools…. Except, except …. System will generate 120 Million GP Hr. 15 © Responsive Systems 2010 www. responsivesystems. com

BP 20 - 210, 000 Buffers All the TS = 2100 buffers 16 © Responsive Systems 2010 www. responsivesystems. com

BP 20 2 objects, 2/3 of the SP access 17 © Responsive Systems 2010 www. responsivesystems. com

BP 20 First heavy sequential Memory Resident, 20% of pool GP, 1/3 of SP GP 18 © Responsive Systems 2010 www. responsivesystems. com

BP 20 Second heavy sequential 1/3 19 © Responsive Systems 2010 www. responsivesystems. com

BP 21 - all Indexes 210, 000 Buffers 2100 pages 20 © Responsive Systems 2010 www. responsivesystems. com

BP 21 Heavy sequential Index 21 12% of system getpages © Responsive Systems 2010 www. responsivesystems. com

BP 21 Heavy sequential Index - Resident 60% of SP G/P 22 12% of system getpages, resident © Responsive Systems 2010 www. responsivesystems. com

Sequential access costs…. $1 M per year Top 8 > 10% 23 Over $1 Million/Year © Responsive Systems 2010 www. responsivesystems. com

What does a Hit Ratio really tell you? 24 ©The first 50% does not get much payback Responsive Systems 2010 www. responsivesystems. com

The I/O rate is a measurable Metric 25 Why does the next 50% help so much? © Responsive Systems 2010 A critical WKSET was reached www. responsivesystems. com

What is a Working Set? A WKSET is the number of pages in the pool at any point in time… No relationship to DB 2 catalog information Track and calculate over time, not a momentary snapshot Snapshots are useless and mis-leading 26 © Responsive Systems 2010 www. responsivesystems. com

As the pool size increases, the WKSET increases Pool increased by 125% wksets increased by 114% and 112% Other objects may have higher, or smaller, growth percentages 27 © Responsive Systems 2010 www. responsivesystems. com

Objects that got the greatest benefit… Sometimes not as we expect… 28 © Responsive Systems 2010 www. responsivesystems. com

An original Pool Size, Sequential Objects 5000 29 © Responsive Systems 2010 www. responsivesystems. com

Larger Pool Size 15, 000 Also one fewer object in cluster 30 © Responsive Systems 2010 www. responsivesystems. com

Max Wkset does not grow – 1 st Object Payoff But Avg Wkset does grow - note the as the pool size increases 31 © Responsive Systems 2010 www. responsivesystems. com

Wkset does grow, initially. . – 2 nd Object Payoff 32 © Responsive Systems 2010 www. responsivesystems. com

Different DB 2 System 33 © Responsive Systems 2010 www. responsivesystems. com

34 © Responsive Systems 2010 www. responsivesystems. com

The objects in BP 3, sorted by SP access 35 © Responsive Systems 2010 www. responsivesystems. com

Largest contributors to the pool I/O rate 36 © Responsive Systems 2010 www. responsivesystems. com

Metrics for the heaviest I/O object 30% of the BP 3 I/O rate 37 © Responsive Systems 2010 www. responsivesystems. com

Base simulation 30, 000 buffers saves 100 I/O Sec 38 © Responsive Systems 2010 www. responsivesystems. com

Cluster analysis of the Wksets Two very large objects, may not belong in this pool 39 © Responsive Systems 2010 www. responsivesystems. com

Exclude – effect of taking them out of the pool Saves 260 I/O per Sec. At the original pool size 40 © Responsive Systems 2010 www. responsivesystems. com

Include into new pool Component: >>> DB 2 Buffer Pool Simulation <<< Results of Simulation for Buffer Pool. . BP 5 Bpool Get. P total. . . 1, 639, 783 Bpool Size Get. P used Ap. Hit Ratio Elapsed Time 12, 000 1, 611, 264 1, 465, 361 91. 0 % 00: 11: 47 16, 000 1, 606, 878 1, 471, 613 91. 6 % 00: 11: 44 20, 000 1, 601, 930 1, 474, 626 92. 1 % 00: 11: 41 24, 000 1, 593, 240 1, 473, 702 92. 5 % 00: 11: 38 28, 000 1, 561, 755 1, 448, 138 92. 8 % 00: 11: 32 32, 000 1, 549, 369 1, 441, 324 93. 1 % 00: 11: 25 Bpool Size 41 Num. of Hits Pages Read I/O Sy. Hit Ratio Norm. I/O Rate 12, 000 370. 9 /S 212. 4 /S 83. 7 % 9. 3 % 16, 000 341. 7 /S 197. 8 /S 85. 0 % 8. 7 % 20, 000 322. 2 /S 187. 1 /S 85. 9 % 8. 2 % 24, 000 305. 1 /S 176. 6 /S 86. 6 % 7. 7 % 28, 000 294. 0 /S 169. 6 /S 87. 0 % 7. 5 % 32, 000 284. 1 /S 163. 0 /S 87. 4 % 7. 2 % © Responsive Systems 2010 www. responsivesystems. com

BP 4 42 © Responsive Systems 2010 www. responsivesystems. com

BP 4 Two dozen analysis graphs…. 43 © Responsive Systems 2010 www. responsivesystems. com

BP 4 44 © Responsive Systems 2010 www. responsivesystems. com

BP 4 45 © Respo nsive Syste ms 2008 www. re

One large object – perhaps this should be moved out… 46 © Responsive Systems 2010 www. responsivesystems. com

Make the pool larger - how much can we save? 47 © Responsive Systems 2010 www. responsivesystems. com

Put it into a new pool Results of Simulation for Buffer Pool. . BP 6 Bpool Get. P total. . . 162, 392 Bpool Size Num. of Hits Ap. Hit Ratio Elapsed Time 12, 000 139, 769 123, 354 88. 3 % 00: 19 14, 000 137, 578 122, 590 89. 2 % 00: 16 16, 000 134, 934 121, 677 90. 2 % 00: 11 18, 000 134, 082 121, 914 91. 0 % 00: 10: 09 20, 000 132, 577 121, 396 91. 6 % 00: 10: 06 22, 000 130, 489 120, 368 92. 3 % 00: 10: 02 Bpool Size 48 Get. P used Pages Read I/O Sy. Hit Ratio Norm. I/O Rate 12, 000 94. 8 /S 30. 6 /S 58. 0 % 13. 6 % 14, 000 82. 0 /S 28. 0 /S 63. 3 % 12. 5 % 16, 000 70. 9 /S 25. 0 /S 67. 9 % 11. 3 % 18, 000 62. 0 /S 22. 8 /S 71. 9 % 10. 4 % 20, 000 54. 5 /S 21. 0 /S 75. 1 % 9. 6 % 22, 000 44. 3 /S 19. 2 /S 79. 6 % 8. 9 % © Responsive Systems 2010 www. responsivesystems. com Moving to a new pool provides better overall performance

Eliminating I/O Saves Money !! 49 © Responsive Systems 2010 www. responsivesystems. com

Last large System – 200 M GP/Hr 50 © Responsive Systems 2010 www. responsivesystems. com

Last large System – after Tuning. . 223 M GP/Hr 51 © Responsive Systems 2010 GP rate is up 10%, IO rate decreased 3, 000/Sec www. responsivesystems. com

I/O Cost Analysis Tab 52 © Responsive Systems 2010 www. responsivesystems. com

Expands existing presentation data w/24 hr projections 53 © Responsive Systems 2008 www. responsivesystems. com

Partition Analysis – highlights objects 54 © Responsive Systems 2010 www. responsivesystems. com

Partition Analysis – Partition data 55 © Responsive Systems 2010 www. responsivesystems. com

Memory Analysis 56 © Responsive Systems 2010 Graphic or Detail www. responsivesystems. com

Recent User data & question _ CRIT 04: 03: 38 -04: 03: 38 High percent of BP 8 reads encountered paging during the last minute: 536100 _ CRIT 07: 51: 20 -07: 51: 20 High percent of BP 48 reads encountered paging 09/29/09 during the last minute: 100 _ CRIT 17: 49: 19 -17: 49: 19 High percent of BP 49 reads encountered paging 09/30/09 during the last minute: 92 _ CRIT 12: 02: 12 -12: 02: 12 High percent of BP 3 reads encountered paging 10/01/09 during the last minute: 330 _ CRIT 09: 46: 48 -09: 48 High percent of BP 5 reads encountered paging 09/28/09 during the last minute: 327 57 © Responsive Systems 2010 www. responsivesystems. com

Performance Wizard for DB 2 • The Performance Wizard • Reduces your work and analysis time • Reduces your stress, you know your next tuning actions • Uses Buffer Pool Tool Consrep data as input • Shows the performance issues • Points you right at the problem areas • Illustrates the specific issue • Recommends actions when appropriate • Addresses many specific performance areas, and cost factors of your system and application performance 58 © Responsive Systems 2010 www. responsivesystems. com

• Performance Wizard analysis areas • Memory and Paging • Buffer pool sizes, thresholds, IO issues • Tells you how much you can save from changes • DASD performance • Tells you which objects have poor performance • Object Analysis • Objects with heavy SP access, CPU saving opportunity • Partition usage by object • Group Buffer Pool • Cross system performance issues 59 © Responsive Systems 2010 www. responsivesystems. com

• Performance Wizard analysis areas • $ Savings • Provides a guesstimate of potential cost savings from CPU cost reductions, IO elimination, and productivity improvements • Workload Analysis • Characteristics of your workload • Futures… • Many more rules and analyses are being coded • Other bells and whistles as we have time to program them • Printed report format • Look at the areas online • Print/save the report 60 © Responsive Systems 2010 www. responsivesystems. com

Performance Wizard subject areas Subject Area Tabs for the Types of Analysis ©Responsive Systems 2010 61

Performance Wizard Buffer Pool sample Buffer Pool Analysis ---------------------BP 0 : ------- WBP # 1 - Non-catalog objects in BP 0, they should be moved to other pools. WBP # 17 - Heavy access to SYSEBCDC, application use of SYSDUMMY. WBP # 31 - BP 0 is too small, increase to 8000 if memory is available. BP 1 : ------- WBP # 6 - vdwqt should be less than dwqt WBP # 7 - vpseqt should not be 100%, suggest 95 -98% range. WBP # 11 - Pool may be too large and wasting space. WBP # 13 - vdwqt should not be > 89% - and may cause spth, dwth, iwth and write engine problems. BP 4 : ------- WBP # 34 - Pagefix would save 8% CPU, the system is already paging, memory is over allocated. WBP # 27 - IO rate can be reduced 100/Sec or more by increasing buffers, if memory is available. WBP # 28 - IO rate can be reduced 20% or more by increasing buffers, if memory is available. WBP # 32 - 4 K pools should be allocated in multiples of 1, 000 buffers BP 7 : ------- WBP # 9 - Random Object(s) are monopolizing the pool, check Cluster Analysis WKSet sizes. DWF 005 P. WK. XWF 0620 WBP # 34 - Pagefix would save 8% CPU, the system is already paging, memory is over allocated. WBP # 27 - IO rate can be reduced 100/Sec or more by increasing buffers, if memory is available. WBP # 28 - IO rate can be reduced 20% or more by increasing buffers, if memory is available. WBP # 10 - Sequential Object(s) are monopolizing the pool, check Cluster Analysis WKSet sizes. DOM 001 P. DBDB 2. SOM 004 WBP # 22 - Pages/Write 1. 66 set vdwqt=(0, 40) or (0, 80). WBP # 24 - Number of pages read by Dyn. Pref > number of Getpages WBP # 30 - dwqt should be reduced to 20 or less based on objects in the pool and pool size ©Responsive Systems 2010 62

Performance Wizard DASD Perf sample DASD Performance Analysis ---------------------BP 2 : ------- WDP # 2 - DASD Synch Read IO poor performance, > 3 Ms - worst ten. Evaluate PAV and DASD Cache size. DOMW 01 P. DBDB 2. SOMW 12 DPC 006 P. DBDB 2. SPC 171 DGO 001 P. DBDB 2. SGO 028 WDP # 3 - DASD SP Read IO poor performance, > 7 Ms - worst ten. Evaluate PAV and DASD Cache. DMC 001 P. DBDB 2. SMC 120 DWF 007 P. DBDB 2. SWF 301 DRA 003 P. DBDB 2. SRA 107 DRA 003 P. DBDB 2. SRA 099 DGO 002 P. DBDB 2. SGO 095 DRV 001 P. DBDB 2. SRV 011 WDP # 5 - DASD ASynch Write IO poor performance, > 3 Ms - worst ten. Evaluate PAV and DASD Cache. DDI 001 P. DBDB 2. SDI 027 DRE 001 P. DBDB 2. SRE 002 DEPS 08 P. DBDB 2. SEPSEB 49 DMC 001 P. DBDB 2. SMC 008 DMC 001 P. DBDB 2. SMC 069 DMC 001 P. DBDB 2. SMC 067 DOM 001 P. DBDB 2. SOM 058 ©Responsive Systems 2010 63

Performance Wizard Object Analysis sample Object Analysis ---------------------WOB # 1 - Following Indexes have excessive SP access. Avoiding SP access will save a lot of CPU. BP 5 DDL 001 P. WK. XDL 0060 BP 5 DPW 003 P. WK. XPW 032 A BP 5 DPW 001 P. WK. XPW 0014 BP 5 DSL 001 P. WK. XSL 0164 WOB # 4 - Following Tablespaces have excessive SP access. Correcting this access will save a lot of CPU. BP 4 DPW 001 P. DBDB 2. SPW 001 BP 4 DWM 003 P. DBDB 2. SWM 314 BP 6 DWM 001 P. DBDB 2. SWM 007 WOB # 2 - Following partitioned indexes are not using all partitions during this collection. BP 3 # Parts. = 6 Used = 4 Low # = 1 High # = 6 DAM 001 P. WK. XAM 229 A BP 3 # Parts. = 257 Used = 106 Low # = 2 High # = 257 DRE 002 P. WK. XREW 40 A BP 3 # Parts. = 16 Used = 5 Low # = 1 High # = 16 DSL 001 P. WK. XSLW 05 A BP 3 # Parts. = 117 Used = 47 Low # = 3 High # = 117 DWC 003 P. WK. XWC 032 A WOB # 3 - Following partitioned tablespaces are not using all partitions during this collection. BP 2 # Parts. = 117 Used = 50 Low # = 3 High # = 117 DPC 006 P. DBDB 2. SPC 171 BP 2 # Parts. = 117 Used = 47 Low # = 3 High # = 117 DWC 003 P. DBDB 2. SWC 031 ©Responsive Systems 2010 64

What do you think is happening here? ©Responsive Systems 2010 65

• What Changed Analysis – DIFF for DB 2 • There was a change in overall system and application performance compared to last month (or week, etc) • The cause may be difficult to find • We will provide an automated approach that will enable you to drill down to the system and object access/usage changes between two sets of performance data. 66 © Responsive Systems 2010 www. responsivesystems. com

Comparing Performance Measurements • Has the mix or ratio of access changed? • Random vs. Sequential • List Prefetch, Dynamic Prefetch • Dynamic Prefetch is still Random Access • Different objects in use, or the most heavily accessed objects are not the same as the last run • Did some large batch jobs slip into your online performance period? 67 © Responsive Systems 2010 www. responsivesystems. com

Looking for changes in performance & activity 68 © Responsive Systems 2010 www. responsivesystems. com

Looking for changes in performance & activity 69 © Responsive Systems 2010 www. responsivesystems. com

Looking for changes in performance & activity 70 © Responsive Systems 2010 www. responsivesystems. com

Looking for changes in performance & activity 71 © Responsive Systems 2010 www. responsivesystems. com

A recent real world example…. We increased BP 2 Why did IOs go up instead of down for the same workload…? ? ? 72 © Responsive Systems 2010 www. responsivesystems. com

A recent real example…. IO rate for BP 2 did drop, it’s the other pools that increased. 73 © Responsive Systems 2010 www. responsivesystems. com

A recent real example…. Workload comparison is difficult…. DIFF makes it easy Summary – The workloads were not the same. 74 © Responsive Systems 2010 www. responsivesystems. com

Summary • Buffer Pool Tool • Predicts the effect of pool changes on the IO rate/second • Your tuning goal is to reduce the IO rate… and elapsed times • Shows you memory availability, and the IO saving/payback at varying pool sizes, object performance & DASD performance • Provides the industry proven object grouping approach • Predicts GBP performance • Performance Wizard - feature of workstation component • Provides quick and easy analysis of: 75 • • • LPAR memory and paging System buffer pools Objects within the pools, and partitions of objects Object DASD performance GBP performance concerns Workload analysis, and potential monetary saving opportunitiies © Responsive Systems 2010 www. responsivesystems. com

Summary • DIFF • Makes it easy to compare workloads, and workload performance • Maintains a workstation performance database, so you can compare yesterday to last week, or last month. • Aggregates data at the Sysplex Group level, allowing you to see performance for your entire Sysplex, as well as individual members. Come see a Demo of the Future Version of Buffer Pool Tool At our Vendor Booth 76 © Responsive Systems 2010 www. responsivesystems. com

Session F 09 Optimize DB 2 Buffer Pool performance with the new Performance Wizard and Buffer Pool Tool for DB 2 Joel Goldstein Responsive Systems Company joel@responsivesystems. com www. responsivesystems. com