This is my second article in a series about Db2 13 for z/OS. As one of my old colleagues in IBM Research used to say “Performance sells”. It has always been the case for Db2 and customers eagerly await the performance enhancements in each and every new release. There are some very nice, much needed, incremental performance enhancements in Db2 13. This article will introduce and discuss these performance enhancements to reduce CPU resource consumption and/or improve elapsed time performance. The specific enhancements I would like to discuss are:
- Enhancements to Fast Index Traversal (FTB)
- Enhancements to Index Lookaside (ILA)
- Enhancement for INSERT processing for Partition-By-Growth Universal Tablespace (PBG UTS)
- Enhancements to use of the SORTL instruction for SQL query processing
- Reduced false global lock contention for Partition-By-Range Universal Tablespace (PBR UTS) using Relative Page Numbering (RPN).
Enhancements to Fast Index Traversal (FTB)
Let me start by discussing the enhancements to Fast Index Traversal Blocks (FTB). This feature is an in-memory index performance enhancement providing for faster index lookup. The design point is to avoid expensive index B-tree traversal for random index access. When first introduced it was limited to only unique indexes and key size limited to 64 bytes including INCLUDE columns. This feature introduced a new separate FTB memory area which is not part of buffer pool memory with the following characteristics:
- L2 cache aware B-tree like structure
- Each page is equivalent to one cache line (256 bytes)
- Only includes root and non-leaf pages.
Db2 implemented a daemon that is continuously monitoring to determine which index pageset/partitions are good candidates for FTB. Db2 calculates a traverse count (factor) for each pageset/partition and then applies an internal threshold (cut off) for FTB use. The traverse count to adjusted as follows:
- Each index B-tree lookup +1
- Each index only access +2
- Each index lookaside -1
- Each sequential access -1
- Each index leaf page split divide by 2!.
As you can see above, an index pageset/partition is penalised as a candidate for FTB use when there are frequent index leaf page splits. Therefore it is important from a tuning perspective to reduce index disorganisation through a combination of pre-allocated distributed free space (PCTFREE) for the index and frequent index REORG.
Activation of Db2 12 Function Level M508 with the addition of PTF for APAR PH30978 extended FTB support to include within scope non-unique indexes with 56 byte limit applied. Furthermore Db2 removed the INCLUDE columns from counting towards the 64 bytes limit. Exploitation of this incremental enhancement to FTB required Db2 system parameter (ZPARM) FTB_NON_UNIQUE_INDEX to be set to YES. However, the default for system parameter FTB_NON_UNIQUE_INDEX was NO. With this default setting, customers were not benefiting from this enhancement.
Along the way in Db2 12 some useful Db2 commands for serviceability and customer use were added through PTFs in the service stream:
- -DISPLAY STATS(IMU) which provides snapshot of which index pageset/partitions ae using FTB and associated memory used
- -DISPLAY STATS(ITC)which provides FTB traverse count (factor) information in descending order for index pageset/partitions.
With the advent of Db2 13, the key length for unique indexes was extended to 128 bytes, the key length for non-unique indexes was extended to 120 bytes, and the default for the Db2 system parameter FTB_NON_UNIQUE_INDEX was changed to YES.
Initially with the introduction of Db2 12 there were robustness issues with the FTB feature and customers were advised to disable this feature. Since then significant reliability improvements have been made by the Db2 for z/OS Development Team. As a result of applying maintenance FTB is now running smoothly in production across many customers without any issues. So it with confidence that Db2 for z/OS Development have “opened the aperture” by introducing the enhancements described above in Db2 12 Function Level M508 and in Db2 13 to the FTB feature. If you have not done so already, I recommend that you consider giving the FTB feature a second chance as the potential for a reduction in CPU resource consumption in particular for online transaction workloads can be significant.
Enhancements to Index Lookaside (ILA)
Way back in V2R3 for OS/390 (1990), Db2 introduced a performance enhancement called Index Lookaside (ILA). This technique is used by Db2 to avoid repeated GETPAGE requests and index B-tree traversals on index leaf pages. Using ILA, Db2 tracks index key value ranges and checks whether additional requests for the same SQL statement or SQL cursor can be satisfied from the same index leaf page. If it can, the CPU resource consumption for additional requests related to the GETPAGE and index B-tree traversal can be avoided. This performance enhancement was particularly beneficial for skip sequential data driven batch processing and some types of complex query.
However, this technique was only applicable to SQL INSERT and DELETE on the clustering index and on non-clustering indexes having a high CLUSTERATIO. SQL UPDATE was excluded.
Db2 13 has now been enhanced to support:
- SQL UPDATE
- Non-clustering Indexes regardless of the CLUSTERRATIO value for SQL INSERT, UPDATE and DELETE
- Non-leaf pages are now in scope provided at least three qualifying statements in the same commit scope.
These enhancements should yield a reduction in CPU resource consumption when access path statistics collected by RUNSTATS are not current and where FTB is not being used for the subject index pageset/partition. Many DBAs have resisted adding additional indexes to improve SQL SELECT performance owing to index maintenance, but this enhancement may encourage those same DBAs to create additional indexes especially to encourage index only access.
Enhancement for INSERT processing for Partition-By-Growth Universal Tablespace (PBG UTS)
One of the challenges encountered with high concurrency INSERT processing is contention for obtaining locks when inserting a row.
For a multi-partition PBG UTS tablespace type, if Db2 cannot take a conditional partition lock (with no wait time), then Db2 will try the next and subsequent partitions. If no partitions are available, then the INSERT fails with SQLCODE -904 either because “partition full” (reason code 00C(009C) condition or “conditional lock failure” (reason code 00C90090) condition. Usually, the duration of the lock contention is minimal, but problems occur when an application thread issues SQL LOCK TABLE or lock escalation has taken place.
Db2 13 has been enhanced to attempt to avoid the SQLCODE -904 error condition. Db2 will maintain an in-memory list of 10 partitions, typically the higher partition numbers. Where the lock attempt was unsuccessful, Db2 13 will retry up to 5 of them before returning an error to the application.
Enhancements to use of the SORTL instruction for SQL query processing
Db2 12 with APAR PH31684 was enhanced to take advantage of the SORTL instruction available on IBM z15 processor model and later models via the Integrated Accelerator for IBM Z Sort feature.
To exploit this enhancement a customer must obviously be running Db2 on an IBM z15 processor model or later model, the Db2 package must be bound under Db2 12, the key size must not exceed 136 bytes and the data size must not exceed 256 bytes.
Db2 13 has been enhanced to analyze the data (SRTPOOL used, key size, data size) from previous executions and more realistically size workfile usage and memory consumption. This enhancement should help to reduce CPU resource consumption and elapsed time for sorts.
Reduced false global lock contention for Partition-By-Range Universal Tablespace (PBR UTS) using Relative Page Numbering (RPN).
False contention in a Db2 for z/OS data sharing environment occurs when two or more different objects hash to the same Lock Table entry (LTE) in the Lock Entry Table in the Global Lock Structure (LOCK1) in the Coupling Facility (CF). The processing path for determining false contention and responding is super expensive.
High CPU resource consumption has been observed for PBR UTS tablespaces using RPN. This is caused by Db2 using an unbalanced hashing algorithm resulting in excessive false contention typically when row level locking is used.
Db2 13 has introduced internal changes to the hashing algorithm to provide a better spread of hash values to reduce the number of false contentions. Function Level M500 is required for this enhancement. Any new PBR UTS tablespaces using RPN created will automatically use the new hash values. Existing PBR UTS tablespaces using RPN require a partition level REORG or LOAD REPLACE operation to effect the change to new hash values.
This completes my article introducing and describing the key performance enhancements in Db2 13. I hope you have found it useful. Look out for future articles from me about Db2 13 and all things Db2.