Re: Increased extent size, but still hit max extents

On 12/5/05, Neil Truby <neil.truby@ardenta.com> wrote: > "HarryH" <cbullivant@orange.net.au> wrote in message > news:1133821796.746885.14550@g49g2000cwa.googlegroups.com... > > <Note: Thought I'd submitted this 24 hours ago but can't find it today. > > Apologies if it turns up twice> > > > > Quite large table (32M rows, 2.8G), hit max extents. Unloaded the > > table, dropped it, rebuilt it, reloaded data, rebuilt indexes. Here is > > the current dbschema output: > > { TABLE <tabname> row size = 75 number of columns = 9 index size > > = 39 } > > create table <tabname> > > ( [...] > > ) extent size 204800000 next size 20480000 lock mode page; > > I read this as a first extent of 200M, and then 20M per extent after > > that. Extent sizes are expressed in KB. I read that as an initial extent size of 200 GB, increasing by 20 GB. One day, you'll be able to use K, M, G, T as multipliers - hence 200M. > > Now I believe that Informix doubles extent size every 8 (16?) > > extents but even if every extent is 20M I still only count 130-140 > > extents needed, but instead it's hit 225 As Neil said, you've probably got internal fragmentation issues. IDS probably can't allocate 200 GB, so it grabs what it can for the initial extent; likewise, it probably can't grab 20GB for the next extents, so it grabs what it can. > > When I isssue this query: > > select start, size > > from sysextents > > where tabname = '<tabname>' > > and dbsname='<dbname>' > > order by 2> > > > I get: > > > > 8120747 215 > > 10698730 222 > > 19347744 223 > > 8133256 224 > > 8146980 224 > > 7848583 224 > > 11485330 226 > > etc........ > > > > How am I getting extents of 400K, when the minimum should be 20M? I > > just checked the status and it was created on 12/2/05, so I haven't > > accidentally left the old one lying around. Any ideas anyone? Anything > > I should check? > > Are there in fact any larger contiguous extents free in the dbspace (is it > in the root dbspace btw?)? I'd look at the oncheck report and see what's going on, but the chances are you've got lots of interleaved small extents and no big free extents. As near as you can manage it, you need to vacate the dbspace (eg drop and rebuild), preserving the relevant data. This is one place where ON-Unload and ON-Load might conceivably help - but test first. Otherwise, do it the old-fashioned way - unload and drop the tables. Note that doing it one table at a time doesn't help. -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2005.02 -- http://dbi.perl.org/ sending to informix-list

Some facts to remember about extent sizing: 1. what is requested in the CREATE TABLE is a suggestion, not a demand. The engine will give you the largest contiguous set of pages it can find in a chunk in the dbspace for that structure, up to your request, rounded to a page size. 2. max number of extents is determined by the size of the partition page, ie: the page in the tablespace tablespace for that structure. There are 5 slots on that page - the 5th slot keeps track of the extent list. Whatever room is available there for the extent list is how many extents you get. The size of each extent list entry depends on whether large chunk support is on or not. So it is very possible to hit "max extents" for a structure "early" due to many very small extents. Keep in mind also that if you unload a structure and reload it to the same dbspace - even with a larger extent size request (large EXTENT SIZE) - the algorithm for extent allocation is still the same, and you could put your pages right back from where they came, all things equal. HTH - Mark Scranton www.markscranton.com Jonathan Leffler wrote: > On 12/5/05, Neil Truby <neil.truby@ardenta.com> wrote: > > "HarryH" <cbullivant@orange.net.au> wrote in message > > news:1133821796.746885.14550@g49g2000cwa.googlegroups.com... > > > <Note: Thought I'd submitted this 24 hours ago but can't find it today. > > > Apologies if it turns up twice> > > > > > > Quite large table (32M rows, 2.8G), hit max extents. Unloaded the > > > table, dropped it, rebuilt it, reloaded data, rebuilt indexes. Here is > > > the current dbschema output: > > > { TABLE <tabname> row size = 75 number of columns = 9 index size > > > = 39 } > > > create table <tabname> > > > ( > [...] > > > ) extent size 204800000 next size 20480000 lock mode page; > > > > I read this as a first extent of 200M, and then 20M per extent after > > > that. > > Extent sizes are expressed in KB. I read that as an initial extent > size of 200 GB, increasing by 20 GB. One day, you'll be able to use > K, M, G, T as multipliers - hence 200M. > > > > Now I believe that Informix doubles extent size every 8 (16?) > > > extents but even if every extent is 20M I still only count 130-140 > > > extents needed, but instead it's hit 225 > > As Neil said, you've probably got internal fragmentation issues. IDS > probably can't allocate 200 GB, so it grabs what it can for the > initial extent; likewise, it probably can't grab 20GB for the next > extents, so it grabs what it can. > > > > When I isssue this query: > > > select start, size > > > from sysextents > > > where tabname = '<tabname>' > > > and dbsname='<dbname>' > > > order by 2> > > > > > I get: > > > > > > 8120747 215 > > > 10698730 222 > > > 19347744 223 > > > 8133256 224 > > > 8146980 224 > > > 7848583 224 > > > 11485330 226 > > > etc........ > > > > > > How am I getting extents of 400K, when the minimum should be 20M? I > > > just checked the status and it was created on 12/2/05, so I haven't > > > accidentally left the old one lying around. Any ideas anyone? Anything > > > I should check? > > > > Are there in fact any larger contiguous extents free in the dbspace (is it > > in the root dbspace btw?)? > > > I'd look at the oncheck report and see what's going on, but the > chances are you've got lots of interleaved small extents and no big > free extents. As near as you can manage it, you need to vacate the > dbspace (eg drop and rebuild), preserving the relevant data. This is > one place where ON-Unload and ON-Load might conceivably help - but > test first. Otherwise, do it the old-fashioned way - unload and drop > the tables. Note that doing it one table at a time doesn't help. > > > -- > Jonathan Leffler #include <disclaimer.h> > Email: jleffler@earthlink.net, jleffler@us.ibm.com > Guardian of DBD::Informix v2005.02 -- http://dbi.perl.org/ > sending to informix-list