Ben:
If you are seeing significant root node contention on the index, consider creating a Forest of Trees index hashing on the first, integer, key column. Then the sub-trees will be very shallow and only contain the strings. I would probably hash into a number of sub-trees that is about 1/4th the average number of concurrent sessions. Obviously if that's very large you can cut that down to 1/8 or 1/100. Test and see what works best for you.
Since it is unlikely that you are doing any range searches on the integer part of the key, you won't also need a "regular" index on that key, though you may want to create a normal index just on the string column to support other searches including the string without the integer.
On the unbalancing, I imagine that the index was created with the default FILLFACTOR so all nodes start out 90% full and that's fine. If the int key column is a serial type it is monotonically increasing so new values always get added to the last node which quickly splits and one of the new pair, the new "last node" fills while the previous one remains at 50% full. Then the new on splits and two are 50% full, and so on. The btree cleaners cannot merge a 1/2 full node with a full node to rebalance so the only way to make any index the starts with a SERIAL type column more efficient is to rebuild it periodically. An FOT index will not suffer from the same fate however!
------------------------------
Art S. Kagel, President and Principal Consultant
ASK Database Management Corp.
www.askdbmgt.com------------------------------
Original Message:
Sent: Wed May 25, 2022 06:46 AM
From: Benjamin Thompson
Subject: Index rebuild?
I always thought index rebuilds on Informix weren't necessary but a recent investigation has made me question that.
The incident involved a large number of sessions accessing the table via this index during a period of exceptionally high traffic. The sessions experienced buffer waits accessing the index.
The index is a two-part unique one on an int field and a varchar. The query uses a single id for the int and a list of possible values for the varchar. This results in a plan which does multiple index look-ups for each combination of the int and varchar.
I am looking at this from a number of angles but the bit I want to ask about in this post is index balancing. It is clear the below extracts from 'oncheck -pT' that the index is quite imbalanced, in particular the very low number of keys at level 1. I suspect this contributes to the contention seen. I have a vague expectation that the BTree Scanner should rebalance the index over time but this does not appear to happen.
Before (now):
Index Usage Report for index ix_unique_pk on database:owner.table Average Average Average Level Total No. Keys Free Bytes Del Keys ----- -------- -------- ---------- -------- 1 1 2 4036 2 2 85 1867 3 170 77 2069 4 13127 79 2007 5 1048805 73 2053 0 ----- -------- -------- ---------- -------- Total 1062105 73 2052 507
After an index rebuild on a cloned system (drop/create) the new index looks like this and has 28 keys at level 1, as well as having only four levels and being around half the size:
Index Usage Report for index ix_unique_pk on database:owner.table Average Average Average Level Total No. Keys Free Bytes Del Keys ----- -------- -------- ---------- -------- 1 1 28 3358 2 28 132 548 3 3711 146 182 4 542948 141 176 0 ----- -------- -------- ---------- -------- Total 546688 141 176 0
Is there any way I can achieve the same or similar effect without a rebuild? I know I can set index compression to high for this specific index but it's unclear that this would be as effective.
Ben.
------------------------------
Benjamin Thompson
------------------------------
#Informix