Informix

 View Only
  • 1.  Index shrinks an entire b-tree level when removed/rebuilt....

    Posted Wed March 01, 2023 10:26 AM
    Edited by Jared Heath Wed March 01, 2023 10:42 AM

    Last night I had a production system run out of space when the engine decided to consume 50,000+ pages in a dbspace for an index out of the blue.   During the review of what was going on, I noticed this table had an index with a 5 level b-tree, which immediately caught my eye, especially when this index itself was larger than the table data  (1.3gb vs 1.1gb) which made no sense with an index with only about 1/8 of the rows in the table in int.

    On a test system, dropping this index and re-creating it changed it to a 4 level b-tree and obviously removed tremendous space from this index.  We fixed our problem by doing this same thing on our prod system and restarting the job.

    My follow-up today is running into a lot of barriers though.

    1 - Why didn't the btree scanner handle this?   Shouldn't it have realized at some point over multiple months this index was far too big vs what it was?

    2 - Our theory is the db ran out of space because it wanted to go down to a 6th level for this index.....any ideas what can make Informix do this

    3 - I need to find all the indexes in the system that might meet the same criteria.   At this point the only way I know is to run oncheck -pe and parse the entire database output.   Is there a better way?

    I just read another question that is very similar to mine on here, however, that one Art references the index having a serial key in it.   Mine doesn't have the serial in the index in question, but it seems to be exhibiting the same behavior.



    ------------------------------
    Jared Heath
    ------------------------------



  • 2.  RE: Index shrinks an entire b-tree level when removed/rebuilt....

    Posted Wed March 01, 2023 10:59 AM

    Per database on your system: select * from database_name:sysindexes where levels >= 4; will give you all of indexes per database that have 4 or higher levels.

     

    You can repack the index as well via SQLADMIN API: https://www.ibm.com/docs/en/informix-servers/14.10?topic=saaf-index-compress-repack-shrink-arguments-optimize-storage-b-tree-indexes-sql-administration-api please read up on it.

     






  • 3.  RE: Index shrinks an entire b-tree level when removed/rebuilt....

    Posted Wed March 01, 2023 05:07 PM

    1. I am not sure it will always merge all pages, it is more focused on removing deleted entries from pages e.g. it will only look at pages with deleted rows. There is an option to repack indexes.

          2. If in a part of the index a page becomes full and a new row needed to be inserted the page will be split and another level created. The level does not have to be the same in all branches of an index.



    ------------------------------
    David Williams
    ------------------------------



  • 4.  RE: Index shrinks an entire b-tree level when removed/rebuilt....

    Posted Wed March 01, 2023 05:49 PM

    Correct David. The btree cleaner threads only merge adjacent nodes that are both less than 50% full. It will not merge a 60% node with a 10% node.

    Art



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



  • 5.  RE: Index shrinks an entire b-tree level when removed/rebuilt....

    Posted Wed March 01, 2023 07:35 PM

    David, Jared,

    I will try to answer point 2 regarding the structure of the index.

    David, your assertion on the page splitting and the levels of an index or what I call the depth of the index tree is not correct (all of the leaves are ate the sale level).  Let me explain in more details but not too much detail since it will be long to explain here in an answer.

    Informix uses a B-TREE structure for its indexes on most indexes that we use, or more precisely a B+TREE. A BTREE or a B+TREE by definition has a BALANCED (B in BTREE) tree structure at all times meaning all of the leaf nodes are on the same level or the same depth. No matter what key value you are trying to locate in a index, the engine uses the same number of disk accesses since you have the same number of levels for all of the keys stored in a node (a disk page). That is one of the important features of a B+TREE used by the Informix engine. A key value is stored in a leaf node (page) but the same value can also be present in the higher level nodes to allow the engine to decide in which node page the value is located. Informix does not use BTREEs but B+TREEs.

    The difference between BTREE and B+TREE is that the B+TREE contains ALL of the keys (together with their ROWIDS) in the deepest level or the highest level and all of the nodes (pages) at the highest level are linked together in both directions to allow scans in both directions since the data is always sorted; in order to allow clauses in the WHERE clause of a SELECT such > n to scan from left to right and < n to scan from right to left without having to go up a level of the tree and descend to the following page (saving disk accesses).

    When a page (node)  is full, it is split in half and the middle (pivot value) value gets bumped up to the higher level node if the page split contains an odd number of values and if the number of keys is an even number, it bumps up the higher value of the first half of the values. The engine continues to add pages at the same level until the upper node is full and so on.

    It is impossible to have 5 levels in one section of the B+TREE and 4 levels in another section of the B+TREE (index structure). As a matter of fact, there is one single statistic generated by UPDATE STATISTICS and stored in sysindices (sysindexes view) per index. The highest level of any index is 20 in Informix. This will theoritically allow a humongous number of rows; never seen it myself. Most of the large database that we know have 5 or 6 levels or may be 7 (rarely); it is exponential. A table will have a large number of levels in a index if the table is indeed large or very volatile (a lot of holes after deletes and updates). That is why recreating indexes sometimes can save levels and hence disk accesses. Going from 5 levels to 4 saves 1 disk access or 20% of the index disk accesses. What I would suggest is to may be use a larger page size to store more keys in an index page and lower the levels. For large indexes, go yo 16K pages for the index.

    oncheck -pT database:tabname will not only give you the levels per index for that table but also the average number of free bytes at a level

    Jared, going to a sixth level made you need mode disk space since the engine needs to keep the B+TREE balanced at ll times even if the pages contains a lot of holes. Of course, what you did (redreate the index) saved you levels and disk space since your new inex structure is clean (a lot less holes). So what you observed is normal.

    This is the same for mono-column indexes or composite indexes.

    I hope that this helps inderstand the index structure functionning and why you faced the space issue.


    Cordialement, Regards,    Khaled Bentebal Directeur Général - ConsultiX Tél: 33 (0) 1 39 12 18 00 Mobile: 33 (0) 6 07 78 41 97 Email: khaled.bentebal@consult-ix.fr Site Web:  www.consult-ix.fr
    Le 01/03/2023 à 23:07, David Williams via IBM Community a écrit :
    010001869f374d82-ec14d26a-33fc-4e98-9d75-1d7b13cc921a-000000@email.amazonses.com">
    I am not sure it will always merge all pages, it is more focused on removing deleted entries from pages e.g. it will only look at pages with... -posted to the "Informix" group
    IBM Community

    Informix

    Post New Message
    Re: Index shrinks an entire b-tree level when removed/rebuilt....
    Reply to Group Reply to Sender
    David Williams
    Mar 1, 2023 5:07 PM
    David Williams
    1. I am not sure it will always merge all pages, it is more focused on removing deleted entries from pages e.g. it will only look at pages with deleted rows. There is an option to repack indexes.

          2. If in a part of the index a page becomes full and a new row needed to be inserted the page will be split and another level created. The level does not have to be the same in all branches of an index.



    ------------------------------
    David Williams
    ------------------------------
      Reply to Group Online   View Thread   Recommend   Forward   Flag as Inappropriate  




     
    You are subscribed to "Informix" as khaled.bentebal@consult-ix.fr. To change your subscriptions, go to My Subscriptions. To unsubscribe from this community discussion, go to Unsubscribe.



    Original Message:
    Sent: 3/1/2023 5:07:00 PM
    From: David Williams
    Subject: RE: Index shrinks an entire b-tree level when removed/rebuilt....

    1. I am not sure it will always merge all pages, it is more focused on removing deleted entries from pages e.g. it will only look at pages with deleted rows. There is an option to repack indexes.

          2. If in a part of the index a page becomes full and a new row needed to be inserted the page will be split and another level created. The level does not have to be the same in all branches of an index.



    ------------------------------
    David Williams
    ------------------------------


  • 6.  RE: Index shrinks an entire b-tree level when removed/rebuilt....

    Posted Thu March 02, 2023 03:34 AM

    Hi Jared,

    what's still missing in this discussion is Informix version info.

    I do remember a defect, a while ago, that caused incomplete index cleaning with indices in non-default page size dbspaces.

    And, btw., the most accurate info on index depth you'd get from 'oncheck -pT db:tabname' which would scan the entire table and all its indices (so pretty expensive) and produce an Index Usage Report.  What you can get from sysindices' or sysfragments' levels column is only as good as your statistics are current.



    ------------------------------
    Andreas Legner
    ------------------------------



  • 7.  RE: Index shrinks an entire b-tree level when removed/rebuilt....

    Posted Thu March 02, 2023 02:48 PM
    Edited by Jared Heath Thu March 02, 2023 03:32 PM

    I should have put the version in originally....this is a recently upgraded 14.10.FC7 system.   It was on 11.70.FC12 before (July).

    This table was on a 2k page, and only has 45ish million records.   

    We've had three really busy days (some related to another post I just made about raw devices) so I haven't had any time to read any of this...I hope to dig into it later this afternoon.



    ------------------------------
    Jared Heath
    ------------------------------