Db2 for z/OS: Avoiding Splitting Headaches (the Index Page Kind)

This blog entry is about a Db2 for z/OS-internal process that has long been a headache-inducer for DBAs, and about some Db2 13 enhancements that can provide effective relief for those headaches. The Db2-internal process to which I'm referring is index page splitting.

Index page splitting: what it is and why it matters

Db2 for z/OS can support very high levels of row-insert throughput. One reason why this is so: a to-be-inserted row does not have to go into a particular page of a table's table space. Yes, if there is a clustering index on the table (and there will be such an index, if the table has any indexes) then there will be a "target page" for a to-be-inserted row - the page into which the new row should be inserted in order to best maintain row-clustering for the table; however, if that target page is locked at the time by some other process or is found to be full, Db2 will insert the new row into some other page - ideally, into a page relatively close (physically) to the target page, but into a "farther away" page if need be. The point here: insert throughput will not be slowed due to contention on, or fullness of, the table space page into which a new row should ideally go.

It's a different story for indexes on Db2 tables. When a new entry is to be added to an index (as a result of an insert operation), that new entry must go into a particular page, because Db2 maintains physical ordering of index entries according to index key value. If the page into which a new index entry must go is full, Db2 will do something called index page splitting. That operation involves moving a portion of the entries in the page to an empty page of the index, so that the formerly-full page will have room for the new entry (Db2 provides a pointer from the formerly-full page to the page into which relocated entries were placed, and a pointer from that page to the one that is next in terms of logical sequence).

The good about index page splitting is that it preserves physical ordering of index key values. As for the not-so-good effects of index page splitting...

• It causes the index in question to be less well-organized in terms of physical versus logical page sequencing. The index entries relocated as a result of an index page split can't just go into some page of the index that has some room - they have to go into an empty page. If all of the pages in an index structure are populated, the relocated entries will have to be placed in a new page at the "far end" of the physical index space. As more and more such relocations happen, the performance of index-based data access paths such as "matching index scan" degrades, because Db2 has to "jump" - maybe quite a long ways - from index page n ("n" referring to logical sequence) to page n+1, because logical page n+1 may be physically quite far from the physical location of logical page n (and then Db2 has to jump back to page n+2 in the logical sequence). An index REORG is required to get the physical sequence of pages in line with their logical sequence.
• Especially for indexes on tables that are the target of high-volume insert processes, index page splitting can be a real drag on performance, because an index page split operation is serialized by Db2 to protect the data integrity of the index, and that causes processes that need to access the affected part of the index tree structure to wait for the index page split operation to complete. This impediment to application throughput can be more pronounced when Db2 is running in data sharing mode in a Parallel Sysplex cluster of z/OS systems, because of the extra logging that accompanies index page split operations.

Given these negative impacts of index page splitting, you'd think that Db2 DBAs would want to be on the lookout for processes affected by index page splits, and for indexes getting a lot of index page split activity, and you'd be right in that thinking. Just one problem there: actually zeroing in on index page splitting hot spots was challenging (before Db2 13, that is).

A longstanding issue (prior to Db2 13): pinpointing index page split problems

For a long time, positively identifying processes driving index page splitting, and indexes getting a lot if index page split activity, was no easy thing. The issues:

• Identifying split-heavy processes - There was a trace record, IFCID 359 (associated with performance trace class 4), that could be used to monitor index page split activity, but this trace was not often activated at Db2 sites. One reason for that lack of use: IFCID 359 is a relatively high-overhead trace - it causes a trace record to be generated for every index page split operation that occurs in a Db2 system. In some cases, there can be a lot of index page splitting going on - especially if there is a high volume of insert activity for tables that have indexes defined on keys that are not continuously-ascending (index page splits do not occur for indexes defined on continuously-ascending keys - i.e., keys for which every new entry has a higher key value than all previous entries in the index). Overhead issues aside, IFCID 359 trace records also lack some information that would be helpful in identifying processes driving index page split activity: it does not provide the unit of recovery (UR) ID of the process that is causing an index page split to happen, and in a Db2 data sharing environment it does not provide the ID of the member subsystem that is performing the index page split.
• Identifying indexes with high levels of index page split activity - You could check the value of the REORGLEAFFAR column of the row for an index (or index partition) in the SYSINDEXSPACESTATS real-time statistics table in the Db2 catalog, and get maybe a sense that the level of index page splitting could be high for the index in question, but it was hard to be sure about this - index page splitting is not the only action that causes this counter to be incremented. On top of that, you couldn't determine if these (possible) index page splits for the index were taking a lot of time to be processed.

Bottom line: at best you could get kind of a fuzzy picture of indexes that might be seeing a lot of index page split activity, and you basically didn't have any clear indication of the processes driving index page split activity. Hard to take decisive mitigating actions when you don't have really good intel. Fortunately, Db2 13 provides relief for DBA headaches related to index page splitting.

Db2 13 for z/OS: more helpful (and more CPU-efficient) tracing, more real-time stats

First, the good news on the tracing front: Db2 13 introduces a new trace record, IFCID 396, that is associated with statistics class 3 and is, therefore, active by default (Db2 statistics trace classes 1, 3, 4, 5, and 6 are on by default, per the SMFSTAT parameter in the Db2 ZPARM module). Does this make the CPU overhead of statistics class 3 a matter of concern? Nope - stats class 3 remains a very low-overhead trace. How so? Well, the new IFCID 396 trace record is generated not for every index page split in a Db2 system, but only for those that take an unusually long time to complete (specifically, more than one second). That's generally what you want a spotlight on - more than on all the index page splits that get done lickety-split. What's more, an IFCID 396 trace record provides the UR ID of the process driving the unusually-long-running index page split operation, and (in a Db2 data sharing environment) the ID of the Db2 member that processed the index page split. Result: identifying processes that are driving unusually high levels of unusually long-time-to-complete index page split operations is now a lot more straightforward than it was before.

The real-time statistics situation is also improved with Db2 13, thanks to three columns that are added to SYSIBM.SYSINDEXSPACESTATS when the catalog level goes to V13R1M501 (and the catalog can be taken to that level once function level V13R1M500 has been activated):

• REORGTOTALSPLITS - Since the index (or index partition) was last REORG-ed, how many index page splits have happened?
• REORGSPLITTIME - What has been the total amount of time consumed in processing all the splits indicated by the REORGTOTALSPLITS value?
• REORGEXCSPLITS - Of the number of splits indicated by the REORGTOTALSPLITS value, how many took an exceptionally long time (more than 1 second) to be processed?

With these new real-time stats columns, identifying indexes for which page splitting is problematic is a snap. With such an index identified, you can take an action (or combination of actions) to address the issue - for example:

• Go to a larger index page size to reduce the incidence of page splitting (it takes more index entry inserts to fill a larger index page).
• Increase the PCTFREE value for the index, so that after a REORG there will be more space in each page to hold newly-inserted entries.
• Increase the frequency of REORGs for the index.

Here's the key takeaway for Db2 for z/OS DBAs: whereas before you were kind of shooting in the dark in trying to identify and deal with index page split issues, Db2 13 gives you actionable intelligence that you can use to effectively and efficiently focus your index-page-split-mitigating actions.