Lightning Fast I/O via zHyperLink and Db2 for z/OS Exploitation

As the size of database and the volume of transactions increase, many of Db2 transactions tend to spend significant amount of wait time in database read and log write I/Os. zHyperLink is introduced to help reduce b2 transaction latency. zHyperLink is a new short distance mainframe link technology, connecting directly between the CPU and the I/O device.

It is designed for low I/O latency to access the disk cache. This article introduces zHyperLink technology and how Db2 applications can take advantage of zHyperLink to reduce the transaction latency.

zHyperLinks and Requirements

zHyperLink is designed to reduce the I/O latency by providing a point-to-point communication path between IBM z14 or z14 ZR1 Central Processor Complex (CPC) and the I/O bay of DS8880 family through optical fiber cables and zHyperLink adapters as shown in figure 1. below. zHyperLink connection is limited to a distance of 150 meters (492 feet) similar to CF direct links. As of this writing, zHyperLink is exploited by VSAM and Db2 for z/OS.

How fast is the zHyperLink I/O? The standard I/O processing using High Performance Ficon (zHPF) requires 100 to 200 microsecond per read I/O operation to the disk cache. We have observed zHyperLink read I/Os as fast as 17 to 22 microsecond per 4K I/O operation. The enablement of zHyperLink requires both hardware and software updates.

Hardware requirements are:

• Z14 or z14 SR1 with zHyperLink Express adapter installed in PCIe I/O Drawer
• DS8880 zHyperLink adapters
• DS8880 with I/O bay planar board and firmware level 8.3 or above
• 24x MTP-MTP cables (24-fiber cables with Multi-fiber Termination Push-on (MTP) connectors.)

Software requirements are:

• z/OS 2.1 and required z/OS (IOS, RMF and DFSMS) maintenance
• For Db2 exploitation of zHyperLink read, Db2 11 for z/OS and Db2 12 for z/OS(Function Level 100 and above) with maintenance
• For Db2 exploitation of zHyperLink write, Db2 12 for z/OS (FL100 and above) with maintenance

Figure 1. zHyperLink high level configuration

zHyperLink and Synchronous I/O

During the development phase, zHyperLinks were internally called as “sync I/O”. This confused us, Db2 development team and I would like to clarify what “synchronous I/O means. Standard synchronous I/O : Traditionally, Db2 calls random database I/O as “sync I/O” because the I/O operation is synchronous to the Db2 application which issues SQL calls. Once Db2 requests “sync I/O” to z/OS, the application is suspended while waiting for I/Os to complete and get re-dispatched. The wait time is accounted as database sync I/O time (QWACAWTI ) in accounting class 3. The CPU time of I/O interrupt process is accounted as DBM1 address space I/O interrupt time in Db2 statistics trace if you have PI92652 installed.

zHyperLink synchronous I/O : Unlike traditional sync I/O, zHyperLink I/O operation is synchronous to the CPU processing. When a synchronous I/O is performed, the CPU “spins” until the I/O is completed, or the timeout value is reached. zHyperLink can significantly reduce the time that is required to complete the I/O because the major processing from standard I/Os such as the dispatching, interrupt handling, CPU queue time, and CPU cache reload activities are no longer necessary. At the same time, zHyperLink read I/O time is included as a part of Db2 class 2 CPU time (SQL processing time). This is similar to synchronous Coupling Facility requests such as GBP reads where the CPU time for CF access is charged to class 2 CPU time. We will cover CPU time considerations and observations in the later section.
The above paragraph explains why zHyperLink is called “sync I/O” by z/OS. As a matter of fact, the updated RMF reports DASD device activity performed by zHyperLink technology in the new “ Synchronous I/O Device Activity “ section.

This is a sample RMF report for your reference:

Figure 2. Sample RMF Synchronous I/O Device Activity report

IBM Rebook in the reference section contains more detail information on zHyperLink specification and requirement.

Db2 Exploitation of zHyperLink

Db2 for z/OS exploits zHyperLink technology where synchronous I/Os are requested, which are:

1. Database random read
2. Active log force write where Db2 suspends transactions such as during commit processing

A new Db2 system parameter, ZHYPERLINK, is introduced to control zHyperLink enablement at Db2 subsystem level. In addition to Db2 subsystem level, there are multiple layers of control to enable zHyperLinks.

• LPAR level control:
  ZHYPERLINK statement in IECIOSxx member in SYS1.PARMLIB or,
  z/OS command SETIOS ZHYPERLINK,OPER={ALL|READ|WRITE|NONE}
