As many enterprise applications mature, the increases in customers, functions, features and transaction rates require scaling up. Several alternatives for maintaining acceptable performance include installing or upgrading hardware resources such as CPU speeds, disk volume and computer memory. Of these, adding memory can sometimes be the cheapest and most convenient.
In this article we review how features in Db2 for z/OS version 12 can use additional real memory to reduce transaction times and more efficiently use CPU and other resources to meet critical application needs.
Understanding Mainframe Resource Constraints
Running applications under a z/OS operating system is quite different from small server-based configurations. One major difference is the ability on the mainframe to run the machine at near one hundred percent CPU utilization with almost no performance degradation. On small systems, typical maximum CPU utilizations are around thirty percent; at higher utilizations tasks begin competing for CPU resources, resulting in slowdowns and elongated transaction times.
The result is that in the case of small server-based systems, a typical performance improvement is to add additional CPUs to machines, or more machines to the cluster. With mainframe systems, other resource-related performance enhancements are more effective. Of these (adding real memory, adding paging disk storage, adding network bandwidth, etc.) adding real memory tends to be less expensive and easier to implement.
In general, you look for resource constraints, then change the mix of available resources and configuration parameters in order to relieve the constraint. For example, if your system is memory constrained, you analyze tasks and processes that utilize memory. If you find that many sort processes are executing in-memory you may consider adding disk space or flash storage specifically for sorts. Alternatively, if your system is disk storage constrained, you consider tasks that use lots of disk storage (database backups, logging, data copies, etc.) and consider changes that reduce disk usage.
With Db2 version 12 for z/OS there are multiple Db2 processes that can utilize real memory while reducing CPU or other resource usage.
CPU Usage and Software Licensing
One additional consideration is the way that some z/OS software is licensed. IBM and some third-party vendors bill you for their software based upon CPU consumption. One common method is called the monthly license charge (MLC). z/OS keeps track of the average amount of CPU used and uses that amount as a basis for the amount of work done by the software. Sometimes the average is computed in complex ways, such as a four-hour moving average.
If software charges depend upon CPU usage, then shops have an incentive to reduce CPU times, and resource constraint analysis is a good beginning. Considering CPU usage as a constraint, review your database management system (DBMS) and application use of CPU and devise ways to reduce it by adding other resources. In this case, we consider adding real memory as a way to reduce CPU, and Db2 12 has several features that can take advantage of memory.
Add Memory to Increase Buffer Pool Sizing
One potential method of reducing CPU usage is to increase the sizes of various memory pools used by Db2. Of these, your virtual buffer pools are probably the largest. These are the memory areas used by Db2 I/Os when reading or writing data and index pages to and from disk storage.
A larger buffer pool allows some of the following to take place:
- Data and index pages read into an enlarged pool will remain there longer because they are not overwritten as often; hence, multiple references to specific data will find that data already in the buffer pool more often, reducing the need to re-read the data from disk;
- Updated pages can remain in the pool longer, reducing the need to write them to disk;
- Index processing can involve reading the index root page into the pool and then querying that page to determine what additional non-leaf pages must also be read (this being necessary in order to search an index for a range of key values); these pages will reside longer in the pool, making it less likely that later index searches will require disk access.
The result of larger buffer pools is usually to reduce I/Os required for reading and writing pages, and this has the effect of reducing CPU usage to execute those I/Os.
Consider Using In-memory Objects
An extension of the concept of increasing virtual buffer pool sizing is the ability to use in-memory objects. Prior to Db2 version 12, DBAs could implement an in-memory table or index by defining a buffer pool of sufficient size to contain the entire objects, then executing an SQL statement that resulted in scanning the entire object into the pool. This typically works only for small tables and indexes, and objects and pools must be defined carefully lest other objects read into the pool crowd out the selected tables and indexes.
Db2 version 12 introduces a new object, the index fast traverse block (FTB). This is a new structure defined within a block of memory outside of the buffer pools. Upon SQL execution that accesses an index, Db2 automatically determines whether an FTB would provide fast access to that index. If so, Db2 creates an FTB in the new area and uses it in subsequent access to the index when appropriate. Traversing the FTB uses less CPU than accessing a traditional index structure.
Add Memory to Reduce Paging
The z/OS operating system uses virtual memory. This allows defining a large virtual space of memory addresses to be used by applications while physical memory is limited. Internal tables define what virtual address ranges are being used and how they map to real memory. At times, it is possible that applications are using more memory than is physically available; in order to keep running, the systems must at times take a memory block (one not currently being used) and write it out to a disk file. Later, if that memory block is needed, the system must read it in from the disk into real memory. This process is called paging.
If a page of memory is ‘paged out’, it is not available for either execution of instructions on that page or to access data on the page. In the case of paging out memory containing executable code, this slows down application execution. If a data page containing updated data is paged out, the data remains unusable and locked until paged back in. Of course, reading and writing pages in and out of memory consumes CPU; hence, it is to the advantage of all applications that paging be minimized or eliminated.
Increasing the size of real memory can help to reduce or eliminate paging.
Dynamic SQL Caching
SQL that is created at the time of execution (and is not already stored in Db2 along with its access path) is called dynamic SQL. When such SQL arrives, Db2 must execute a process called a prepare. The prepare includes syntax checking, security authorization, and access path selection. Choosing a good access path can be quite resource-consuming, particularly for SQL with many joins subSelect or union operations. The greater the number of tables accessed, the greater the number of possible access paths. Even for a simple SQL statement joining two tables, Db2 may have to consider table scans, index usage (perhaps of multiple indexes), whether to sort, and other possibilities. Db2 must then cost all the choices and pick the one with the lowest cost.
Naturally, if the same (or similar) SQL statement arrives multiple times, Db2 may need to go through the same prepare each time. To improve this, Db2 implements several dynamic statement cache areas in memory. When the dynamic SQL statement is first executed, Db2 stores the statement and the prepared access path in a cache. Then, if the statement is executed again later, a cached version of the access path can be used, thus avoiding a prepare and its CPU costs.
Summary
Db2 version 12 for z/OS contains multiple features that allow CPU reductions through the use of additional real memory. Memory is usually the cheapest resource to add to a z/OS system. In addition, IBM notes that their strategic direction for Db2 for future scalability and performance improvements will require memory expansion. By allocating a substantial amount of real memory you can reduce CPU usage, reduce costs and prepare for future enhancements.
