By Akiko Hoshikawa, Bart Steegmans, Bituin Vizconde, John Campbell, Paul McWilliams.
IBM just announced the upcoming release of Db2 13. Among the many enhancements in the new release, Db2 13 improves real storage management and eliminates the need of setting the system parameter REALSTORAGE_MANAGEMENT. In this blog entry, we describe the latest recommendations for real storage management in Db2 12.
In short, if you have ample amount of available real memory in your z/OS LPAR, the recommendation is to use the REALSTORAGE_MANAGEMENT=OFF setting in Db2 12. Read on for more background, what we mean by “enough memory”, other considerations, and more about the changes coming in Db2 13.
When Db2 started to exploit more memory above the 2GB bar to improve scalability and performance in version 10, real storage was a ‘scarce’ and rather expensive resource. Therefore, Db2 Development introduced a subsystem parameter REALSTORAGE_MANAGMENT that allows customers to control how Db2 releases real storage frames back to z/OS that are allocated to Db2.
In recent years, many customers have seen significant increases in their Db2 for z/OS workloads. The workload growth naturally leads to the higher storage demands and an increase in the number of concurrently executed Db2 threads.
Customers are investing in memory to cope with these increased workloads, but also to take advantage of performance improvements that can be obtained from utilizing more memory.
However, with increases in the size and the number of threads, Db2 customers have also started to see the impact of releasing the 64-bit memory associated with these threads.
REALSTORAGE_MANAGEMENT subsystem parameter
In Db2 12, the REALSTORAGE_MANAGEMENT subsystem parameter defines how and when Db2 releases real frames consumed for 64-bit threads in Db2 buffer pools. To return the unused 64-bit real frames to z/OS Real Storage Manager, Db2 uses a z/OS service, IARV64 REQUEST=DISCARDDATA. We will refer to the invocation of z/OS IARV64 service with DISCARDDATA as “discard” or “discarddata processing”.
The options for REALSTORAGE_MANAGEMENT are, AUTO, ON and OFF and it determines the discard behavior and frequency. The default value is AUTO.
REALSTORAGE_MANAGEMENT = AUTO
Db2 discards thread storage at thread deallocation, or every 120 commits if the thread is reused.
If the available real storage on the LPAR drops below the z/OS frame Available Frame Queue Low threshold, Db2 enables “contraction mode”. In contraction mode, Db2 discards every 30 commits. The console messages DSNV516I and DSNS005I with a reason “LOW ON AVAILABLE FRAMES” indicate that Db2 is in contraction-mode.
DSNV516I csect name DSNVMON - BEGINNING STORAGE CONTRACTION MODE
DSNS005I csect-name DSNVMON - CONTRACTION INITIATED DUE TO LOW ON AVAILABLE FRAMES
When Db2 drops out of contraction mode, it issues the following message:
DSNV517I csect-name DSNVMON - ENDING STORAGE CONTRACTION MODE.
REALSTORAGE_MANAGEMENT = ON
Db2 is always operating in contraction mode when REALSTORAGE_MANAGEMENT is ON, and the following console messages are issued.
DSNV516I csect-name DSNVMON - BEGINNING STORAGE CONTRACTION MODE
DSNS005I csect-name DSNVMON - CONTRACTION INITIATED DUE TO REALSTORAGE_MANANGENT=ON
Frequently discarding real storage frames results in some CPU overhead, and this option is intended for systems in which the availability of real storage is limited. This value is not recommended for production systems, but it might be appropriate for development LPARs that have many Db2 subsystems and a limited amount of real storage.
REALSTORAGE_MANAGEMENT = OFF
Db2 does not request discarddata processing at thread deallocation, nor at commit.
When REALSTORAGE_MANAGEMENT is set as AUTO (or ON), Db2 uses IARV64 REQUEST = DISCARDDATA with the KEEPREAL(YES) option as described in APAR PM99575. The KEEPREAL(YES) option is used to reduce the overhead from constantly re-acquiring the frames immediately after releasing the ownership. However, this option makes monitoring of Db2 shared and common frames and estimating the required Db2 dump storage very difficult.
Unless the discarded KEEPREAL(YES) frames are stolen by non-Db2 usage, z/OS continues to account them under Db2, even though Db2 discarded the frames.
If a precise estimation is required, you can use a serviceability feature introduced via PI78979. Activating IFCID 0503 allows you to determine the amount of real memory truly allocated by Db2 at midnight in local time.
When severe storage conditions are detected, Db2 specifies KEEPREAL(NO)during discarddata processing. In that case the real frames backing the pages to be discarded are freed. They are no longer accounted to Db2 and are available for reuse by non Db2 applications.
One such condition is when a critical shortage on auxiliary storage is detected. In that case, messages DSNV516I and DSNS005I, this time with reason 'AUXILIARY SHORTAGE”, as well as message IRA201E CRITICAL AUXILIARY STORAGE SHORTAGE are expected.
Another severe storage condition occurs when you specify a value for the subsystem parameter, REALSTORAGE_MAX and the total amount of allocated Db2 real storage reaches the specified value.
REALSTORAGE_MANAGEMENT = AUTO implications
When using REALSTORAGE_MANAGEMENT=AUTO, which is the default behavior in Db2 12, Db2 is a good citizen and frequently releases unused frames back to z/OS. However, excessive discard requests from Db2 can overwhelm the z/OS LPAR and might result in LPAR level serialization. This is observed as a significant increase in Db2 system service address space (SRB) CPU time in the Db2 statistics report, and/or RSMQ spin lock contention in RMF or SMF 98. In some cases, excessive discard can cause z/OS common storage shortage (ESQA) as z/OS utilizes z/OS common storage for discarddata operation.
As indicated above, discarddata processing is triggered by thread deallocation. Therefore, minimizing the number of thread deallocations by increasing thread reuse can significantly help to reduce this impact.
Various optimizations have been implemented to reduce this overhead. For example, APAR PH36114 prevents the surge of discarddata requests to z/OS when the large numbers of distributed threads (DBATs) are deallocated by POOLINAC processing.
However, as Db2 development continues to introduce more features that increase memory usage to optimize performance, or as the number of concurrent threads grows, there is always a risk that the overhead from discarddata operations becomes more visible.
Recently, some customers who are running on IBM z15 observed an increase in CPU consumption in the Db2 system service address space after applying the PTF for APAR PH31684. PH31684 introduced sort acceleration to improve ORDER BY and GROUP BY sort processing by using the SORTL instruction (a new hardware instruction on IBM z15). The APAR can increase the thread memory usage when sort operations are performed, and subsequently also the amount of thread memory to be discarded at the thread deallocation or 120th commit.
In OLTP environments where the same, or a similar set of transactions are executed most of the time, Db2 often immediately reuses the thread storage that has just been deallocated.
When there is no shortage of real storage frames on the LPAR, frequent discard operations are not necessary. To avoid the possible negative performance impact caused by REALSTORAGE_MANAGEMENT AUTO option, triggering frequent discard processing, the recommendation is to set REALSTORAGE_MANAGEMENT to OFF if you have enough available real memory in your z/OS LPAR.
How much real storage is available on the LPAR?
If you want to determine how much real storage is available on the LPAR, you can use the IFCID 225 QW0225_REALAVAIL field. It indicates the number of available frames on the LPAR.
If you are using ZOSMETRICS=YES, you can also look at field QWOSLRSF (amount of free real storage on the LPAR in MB) in IFCID 0001.
How much available memory is “enough”?
Ideally, you want to have enough available real frames to avoid any paging activity during peak workload times, even when a system dump operation would be triggered during that time. To be on the safe side, make sure there is also some headroom for workload growth and unexpected changes in workload behavior, so it is advisable to have some additional real storage available at all times to absorb those as well.
We consider you to have “enough” memory when the number of available real frames at the peak is greater than the value of the MAXSPACE parameter in SDUMP option, with additional 20% head room for unexpected demand. In other words, if you meet the following condition at all times, it should be safe to switch to REALSTORAGE_MANAGEMENT = OFF:
MIN(REALAVAIL) > 1.2 * MAXSPACE
Db2 plan for real storage management
Db2 development is considering the following updates to the product to improve the resiliency and performance.
- In Db2 13 for z/OS, the storage management component is updated to avoid causing z/OS RSM serialization from discard requests. At the same time, the REALSTORAGE_MANAGEMENT subsystem parameter is eliminated in Db2 13.
- We are considering updates to REALSTORAGE_MANAGEMENT = AUTO behavior in Db2 12.
When REALSTORAGE_MANAGEMENT = AUTO is used in Db2 12, Db2 triggers storage cleanup that can result in CPU increase in the system service address space from CPU spin conditions. Our recommendation is to set REALSTORAGE_MANAGEMENT =OFF in Db2 12, if you have enough real frames in the LPAR to support the peak memory usage. Otherwise, we recommend to stay with AUTO. However, also consider looking into optimizations, such as increasing thread reuse. Db2 development is looking into improving this process to avoid the performance impact from the storage cleanup.