BTScanner

The following information relates to the BTSCANNER configuration parameter.

BTSCANNER num=<number>:prio=<high|low>:rangescan=<number>:threshold=<number>

What should the threshold be set to?

Question: If this is set low the btscanner will continually be scanning the leaf pages, which causes excessive I/O. If it is high there would be large numbers of 'dirty' index pages which would cause normal threads to do extra work on a regular basis.

Answer: The setting of the threshold (onmode -C threshold {size}) sets the number of deleted items an index must encounter before it is placed onto the hot list to be cleaned. The size is dependent on usage, thus falls into the tuning area.

Btscanner Priority

Question: What is the resource use of the btscanner like? For example, if the btscanner is given a low priority on a busy system will it still be able to keep up with the cleaning it needs to do, or will it be overwhelmed as it rarely gets the chance to run? Would this be characterised by lots of yielding scanner threads?

Answer: The priority of the btscanner thread(s) can only be set to either low or high — low being a priority lower than normal user threads, and high being a priority equal to that of normal user threads. If the system is busy and you want index cleaning to occur, use onmode -C high to increase the priority as above.

Range Scanning

Question: Does this work with version 9.40x? Currently we have it disabled and any onmode commands relating to range scanning just return a usage message. How is it enabled?

Answer: The Light Range Scan is incorporated in 9.40x. It is only applicable when the number of indexes on a partition is exactly equal to one (i.e. detached indexes or an attached index with only one index on the table). When using the onmode -range {value} command, Light Range scanning will automatically occur when only one index exists on that partition.

Multiple Btscanner Threads

Question: Is it possible to permanently configure the number of btscanner threads? At the moment additional threads can be added with onmode but these are lost when the instance is restarted.

Answer: The engine will start by default one and only one thread. The thread will run at a low priority; you can run the onstats to see this. In the later engines the number of threads can be configured at startup. In earlier engines there's no way to configure it to start multiple threads each time — if you want to add more, you must do that with onmode each time.

Overview of the BTScanner

The new btscanner replaces the earlier btree cleaner implementation of earlier Informix Dynamic Server versions. The design covers three major areas that have changed:

Workload generation — The workload for cleaning indexes is determined by keeping track of how many times items in an index caused the server to do extra work. The index which causes the server to do the most extra work will be the next index cleaned by the btree scanner thread(s).

How the btree is cleaned of dirty items — An index will have its entire leaf level examined looking for deleted items. Upon finding a deleted index item, the cleaner will test-lock the item, then undertake a foreground remove of the item, and then determine if the page warrants compression.

The use of multiple threads — The implementation now allows the dynamic allocation of threads for configurable workloads.

Design Details

Submission of work to be cleaned is now accomplished by profiling the number of times a reader of the index encounters deleted items that require the reader to do extra work. The extra work is profiled for each index, and is the basis for developing a hot list which drives the workload of the btree scanner.

This hot list is created under the following conditions:

• when the hot list is empty of work;
• a user request is placed (by onmode); or
• when the list has changed enough to be out of date.

Any btree scanner seeing a sort task pending will acquire the task, setting the sort task in progress and starting a scan of the partitions, creating a list of part numbers, key numbers and dirty hits. This list is then sorted by hits and replaces the previous hot list.

The btree scanner has three main task groups, processed in order:

• administrative tasks,
• sort tasks, and
• index cleaning tasks.

Scan methodologies

There now exist two types of scan that may be undertaken to clean an index. The first is a Basic Index Scan and the second is a Light Range Scan. The Light Range Scan can only be used when the number of indexes on a partition is exactly equal to one. The Basic Index Scan can be used at any time.

Basic Index (Leaf) Scan

The Basic Index (Leaf) Scan consists of starting at the root node of the index and then walking to the farthest-left leaf. Once at the leaf level, each node is checked for dirty items and cleaned if required. The leaf node's next pointer is used to move to the next node to be processed, until the last leaf node is visited.

Advantages: examines all index nodes from left to right; looks at the buffer pool; able to operate when more than one index key exists in a partition.
Disadvantages: reads every index page into the buffer pool; slow due to the amount of I/O operations involved.

Light Range Scan

The Light Range Scan was added to improve the performance of index cleaning. It combines several other online performance features, such as light scanning and range scanning, into the index cleaning scan. When a user submits a request for cleaning, the minimum and maximum logical page numbers are tracked off the memory partition. The scan then starts by reading a block of pages from disk starting with the lowest logical page that a request has been submitted for. This block is then examined for any leaf pages having deleted items; any such pages are read into the buffer pool and cleaned.

Advantages: uses light I/O scans; only scans between the high and low boundaries.
Disadvantages: does not clean index pages that have not been flushed to disk.

User Interface Changes

Both the onmode and the onstat commands have a new option, -C.

onmode -C

onstat -C

Performance Tuning

Number of threads versus priority: the btree scanner threads run at a lower priority than user threads, so when the system becomes busy the cleaning of the indexes will not occur as fast. If the system is busy and you want index cleaning to occur, set the threads to high priority (a priority equal to normal users' priority).

Notes (post to CDI by Mark Ashworth)

In 11.50.xC5 and earlier, the BTS index operations execute in a single bts VP and each operation executes sequentially. Multiple readers and writers can interleave their operations. There is an internal lock to ensure no concurrent (threaded) operations execute on the VP, and multiple VPs were not allowed.

Starting with 11.50.xC6, bts was enabled to work with multiple bts VPs. There is still a restriction that each VP only executes one operation at a time; however multiple bts VPs may be created with the VPCLASS onconfig variable, or more bts VPs can be added with the onmode -p command.

There may be multiple readers and writers of any bts index across several bts VPs. There is a critical section in the transaction commit which puts an exclusive (write) lock on the BTS index during this phase. Unlike the first release of BTS, BTS in 11.50 will eventually reuse pages in the index when new rows are inserted after rows have been deleted, which greatly reduces the need to compact.