Re: FW: Upgrading from 7.31 to 9.3 - storage requirements

On 2 Nov 2003 19:49:36 -0600, Coder <lol@tryagain.com> wrote: >We could think of no reason for the growth either, but alas it happened. >Since it was over a year ago, no we don't have any case numbers written >down. The 1st weekend after our upgrade to 9.3, we performed our >standard reorg processes. Unloading data, dropping tables, standing >'em back up and rebuilding indexes & updating stats (we have these >procedures automated by now). We ended up having to add enough >chunks to increase each DB space to 10gb (original entire DB spaces >were typically 3 to 3 1/2 gb. This over growth happened regardless >or our setting index fill factor to 100 %. After many calls to IBFormix, >some tech finally admited to the detached indexes not only being >large, but like I'd said, inefficient due to some code mistakes. We >found about the DEFAULT_ATTACH variable, set it up, and next reorg >time everything shrunk back to normal (alice drank the other potion). > Concerning the growth . . . . In a table schema, the EXTENT SIZE will include all attached index pages. When you reorged and all of the index pages were then detached, did you change the initial extent size to eliminate the index pages as part of you initial extent for your table?

John Carlson <john_carlson@whsmithusa.com> wrote: >On 2 Nov 2003 19:49:36 -0600, Coder <lol@tryagain.com> wrote: > >>We could think of no reason for the growth either, but alas it happened. >>Since it was over a year ago, no we don't have any case numbers written >>down. The 1st weekend after our upgrade to 9.3, we performed our >>standard reorg processes. Unloading data, dropping tables, standing >>'em back up and rebuilding indexes & updating stats (we have these >>procedures automated by now). We ended up having to add enough >>chunks to increase each DB space to 10gb (original entire DB spaces >>were typically 3 to 3 1/2 gb. This over growth happened regardless >>or our setting index fill factor to 100 %. After many calls to IBFormix, >>some tech finally admited to the detached indexes not only being >>large, but like I'd said, inefficient due to some code mistakes. We >>found about the DEFAULT_ATTACH variable, set it up, and next reorg >>time everything shrunk back to normal (alice drank the other potion). >> > >Concerning the growth . . . . > >In a table schema, the EXTENT SIZE will include all attached index >pages. When you reorged and all of the index pages were then >detached, did you change the initial extent size to eliminate the >index pages as part of you initial extent for your table? yes. But even before we did, I was still confused as to why it would take 2-3 times the dbspace just for the indicies. ----== Posted via Newsfeed.Com - Unlimited-Uncensored-Secure Usenet News==---- http://www.newsfeed.com The #1 Newsgroup Service in the World! >100,000 Newsgroups ---= 19 East/West-Coast Specialized Servers - Total Privacy via Encryption =---

On 3 Nov 2003 15:40:20 -0600, coder <lol@notme.com> wrote: >John Carlson <john_carlson@whsmithusa.com> wrote: >>On 2 Nov 2003 19:49:36 -0600, Coder <lol@tryagain.com> wrote: >> >>>We could think of no reason for the growth either, but alas it happened. >>>Since it was over a year ago, no we don't have any case numbers written >>>down. The 1st weekend after our upgrade to 9.3, we performed our >>>standard reorg processes. Unloading data, dropping tables, standing >>>'em back up and rebuilding indexes & updating stats (we have these >>>procedures automated by now). We ended up having to add enough >>>chunks to increase each DB space to 10gb (original entire DB spaces >>>were typically 3 to 3 1/2 gb. This over growth happened regardless >>>or our setting index fill factor to 100 %. After many calls to IBFormix, >>>some tech finally admited to the detached indexes not only being >>>large, but like I'd said, inefficient due to some code mistakes. We >>>found about the DEFAULT_ATTACH variable, set it up, and next reorg >>>time everything shrunk back to normal (alice drank the other potion). >>> >> >>Concerning the growth . . . . >> >>In a table schema, the EXTENT SIZE will include all attached index >>pages. When you reorged and all of the index pages were then >>detached, did you change the initial extent size to eliminate the >>index pages as part of you initial extent for your table? >yes. >But even before we did, I was still confused as to why it would >take 2-3 times the dbspace just for the indicies. Not certain why, either, maybe I'm oversimplifying the issue . . . . Table tab_1 has a first extent of 10000 pages Within tab_1 exist: idx_1 -- 1000 index pages idx_2 -- 2000 index pages idx_3 -- 3000 index pages When tab_1 is created, a single extent of 10000 pages is created. The attached index pages are part of this initial extent. When the table is recreated at part of the reorg, the initial extent of 10000 pages is created, but when the detached indices are created, they don't utilize the 10000 first extent. Informix uses some formula to determine a detached index's FIRST and NEXT sizes. Total space taken up now is 15000 pages. Other factors that may enter in would be FILLFACTOR and the extra storage required per index record (4 bytes??). Hope it helps

Its based on the width of the index compared to the width of the row and the estimated number of rows that will fit into the initial extent. "John Carlson" <john_carlson@whsmithusa.com> wrote in message news:4gjdqvsugf76mjpjabcnj34nr23k7lr5ci@4ax.com... > On 3 Nov 2003 15:40:20 -0600, coder <lol@notme.com> wrote: > > >John Carlson <john_carlson@whsmithusa.com> wrote: > >>On 2 Nov 2003 19:49:36 -0600, Coder <lol@tryagain.com> wrote: > >> > >>>We could think of no reason for the growth either, but alas it happened. > >>>Since it was over a year ago, no we don't have any case numbers written > >>>down. The 1st weekend after our upgrade to 9.3, we performed our > >>>standard reorg processes. Unloading data, dropping tables, standing > >>>'em back up and rebuilding indexes & updating stats (we have these > >>>procedures automated by now). We ended up having to add enough > >>>chunks to increase each DB space to 10gb (original entire DB spaces > >>>were typically 3 to 3 1/2 gb. This over growth happened regardless > >>>or our setting index fill factor to 100 %. After many calls to IBFormix, > >>>some tech finally admited to the detached indexes not only being > >>>large, but like I'd said, inefficient due to some code mistakes. We > >>>found about the DEFAULT_ATTACH variable, set it up, and next reorg > >>>time everything shrunk back to normal (alice drank the other potion). > >>> > >> > >>Concerning the growth . . . . > >> > >>In a table schema, the EXTENT SIZE will include all attached index > >>pages. When you reorged and all of the index pages were then > >>detached, did you change the initial extent size to eliminate the > >>index pages as part of you initial extent for your table? > >yes. > >But even before we did, I was still confused as to why it would > >take 2-3 times the dbspace just for the indicies. > > Not certain why, either, maybe I'm oversimplifying the issue . . . . > > Table tab_1 has a first extent of 10000 pages > > Within tab_1 exist: > idx_1 -- 1000 index pages > idx_2 -- 2000 index pages > idx_3 -- 3000 index pages > > When tab_1 is created, a single extent of 10000 pages is created. The > attached index pages are part of this initial extent. When the table > is recreated at part of the reorg, the initial extent of 10000 pages > is created, but when the detached indices are created, they don't > utilize the 10000 first extent. Informix uses some formula to > determine a detached index's FIRST and NEXT sizes. Total space taken > up now is 15000 pages. Other factors that may enter in would be > FILLFACTOR and the extra storage required per index record (4 > bytes??). > > Hope it helps