RE: FW: Upgrading from 7.31 to 9.3 - storage requirements
Received this from a fellow DBA: Here is dettached index size calc in 9.4 Estimating Extent Size of Detached Index For a detached index, the database server uses the ratio of the index key size plus some overhead bytes to the rowsize to assign an appropriate extent size for the index, as the following formula shows: Detached Index extent size = ( (index_key_size + 9) / table_row_size) * table_extent_size -----Original Message----- From: Madison Pruet [mailto:mpruet@comcast.net] Sent: Tuesday, November 04, 2003 5:13 AM To: informix-list@iiug.org Subject: Re: FW: Upgrading from 7.31 to 9.3 - storage requirements Murray, I don't know why there would be degradation or why there would be an increased storage requirement with detached indexes. I've been checking for any know problems and have found none in the case management tool. However, I do know that each index requires an entry in the partition page and that the size of the entry did increase with 9.x over 7.x because of the support of functional indexes. So that would mean that you need to manage data page extent sizing much more carefully if you use attached indexes in 9.x. I'd really like to know the specifics of the detached index issue that has been referred to in this chain. "Murray Wood (IList)" <ifxmaillist@quanta.co.nz> wrote in message news:bo6ucr$129$1@terabinaries.xmission.com... > > So for a large table with many indexes, what is the pages allocated, pages > used both in 7.31 and 9.3? There will be 1 figure before as all the indexes > are attached. There will be many in 9.3 as all indexes are detached. How > much free space is in each data extent ? > > MW > > > > -----Original Message----- > > From: owner-informix-list@iiug.org > > [mailto:owner-informix-list@iiug.org]On Behalf Of coder > > Sent: Tuesday, 4 November 2003 10:40 a.m. > > To: informix-list@iiug.org > > Subject: Re: FW: Upgrading from 7.31 to 9.3 - storage requirements > > > > > > 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 =--- > > sending to informix-list sending to informix-list