One of the changes in Advantage CA-Datacom/DB r11 SP2 is the addition of multiple index queue processors. With this change, the Multi User Facility (MUF) can process the index queue more effectively, which can in turn boost application and MUF performance in special cases.
To grasp why this change was made and how it can help performance, you must first know a little bit about the index queue itself. The index queue is used to asynchronously balance the tree structure of the index, which in turn minimizes the number of index blocks that must be accessed in order to find a row. This balancing by the index queue is done in two cases:
First, index balancing is always done when an index block is deleted (reclaimed for free space). This occurs when all entries in the block are deleted and the associated data changes have been committed. This delete code removes pointers to the index block in both the horizontal and vertical tree structure and allows the block to become a free block for future reuse. Since this process is I/O intensive, it is offloaded from the task processing the delete request to the index queue processor (so that the actual delete request is not unnecessarily delayed).
Second, if an index block splits because it is full and a new index entry needs to be inserted, the balancing work of adding additional index blocks to the vertical index structure can be off loaded to the index queue (so that the actual insert request is not unnecessarily delayed).
The index queue runs at a high priority and, in a normal environment, it keeps pace with the amount of work offloaded to it. But there are special cases in which the index queue can get behind due to a large spike in the index workload. Typically, this is seen at sites that repeatedly build large CBS temporary indexes, use them for very few requests and then free or delete the temporary indexes. It can also occur when a user program deletes a massive number of rows from a table all at once.
The index queue has been improved in SP2 in several ways for these sudden surges in its workload. Prior to SP2, the index queue had a physical limit as to the number of entries it could hold. When it became full, it had an overflow. This meant, the balancing work for each block that overflowed was postponed until both the index queue was no longer full and the block was accessed again in normal processing. These overflows were reported in the PXX statistics. An overflow does not cause the index to malfunction in any way, but it could lead to temporary performance degradation. With the new index queue structure the queue will never overflow.
Just as important, the index queue has been changed from a single task to up to ten tasks that are used on an as needed basis. Each of these ten tasks can simultaneously process index queue entries as long as they are for different DBIDs. Multiple databases' index balancing can now be done both simultaneously and independently. For example, CBS index balancing does not affect index balancing for a user database.
New statistics are provided in the MUF EOJ report related to the ten index queue processors and the depth of the index queue.
The new index queue improvements are one more reason to upgrade to r11 SP2!