• Storage Class level control:
  The SMS storage class of the Db2 data sets must be enabled for zHyperLink eligibility using the Integrated Storage Management Facility (ISMF). Note that the default is set as not to use zHyperLink.
• Db2 subsystem parameter : ZHYPERLINK
  DISABLE: Db2 does not use zHyperLink and default value
  ENABLE : Db2 makes zHyperLink for all eligible I/Os (both database read and active log write)
  DATABASE : Db2 requests zHyperLink only for database synchronous reads. Note that zHyperLink read I/Os are currently limited to the page set with 4K CIs.
  ACTIVELOG : Db2 requests zHyperLink only for active log writes

Db2 Database Synchronous reads via zHyperLinks

Db2 11 and 12 exploits zHyperLink read capability on database sync read requests. As of this writing, zHyperLink technology is limited to I/O operations on the data set with 4K CIs. The page set with 8K, 16K and 32K are currently not supported due to z/OS restriction.

Another important consideration is that only the data which found in the disk control unit cache is eligible for zHyperLink read I/Os. Since the benefit of zHyperLink read is to reduce I/O latency, Db2 applications with large numbers of sync reads against tables or indexes are good candidates for zHyperLink exploitation. However, not all disk read requests will find the pages in the cache. When the system parameter ZHYPERLINK is set as DATABASE or ENABLE, Db2 requests zHyperLink I/Os through DFSMS Media Manager for the eligible 4K random reads. When zHyperLink is available and the data is found in the disk cache, Db2 requests for zHyperLink I/O will be successful. If the data is not in the cache, DFSMS Media Manager will convert the request to the standard FICON I/O requests and suspend requesters (Db2 application).

To estimate the benefit of zHyperLink, it is important to understand the disk cache hit rate. Db2 and DFSMS added the additional instrumentations to indicate the cache hit.

Db2 thread level information : Db2 accounting class 3 : This new field indicates how many database synchronous I/Os hit the disk cache (QWACWCD)

Data set level information : SMF 42 record type 6:

Db2 Performance of zHyperLink Read

Performance measurements for zHyperLink Read were performed under the following environment:

• Db2 12 FL501, z/OS 2.2 with necessary maintenance
• z14 M05-3907-7E7 and z14 zR1 connected to DS8886
• Brokerage online transaction workload (approx. 800GB data base size)
• The transactions were executed as native SQLPL applications from zLinux clients, approximately 4000 transactions per second
• With and without additional background heavy query workloads
• Most of the measurements were done with >95% disk cache hit rate

The following chart (figure 3) shows average transaction elapsed time for the same workloads, one with zHyperLink read enabled, one with standard I/Os using High Performance Ficon. The total size of buffer pools is set as 10GB and there are average 28 random read I/Os per transaction.

Because the sync I/O wait time is reduced dramatically with zHyperLink, total elapsed time is reduced to half.

Figure 3. Average transaction elapsed time with 10GB bpool

Figure 4 shows the similar measurements comparison between High Performance Ficon and zHyperLinks. The difference from the previous measurement is the total size of buffer pools is set as 70GB and average numbers of random I/Os was 7.5 per transaction.

Again, due to the reduction of the sync I/O wait time, the transaction elapsed time is reduced by 23 % with zHyperLink. The benefit is less than above case (10GB buffer pool) as there is less sync I/Os due to more buffer pool hit with larger buffer pools.

Figure 4. Average transaction elapsed time with 70GB bpool

In addition to the number of sync I/O, the degree of improvement from the zHyperLink highly depends on the numbers of random I/O which are cache hit. We do not recommend enabling zHyperLink if the disk cache hit rate is less than 75-80%.

Figure 5 explains the locations where Db2 finds the data, indicating fastest from the top. When Db2 accesses the data, the fastest method is to access the data in memory. A good example is index in-memory (or Fast Traversal Blocks reference) that Db2 introduced in Db2 12. If the pages are not in memory outside of buffer pools, then the next optimal place is to locate them in the Db2 buffer pools to avoid I/O operation. Then zHyperLink technology comes next as it can be significantly faster than the standard I/O from the disk cache. If the data is not in the disk cache, the disk access (SSD or HDD) needs to be performed. The bottom line is that our recommendation to invest on the memory to optimize the performance has not changed.

However, zHyperLink read support can help if you have the requirement to improve or scale the I/O bound transactions without increasing the memory foot print.

Figure 5. Db2 database access

Next week, we’ll start by looking at the CPU cost of zHyperLink read…