Re: FW: Upgrading from 7.31 to 9.3 - storage requirements
When we upgraded to 9.3 about a year ago it made our indexes almost 5 times bigger than previously. In addition, we also found out due to an internal code glitch, the detached indexes were not giving the improved speed performance that they were designed to do. Matter of fact it was slower than detached. I'd recommend migrating but attaching the indexes until IBFormix gets their act togeather. 1 very easy way to do that is to set the parameter in the $INFORMIXDIR/etc/informix.rc file DEFAULT_ATTACH 1 (basically setting it to anything besides null turns default attach off). Hope this helps. "Dirk Moolman" <DirkM@mxgroup.co.za> wrote: > >Anyone ? > >-----Original Message----- >From: Dirk Moolman >Sent: Wednesday, October 04, 2003 10:38 AM >To: informix-list@iiug.org >Subject: Upgrading from 7.31 to 9.3 - storage requirements > >I know that the indexes are detached in version 9, so upgrading from 7 to 9 >will require more storage. > >Question: Does anyone have a query I can use that will calculate my storage >requirements for the upgrade ? > > > > >sending to informix-list > >sending to informix-list ----== 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 =---
I can think of no reason that the indexes would have been 5 times larger. The performance difference is not due so much that the indexes are detached, but that the index pages are not intermingled with the data pages. That means that when an table scan is being performed that it is possible to get through the scan quicker because we are not having to read and skip over index pages. Do you have any case numbers associated with the increased index page usage? I had not heard of this problem and would like the specifics. Thanks M.P. "Coder" <l0l@comcast.net> wrote in message news:3fa5243f$1_2@127.0.0.1... > When we upgraded to 9.3 about a year ago it made our indexes almost 5 > times bigger than previously. In addition, we also found out due to an > internal code glitch, the detached indexes were not giving the improved > speed performance that they were designed to do. Matter of fact it was > slower than detached. I'd recommend migrating but attaching the indexes > until IBFormix gets their act togeather. 1 very easy way to do that is to > set the parameter in the $INFORMIXDIR/etc/informix.rc file > DEFAULT_ATTACH 1 > (basically setting it to anything besides null turns default attach off). > > Hope this helps. > "Dirk Moolman" <DirkM@mxgroup.co.za> wrote: > > > >Anyone ? > > > >-----Original Message----- > >From: Dirk Moolman > >Sent: Wednesday, October 04, 2003 10:38 AM > >To: informix-list@iiug.org > >Subject: Upgrading from 7.31 to 9.3 - storage requirements > > > >I know that the indexes are detached in version 9, so upgrading from 7 to 9 > >will require more storage. > > > >Question: Does anyone have a query I can use that will calculate my storage > >requirements for the upgrade ? > > > > > > > > > >sending to informix-list > > > >sending to informix-list > > > > ----== 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 =---
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). The only time we detach indexes now is when we fragment tables. "Madison Pruet" <mpruet@comcast.net> wrote: >I can think of no reason that the indexes would have been 5 times larger. >The performance difference is not due so much that the indexes are detached, >but that the index pages are not intermingled with the data pages. That >means that when an table scan is being performed that it is possible to get >through the scan quicker because we are not having to read and skip over >index pages. > >Do you have any case numbers associated with the increased index page usage? >I had not heard of this problem and would like the specifics. > >Thanks > >M.P. > >"Coder" <l0l@comcast.net> wrote in message news:3fa5243f$1_2@127.0.0.1... >> When we upgraded to 9.3 about a year ago it made our indexes almost 5 >> times bigger than previously. In addition, we also found out due to an >> internal code glitch, the detached indexes were not giving the improved >> speed performance that they were designed to do. Matter of fact it was >> slower than detached. I'd recommend migrating but attaching the indexes >> until IBFormix gets their act togeather. 1 very easy way to do that is to >> set the parameter in the $INFORMIXDIR/etc/informix.rc file >> DEFAULT_ATTACH 1 >> (basically setting it to anything besides null turns default attach off). >> >> Hope this helps. >> "Dirk Moolman" <DirkM@mxgroup.co.za> wrote: >> > >> >Anyone ? >> > >> >-----Original Message----- >> >From: Dirk Moolman >> >Sent: Wednesday, October 04, 2003 10:38 AM >> >To: informix-list@iiug.org >> >Subject: Upgrading from 7.31 to 9.3 - storage requirements >> > >> >I know that the indexes are detached in version 9, so upgrading from 7 to >9 >> >will require more storage. >> > >> >Question: Does anyone have a query I can use that will calculate my >storage >> >requirements for the upgrade ? >> > >> > >> > >> > >> >sending to informix-list >> > >> >sending to informix-list >> >> >> >> ----== 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 >=--- > > ----== 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 =---