onstat -C BTree Cleaner Requests

The onstat -c command provides information on the status of the BTree cleaner thread that removes deleted entries from indexes. Each index key value includes a one-byte field that indicates whether the value has been deleted. The BTree cleaner thread is provided a list of the pages containing these indicators when they have been set and actually performs the deletion against the index in addition to collapsing the levels within the index, if necessary.

More details on the BT Scanner can be found here

B-tree Scanner Information
Profile
=======
Active Threads                               1
Global Commands                        2000000   Building hot list
Number of partition scans               183743
Main Block                          0x00000003d705dd58
BTC Admin                           0x00000003d7fdf0b8


BTS info             id   Prio    Partnum      Key     Cmd
0x3d591a600           0   High   0x00000000     0       40  Yield N
    Number of leaves pages scanned                  427937729
    Number of leaves with deleted items              32338487
    Time spent cleaning (sec)                           83521
    Number of index compresses                         184963
    Number of deleted items                         327276839
    Number of index range scans                             0
    Number of index leaf scans                           1059
    Number of index alice scans                        233935

</div>

Output Description

Btree Cleaner Info

Column Heading	Column Description	Format	See Also
btcleanr	In-memory address of the RSAM TCB for the BTree Cleaner thread.	Hex	onstat -g sle
pool	Address of first pool of memory allocated for the BTree Cleaner thread.	Hex
flags	Describes the status of the BTree Cleaner thread using the following hexadecimal values: 0x01 Cleaner is sleeping 0x02 Convert keys 0x04 Stop cleaner (debugging)	Hex
pools	Number of pools allocated.	Dec
npend	Number of pending requests.	Dec
busypnum	Partnum of request in progress.	Hex
head	In-memory address of head of list to clean.	Hex
tail	In-memory address of tail of list to clean.	Hex
free	In-memory address of the head of the free list of BTree cleaner structures.	Hex
nreqs	Number of cleaning requests.	Dec
dups	Number of duplicate deletion requests that have occurred for the same table, page and key combination.	Dec
success	The number of successful clean requests.	Dec
dfrmv	Removed deleted items from page.	Dec
unnec	Unnecessary cleaning request.	Dec
ditems	Items removed by cleaner.	Dec
ditlks	Test locks on deleted items	Dec
cmprs	Cleaner did an index page compression on the node.	Dec
palcs	Number of times a pool was allocated.	Dec

Outstanding Requests

Label	Description	Format	See Also
address	In-memory address of head of list to clean	Hex
next	Next node in busy/free list	Hex
partnum	Partition number of the index or index fragment	Hex
pagenum	Page number within the partition	Hex
keynum	Key number within the page	Dec

Summary Definitions

Label	Description	See Also
pending	Number of pending requests (same as npend)	npend
total	Number of Btree Cleaner entries per pool times number of pools
hash buckets	Number of BTree Cleaners entries per pool
pools	Number of BTree Cleaner pools

Notes

The BTree cleaner thread runs every 60 seconds or when the number of pending requests exceeds the number of BTree cleaner hash buckets / 2.

The BTree cleaner thread name is btclean.

Only one BTree cleaner thread is started for each instance of Informix Dynamic Server. This is not currently tunable.

While the Btree cleaner thread is working on a partition, any DROP TABLE or DROP INDEXrequest against that paritition is forced to wait until the Btree cleaner thread is finished with the delete request.

Monitoring and Tuning

The BTree cleaner thread cannot be tuned. However, the timeout for the Btree cleaner thread can be monitored via the onstat -g sle command which lists threads on the sleep queue.

There is an undocumented ONCONFIG parameter, BTAPPENDERS, which you set to the number of append threads used to build an index in parallel. Range 1-unlimited, so be careful. Suggested max between #CPU VPs and 2X CPU VPs. Default is dynamically calculated based on the #rows in the table.