NPTOTAL
Question (IDS 10): why does sysptnhdr show NPTOTAL much larger than NPUSED for index partitions, and what happens when they're equal? Answers: NPTOTAL is the total pages in all allocated extents, NPUSED the pages actually in use; when they match, a new extent is allocated as soon as another page is needed (a new node/leaf or node split). The gap for indexes comes from Informix deriving index extent sizes from the table's EXTENT/NEXT SIZE scaled by key-size/row-size, which over-allocates for duplicate-key indexes; fragmentation doesn't change this (each fragment gets the same sizes), and FILLFACTOR affects pages used but not extent sizing. No fix beyond awareness; users were asking IBM for independent index extent sizing.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Version IDS 10: Why is it that in sysptnhdr NPTOTAL is much higher for indexes as compared to NPUSED. What does it really mean ? - Is it because of Fill Factor - or is it because of size of table that determines how to pre allocate. - Also, what happens when NPUSED = NPTOTAL ?
mohitanchlia@gmail.com wrote: > Version IDS 10: > > Why is it that in sysptnhdr NPTOTAL is much higher for indexes as > compared to NPUSED. What does it really mean ? > - Is it because of Fill Factor > - or is it because of size of table that determines how to pre > allocate. > - Also, what happens when NPUSED = NPTOTAL ? NPTOTAL is the sum of the page extents NPUSED is the sum of the used pages When they are equal then a new extent will be allocated Cheers Paul
mohitanchlia@gmail.com wrote: > Version IDS 10: > > Why is it that in sysptnhdr NPTOTAL is much higher for indexes as > compared to NPUSED. What does it really mean ? > It means that the calculation of extent sizing for indexes is not effective enough. The extent size and initial next size of index partitions is calculated from the extent sizing of the table it indexes by applying a factor calculated as the ratio of the key size to the row size. This doesn't work as well as one would expect for indexes with duplicate keys because the inversion list of rowids in the leaves of such indexes takes up far less room than the leaves of a unique index. That's why many of us have been pressing IBM to allow us to specify extent sizing for indexes at create time independent of the table's extent sizing. > - Is it because of Fill Factor > - or is it because of size of table that determines how to pre > allocate. > - Also, what happens when NPUSED = NPTOTAL ? > A new extent is allocated to the partition when the last page is filled and another is needed. For index partitions, that means when the next node or leaf needs to be added or a node split. Art S. Kagel Oninit > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list > > =========================================================================================== > Please access the attached hyperlink for an important electronic communications disclaimer: > > http://www.oninit.com/home/disclaimer.php > > =========================================================================================== > > =========================================================================================== Please access the attached hyperlink for an important electronic communications disclaimer: http://www.oninit.com/home/disclaimer.php ===========================================================================================
On Jan 20, 7:09 am, "Art S. Kagel (Oninit LLC)" <a...@oninit.com> wrote: > mohitanch...@gmail.com wrote: > > Version IDS 10: > > > Why is it that in sysptnhdr NPTOTAL is much higher for indexes as > > compared to NPUSED. What does it really mean ? > > It means that the calculation of extent sizing for indexes is not > effective enough. The extent size and initial next size of index > partitions is calculated from the extent sizing of the table it indexes > by applying a factor calculated as the ratio of the key size to the row > size. This doesn't work as well as one would expect for indexes with > duplicate keys because the inversion list of rowids in the leaves of > such indexes takes up far less room than the leaves of a unique index. > That's why many of us have been pressing IBM to allow us to specify > extent sizing for indexes at create time independent of the table's > extent sizing. > > > - Is it because of Fill Factor > > - or is it because of size of table that determines how to pre > > allocate. > > - Also, what happens when NPUSED = NPTOTAL ? > > A new extent is allocated to the partition when the last page is filled > and another is needed. For index partitions, that means when the next > node or leaf needs to be added or a node split. > > Art S. Kagel > Oninit > > > _______________________________________________ > >Informix-list mailing list > >Informix-l...@iiug.org > >http://www.iiug.org/mailman/listinfo/informix-list > > > =========================================================================================== > > Please access the attached hyperlink for an important electronic communications disclaimer: > > >http://www.oninit.com/home/disclaimer.php > > > =========================================================================================== > > =========================================================================================== > Please access the attached hyperlink for an important electronic communications disclaimer: > > http://www.oninit.com/home/disclaimer.php > > =========================================================================================== How does fill factor affect the sizing and what should be the criteria for deciding on fill factor ?
On Jan 22, 11:18 pm, mohitanch...@gmail.com wrote: > On Jan 20, 7:09 am, "Art S. Kagel (Oninit LLC)" <a...@oninit.com> > wrote: > > > > > mohitanch...@gmail.com wrote: > > > Version IDS 10: > > > > Why is it that in sysptnhdr NPTOTAL is much higher for indexes as > > > compared to NPUSED. What does it really mean ? > > > It means that the calculation of extent sizing for indexes is not > > effective enough. The extent size and initial next size of index > > partitions is calculated from the extent sizing of the table it indexes > > by applying a factor calculated as the ratio of the key size to the row > > size. This doesn't work as well as one would expect for indexes with > > duplicate keys because the inversion list of rowids in the leaves of > > such indexes takes up far less room than the leaves of a unique index. > > That's why many of us have been pressing IBM to allow us to specify > > extent sizing for indexes at create time independent of the table's > > extent sizing. > > > > - Is it because of Fill Factor > > > - or is it because of size of table that determines how to pre > > > allocate. > > > - Also, what happens when NPUSED = NPTOTAL ? > > > A new extent is allocated to the partition when the last page is filled > > and another is needed. For index partitions, that means when the next > > node or leaf needs to be added or a node split. > > > Art S. Kagel > > Oninit > > > > _______________________________________________ > > >Informix-list mailing list > > >Informix-l...@iiug.org > > >http://www.iiug.org/mailman/listinfo/informix-list > > > > =========================================================================================== > > > Please access the attached hyperlink for an important electronic communications disclaimer: > > > >http://www.oninit.com/home/disclaimer.php > > > > =========================================================================================== > > > =========================================================================================== > > Please access the attached hyperlink for an important electronic communications disclaimer: > > >http://www.oninit.com/home/disclaimer.php > > > =========================================================================================== > > How does fill factor affect the sizing and what should be the criteria > for deciding on fill factor ? I am editing my earlier question: 1. How does fill factor affect the sizing and what should be the criteria for deciding on fill factor ? 2. When Index is fragmented in the dbspace how is extent size calculated for index. Is it NPTOTAL/No of fragments = extent size for each fragment ?
mohitanchlia@gmail.com wrote: > On Jan 22, 11:18 pm, mohitanch...@gmail.com wrote: > >> On Jan 20, 7:09 am, "Art S. Kagel (Oninit LLC)" <a...@oninit.com> >> wrote: >> >> >> >> >>> mohitanch...@gmail.com wrote: >>> >>>> Version IDS 10: >>>> >>>> Why is it that in sysptnhdr NPTOTAL is much higher for indexes as >>>> compared to NPUSED. What does it really mean ? >>>> >>> It means that the calculation of extent sizing for indexes is not >>> effective enough. The extent size and initial next size of index >>> partitions is calculated from the extent sizing of the table it indexes >>> by applying a factor calculated as the ratio of the key size to the row >>> size. This doesn't work as well as one would expect for indexes with >>> duplicate keys because the inversion list of rowids in the leaves of >>> such indexes takes up far less room than the leaves of a unique index. >>> That's why many of us have been pressing IBM to allow us to specify >>> extent sizing for indexes at create time independent of the table's >>> extent sizing. >>> >>>> - Is it because of Fill Factor >>>> - or is it because of size of table that determines how to pre >>>> allocate. >>>> - Also, what happens when NPUSED = NPTOTAL ? >>>> >>> A new extent is allocated to the partition when the last page is filled >>> and another is needed. For index partitions, that means when the next >>> node or leaf needs to be added or a node split. >>> >>> Art S. Kagel >>> Oninit >>> >>>> _______________________________________________ >>>> Informix-list mailing list >>>> Informix-l...@iiug.org >>>> http://www.iiug.org/mailman/listinfo/informix-list >>>> >>>> =========================================================================================== >>>> Please access the attached hyperlink for an important electronic communications disclaimer: >>>> >>>> http://www.oninit.com/home/disclaimer.php >>>> >>>> =========================================================================================== >>>> >>> =========================================================================================== >>> Please access the attached hyperlink for an important electronic communications disclaimer: >>> >>> http://www.oninit.com/home/disclaimer.php >>> >>> =========================================================================================== >>> >> How does fill factor affect the sizing and what should be the criteria >> for deciding on fill factor ? >> > > I am editing my earlier question: > 1. How does fill factor affect the sizing and what should be the > criteria for deciding on fill factor ? > FILLFACTOR will affect the actual pages allocated to the index by increasing or decreasing the number of pages needed by the index, but it does not affect the extent sizing of the index partition at all. > 2. When Index is fragmented in the dbspace how is extent size > calculated for index. Is it NPTOTAL/No of fragments = extent size for > each fragment ? The EXTENT SIZE and NEXT EXTENT size of the table is applied to all fragments without change. Index extent size is still a percentage of those sizes calculated using the ratio of the key size to the row size. No adjustment is made based on whether the table or the index or both are fragmented. Art S. Kagel Onint =========================================================================================== Please access the attached hyperlink for an important electronic communications disclaimer: http://www.oninit.com/home/disclaimer.php ===========================================================================================