Large Memory: Boosting DB2 for z/OS Performance

1 Copyright IBM Corporation 2015. Technical University/Symposia materials may not be reproducedin whole or in part without the prior written permission of memory : Boosting DB2 for z/OS PerformanceMark Rader DB2 for z/OS SpecialistIBM CorporationDelivered to MDUGN ovember 18 Please Note IBM s statements regarding its plans, directions, and intent are subject to change or withdrawal without notice at IBM s sole discretion. Information regarding potential future products is intended to outline our general product direction and it should not be relied on in making a purchasing decision. The information mentioned regarding potential future products is not a commitment, promise, or legal obligation to deliver any material, code or functionality.

2 Information about potential future products may not be incorporated into any contract. The development, release, and timing of any future features or functionality described for our products remains at our sole discretion. Performance is based on measurements and projections using standard IBM benchmarks in a controlled environment. The actual throughput or Performance that any user will experience will vary depending upon many factors, including considerations such as the amount of multiprogramming in the user s job stream, the I/O configuration, the storage configuration, and the workload processed. Therefore, no assurance can be given that an individual user will achieve results similar to those stated here. Copyright IBM Corporation 20151 Acknowledgements and Disclaimers Availability.

3 References in this presentation to IBM products, programs, or services do not imply that they will be available in all countries in which IBM operates. The workshops, sessions and materials have been prepared by IBM or the session speakers and reflect their own views. They areprovided for informational purposes only, and are neither intended to, nor shall have the effect of being, legal or other guidance or advice to any participant. While efforts were made to verify the completeness and accuracy of the information contained in this presentation, it is provided AS-IS without warranty of any kind, express or implied. IBM shall not be responsible for any damages arising out of the use of, or otherwise related to, this presentation or any other materials.

4 Nothing contained in this presentation is intended to, nor shall have the effect of, creating any warranties or representations from IBM or its suppliers or licensors, or altering the terms and conditions of the applicable license agreementgoverning the use of IBM software. All customer examples described are presented as illustrations of how those customers have used IBM products and the results they may have achieved. Actual environmental costs and Performance characteristics may vary by customer. Nothing contained in these materials is intended to, nor shall have the effect of, stating or implying that any activities undertaken by you will result in any specific sales, revenue growth or other results. Copyright IBM Corporation 2015.

5 All rights reserved. Government Users Restricted Rights Use, duplication or disclosure restricted by GSA ADP Schedule Contract with IBM , the IBM logo, ,andDB2 are trademarks or registered trademarks of International Business Machines Corporation in the United States, other countries, or both. If these and other IBM trademarked terms are marked on their first occurrence in this information with a trademark symbol ( or TM), these symbols indicate registered or common law trademarks owned by IBM at the time this information was published. Such trademarks may also be registered or common law trademarks in other countries. A current list of IBM trademarks is available on the Web at Copyright and trademark information at Other company, product, or service names may be trademarks or service marks of others.

6 2 Objectives Exploiting memory to achieve better Performance More memory with z13 Lower TCO by reducing CPU time while achieving lower I/O response time What happens when z/OS starts to run out of memory , how to prevent this Paging Dump considerations Batch window and DFSORT considerations SYSPLEX sympathy sickness DISCARD processing DB2 and system parameters for controlling memory DB2 and system APARs that you should know about it Monitoring REAL/AUX usage3DB2 and Large memory memory is cheap or one time charge, CPUs are expensive For every I/O that you save, you avoid the software charge for the CPU that it took to otherwise do that I/O Copyright IBM Corporation 20154*No server can fully exploit its maximum I/O bandwidthPCI Processor Capacity Index (IBM MIPS)141-wayCustomer ProcessorsPCI for1-way1695 Memory10 TBSystem I/O Bandwidth832 GB/Sec* TB512 GB1202902600288 GB/sec* GB/sec*15143TB384 GB/Sec*101-wayz10 ECz9 ECz196zEC12z13 IBM z13: Advanced system design optimized for digital business Copyright IBM Corporation 201556 memory is cheap Make sure LPAR has enough REAL storage REAL storage upgrade is the cheapest and easiest Performance upgrade REAL storage shortage not only can cause Performance issues.

