![Close [x]](http://dw1.s81c.com/i/c.gif){kind=small}
The B-tree scanner was introduced in IDS versions 7.31.UD8, 9.21.UC7, 9.30.UC6 and later. B-tree scanners clean the dirty index items and compress the B-tree leaf pages. The new btscanner thread replaces the old btcleaner thread. It has been designed to be much more efficient, flexible, and easy to administer.
When an index item is deleted from an index for a logged table, the delete operation does not remove the item from the index page. It just marks the index item as deleted without freeing the space occupied by the item. For the duration of the transaction, a lock is maintained on this uncommitted deleted item. When the transaction is committed, the lock on the item is released, but the space occupied by the committed deleted item is not freed. Committed deleted items can cause a significant drain on system resources because applications that encounter a deleted item need to do the extra work of checking the lock. Also, the index size grows as the number of deleted items increases, causing more I/Os of index pages.
For queries to perform optimally, committed deleted items in the index must be removed, and the index needs to be compressed and rebalanced. The process of removing the committed deleted items from the index is called index cleaning. In IDS, a special thread called the btscanner thread is responsible for cleaning the index asynchronously. This article describes the btscanner thread in detail.
Salient features of B-tree scanners
The design criteria for the B-tree scanners differs significantly from the design of the older btcleaner mechanism. Some of the important salient features for B-tree scanners are:
- The btscanner threads can be created and configured dynamically. A fixed number of btscanner threads are started at server startup.
- Committed deleted items, if not cleaned, cause the database server to do more work. The B-tree scanner considers such indexes, which cause the database server to do extra work as its workload for cleaning.
- For each index, the number of times any committed deleted items are seen, a corresponding hit-count is tracked. If the hit-count for any index goes above a specified threshold, that index becomes a candidate for cleaning. The btscanner threads scans all index partitions in the database server with hit-count >= threshold and adds the indexes into the hot-list.
- The B-tree scanner picks up an index from the hot-list and scans a range of index leaf pages to clean. The number of leaf pages to be scanned is determined by the scan method. The B-tree scanner supports multiple methods for scanning index pages through leaf scan, range scan, and ALICE scan. The btscanner thread removes the committed deleted items from the index leaf pages in the scanned range. If needed, the page is merged and the index is compressed.
You can configure the B-tree scanner using one of the following methods:
- IDS configuration file ($ONCONFIG)
- onmode -C commands in On-Line mode
The following sections examine both methods.
Configure B-tree scanners With ONCONFIG
The BTSCANNER parameter in the configuration file is used to control the B-tree scanner. This configuration parameter has five optional parts that maybe set by the DBA, or the default value is used, as shown in Table 1.
Table 1. BTSCANNER configuration options
| Option | Default Value | Description |
|---|---|---|
| num | 1 | The number of btscanner threads to start at database server startup. |
| threshold | 5000 | The number of hits an index must encounter before it is placed on the hot-list for cleaning. |
| rangesize | -1 | Minimum size (in pages) of index fragments to use range scans. |
| alice | 6 | Instance-wide ALICE mode. |
| compression | default | Instance-wide B-tree scanner compression level. (Available from 11.50.xC2 and later) |
The following is an example configuration:
BTSCANNER num=1,threshold=5000,rangesize=-1,alice=6,compression=default |
Configure B-tree scanners dynamically With Onmode
In On-Line mode, the B-tree scanner can be configured using onmode -C commands, as shown in Table 2.
Note: The onmode -C changes are applicable immediately, but they will not persist through server restarts. The changes that need to be saved have to be made to the BTSCANNER parameter in the ONCONFIG file.
Table 2. onmode -C commands
| Command | Description |
|---|---|
| onmode -C start count | Start count number of new btscanner threads. |
| onmode -C stop count | Stop count number of new btscanner threads. |
| onmode -C duration value | Sets the value in seconds for which the current hot-list is valid. The hot-list is rebuilt without interrupting the currently running btscanners after this duration. |
| onmode -C threshold value | Sets the instance-wide hit-count threshold to value for an index. |
| onmode -C rangesize value | Sets the minimum value (in pages) of index fragments for range scans to be used. |
| onmode -C alice newmode | Sets instance-wide ALICE mode to newmode. |
| onmode -C compression newlevel | Sets the instance-wide index compression level to newlevel. (Available from 11.50.xC2 and later) |
The following query shows the effective value of the B-tree scanner parameters in the instance:
select cf_effective from sysmaster:syscfgtab where cf_name = 'BTSCANNER'; |
Index scan and cleaning methods
In leaf scan mode, the btscanner thread starts at the left-most leaf page of an index, scans all the leaf pages and cleans the leaf pages with deleted items. This method is useful if the index is small with most of the pages in the buffer pool, or if the index is an attached index.
If a leaf scan is used for large indices, the amount of I/O increases as each index leaf page is read into the buffer pool. If only a few leaf pages contain deleted items, then the database server does a lot of unnecessary I/Os. There is also a possibility of knocking out required pages from the buffer pool.
As compared to leaf scan, range scan does not read all the index leaf pages. The B-tree scanner maintains the lowest logical page and the highest logical page with deleted items for each index. Range scan reads the index leaf pages within this range only. Also, it scans through this range in large blocks of 128 pages using light I/Os. The index leaf pages in these blocks with committed deleted items are read into the buffer pool and cleaned.
The range scan method provides a significant improvement over the leaf scan method. It performs less I/O, since it reads only a portion of the index. It also bypasses the buffer pool for most of its work. However, in some extreme cases, the lowest and highest pages with deleted items can cause the range to be the entire index. In such cases, the range scan performs unnecessary I/O.
From 10.00.xC5 onward, IDS supports a new mode of cleaning indexes called Autonomic Linear Index Cleaning. The pages in an index fragment are grouped into regions where each region consists of equally-sized blocks. A bitmap where each region is represented by a bit is maintained for each index. When a committed deleted item is encountered, the bit representing the index region for the page containing the item is set. The B-tree scanner scans only the index regions whose bit is set.
For example:
Table 3. ALICE scan example
| P1, P2 .... P256 | P257, P258 .... P512 | P513, P514 .... P768 | P769, P770 .... P1024 |
| Index Region No. 1 | Index Region No. 2 | Index Region No. 3 | Index Region No. 4 |
| Bit No. 1 | Bit No. 2 | Bit No. 3 | Bit No. 4 |
The B-tree scanner automatically adjusts the bitmap size based on the efficiency of cleaning. Efficiency of cleaning is measured as the ratio of the number of large blocks read to the number of large blocks with a committed deleted item. If the miss ratio is high, then the index cleaning is not efficient and the bitmap is resized to the next higher resource level.
The initial bitmap that is allocated is at least 8 bytes long. The resizing of the bitmap expands the bitmap by multiples of 4 bytes. When the server is online, the bitmap is never shrunk. The default efficiency is 30 percent. To avoid just one single abnormal scan from impacting the efficiency, you want your efficiency to be at a good average. A minimum of five index scans is needed before increasing the bitmap size for an index.
ALICE mode can be set to any value between 0 and 12. If the value is 0, ALICE scanning is turned off. If the ALICE mode is N, then 2(12-N) light I/Os are needed to cover an index range. One light I/O scans through 128 pages.
Compute ALICE mode and bitmap size
Here's how you can compute ALICE mode and bitmap size requirements for the B-tree scanners to clean indexes in ALICE mode, say, for a 4Kb page size platform:
In the default ALICE mode of 6, each bit in the bitmap maps to an index region of size 26 * 128 * 4Kb = 32Mb.
In the highest ALICE mode of 12, each bit in the bitmap maps to an index region of size 20 * 512Kb = 512Kb.
What mode should you be in to represent your index with an
index region size of 2Mb?
2Mb / 512 Kb = 4 = 212-10 = ALICE mode 10.
How many bits will you need for an index of size 2GB in ALICE mode 7?
In ALICE mode 7, each bit in the bitmap will map to
an index region of size 212-7 * 512Kb = 16Mb.
The size of the bitmap needed for a 2GB index in ALICE mode 7 = 2Gb/16Mb = 128 bits bitmap.
Table 4. Column description in 'onstat -C'
| id | B-tree scanner thread ID. |
|---|---|
| Partnum | The index partition on which this btscanner thread is working on. |
| Cmd | Current command this btscanner thread is processing. |
Listing 1. Output of 'onstat -C'
B-tree Scanner Information
Profile
=======
Active Threads 1
Global Commands 2000000 Building hot list
Number of partition scans 47
Main Block 0x000000004c619ce8
BTC Admin 0x000000004b7021b8
BTS info id Prio Partnum Key Cmd
0x4b828a58 0 High 0x00000000 0 40 Yield N
Number of leaves pages scanned 3675
Number of leaves with deleted items 129
Time spent cleaning (sec) 5
Number of index compresses 61
Number of deleted items 487
Number of index range scans 0
Number of index leaf scans 0
Number of index alice scans 4
|
Table 5. Column description in 'onstat -C hot'
| Partnum | The partition number of the index |
|---|---|
| Key | Index key number |
| Hits | Current value of hit-count for this index |
The "*" indicates that the index partition has been cleaned during this hot-list duration.
Listing 2. Output of 'onstat -C hot'
B-tree Scanner Information
Index Hot List
==============
Current Item 2 List Created 18:30:16
List Size 1 List expires in 37 sec
Hit Threshold 5000 Range Scan Threshold -1
Partnum Key Hits
0x00100137 1 5031 *
|
View index partition statistics (onstat -C part)
Table 6. Column description in 'onstat -C part'
| Partnum | The partition number of the index |
|---|---|
| Key | Index key number |
| Positions | Number of times this index has been read |
| Compress | Number of times index pages were compressed |
| Split | Number of times index pages were split |
| C_Level | Compression level for this index partition (Available from 11.50.xC2 and later) |
"C" indicates that the index partition is busy being cleaned.
"N" indicates that the index partition is no longer eligible for cleaning.
Listing 3. Output of 'onstat -C part'
B-tree Scanner Information Index Statistics ================ Partnum Key Positions Compress Split C_Level 0x00100002 1 1852 0 0 Med 0x00100004 1 432 0 4 Med 0x00100004 2 C 698 15 1 Med 0x00100005 1 35 0 0 Med 0x00100005 2 0 0 0 Med ... |
View index partition cleaning statistics (onstat -C clean)
Table 7. Column description in 'onstat -C clean'
| Partnum | The partition number of the index |
|---|---|
| Key | Index key number |
| Dirty Hits | Hit-count for this index |
| Clean Time | Time spent cleaning, in seconds |
| Pg_Examined | Number of pages examined by B-tree scanner in this index |
| Items Del | Number of items removed from this index |
| Pages/Sec | Number of pages examined per second |
Listing 4. Output of 'onstat -C clean'
B-tree Scanner Information Index Cleaned Statistics ========================= Partnum Key Dirty Hits Clean Time Pg Examined Items Del Pages/Sec 0x00100002 1 0 0 1 1 1.00 0x00100004 1 0 0 7 0 7.00 0x00100004 2 0 0 2 0 2.00 0x00100005 1 0 0 32 1564 32.00 0x00100005 2 0 0 13 558 13.00 ... |
View range scan statistics (onstat -C range)
Table 8. Column description in 'onstat -C range'
| Partnum | The partition number of the index |
|---|---|
| Key | Index key number |
| Low | Low boundary for this index |
| High | High boundary for this index |
| Size | Size of index in pages |
| Saving | Percentage of time saved with range scan versus a full index scan |
Listing 5. Output of 'onstat -C range'
B-tree Scanner Information Cleaning Range Statistics ========================= Partnum Key Low High Size Saving 0x0010001c 1 14 14 24 100.0 % 0x00100067 1 10 10 16 100.0 % 0x00100067 2 7 7 16 100.0 % 0x00100073 2 36 69 96 65.6 % 0x00100084 2 24 25 32 96.9 % 0x001000b1 2 36 36 112 100.0 % 0x001000e8 1 1 1 4 100.0 % 0x00100115 2 36 69 96 65.6 % 0x00100126 2 24 25 32 96.9 % 0x0010013d 1 2 2 75 100.0 % ... |
View ALICE scan statistics (onstat -C alice)
Table 9. Column description in 'onstat -C alice'
| Partnum | The partition number of the index |
|---|---|
| Mode | ALICE mode for this index partition |
| BM_Sz | Size of the bitmap for this index partition |
| Used_Pg | Number of used pages in this index |
| Examined | Number of pages examined by B-tree scanner in this index |
| Dirty_Pg | Number of dirty pages seen by B-tree scanner in this index |
| # I/O | Number of pages read by B-tree scanner in this index |
| Found | Number of dirty pages found in the pages read |
| Eff | Efficiency of the bitmap |
| Adj | Current value of the ALICE adjustment factor for this bitmap |
Listing 6. Output of 'onstat -C alice'
B-tree Scanner Information ALICE Cleaning Statistics ========================= System ALICE Info: Mode = 6, Eff = 30 %, Adj = 5 Partnum Mode BM_Sz Used_Pg Examined Dirty_Pg # I/O Found Eff Adj 0x001000b0 6 64 8 0 0 0 0 0.0 % 0 0x001000b1 6 64 8 0 0 0 0 0.0 % 0 0x001000b2 6 64 4 0 0 0 0 0.0 % 0 0x001000b2 6 64 4 0 0 0 0 0.0 % 0 0x001000b5 6 64 88 176 20 18 10 55.6 % 2 0x001000bb 6 64 88 0 0 0 0 0.0 % 0 0x001000bc 6 64 7 0 0 0 0 0.0 % 0 0x001000c4 6 64 26 0 0 0 0 0.0 % 0 ... |
View ALICE mode bitmap (onstat -C map)
Table 10. Column description in 'onstat -C map'
| Partnum | The partition number of the index |
|---|---|
| Key | Index key number |
| Map | ALICE mode bitmap |
Listing 7. Output of 'onstat -C map'
B-tree Scanner Information ALICE Bitmap of Deleted Index Items =================================== Partnum Key Map 0x001000b0 1 0000: 00000080 00000000 0x001000b1 1 0000: 00000080 00000000 0x001000b2 1 0000: 00000080 00000000 0x001000b2 2 0000: 00000080 00000000 0x001000b5 1 0000: 00000000 00000000 0x001000bb 1 0000: 00000080 00000000 0x001000bc 1 0000: 00000080 00000000 ... |
IDS users use index fillfactor to take into account future expansion of the index or to create compact indexes. After any DML activity, users desire to have indexes compressed back to the compaction level as they had specified in the FILLFACTOR onconfig variable or as per the FILLFACTOR option in the CREATE INDEX statement. Fillfactor and index compression are related to each other, but they are not the same. Fillfactor creates the index with a specific percentage of data on each index page, whereas compression attempts to join two partially used index pages. The current B-tree index compression algorithm used by the B-tree scanner has an internal compression level of approximately 80 percent. There's no external interface to change the compression level of the index. The new enhancements:
- Provide a way, at the instance level, to specify three levels to which the B-tree
scanners compress the index -- low, medium (med), and high. The default compression
level is medium, which is consistent with the existing behavior. This is done by
modifying the existing BTSCANNER onconfig variable to take an additional parameter -- compression=[low|med|high|default].
BTSCANNER num=4,threshold=10000,rangesize=-1,alice=6,compression=default
- Provide a way, at each index partition level with a new SQL Admin API
task()/admin() command, to modify compression levels: execute function task("set index compression", "index_partnum", "low|med|high|default");
To change the compression mode to high for an index partition
with partition number 1048967: EXECUTE FUNCTION ADMIN("set index compression", "1048967", "high"); To change the compression mode to low for all fragments of index "idx1" in database "dbs1": SELECT TASK("set index compression", partn, "low") FROM dbs1:systables t, dbs1:sysfragments f WHERE f.tabid = t.tabid AND f.fragtype = 'I' AND indexname ='idx1';
- Provide a new onmode -C compression [low|med|high|default] command to modify the instance level compression threshold.
onmode -C compression high
- Provide a way to show the current global index compression level and the individual index compression levels with the onstat -C part command.
The individual index compression level defined in the Admin API command holds precedence
over the global index compression level defined in the onconfig. There is no
conversion-reversion impact for users migrating from earlier version of IDS, as the
compression level for the indexes is stored in the in-memory index partition profile structure. This also means that the index compression level is not persistent across server restarts. The user can create a Startup Task using the Scheduler API to set the index compression levels at server startup. Startup tasks are run only once whenever IDS is restarted.
Advantages of various compression levels depend on the nature of DML activity on an index.
- If an index is read-only or 90 percent read, then it would be beneficial to use high compression because searching for the data requires less I/Os to traverse. The B-tree scanner does more I/Os in keeping the index smaller, but this also means select requires less I/Os for finding the desired rows.
- If you create your indexes with a fillfactor less than the default, then users might want to consider switching the compression level to low.
Before the btscanner was introduced, cleaning the committed deleted items from an index was not efficient and was hard to administer. The btscanner thread provides a more flexible and efficient way to clean the index items with emphasis on easy administration.
After completing this article, you should be able to determine the correct index cleaning method to use that is appropriate for your system, configure btscanner using the configuration file, ONCONFIG, use onmode to dynamically configure btscanner, and monitor the btscanner activity using onstat. Use these techniques for better transaction performance for your Informix server.
Learn
-
IDS
V11.50 Information Center: Find the entire IDS documentation in HTML frameset, with
full text search, logical categories, easy navigation, and links to troubleshooting
and support files.
-
developerWorks
Informix zone: Find articles, tutorials, and connect to other resources to expand your
Informix skills.
- developerWorks Information Management
Zone: Learn more about Information Management, find technical documentation, how-to
articles, education, downloads, product information, and more.
- Browse the
developerWorks
Technical Events and Webcasts.
- Browse the
technology bookstore for books on these and other technical topics.
Get products and technologies
-
Get evaluation
and trial software, drivers, fixes, and patches for IBM Informix products.
Discuss
- Participate in the discussion forum.
- Check out
developerWorks
blogs and
get involved in the
developerWorks community.
Table of contents
Next steps from IBM
Tags
Use the slider bar to see more or fewer tags.
For articles in technology zones (such as Java technology, Linux, Open source, XML), Popular tags shows the top tags for all technology zones. For articles in product zones (such as Info Mgmt, Rational, WebSphere), Popular tags shows the top tags for just that product zone.
For articles in technology zones (such as Java technology, Linux, Open source, XML), My tags shows your tags for all technology zones. For articles in product zones (such as Info Mgmt, Rational, WebSphere), My tags shows your tags for just that product zone.
Popular article tags |
My article tagsSkip to tags list
Popular article tags |
My article tags
