Configure B-tree scanner information to improve transaction processing

You can improve the performance of transaction processing in logged databases by controlling how the B-tree scanner threads remove deletions from indexes.

The B-tree scanner improves transaction processing for logged databases when rows are deleted from a table with indexes. The B-tree scanner automatically determines which index partitions will be cleaned, based on a priority list. B-tree scanner threads remove deleted index entries and rebalance the index nodes. The B-tree scanner automatically determines which index items are to be deleted.

In a logged database, when a delete or an update operation is performed on a row, any corresponding index entry is not immediately deleted. Instead, the corresponding index entry is flagged as deleted until a B-tree scanner thread scans the index and removes the deleted items. An index containing many deleted items can cause a significant performance problem, because index searches need to scan a larger number of items before finding the first valid item.

The default setting for B-tree scanning provides the following type of scanning, depending on your indexes:

Depending on your application and the order in which the system adds and deletes keys from the index, the structure of an index can become inefficient.

You use the BTSCANNER configuration parameter to specify the following information, which defines the scan mode:

The server treats a forest of trees index the same way it treats a B-tree index. Therefore, in a logged database, you can control how the B-tree scanner threads remove deletions from both forest of trees and B-tree indexes.

The following table summarizes the differences between the scan modes.

Table 1. Scan modes for B-tree scanner threads
Scan Mode Description Performance Advantages or Issues More Information
Leaf scan mode

In this mode, the leaf level of an attached index is completely scanned for deleted items.

This mode is only possible on attached indexes and is the only mode the server can use if more than one attached index exists in a partition.

Leaf and range scan mode settings

Alice (adaptive linear index cleaning) scan mode

If the BTSCANNER alice option is enabled, every index partition receives a bitmap that tracks where a deleted item was found in the index. The scan that occurs excludes all parts of the index where no delete operations are found.

The initial size and granularity of these bitmaps depend on the size of the partitions they represent and the current system-wide alice level. The server periodically checks each bitmap for its efficiency by checking the ratio of pages to be cleaned to pages read, adjusting scanning if necessary to get better information. This mode allocates additional resources (memory) to the index that is consuming excess I/O.

You can greatly improve performance and reduce I/O when using the alice mode. Generally, alice mode is 64 times more efficient than range scanning and can automatically tune itself for unsatisfactory indexes, which range scanning cannot do.

Alice scan mode values

Range scan mode

Range scanning, which is enabled with the rangesize option, is performed in the range between the low and high page address. The leaf level of the index partition is only scanned within this range. The server performs light scans, which do not immediately use and strain the buffer pool, even though cleaning occurs through the buffer pool.

Not recommended for Informix® Version 11.10 or higher. Alice scanning is exactly the same as range scanning, but is 64 times more efficient, uses the same resources, and has 128 equal ranges.

When you set alice mode scanning, range scanning does not have an effect.

If you decide to use range scanning for systems with only a lot of large indexes, set the rangesize option to the minimum partition size for range scanning.

Leaf and range scan mode settings

For more information about the BTSCANNER configuration parameter and for more information about how the database server maintains an index tree, see the chapter on configuration parameters and the chapter on disk structure and storage in the HCL Informix Administrator's Reference.

Use the onstat -C option to monitor the B-tree scanner activities.

Use the onmode -C option to change the configuration of B-tree scanners during runtime.

For more information about onstat -C and onmode -C, see the HCL Informix Administrator's Reference.

Copyright© 2019 HCL Technologies Limited