7 If DB2 needs to create a dump, it can cause a small issue to become a massive SYSPLEX failure Cheapest because MLC and other charges do not factor into the amount of REAL storage Vendors do not charge by the amount of REAL on the CEC/CPC processor6 Copyright IBM Corporation 2015 CPU Cost Saving by Reducing DB2 Synch I/Os Banking (60M account) workload with 2 way data sharing : Reduce 11 % response time and 6 % CPU by increasing GBP from 52GB to 398GB with same LBP size (60GB) for both members Reduce 40% response time and 11% CPU by increasing LBP from 30GB to 236GB for both members with same reasonable GBP size (60GB) 7 Copyright IBM Corporation 2015 Benefit of Larger Buffer Pools Larger buffer pools can potentially reduce CPU usage by reducing synch I/Os z/OS team measures approx.

8 20-40us CPU per one synch I/O The benefit depends on the size of active workload and access pattern May not see any benefit if working set is very small and already fit in the buffer pools today May not see any benefit if working set is too Large and increment is not Large enough Pages have to be re-referenced not for one time sequential. read There is more value if pages are not prefetched. Try & validate, may not work well with customer s workload with high variations Available tool requires expensive set of traces and intensive analysis Copyright IBM Corporation 20158 Buffer Pool Simulation (DB2 11 PI22091) Simulation provides accurate benefit of increasing buffer pool size from production environment ALTER BUFFERPOOL now supports simulation pools To simulate the case of doubling the current 20,000 buffer pools with simulated VPSEQT of 30-ALTER BPOOL(BP1) VPSIZE(20000) SPSIZE(20000) SPSEQT (30) For example, if you want all of the buffer pool growth to be used for random Getpages, set SPSEQT to 0.

9 Default is SPSEQT=VPSEQT. Simulation is against local buffer pools, not group buffer pools. It supports local buffer pools with GBP dependent objects Storage cost for a simulated buffer pool is less than 2% for 4K pages SPSIZE(2000) or of simulated buffer pool will use about 156KB of storage. Copyright IBM Corporation 20159 Buffer Pool Simulation Output Output from simulation is written in statistics traces and DISPLAY buffer pool output OMPE V520 APAR to format statistics (APAR PI28338) DISPLAY BPOOL DETAIL DSNB431I -CEA1 SIMULATED BUFFER POOL SIZE = 20000 BUFFERS -ALLOCATED = 20000 IN-USE = 20000 HIGH IN-USE = 20000 SEQ-IN-USE = 2229 HIGH SEQ-IN-USE = 3684 DSNB432I -CEA1 SIMULATED BUFFER POOL ACTIVITY -AVOIDABLE READ I/O -SYNC READ I/O (R) =365071 SYNC READ I/O (S) =5983 ASYNC READ I/O =21911 SYNC GBP READS (R) =89742 SYNC GBP READS (S)

10 =184 ASYNC GBP READS =279 PAGES MOVED INTO SIMULATED BUFFER POOL =13610872 TOTAL AVOIDABLE SYNC I/O DELAY =158014 MS Copyright IBM Corporation 201510 Potential DB2 Benefit from Larger memory DB2 local and group buffer pools Reduction of elapsed time and CPU time by avoiding I/Os PGSTEAL(NONE) in DB2 10 = In memory data base CPU reduction from PGFIX=YES and Large page frames Thread reuse with IMS or CICS applications Reduction of CPU time by avoiding thread allocation and deallocation Thread reuse and RELEASE(DEALLOCATE) Reduction of CPU time by avoiding package allocation and parent locks DDF High Performance DBATs support with DB2 10 Ability to break-in persistent thread with DB2 11 Global dynamic statement cache EDMSTMTC up to 4G with DB2 11, default 110MB Reduction of CPU time by avoiding full prepare Local statement cache MAXKEEPD up to 200K statements with DB2 11, default 5000 Reduction of CPU time by avoiding short prepare In- memory data cache for sparse index MXDTCACH up to 512MB per thread, default 20MB Reduction of CPU and elapsed time with potentially better access path selection with DB2 11 RID Pool (MAXRBLK)