Fast index traversal - a Db2 12 greatest hit with John Campbell
Paul_McWilliams 110000JT36 Visits (6457)
This post is the first in a series that provides John Campbell's perspective on some of the most popular new features and capabilities in Db2 12 for z/OS.
During the early support program for Db2 12, one client who studied fast index traversal in detail saw a 9.1 percent CPU reduction with a three-level index, and a 22.9 percent CPU reduction with a four-level index. So the more index levels that there are, the greater the CPU benefit from fast index traversal. However, your own mileage will vary in terms of CPU reduction.
Fast index traversal is supported by a separate fast traverse block (FTB) memory area that is allocated outside of the bufferpools. This memory area uses a concatenated structure that contains copies of non-leaf index pages only, in a relative structure. Fast index traversal does not use the bufferpools. That is, the non-leaf index pages (except for the root page) that are cached in the FTB memory area are not fixed in the bufferpool. The index pages in the bufferpool are eligible for stealing, and can be removed from the bufferpool, on a least-recently used basis when the non-leaf pages are stored in the FTB memory area. The performance benefit comes about because the FTB memory area is an L2 cache-aware B-Tree like structure, and each page is equal in size to one cache line, or 256 bytes.
Each Db2 subsystem or data sharing member uses a single FTB memory area. The size of the FTB memory area is controlled by the INDE
A daemon running in the background in each Db2 subsystem determines which indexes are good candidates for the FTB memory area. To qualify, indexes must be UNIQUE, and INCLUDE columns are also supported. The length of the index entry, including the key and any additional columns, can have a maximum size of 64 bytes. The daemon reevaluates and adjusts its priority queue every two minutes.
To determine which non-leaf index pages are good candidates for fast index traversal, the daemon maintains a counter for each index pageset partition, and it applies an internal threshold to the value of the counter. The counter is adjusted differently for each of the following events:
You can use the SYSI
So, how does a index pageset partition come into the FTB memory area? The answer is that the daemon task, which is zIIP-eligible, runs in the background and reevaluates and adjusts the priority queue every two minutes. The daemon is a system thread and you can identify it by using the -DIS
DSNV497I -DB2A SYSTEM THREADS -DB2 ACTI
NAME ST A REQ ID AUTHID PLAN ASID TOKEN
DB2A N * 0 014.RTSTST00 SYSOPR 004C 0
V490-SUSPENDED 17081-10:05:25.83 DSNB1TMR +00000EBF UI38562
DB2A N * 0 014.IDAEMK00 SYSOPR 004C 0
V490-SUSPENDED 17081-10:01:16.95 DSNB1TMR +00000EBF UI38562
DB2A N * 0 014.IFTOMK00 SYSOPR 004C 0
V490-SUSPENDED 17081-10:05:22.32 DSNB1TMR +00000EBF UI38562
DB2A N * 0 010.PM2PCP01 SYSOPR 004C 0
V490-SUSPENDED 17081-10:05:26.51 DSNB1TMR +00000EBF UI3856
DBID PSID DBNAME CREATOR INDEXNAME LEVEL PART SIZE(KB)
---- ------------ -------------- -------------- ----- ----- --------
0256 0005 SZI10D § § § § § § § SZI10X 0002 00001 00000025
0261 0005 SZI20D A2345678901234 SZI20X 0002 00001 00000025
0262 0005 SZI30D SYSADM X2345678901234 0002 00001 00000025
0263 0005 SZI40D SYSADM SZI40X 0002 00001 00000025
******* DISPLAY OF STATS TERMINATED ****
DSN9022I - DB2A DSNTDSTS 'DISPLAY STATS' NORMAL COMPLETION
-START TRACE (PERFM) DEST(SMF) IFCID(477)
-START TRACE(STAT) DEST(SMF) CLASS(8) IFCID(389)
What about data sharing? The following diagram shows the high-level picture of the data sharing design considerations. The example assumes that the index pageset partition is group buffer pool (GBP)-dependent, and GBP-dependent protocols are being followed.
Index pages are always read and updated in the local buffer pool. The respective pages are registered in the associated GBP cache structure. When a Db2 member updates an index page, and it is pushed out to the group bufferpool cache structure, a cross-invalidate signal is sent to other Db2 members that have interest in that page. These members must refresh their copy of the page from the GBP cache structure because their existing version of the page is stale and down-level.
The FTB memory area is a read-only area. When a structure modification occurs for an index page set partition, the FTB must be refreshed. Index leaf page splits and consolidations are examples of such structure changes. A new FTB p-lock and IRLM notify are used for updating the FTB for the subject index pageset partition on all members of the data sharing group.
For migration, Db2 12 can use fast index traversal in function level 100, before you activate new function. In data sharing, the Db2 12 members can use fast index traversal in mixed-release coexistence, where some members still run Db2 11. In that case however, the FTB memory area is used only for index pageset partitions that are not GBP-dependent. If an index pageset partition becomes GBP-dependent, the FTB content is deleted or bypassed. After you activate function level 500 or higher in Db2 12, the FTB memory area can be used for GBP-dependent index page set partitions.
John Campbell is an IBM Distinguished Engineer for Db2 for z/OS development and Paul McWilliams is a technical writer for Db2 for z/OS.