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

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

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?

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

Did you fragment over 5 dbspaces, perhaps?

--
Ciao,
The Obnoxious One

"Ogni uomo mi guarda come se fossi una testa di cazzo"

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

Obnoxio The Clown <obnoxio@hotmail.com> wrote:
>
>
>Did you fragment over 5 dbspaces, perhaps?
>
>--
>Ciao,
>The Obnoxious One
>
>"Ogni uomo mi guarda come se fossi una testa di cazzo"
no, but thanks for trying.
Only 1 % of our tables are fragmented in the entire instance.

"The only time we detach indexes now is when we fragment tables."
And that is to get around another problem with serial keys & indexes
on fragmented tables (but thats another story all togeather).

My site does not have a problem, but thank you all very much for
trying to assist.

I am trying to recomend to Dirk to use the DEFAULT_ATTACH variable in
his informix.rc file. It should take care of his problems like it did ours.





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