"Re-indexing" Informix
My users, having been raised on whOracle, seem to believe that if we drop all 40 GB of detached indexes from our 180-GB database and recreate them, without changing anything else, it will improve performance, just like in the other DBMS. Despite my explanation that Informix uses auto-balancing B+ tree indexes which obviate the need for "reindexing," they insist this be done, though they cannot explain the origin of the performance gain. From what I've gleaned, I can only see that the same indexes will be recreated in the same way, with everything the same: index placement on disk, number of extents, index levels, etc. Can anybody explain some performance improvement that is possible by "reindexing" in Informix? I mean, I can learn. Using IDS 11.50.FC4 on HPUX 11.23.
Periodic reindexing is NOT required for Informix mostly because of the BTREE Scanner threads which remove deleted keys from the indexes and merge (or compress in Informix terminology) mostly empty index nodes to maintain performance. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) IIUG Board of Directors (art@iiug.org) Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Sat, Sep 25, 2010 at 12:54 PM, red_valsen <red_valsen@yahoo.com> wrote: > My users, having been raised on whOracle, seem to believe that if we > drop all 40 GB of detached indexes from our 180-GB database and > recreate them, without changing anything else, it will improve > performance, just like in the other DBMS. Despite my explanation that > Informix uses auto-balancing B+ tree indexes which obviate the need > for "reindexing," they insist this be done, though they cannot explain > the origin of the performance gain. From what I've gleaned, I can > only see that the same indexes will be recreated in the same way, with > everything the same: index placement on disk, number of extents, index > levels, etc. Can anybody explain some performance improvement that is > possible by "reindexing" in Informix? I mean, I can learn. > > Using IDS 11.50.FC4 on HPUX 11.23. > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list >
On Sep 25, 10:32 pm, Art Kagel <art.ka...@gmail.com> wrote: > Periodic reindexing is NOT required for Informix mostly because of the BTREE > Scanner threads which remove deleted keys from the indexes and merge (or > compress in Informix terminology) mostly empty index nodes to maintain > performance. > > Art > > Art S. Kagel > Advanced DataTools (www.advancedatatools.com) > IIUG Board of Directors (a...@iiug.org) > > Disclaimer: Please keep in mind that my own opinions are my own opinions and > do not reflect on my employer, Advanced DataTools, the IIUG, nor any other > organization with which I am associated either explicitly, implicitly, or by > inference. Neither do those opinions reflect those of other individuals > affiliated with any entity with which I am affiliated nor those of the > entities themselves. > > On Sat, Sep 25, 2010 at 12:54 PM, red_valsen <red_val...@yahoo.com> wrote: > > My users, having been raised on whOracle, seem to believe that if we > > drop all 40 GB of detached indexes from our 180-GB database and > > recreate them, without changing anything else, it will improve > > performance, just like in the other DBMS. Despite my explanation that > > Informix uses auto-balancing B+ tree indexes which obviate the need > > for "reindexing," they insist this be done, though they cannot explain > > the origin of the performance gain. From what I've gleaned, I can > > only see that the same indexes will be recreated in the same way, with > > everything the same: index placement on disk, number of extents, index > > levels, etc. Can anybody explain some performance improvement that is > > possible by "reindexing" in Informix? I mean, I can learn. > > > Using IDS 11.50.FC4 on HPUX 11.23. > > _______________________________________________ > > Informix-list mailing list > > Informix-l...@iiug.org > >http://www.iiug.org/mailman/listinfo/informix-list Here's what happened after indexes were dropped and recreated: The number of extents used by individual indexes was substantially reduced. Greatest number prior was 176; afterwards, 9. I'm somewhat surprised, yet can't see that this would have anything but a positive effect on performance, even if to a minor degree. I'll have to do a little digging to explain the change.
The effect on performance of defragmenting indexes can sometimes be big but is usually small. Most index pages of active indexes tend to remain in cache for most of the day which means that the effect of the fragmentation is only at the time that the pages are read in. Note that index fragmentation is usually a result of the extent sizing of the parent table being too small so that the table is likely fragmented as well (since the index's extents are sized as a percentage of the table's extents based on the ratio of the key to the row size). Let us know what the real effects are for your installation. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) IIUG Board of Directors (art@iiug.org) Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Mon, Sep 27, 2010 at 10:47 AM, red_valsen <red_valsen@yahoo.com> wrote: > On Sep 25, 10:32 pm, Art Kagel <art.ka...@gmail.com> wrote: > > Periodic reindexing is NOT required for Informix mostly because of the > BTREE > > Scanner threads which remove deleted keys from the indexes and merge (or > > compress in Informix terminology) mostly empty index nodes to maintain > > performance. > > > > Art > > > > Art S. Kagel > > Advanced DataTools (www.advancedatatools.com) > > IIUG Board of Directors (a...@iiug.org) > > > > Disclaimer: Please keep in mind that my own opinions are my own opinions > and > > do not reflect on my employer, Advanced DataTools, the IIUG, nor any > other > > organization with which I am associated either explicitly, implicitly, or > by > > inference. Neither do those opinions reflect those of other individuals > > affiliated with any entity with which I am affiliated nor those of the > > entities themselves. > > > > On Sat, Sep 25, 2010 at 12:54 PM, red_valsen <red_val...@yahoo.com> > wrote: > > > My users, having been raised on whOracle, seem to believe that if we > > > drop all 40 GB of detached indexes from our 180-GB database and > > > recreate them, without changing anything else, it will improve > > > performance, just like in the other DBMS. Despite my explanation that > > > Informix uses auto-balancing B+ tree indexes which obviate the need > > > for "reindexing," they insist this be done, though they cannot explain > > > the origin of the performance gain. From what I've gleaned, I can > > > only see that the same indexes will be recreated in the same way, with > > > everything the same: index placement on disk, number of extents, index > > > levels, etc. Can anybody explain some performance improvement that is > > > possible by "reindexing" in Informix? I mean, I can learn. > > > > > Using IDS 11.50.FC4 on HPUX 11.23. > > > _______________________________________________ > > > Informix-list mailing list > > > Informix-l...@iiug.org > > >http://www.iiug.org/mailman/listinfo/informix-list > > Here's what happened after indexes were dropped and recreated: The > number of extents used by individual indexes was substantially > reduced. Greatest number prior was 176; afterwards, 9. I'm somewhat > surprised, yet can't see that this would have anything but a positive > effect on performance, even if to a minor degree. I'll have to do a > little digging to explain the change. > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list >
On Mon, Sep 27, 2010 at 3:47 PM, red_valsen <red_valsen@yahoo.com> wrote: > > Here's what happened after indexes were dropped and recreated: The > number of extents used by individual indexes was substantially > reduced. Greatest number prior was 176; afterwards, 9. I'm somewhat > surprised, yet can't see that this would have anything but a positive > effect on performance, even if to a minor degree. I'll have to do a > little digging to explain the change. > This is perfectly normal. The B-Tree scanner will not reduce index extents. So don't be surprised. What you should check it the number of extents on your tables. The index number of extents should somewhat reflect the table number of extents. And 176 is too high specially if your pagesize is 2K. Regardind performance, yes, if you were able to measure any difference it would be an improvement. But again, this would only be noticeable if you did frequent and very large index range scans. Regards. -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently...