Increased extent size, but still hit max extents
A 2.8 GB table kept hitting max extents even after being rebuilt with "extent size 204800000 next size 20480000", yet sysextents showed many tiny ~224-page extents. Replies explained two things: the extent values are in KB, so the poster had actually asked for ~200 GB first/~20 GB next extents (values that wrap in 32 bits, giving unpredictable results), and Informix will fall back to smaller chunks of free space when no contiguous area of the requested size exists. Advice: run oncheck -pe to see fragmented free space, unload/drop and rebuild the dbspace contents with sensible extent sizes, or move the table to a new dbspace. Poster accepted the explanation.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
<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>
(
phone varchar(10) not null ,
upd_year_month char(7) not null ,
call_type varchar(20),
rate_period integer,
call_count integer,
seconds integer,
airtime_revenue decimal(13,2),
toll_revenue decimal(13,2),
tax_charge decimal(13,2)
) extent size 204800000 next size 20480000 lock mode page;
revoke all on <tabname> from "public";
create index ix11099_1 on <tabname> (phone);
create index ix11099_2 on <tabname> (upd_year_month);
I read this as a first extent of 200M, and then 20M per extent after
that. 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
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?
The app guys got around it by deleting some of the oldest data and then
loading the new. But the next data load is going to have a problem.
I'll probably re-build it again this weekend, but I'd like to know
what's happening here before I do this.
Thanks in advance for any help.
"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>
> (
> phone varchar(10) not null ,
> upd_year_month char(7) not null ,
> call_type varchar(20),
> rate_period integer,
> call_count integer,
> seconds integer,
> airtime_revenue decimal(13,2),
> toll_revenue decimal(13,2),
> tax_charge decimal(13,2)
> ) extent size 204800000 next size 20480000 lock mode page;
> revoke all on <tabname> from "public";>
> create index ix11099_1 on <tabname> (phone);
> create index ix11099_2 on <tabname> (upd_year_month);>
> I read this as a first extent of 200M, and then 20M per extent after
> that. 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
>
> 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?)?
The table is in a DBSPACE of related tables. Other "schemas" are in their own dbspaces, and each dbspace has its own group of chunks. Can I infer from your question that if Informix can't find a 20M contigous area, it will just choose the next larger? Which would imply that I need to rearrange not just this table but other tables within the same dbspace?
Yes.
Once you have the table dropped run oncheck -pe and check where the
free space in
dbspace. You will probably find lots of small free space areas.
You will probably have to unload and drop everything in the dbspace.
Then rebuild everything with the correct extent sizes.
You might want to consider creating a new dbspace and reloading the
table into that for now.
You can then rebuild the rest of the stuff in the problem dbspace
later.
Extent sizing is very important in Informix.
HarryH wrote:
> <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>
> (
> phone varchar(10) not null ,
> upd_year_month char(7) not null ,
> call_type varchar(20),
> rate_period integer,
> call_count integer,
> seconds integer,
> airtime_revenue decimal(13,2),
> toll_revenue decimal(13,2),
> tax_charge decimal(13,2)
> ) extent size 204800000 next size 20480000 lock mode page;
As others have pointed out these are in KB so you're requesting a first
extent of just shy of 200GB and a next extent size of just under 20GB.
Since both of these values wrap in 32bits I would not even venture to guess
what the engine actually saw as the extent sizes.
Art S. Kagel
<SNIP>
Thanks everyone for your replies. It's a lot clearer to me now what I have to do.