"no more extents" error
A user loading ~50GB into one table on IDS 7.31 (HP-UX) hit "271: Could not insert new row / 136: ISAM error: no more extents" at about 33GB, despite free space in the dbspace and only 17 extents allocated. Replies explained that 7.x caps an extent at 2GB (or the chunk size), and more importantly that a single partition (table/index fragment) cannot exceed 2^24 pages, i.e. 32GB with 2K pages. The recommended fix was to fragment the table across several dbspaces (round robin or by expression, via CREATE TABLE/ALTER FRAGMENT); smaller NEXT SIZE values and the Performance Guide's formula for the maximum extent count were also noted.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Our process is uploading data to a table. Expected data is around 50
GB. First extent was set to 40GB and next extent to 4 GB. However the process
stops after uploading around 33 GB of data. The error message is as follows:
271: Could not insert new row into the table.
136: ISAM error: no more extentsThis error message implies that the number of extents allocated for this table
has been exhausted and no more extents can be allocated to this table. Our
analysis shows that overall 220 extents can be allocated to this table and 17
extents have been allocated to this table till the error message. The dbspace
has chunks of size 2GB. And the initial extent allocated is of size 2GB and
next extent also is of size 2GB irrespective of the limits we have set
earlier(40GB and 4GB). This is understandable as the max size of an extent is
limited by the chunk size. However, what baffles us is the no more extents
being allocated after 17 eventhough there are free chunks in the dbspace. Any
pointers on what could be happening?
We are using Informix 7.31 on HP-UX 11i.
Hello,
Maximum extent size is 2 GB. Informix setting next
extent as 2 GB. When informix adds 16 extents then at
the 17th time it doubles the size of next extent. In
your case system is trying to add 18th extent of size
4 GB. But it is not possible to create extent of 4 GB.
Soution for this issue:
You can have initial extent of 2 GB and next extent of
500 MB. In this way mamimum size of table can be.
2 GB + 500MB * 16+ 1GB*16+2GB*16 (58 GB). If you
expect your table to grow more than 48 GB then you can
set next extent as 250 MB or may be less.....
I hope you are clear on this.
If you need any more information let me know please.
Thanks,
Shivaji Nimase
Cell: +91-9850085581
--- DALJIT SINGH <daljit_reck@yahoo.com> wrote:
> Our process is uploading data to a table. Expected
> data is around 50 GB. First extent was set to 40GB
> and next extent to 4 GB. However the process stops
> after uploading around 33 GB of data. The error
> message is as follows:
> 271: Could not insert new row into the table.>
> 136: ISAM error: no more extents> This error message implies that the number of
> extents allocated for this table has been exhausted
> and no more extents can be allocated to this table.
> Our analysis shows that overall 220 extents can be
> allocated to this table and 17 extents have been
> allocated to this table till the error message. The
> dbspace has chunks of size 2GB. And the initial
> extent allocated is of size 2GB and next extent also
> is of size 2GB irrespective of the limits we have
> set earlier(40GB and 4GB). This is understandable as
> the max size of an extent is limited by the chunk
> size. However, what baffles us is the no more
> extents being allocated after 17 eventhough there
> are free chunks in the dbspace. Any pointers on what
> could be happening?
>
> We are using Informix 7.31 on HP-UX 11i.
>
>
__________________________________
Do you Yahoo!?
Read only the mail you want - Yahoo! Mail SpamGuard.
http://promotions.yahoo.com/new_mail
There exists
a limit of 32 GB for each table fragment. That means
if you don't use a fragmented table the table size is limited to 32 GB.
If you detach the index 32 GB is the limit for the table data and for
each detached index. If you don't have detached indices the limit is
for the sum of table data pages and index pages.
The error message is the same - the database server cannot allocate a
new extent for that table (because it has reached its maximum size).
The only solution: Create your table as fragmented table (either by
expression or round robin - see the SQL Syntax Guide).
Regards,
Andreas Kutsche
> -----Ursprüngliche Nachricht-----
> Von: forum.subscriber@iiug.org [mailto:forum.subscriber@iiug.org]Im
> Auftrag von DALJIT SINGH
> Gesendet: Donnerstag, 30. Dezember 2004 06:58
> An: ids@iiug.org
> Betreff: "no more extents" error [3937]
>
>
> Our process is uploading data to a table. Expected data is
> around 50 GB. First extent was set to 40GB and next extent
> to 4 GB. However the process stops after uploading around 33
> GB of data. The error message is as follows:
> 271: Could not insert new row into the table.
> 136: ISAM error: no more extents> This error message implies that the number of extents
> allocated for this table has been exhausted and no more
> extents can be allocated to this table. Our analysis shows
> that overall 220 extents can be allocated to this table and
> 17 extents have been allocated to this table till the error
> message. The dbspace has chunks of size 2GB. And the initial
> extent allocated is of size 2GB and next extent also is of
> size 2GB irrespective of the limits we have set earlier(40GB
> and 4GB). This is understandable as the max size of an extent
> is limited by the chunk size. However, what baffles us is the
> no more extents being allocated after 17 eventhough there are
> free chunks in the dbspace. Any pointers on what could be happening?
>
> We are using Informix 7.31 on HP-UX 11i.
>
>
DALJIT SINGH said:
> Our process is uploading data to a table. Expected data is around 50 GB.
> First extent was set to 40GB and next extent to 4 GB. However the process
> stops after uploading around 33 GB of data. The error message is as
> follows:
> 271: Could not insert new row into the table.
> 136: ISAM error: no more extents> This error message implies that the number of extents allocated for this
> table has been exhausted and no more extents can be allocated to this
> table. Our analysis shows that overall 220 extents can be allocated to
> this table and 17 extents have been allocated to this table till the error
> message. The dbspace has chunks of size 2GB. And the initial extent
> allocated is of size 2GB and next extent also is of size 2GB irrespective
> of the limits we have set earlier(40GB and 4GB). This is understandable as
> the max size of an extent is limited by the chunk size. However, what
> baffles us is the no more extents being allocated after 17 eventhough
> there are free chunks in the dbspace. Any pointers on what could be
> happening?
>
> We are using Informix 7.31 on HP-UX 11i.
Errrrr.... how can you allocate a 4GB chunk on a database platform that
doesn't support it? I suspect that it's trying to double the NEXT SIZE and
failing, giving a misleading error message. Try setting EXTENT SIZE to a
whisker under 2GB and NEXT SIZE to the same.
Or upgrade to 9.40.
--
Bye now,
Obnoxio
"C'est pas parce qu'on n'a rien à dire qu'il faut fermer sa gueule"
- Coluche
"I'm trying to see things your way, but I can't get my head up my ass"
- JCH
"Ogni uomo mi guarda come se fossi una testa di cazzo"
- Marco
I went to the airport to check in and they asked what I did because I
looked like a terrorist. I said I was a comedian. They said, "Say
something funny then." I told them I had just graduated from flying
school.
-- Ahmed Ahmed
You can partition the table with round robin
setting first and next extend of 2G
-----Original Message-----
From: forum.subscriber@iiug.org [mailto:forum.subscriber@iiug.org]On
Behalf Of DALJIT SINGH
Sent: Thursday, December 30, 2004 7:58 AM
To: ids@iiug.org
Subject: "no more extents" error [3937]
Our process is uploading data to a table. Expected data is around 50 GB. First
extent was set to 40GB and next extent to 4 GB. However the process stops
after uploading around 33 GB of data. The error message is as follows:
271: Could not insert new row into the table.
136: ISAM error: no more extentsThis error message implies that the number of extents allocated for this table
has been exhausted and no more extents can be allocated to this table. Our
analysis shows that overall 220 extents can be allocated to this table and 17
extents have been allocated to this table till the error message. The dbspace
has chunks of size 2GB. And the initial extent allocated is of size 2GB and
next extent also is of size 2GB irrespective of the limits we have set
earlier(40GB and 4GB). This is understandable as the max size of an extent is
limited by the chunk size. However, what baffles us is the no more extents
being allocated after 17 eventhough there are free chunks in the dbspace. Any
pointers on what could be happening?
We are using Informix 7.31 on HP-UX 11i.
***********************************************
This Mail Was Scanned By Mail-seCure System in
Matrix Herzeliya
***********************************************
The answer is you're looking in the wrong place. First IDS 7.xx will not
allocate extents larger than 2GB (in truth no larger than the chunk in which
they are placed if smaller) but that's not the real problem, but just a
distraction. The problem is that a single partition (ie table, index, table
fragment, index fragment) cannot exceed 16,777,216 (2^24) pages on most
platforms, including HPUX, IDS's page size is 2K so the maximum partition size
is 32GB. In order to have a table larger than 2^24 pages you have to fragment
the table across 2 or more dbspaces. Check out the FRAGMENT BY clause of the
CREATE TABLE and ALTER FRAGMENT statements (the latter is used to fragment orrefragment an existing table) in the Guide to SQL Syntax manual.
Art S. Kagel
----- Original Message -----
From: Daljit Singh <daljit_reck@yahoo.com>
At: 12/30 1:11
> Our process is uploading data to a table. Expected data is around 50 GB.
First
> extent was set to 40GB and next extent to 4 GB. However the process stops
after
> uploading around 33 GB of data. The error message is as follows:
> 271: Could not insert new row into the table.
> 136: ISAM error: no more extents> This error message implies that the number of extents allocated for this
table
> has been exhausted and no more extents can be allocated to this table. Our
> analysis shows that overall 220 extents can be allocated to this table and 17
> extents have been allocated to this table till the error message. The dbspace
> has chunks of size 2GB. And the initial extent allocated is of size 2GB and
next
> extent also is of size 2GB irrespective of the limits we have set
earlier(40GB
> and 4GB). This is understandable as the max size of an extent is limited by
the
> chunk size. However, what baffles us is the no more extents being allocated
> after 17 eventhough there are free chunks in the dbspace. Any pointers on
what
> could be happening?
>
> We are using Informix 7.31 on HP-UX 11i.
You
have exceeded the maximum number of extents
allowed for that table.
As I recall, there is am absolute limit on the number
of extents a table can have, determined by the fact
that extent info is held on a single page which
records all info on a table. Slot 5 rings a bell?
The effect is that this limit varies with table name
length, number of columns, number of indexes, number
of constraints etc. and tends to be in the 180 to 220
extent region but can vary wildly for tables with many
columns, many indexes and so on.
How many extents does the table have - do oncheck -pt
<dbname>:<tabname> and add up "Number of extents" for
the table and indexes.
I'm sure I saw the calculation somewhere ....ah yes,
in the Performance Guide for 7.31, it's on page 4-32
as follows:
=== EXTRACT FOLLOWS ===
Upper Limit on Extents
Do not allow a table to acquire a large number of
extents because an upper
limit exists on the number of extents allowed. Trying
to add an extent after
the limit is reached causes error -136 (No more
extents) to follow an INSERT
request.
The upper limit on extents depends on the page size
and the table definition.
To learn the upper limit on extents for a particular
table, use the following set
of formulas:
vcspace = 8 *vcolumns + 136
tcspace = 4 *tcolumns
ixspace = 12 *indexes
ixparts = 4 *icolumns
extspace = pagesize (vcspace + tcspace + ixspace +
ixparts + 84)
maxextents = extspace/8
The table can have no more than maxextents extents.
vcolumns is the number of columns that contain BYTE or
TEXT data and
VARCHAR data.
tcolumns is the number of columns in the table.
indexes is the number of indexes on the table.
icolumns is the number of columns named in those
indexes.
pagesize is the size of a page reported by oncheck
-pr.
=== EXTRACT ENDS ===
Hope this helps
Malc