"Re-indexing" Informix

[red_valsen]

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.

[Art Kagel]

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.

_______________________________________________
Informix-list mailing list
Inform...@iiug.org
http://www.iiug.org/mailman/listinfo/informix-list

[red_valsen]

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.

[Art Kagel]

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

[Fernando Nunes]

On Mon, Sep 27, 2010 at 3:47 PM, red_valsen <red_v...@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...

[Cesar Inacio Martins]

Will be nice if, some day,  IBM create the "index repack" (sysadmin API) to regroup the index extents without need read all table. --- Em seg, 27/9/10, Fernando Nunes <domus...@gmail.com> escreveu:

-----Anexo incorporado-----

[Obnoxio The Clown]

On 28/09/2010 11:57, Cesar Inacio Martins wrote:
> Will be nice if, some day, IBM create the "index repack" (sysadmin API)
> to regroup the index extents without need read all table.

Log a feature request with technical support.

--
Cheers,
Obnoxio The Clown

http://obotheclown.blogspot.com
I will now proceed to pleasure myself with this fish.

--
This message has been scanned for viruses and
dangerous content by OpenProtect(http://www.openprotect.com), and is
believed to be clean.

[Fernando Nunes]

Hello Cesar.
You should not confuse repack with extent reorganization. Repack moves rows to the begining of the table and marks pages at the end of the table as free. These pages can then be freed with shrink.
What you're referring to is the extent concatenation (possibly moving them to bigger extents).
B-Tree scanner already "compacts" the indexes if there are deleted items. This will prevent the index to allocate more extents. It also increases performance because with the same number of page reads you will get more index keys.

And finnaly, neither of these two options will make a full "rebalance" of the index.
So we're facing three types of improvements:

- Reuse the alocated but deleted entries in the index (witch compacts the keys in the already allocated space): This is done by the b-tree scanner and could in some degree be compared to the repack task
- Join small extents into bigger extents. This is what you were talking about. Not done in any GA version of Informix currently
- Rebuild the index to make it more "ballanced" (there's much more to this than what we have space and time here). This is not done currently unless you recreate the Index. I believe other RDBMS can REBUILD the indexes without having to read the data. They just read the index already created and take advantage of the already sorted data. We don't do this and possibly it would be nice. However, if the index is really "unballanced", scanning the index in an ordered way is really slow, and I have great doubts that it would be better than read the data and order it. A problem I helped some months ago, make me think it's not a good idea.
It would be faster is the index was ok, but in those cases it would be useless to rebuild the index.
So, in short, I wouldn't choose this as a priority if I were in development.

Regards.

On Tue, Sep 28, 2010 at 11:57 AM, Cesar Inacio Martins <cesar_inac...@yahoo.com.br> wrote:

Will be nice if, some day,  IBM create the "index repack" (sysadmin API) to regroup the index extents without need read all table.

[Cesar Inacio Martins]

Hi Fernando, I just use the "index repack" name because today to solve problems with extents we can use the "table repack" (+shrink), but you right, they are differ things...  So.. let's say "index defrag" .... --- Em ter, 28/9/10, Fernando Nunes <domus...@gmail.com> escreveu:

[Fernando Nunes]

On Tue, Sep 28, 2010 at 5:21 PM, Cesar Inacio Martins <cesar_inac...@yahoo.com.br> wrote:

Hi Fernando, I just use the "index repack" name because today to solve problems with extents we can use the "table repack" (+shrink), but you right, they are differ things...  So.. let's say "index defrag" ....

Today you'll only solve problems with extents using repack IF you have deleted (many?) rows from the table. If you haven't it will not solve the extents problem.
Repack will not compact extents. Only rows within the already allocated extents. If the row relocation leaves some extents with just free pages (at the end of the table) than shrink will be able to release some extents.

But if you don't have "holes" in the table created by deletes, it will not solve the extents issue.

That would be the already requested feature for REORG a table...