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:
- If the table has more than one attached index, the B-tree scanner uses the leaf scan mode. Leaf scan mode is the only type of scanning possible with multiple attached indexes.
- If the table contains a single attached index or if the indexes are detached, the B-tree scanner uses alice (adaptive linear index cleaning) mode. The initial alice scan mode is optimized for small- to medium-sized systems with few or no indexes above 1 GB. However, if the database server detects that the alice mode is inefficient, the alice scan mode setting is adjusted automatically to accommodate larger 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 number of B-tree scanner threads to start when the database
server starts
The number of B-tree scanner threads is configurable to any positive number. One B-tree scanner thread will always clean an individual index partition, so if you occasionally or consistently have a higher number of index partitions requiring cleaning, you might want to use more than one B-tree scanner thread. At runtime, you can turn off any B-tree scanner activity by issuing an onmode -C command. This command stops all B-tree scanner threads.
- The threshold, which is the minimum number of deleted items an
index must encounter before an index is placed on the priority list
for eligibility for scanning and cleaning by a B-tree scanner thread
For example, if you increase the threshold beyond 5000, you might be able to avoid frequent B-tree scanner activity on the indexes that receive the most updates and deletes.
- The range size, in kilobytes, that an index or index fragment must exceed before the index is cleaned with range scanning
- An alice mode value
- The level at which B-tree scanner threads compress indexes by merging two partially used index pages
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.
| 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. |
|
| 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. |
|
| Range scan mode | Range scanning, which is enabled with the |
Not recommended for OneDB 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 |
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 OneDB™ 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 OneDB Administrator's Reference.