IBM z Systems: Performance Report on Exploiting Large Memory for DB2 Buffer Pools with SAP

Abstract

It is reasonable to assume that using more memory will improve overall system performance. One obvious way to make use of more memory is to allocate more memory to DB2 buffer pools. Reducing I/O by caching more data in buffer pools should reduce response time, increase throughput, and provide CPU savings. The IBM zEnterprise 196 and EC12 systems can support up to 3 TB of real memory per server and 1 TB per LPAR. The IBM z13 system, announced in January 2015, supports up to 10 TB of real memory per server, more than three times that of the two previous generations of systems, and IBM has announced plans to support up to 4 TB of real memory per LPAR in the future. Currently, DB2 10 and 11 for z/OS theoretically allow up to 1 TB of memory to be used for all buffer pools in a given member. Special pricing for memory on the z13 system makes using larger amounts of memory more attractive and affordable.The IBM SAP on System z Performance Team, located in Poughkeepsie, NY, conducted a number of experiments to evaluate the performance effects of using large amounts of memory for DB2 buffer pools. We systematically increased the sizes of DB2 buffer pools and measured the effects on system performance. We used the SAP Banking Services (SBS) Day Posting workload, which is a good representation of a customer online transaction processing (OLTP) workload. It is memory intensive, accesses a large number of tables, and exhibits random I/O behavior. We ran with DB2 11 for z/OS in both single system and data sharing environments. In data sharing, the group buffer pools on the coupling facility (CF) provide an extra layer of caching. We explored the effects of adding memory to both the local buffer pools and the group buffer pools.We also experimented with reducing the number of buffer pools used. Although isolating or separating objects into their own buffer pools can provide essential monitoring capabilities and performance optimizations, it can also produce a large number of buffer pools over time which can be labor intensive and time consuming to maintain and tune. We conducted a set of experiments to evaluate the performance impact of reducing the number of buffer pools with hopes of simplifying buffer pool management without adversely affecting performance.With the introduction of the IBM z13 system, we repeated a couple of the measurements that we did on the zEC12 on a z13 to validate that the results would be similar.