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):

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:

A test of the same query shows that the number of buffer reads necessary on the index reduces from 505 to 128 after the rebuild. However I am really more interested in reducing root node contention.

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

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
------------------------------

Thanks for the comprehensive answer Art. We are already considering a FoT but that will need application vendor approval, hence focussing on the existing index. Your description of how the scanner works is very helpful.

Ben.

------------------------------
Benjamin Thompson
------------------------------

Original Message:
Sent: Wed May 25, 2022 07:50 AM
From: Art Kagel
Subject: Index rebuild?

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
------------------------------

One addition, clarification: If two adjacent nodes are each less than 50% full, the btree cleaner will compress them into one. But, IB that if both are exactly 50% it doesn't happen in practice. So if you delete rows in the table, that will open the door to node "compression.

------------------------------
Art S. Kagel, President and Principal Consultant
ASK Database Management Corp.
www.askdbmgt.com
------------------------------

Original Message:
Sent: Wed May 25, 2022 08:39 AM
From: Benjamin Thompson
Subject: Index rebuild?

Thanks for the comprehensive answer Art. We are already considering a FoT but that will need application vendor approval, hence focussing on the existing index. Your description of how the scanner works is very helpful.

Ben.

------------------------------
Benjamin Thompson
------------------------------


Original Message:
Sent: Wed May 25, 2022 07:50 AM
From: Art Kagel
Subject: Index rebuild?

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