For many years the price of z/OS memory has been decreasing, and IBM has been pushing the idea of large amounts of memory. Db2 for z/OS has virtually eliminated its virtual storage constraints.
Db2 performs best when it has lots of memory (i.e. real memory). Memory is still not free, but large memory can save money by reducing CPU consumption while at the same time reducing Db2 transaction response time. More memory also increases Db2 availability in cases where it is necessary to dump the Db2 address space, because if dumping causes paging to occur, the dump will take longer, and Db2 is not available during that time.
Db2 Buffer Pool Analyzer for z/OS
The first thing that comes to mind for the use of large memory is to increase the size of Db2 buffer pools. This can reduce the number of synchronous I/Os by increasing the buffer hit ratio. Furthermore, reducing the number of synchronous I/Os will reduce CPU consumption, because I/Os cost CPU time.
But, in the past customers have been reluctant to buy more memory without having the confidence that more memory would improve the performance. (That was especially true when memory was more expensive.) There is an old Db2 buffer pool simulation tool called Db2 Buffer Pool Analyzer for z/OS that requires collecting a Getpage trace (Db2 ifcid 198).
That tool had the flexibility to simulate moving Db2 objects from one buffer pool to another and to run the simulation with many permutations using the same trace. However, as memory sizes became very large, the size of the trace also needed to become very large. Eventually, this became impractical and IBM stopped recommending it.
Db2 Buffer Pool Simulation
In recent years IBM developed a more practical solution. It built a simulation facility into Db2 itself. Unlimited buffer pool sizes can be simulated accurately and can be run for long durations, such as hours or days. This simulation facility is simple to use. It is done by specifying a simulated buffer pool size parameter called SPSIZE. This complements the actual buffer pool size parameter called VPSIZE.
Db2 uses storage to track the pages in the simulated buffer pool, but it does not actually allocate the buffers. The approximate amount of storage used for tracking purposes is 2% of SPSIZE*4KB. For example, if SPSIZE is 6553600, approximately 500MB of storage is needed (regardless of the page size). The estimated CPU cost of simulation is 1% for each simulated buffer pool.
Up to 3 buffer pools can be simulated at the same time. However, since SPSIZE must be greater than VPSIZE, this facility cannot be used to simulate moving buffers from one buffer pool to another. Nor can it simulate moving a Db2 object to a different buffer pool.
VPSEQT is the sequential steal threshold (as a percentage of VPSIZE). When the number of sequential buffers exceeds this threshold, a sequential Getpage will steal the oldest sequential buffer. This is a way to preserve a portion of the Db2 buffer pool as a cache for random Getpages.
Since Db2 will use prefetch to avoid most sequential synchronous I/Os, you only need enough sequential buffers to support the amount of prefetch I/O activity in your system. For example, if you create many image copies during the batch window, the COPY utility will use prefetching, and it will steal a lot of random buffers if VPSEQT doesn’t constrain it. Subsequently, the buffer pool hit ratio might be less than it was before COPY was executed.
The default value for VPSEQT is 80%, which means that if the buffer pool size is a terabyte, then by default Db2 will allow prefetch to consume 800GB. Given that each Db2 utility prefetch I/O can read 512KB, and considering the need for double buffering, you can assume that each prefetch stream needs 1MB of storage to avoid having prefetched pages stolen before the application needs the pages.
If that were to happen, then the prefetch I/O would be followed by get sequential synchronous I/Os, which is a big problem. Therefore, 800GB is sufficient for 800 prefetch streams. But, each Db2 member is limited to 600 prefetch SRBs, which are shared among all buffer pools in the member. So there is no need to leave VPSEQT at 80% for a large buffer pool. IBM recommends lowering VPSEQT when the buffer pool is very large.
This is why Db2 supports a complimentary simulation sequential steal threshold called SPSEQT. For example, if you set SPSIZE to twice the value of VPSIZE, and if you want to prevent the number of sequential buffers from growing, then you would set SPSEQT to half the value of VPSEQT.
Db2 Deferred Writes
Be aware that this simulation feature only simulates reads, not writes. Whether you decide to use buffer pool simulation or not, when you do decide to increase the buffer pool size significantly, beware that if the vertical deferred write threshold is expressed as a percentage (which is true by default), then increasing the buffer pool size may result in larger spikes in the deferred write I/O activity.
If you want to avoid larger spikes, then lower VDWQT. The lowest possible percentage is 1%, but if you want smaller spikes than 1% will achieve, then specify VDWQT as an absolute number of buffers.
Profiling zHyperLink Performance and Usage
In this blog, we demonstrate how to profile zHyperLink performance and usage by reviewing one mainframe site’s recent production implementation of zHyperLink for reads, for Db2.
How A Db2 Newbie Quickly Spotlights the Root Cause of a Db2 Slowdown
This blog explores how a non-Db2 expert quickly identified latch contention arising from a Data Sharing Index Split as the root cause of a Db2 delay/slowdown.
Integrating Dataset Performance (SMF 42.6) and Db2 Buffer Pools & Databases (SMF 102/IFCID 199) Data
Dataset performance data from SMF 42.6 records provides disk cache and response time data at the Db2 buffer pool and Db2 database levels when integrated with Db2 Statistics IFCID 199 data.