Can only allocate 4 extents
A user on IDS 11.10.UC2 (Linux) hit "no more extents" when inserting into a huge table (~212M rows) that had only 4 extents but ~16,777,215 2K pages allocated and used, despite plenty of free chunk space. Replies explained this is the per-table limit of roughly 16.77 million addressable pages in a single tablespace, not an extent-count problem. Suggested fixes: fragment the table (round-robin or by expression, in one or several dbspaces), detach indexes so their pages can be used for data, and/or use a larger page size (e.g. 16K dbspace with its own buffer pool) to fit more rows per page. Release notes section "Limits in IBM Informix Dynamic Server" was cited for confirmation; the poster did not report back.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Informix 11.10.UC2 running on linux is reporting an error "Could not
insert new row into the table. ISAM error: no more extents". onstat
-T shows that for this table I've allocated 4 extents; 212,227,546
rows; 16,777,215 pages have been allocated (each page is 2K);
16,777,215 pages have been used.
The table has both an extent size and a next extent size of 10,485,760
K. onstat -d shows we have 8 free chunks each of 10,485,760 K.
I don't understand why there are no more extents.
Thanks for any help.
Steve Kurlander
On 12/03/2008, skurlander@yahoo.com <skurlander@yahoo.com> wrote:
> Informix 11.10.UC2 running on linux is reporting an error "Could not
> insert new row into the table. ISAM error: no more extents". onstat
> -T shows that for this table I've allocated 4 extents; 212,227,546
> rows; 16,777,215 pages have been allocated (each page is 2K);
> 16,777,215 pages have been used.
>
> The table has both an extent size and a next extent size of 10,485,760
> K. onstat -d shows we have 8 free chunks each of 10,485,760 K.
>
> I don't understand why there are no more extents.
>
> Thanks for any help.
>
> Steve Kurlander
> _______________________________________________
> Informix-list mailing list
> Informix-list@iiug.org
> http://www.iiug.org/mailman/listinfo/informix-list
>
Steve
There is an absolute limit of 16,775,134 addressable pages in a table
space (even though the engine may allocate a few more !). Two
(possibley three) ways around this. Are you indexes 'in table' if so
create them explicitly detached which will allow the pages they are
occupying to be used for data (probably short term and not over
effective). Second your could create the table as fragmented (either
in the same dbspace or different dbspaces). Fragmentation could be by
round robin or (better) by some expression. Finally you could take
advantage of the variable page size feature and create your dbspace
with (say) 16k pages. You need to have two buffer pools set up but you
could dramatically increase the number of records you can hold in the
same area of disk.
I have a record of 1082 bytes on a 2k page system, thus 1 record per
page :-(( If I used 16k pages (futre intention but not quite there
yet) I could hold 15 records on a page but in the same physical space
that could only hold 8 records previously.
The 'correct' option for you depends very much on your particular crcumstance.
Keith
Hi,
try to find section "1.22 Limits in IBM Informix Dynamic Server"
in the release notes file "ids_unix_relnotes_11.10.txt" in
directory "$INFORMIXDIR/release/en_us/0333".
I think there you will find the answer to your question.
To solve your problem I think you need to fragment the table.
Regards,
Martin
--
Martin Fuerderer
IBM Informix Development Munich, Germany
Information Management
IBM Deutschland Entwicklung GmbH
Chairman of the Supervisory Board: Martin Jetter
Board of Management: Herbert Kircher
Corporate Seat: Boeblingen, Germany
Reg.-Gericht: Amtsgericht Stuttgart, HRB 243294
informix-list-bounces@iiug.org wrote on 12.03.2008 09:39:46:
> Informix 11.10.UC2 running on linux is reporting an error "Could not
> insert new row into the table. ISAM error: no more extents". onstat
> -T shows that for this table I've allocated 4 extents; 212,227,546
> rows; 16,777,215 pages have been allocated (each page is 2K);
> 16,777,215 pages have been used.
>
> The table has both an extent size and a next extent size of 10,485,760
> K. onstat -d shows we have 8 free chunks each of 10,485,760 K.
>
> I don't understand why there are no more extents.
>
> Thanks for any help.
>
> Steve Kurlander
> _______________________________________________
> Informix-list mailing list
> Informix-list@iiug.org
> http://www.iiug.org/mailman/listinfo/informix-list