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 (AT) iiug (DOT) 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 (AT) yahoo (DOT) com> wrote:

On Sep 25, 10:32*pm, Art Kagel <art.ka... (AT) gmail (DOT) 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.

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 (AT) iiug (DOT) 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 (AT) yahoo (DOT) com> wrote:

On Mon, Sep 27, 2010 at 3:47 PM, red_valsen <red_valsen (AT) yahoo (DOT) com> wrote:

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

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 <domusonline (AT) gmail (DOT) com> escreveu:

De: Fernando Nunes <domusonline (AT) gmail (DOT) com>
Assunto: Re: "Re-indexing" Informix
Para: informix-list (AT) iiug (DOT) org
Data: Segunda-feira, 27 de Setembro de 2010, 12:55



On Mon, Sep 27, 2010 at 3:47 PM, red_valsen <red_valsen (AT) yahoo (DOT) 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 didfrequent 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...



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

_______________________________________________
Informix-list mailing list
Informix-list (AT) iiug (DOT) org
http://www.iiug.org/mailman/listinfo/informix-list

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_inacio_martins (AT) yahoo (DOT) com.br> wrote:


--
Fernando Nunes
Portugal

http://informix-technology.blogspot.com
My email works... but I don't check it frequently...

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 <domusonline (AT) gmail (DOT) com> escreveu:

De: Fernando Nunes <domusonline (AT) gmail (DOT) com>
Assunto: Re: "Re-indexing" Informix
Para: informix-list (AT) iiug (DOT) org
Data: Terça-feira, 28 de Setembro de 2010, 8:17

Hello Cesar.
You should not confuse repack with extent reorganization. Repack moves rowsto 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 andcould 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 wouldbe 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_inacio_martins (AT) yahoo (DOT) 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.



--- Em seg, 27/9/10, Fernando Nunes <domusonline (AT) gmail (DOT) com> escreveu:


De: Fernando Nunes <domusonline (AT) gmail (DOT) com>
Assunto: Re: "Re-indexing" Informix
Para: informix-list (AT) iiug (DOT) org

Data: Segunda-feira, 27 de Setembro de 2010, 12:55



On Mon, Sep 27, 2010 at 3:47 PM, red_valsen <red_valsen (AT) yahoo (DOT) 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 didfrequent 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...




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

_______________________________________________
Informix-list mailing list
Informix-list (AT) iiug (DOT) org

http://www.iiug.org/mailman/listinfo/informix-list





*
_______________________________________________

Informix-list mailing list

Informix-list (AT) iiug (DOT) org

http://www.iiug.org/mailman/listinfo/informix-list





--
Fernando Nunes
Portugal

http://informix-technology.blogspot.com
My email works... but I don't check it frequently...



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

_______________________________________________
Informix-list mailing list
Informix-list (AT) iiug (DOT) org
http://www.iiug.org/mailman/listinfo/informix-list

On Tue, Sep 28, 2010 at 5:21 PM, Cesar Inacio Martins <
cesar_inacio_martins (AT) yahoo (DOT) com.br> wrote:

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

--
Fernando Nunes
Portugal

http://informix-technology.blogspot.com
My email works... but I don't check it frequently...