onstat -C BTree Cleaner Requests

The 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

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.