Re: IDS 7.30: a table with 150 extents
Madison, If I have a plan to unload, drop and reload a table to reduce the no. of extents, is it a good idea to stop logging, do unload, recreate table, load data and then start logging back and take an fake archive to /dev/null? Thanks for the reply Vinod Bhansali >From: Madison Pruet <mpruet@home.com> >To: Vinod Bhansali <iiug@hotmail.com> >Subject: Re: IDS 7.30: a table with 150 extents >Date: Thu, 15 Jun 2000 16:51:15 -0500 > >Don't get me wrong. It is still an issue because you can get into a >situation >where a new extent can not be allocate, simply because the extent >information >will not fit within the partition page. However, the performance impact is >not >nearly as great as it was in 5.x. > >The number of extents that can be allocated depend on the number of indexes >that >the table has and the page size. For 2K page systems, I'd become a bit >concerned if the number of extents reached 150, however, I would not be too >concerned with 40. On 5.x systems, anything above 8 extents would be >grounds >for a table reorg/recluster. > >Vinod Bhansali wrote: > > > Madison, > > > > >had to be scanned. In 7.x+ the extent table is dynamically sized so >that > > >all of the extent information is kept in memory. > > > > So do you mean to say that having more extents in no more a serious >issue? > > What is the max no. of extents for a table in 7.x? > > > > Thanks > > Vinod Bhansali > > > > >From: Madison Pruet <mpruet@informix.com> > > >Reply-To: madison.pruet@informix.com > > >To: informix-list@iiug.org > > >Subject: Re: IDS 7.30: a table with 150 extents > > >Date: Fri, 16 Jun 2000 00:43:22 -0500 > > > > > >Alex Barilo wrote: > > > > > > > Hi folks, > > > > > > > > I have this table: 4 million rows (at one point it had 17 mill), >700Mb > > > > and 151 (!) extents! It's not fragmented. I wasn't the one who >designed > > > > it that way so please don't nail me for that. > > > > > > > > My question is: how come we don't see any performance problems? Is >there > > > > optimal extents number for IDS 7.30 (like 8 for online 5)? Or is it >just > > > > powerful box? > > > > > >The main reason for the 8 rule in online 5 was that the extent table >was > > >fixed at 8 entries. For any table larger than 8 extents the partition >page > > >had to be scanned. In 7.x+ the extent table is dynamically sized so >that > > >all of the extent information is kept in memory. > > > > > > > > > > > > > > > > > > > > > Hardware: HP NetServer LH4, 4x PII Xeon 400Mhz, 2Gb RAM, 6 logical >HDD's > > > > (12 physical, RAID 10) for dbspaces > > > > > > > > OS: SCO OS 5.0.5 > > > > > > > > Any ideas will be greately appreciated. > > > > > > > > Thanx! > > > > > > > > Alex. > > > > -- > > > > Before the accident, I could not even spell UNIX > > > > > > > > Sent via Deja.com http://www.deja.com/ > > > > Before you buy. > > > > > >-- > > >Madison Pruet > > > > > >=========================================== > > >Enterprise Replication Product Developement > > >Dallas, Texas > > >Informix Software > > >=========================================== > > > > > > > > > > ________________________________________________________________________ > > Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com > ________________________________________________________________________ Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com
Vinod Bhansali wrote in message <8ibmct$lnb$1@news.xmission.com>... > >Madison, > >If I have a plan to unload, drop and reload a table to reduce the no. of >extents, is it a good idea to stop logging, do unload, recreate table, load >data and then start logging back and take an fake archive to /dev/null? You might find it better to re-create the table without indexes, then re-add these after the load. Better still, consider changing the next extent size, then use ALTER FRAFMENT .... INIT IN construct.
Probably - Just remember that if you stop logging on the database that you will not be able to recover that database until after you have taken a level-0 backup - a 'real' backup ;-) Vinod Bhansali wrote: > Madison, > > If I have a plan to unload, drop and reload a table to reduce the no. of > extents, is it a good idea to stop logging, do unload, recreate table, load > data and then start logging back and take an fake archive to /dev/null? > > Thanks for the reply > Vinod Bhansali > > >From: Madison Pruet <mpruet@home.com> > >To: Vinod Bhansali <iiug@hotmail.com> > >Subject: Re: IDS 7.30: a table with 150 extents > >Date: Thu, 15 Jun 2000 16:51:15 -0500 > > > >Don't get me wrong. It is still an issue because you can get into a > >situation > >where a new extent can not be allocate, simply because the extent > >information > >will not fit within the partition page. However, the performance impact is > >not > >nearly as great as it was in 5.x. > > > >The number of extents that can be allocated depend on the number of indexes > >that > >the table has and the page size. For 2K page systems, I'd become a bit > >concerned if the number of extents reached 150, however, I would not be too > >concerned with 40. On 5.x systems, anything above 8 extents would be > >grounds > >for a table reorg/recluster. > > > >Vinod Bhansali wrote: > > > > > Madison, > > > > > > >had to be scanned. In 7.x+ the extent table is dynamically sized so > >that > > > >all of the extent information is kept in memory. > > > > > > So do you mean to say that having more extents in no more a serious > >issue? > > > What is the max no. of extents for a table in 7.x? > > > > > > Thanks > > > Vinod Bhansali > > > > > > >From: Madison Pruet <mpruet@informix.com> > > > >Reply-To: madison.pruet@informix.com > > > >To: informix-list@iiug.org > > > >Subject: Re: IDS 7.30: a table with 150 extents > > > >Date: Fri, 16 Jun 2000 00:43:22 -0500 > > > > > > > >Alex Barilo wrote: > > > > > > > > > Hi folks, > > > > > > > > > > I have this table: 4 million rows (at one point it had 17 mill), > >700Mb > > > > > and 151 (!) extents! It's not fragmented. I wasn't the one who > >designed > > > > > it that way so please don't nail me for that. > > > > > > > > > > My question is: how come we don't see any performance problems? Is > >there > > > > > optimal extents number for IDS 7.30 (like 8 for online 5)? Or is it > >just > > > > > powerful box? > > > > > > > >The main reason for the 8 rule in online 5 was that the extent table > >was > > > >fixed at 8 entries. For any table larger than 8 extents the partition > >page > > > >had to be scanned. In 7.x+ the extent table is dynamically sized so > >that > > > >all of the extent information is kept in memory. > > > > > > > > > > > > > > > > > > > > > > > > > > > Hardware: HP NetServer LH4, 4x PII Xeon 400Mhz, 2Gb RAM, 6 logical > >HDD's > > > > > (12 physical, RAID 10) for dbspaces > > > > > > > > > > OS: SCO OS 5.0.5 > > > > > > > > > > Any ideas will be greately appreciated. > > > > > > > > > > Thanx! > > > > > > > > > > Alex. > > > > > -- > > > > > Before the accident, I could not even spell UNIX > > > > > > > > > > Sent via Deja.com http://www.deja.com/ > > > > > Before you buy. > > > > > > > >-- > > > >Madison Pruet > > > > > > > >=========================================== > > > >Enterprise Replication Product Developement > > > >Dallas, Texas > > > >Informix Software > > > >=========================================== > > > > > > > > > > > > > > ________________________________________________________________________ > > > Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com > > > > ________________________________________________________________________ > Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com