R
Richard Banville
Guest
The maximum number of index b-tree levels currently supported in OpenEdge 11.5 is 16. It has been this way for a very long time. With the compression algorithms in our index b-trees, I'd be very interested to hear who has the deepest index b-tree to see if this should be improved on in the future. The change requiring pre-allocation of free space just prior to an index block split was introduced 9 years ago in 10.0B04 and 10.1A to solve a bug that would cause the database to hang waiting on a buffer lock. The hang occurred due to a buffer locking protocol inconsistency/conflict when additional free space is required by an index object when in the middle of a block split operation. Due to the highly concurrent optimistic locking protocol in place when traversing down the index b-tree (a technique called "crabbing") there is currently no way to know how deep the index really is when beneath the root block since another user could have performed a split along a different path in the index b-tree which cascaded upward, above where you currently are in the tree and not involving any index buffers you have a lock on. Of course this could be improved upon since at some point you have all the index blocks you need locked to perform the split operation but it is more complicated than just that. There are many ways to solve this problem, some riskier than others, and I have no plans to debate the approach previously taken in this forum. Suffice it to say that ensuring there are at least 16 blocks available to the index object just prior to the split operation ensures that it will complete without needing to request additional space from the storage area (or the file system). You wouldn't want to be allocating new space when in the middle of split operation anyway because of the performance impact on the rest of the application while in the middle of a multi-block index split operation. This explains why the suggestion for using a cluster size of 64 for index areas was made. Allocation of space just prior to an index split operation will occur much less frequently than with a cluster size of 8. How much it improves your performance is really dependent on your application and for many deployments as is often the case with such things, we are probably just splitting hairs here anyway. However, changing the cluster size in an area containing data requires some type of data movement so it is best to use decent values to start with.
Continue reading...
Continue reading...