Maximum size of an informix table pages/rows ?
Rohit Laungani
extents ( extent and next extent size 2GB). I know the maximum
number of extents that informix allows is approx 200.
What kind of limit could the system have reached that we get a
-271/-136 error( No more chunks), every time we try to insert a record
???
There are over 200 empty chunks in the dbspace where this table
resides !
thanks
-Rohit
here are o/p's of some ifx commands:
uname -a
SunOS riptide 5.8 Generic sun4u sparc SUNW,Ultra-80
onstat -
Informix Dynamic Server 2000 Version 9.21.FC4 -- On-Line -- Up
00:59:23 -- 624640 Kbytes
oncheck -pt
TBLspace Report for ccailite:informix.stationarchive
Physical Address 23c3982
Creation date 05/19/2001 15:31:49
TBLspace Flags 802 Row Locking
TBLspace use 4 bit
bit-maps
Maximum row size 141
Number of special columns 0
Number of keys 1
Number of extents 17
Current serial value 1
First extent size 1048571
Next extent size 2097142
Number of pages allocated 16777215
Number of pages used 16777215
Number of data pages 15468694
Number of rows 201093022
Partition partnum 5246448
Partition lockid 5246448
Extents
Logical Page Physical Page Size
0 2600003 1048571
1048571 3c00003 1048571
2097142 6e00003 1048571
3145713 7700003 1048571
4194284 7800003 1048571
5242855 7a00003 1048571
6291426 7e00003 1048571
7339997 8000003 1048571
8388568 8800003 1048571
9437139 9800003 1048571
10485710 9e00003 1048571
11534281 a300003 1048571
12582852 a700003 1048571
13631423 ac00003 1048571
14679994 b300003 1048571
15728565 b700003 1048571
16777136 b9ed7a7 79
Murray Wood
The list of limits always came with 7, I dont think they are with 9 but this
is from my 7 release notes.
Maximum Capacity
Table-Level Parameters (based on 2K page size) per Table
-----------------------------------------------------------------
Data rows per fragment............................. 4,277,659,295
Data pages per fragment............................... 16,775,134
Data bytes per fragment........................... 33,818,671,136
(excludes Binary Large Objects, BLOBs; roughly double this
number for 4k page size)
Binary Large Object bytes.................................. 2**31
Row length................................................ 32,767
Number of columns............................................ 32K
Item 2 probably applies. There is more.
MW
Allen
little 83 million row table....
Are you doing any fragmentation?
AJ
Jack Parker
There is a 32GB limit per dbspace. Anything that size should long ago have
been fragmented over multiple dbspaces.
cheers
j.
----- Original Message -----
From: "Rohit Laungani" <rohit_l...@yahoo.com>
To: <inform...@iiug.org>
Sent: Wednesday, November 07, 2001 7:34 PM
Subject: Maximum size of an informix table pages/rows ?
Art S. Kagel
> I have this table which has approx 201 million rows and about 17
> extents ( extent and next extent size 2GB). I know the maximum
> number of extents that informix allows is approx 200.
>
> What kind of limit could the system have reached that we get a
> -271/-136 error( No more chunks), every time we try to insert a record
> ???
The limit on extents is actually that the extent list has to fit
on one page (the table's or fragment's tablespace tablespace page).
that page also holds information about special columns so the more
special columns (varchars, BLOBs, etc) you have the fewer extents
you can have in that table. Now that limit is not per table but
per fragment (for an unfragmented table that amounts to the same
thing of course) so you can expand the table by fragmenting it
across other dbspaces. The limits are 2^24 pages per fragment.
Now if for this table the tablespace tablespace page can only hold
17 extents then each extent must be almost 1000000 pages or an
entire 2GB chunk to be able to max out the storage in a single
dbspace or fragment! I'd say compress the data in that dbspace,
release some chunks to create one or two more dbspaces and fragment
the table across them.
Art S. Kagel
Jack Parker
Yes. But his situation is that they're all in one dbspace. Hence the 32GB
limitation takes precedence.
cheers
j.
Jack Parker
I'm sorry, that was strictly unnecessary. I'd already said my piece.
Simmons, Keith
Jack/Art
Please clarify. I currently have a table in a single dbspace with 18 x 2Gb
chunks, 13,911,240 rows, 26 extents (mostly 499,998 pages (2Kb offset and
4Kb page size)).
I'm not encountering problems with this table (at the moment !!) but am
obviously over the 32Gb limit mentioned by Jack.
Looking at Art's figures I estimate my system can handle 68 x 500,000 page
chunks (tblspace:tblspace page is 4Kb and only 500,000 pages per extent
max).
I suppose my fundamental question is what space limitation am I likely to
hit first on this table?
H/W IBM/Sequent NUMA-Q 1000, 8 way
OS ptx/Dynix v4.4.7
IDS 7.31 UC6
Many TIA
Keith Simmons
Banner Business Supplies Limited
Informix Certified Professional
cheers
j.
**********************************************************************************
This message may contain information which is confidential and subject to
legal privilege. If you are not the intended recipient, you may not peruse,
use, disseminate, distribute or copy this message. If you have received this
message in error, please notify the sender immediately by email, facsimile
or telephone and return and/or destroy all copies of the message.
This footnote also confirms that this email message has been swept for
the presence of computer viruses, however we cannot guarantee that this
message is free from such problems.
**********************************************************************************
Neil Truby
tablespace.
"Simmons, Keith" <keith....@bbslimited.co.uk> wrote in message
news:9sg6p8$rjb$1...@news.xmission.com...
Simmons, Keith
Neil,
Sorry, reasonable attempt, but all 18 multi-column (between 6 and 10 each !!
not my design I hasten to add !!) are attached (and can we get the downtime
to detached them !!!!).
Neil Truby
"Simmons, Keith" <keith....@bbslimited.co.uk> wrote in message
news:9sgdq9$in$1...@news.xmission.com...
ART KAGEL, BLOOMBERG/ NEW YORK
Naw, you are correct. I was too busy to look at the extent list and do the
calculations. He was indeed hitting the 32GB limit (as implied by the 16
huge extents and the tiny one to fill out the 2^24 pages). I think my
suggestion to break up the one massive dbspace into smaller ones and
fragment the table still holds though.
Art
----- Original Message -----
From: Jack Parker <jpa...@artentech.com>
At: 11/ 8 19:18
> I'm sorry, that was strictly unnecessary. I'd already said my piece.
>
> cheers
> j.
>
>
> ----- Original Message -----
Jack Parker
1 depends on your engine. Look at your limits section of the release notes.
For 9.2 I see 33,818,671,136 bytes / fragment. 4,277,659,295 rows,
16,775,134 pages
Art and I are talking about two separate unrelated yet related things.
1 - There is a 32GB limit for indices/dbspace. I was under the impression
that the 'fragment' mentioned in the server notes equated to a dbspace - I
could be out to lunch. If you have hit the limit, you will get an error
when trying to insert a new row. That's what I was talking about.
2 - If you do an oncheck -pt on your database/table you will see (like
Rajesh posted yesterday) the partition header for the table. Every dbspace
has a tablespace tablespace which has one or more extents. (Starts with 50
pages and then appends new extents - this will be increased in 9.4 I seem to
remember). Each page describes one table. The header of this page is some
descriptive stuff about the table which is of variable length depending on
whether you are using extended data types, NLS etc. After that you get a
list of extent/lengths for the table. The issue is that you only get one
page per table, if you need more extents than you can fit into that list on
that page - you get an error. The number of entries you can have in that
list depends therefore on the size of your page and the complexity of your
table. For a 2k page with a simple table I believe the number is in the 170
extents range. If you need an exact number I can work it out, but you can
just dump the page in question and see how much free space you have in it.
This is what Art was talking about.
However, if you have a single dbspace of that size you could get some
performance improvement by fragmenting the table across more dbspaces.
cheers
j.
Rohit Laungani
-Rohit
Allen <all...@ndr.com> wrote in message news:<3BEAB10C...@ndr.com>...
Simmons, Keith
Neil
As requested:-
TBLspace Report for live:informix.sales_anal
Physical Address 2400005
Creation date 12/12/1998 18:53:18
TBLspace Flags 2 Row Locking
Maximum row size 1082
Number of special columns 0
Number of keys 18
Number of extents 26
Current serial value 1
First extent size 275000
Next extent size 75000
Number of pages allocated 8414802
Number of pages used 8368262
Number of data pages 4642003
Number of rows 13926007
Partition partnum 14680066
Partition lockid 14680066
Extents
Logical Page Physical Page Size
0 2402717 462500
462500 2500003 487500
950000 2600003 487500
1437500 2700003 487500
1925000 3200003 487500
2412500 3300003 450000
2862500 2c00003 225000
3087500 3700003 487500
3575000 24735bb 27461
3602461 2c36eeb 24997
3627458 327704f 12497
3639955 377704f 12497
3652452 257704f 12465
3664917 267704f 12465
3677382 277704f 12465
3689847 5a00003 487500
4177347 5b00003 499995
4677342 6000003 499995
5177337 6600003 499995
5677332 6a00003 499995
6177327 1c00003 499995
6677322 5a7704f 12495
6689817 7000003 499995
7189812 7600003 499995
7689807 7d00003 499995
8189802 8000003 225000
Keith Simmons
Banner Business Supplies Limited
Informix Certified Professional
-----Original Message-----
From: Neil Truby [mailto:neil....@ardenta.co.uk]
Sent: Friday, November 09, 2001 12:54 PM
To: inform...@iiug.org
Subject: Re: Maximum size of an informix table pages/rows ?
Can you post the oncheck -pt?
"Simmons, Keith" <keith....@bbslimited.co.uk> wrote in message
news:9sgdq9$in$1...@news.xmission.com...
>
> Neil,
> Sorry, reasonable attempt, but all 18 multi-column (between 6 and 10 each
!!
> not my design I hasten to add !!) are attached (and can we get the
downtime
> to detached them !!!!).
>
> Keith Simmons
> Banner Business Supplies Limited
> Informix Certified Professional
>
>
> -----Original Message-----
> From: Neil Truby [mailto:neil....@ardenta.co.uk]
> Sent: Friday, November 09, 2001 9:39 AM
> To: inform...@iiug.org
> Subject: Re: Maximum size of an informix table pages/rows ?
>
> Perhaps your indexes are detatched? If so, they are in a separate
> tablespace.
>
> "Simmons, Keith" <keith....@bbslimited.co.uk> wrote in message
> news:9sg6p8$rjb$1...@news.xmission.com...
> >
<snip>
> >
> > > Rohit Laungani wrote:
<snip>
**********************************************************************************
Rohit Laungani
what does oncheck -pt on the table look like on your system ?
Per Art/MW, my table (fragment) filled up the max pages allowed per
dbspace.
>Number of pages allocated 16777215
>Number of pages used 16777215
its a 365day-24/7 & 1 dbspace system and hence could not compress and
fragment the table, hence had to rename the table and re-create the
view(4 tables now). The union view now has approx 600 mil rows and i
wonder what the limits are on the union view ?
Also, what are the performance implications on using the union view vs
a fragmented table.
cheers
-Rohit
"Simmons, Keith" <keith....@bbslimited.co.uk> wrote in message news:<9sgdq9$in$1...@news.xmission.com>...
Neil Truby
I didn't read your post properly.
It's not a 32Gb limit, it's a 16G page limit/tablespace. So, if you've 4k
pages, you're OK to 72GBytes.
Neil Truby
Ardenta Limited
"Simmons, Keith" <keith....@bbslimited.co.uk> wrote in message
news:9sgvcp$5bd$1...@news.xmission.com...
DJW
"Art S. Kagel" <ka...@bloomberg.net> wrote in message
news:3BEAE3FD...@bloomberg.net...
> The limit on extents is actually that the extent list has to fit
> on one page (the table's or fragment's tablespace tablespace page).
Any ideas on plans to remove this limit?
Can we request it?
> that page also holds information about special columns so the more
> special columns (varchars, BLOBs, etc) you have the fewer extents
> you can have in that table. Now that limit is not per table but
> per fragment (for an unfragmented table that amounts to the same
> thing of course) so you can expand the table by fragmenting it
> across other dbspaces. The limits are 2^24 pages per fragment.
> Art S. Kagel
>
Jack Parker
If you're running up against that limit, then it's way past time to reorg.
186,287.49 miles/second. It's not just a good idea, it's the law. In other
words don't try to remove the guard rails from a mountain highway. Sure
you'd be able to move further to the passenger side, but you might not like
the driving there.
cheers
j.
----- Original Message -----
From: "DJW" <d...@smooth1.fsnet.co.uk>
To: <inform...@iiug.org>
Sent: Friday, November 09, 2001 7:07 PM
Subject: Re: Maximum size of an informix table pages/rows ?
>
DJW
"Jack Parker" <jpa...@artentech.com> wrote in message
news:9sk6vs$56r$1...@news.xmission.com...
>
>
> If you're running up against that limit, then it's way past time to reorg.
> 186,287.49 miles/second. It's not just a good idea, it's the law. In
other
> words don't try to remove the guard rails from a mountain highway. Sure
> you'd be able to move further to the passenger side, but you might not
like
> the driving there.
>
> cheers
> j.
>
We've had customer hit the limit and have to take down mission critical
system to reorg. They'd rather reorg when THEY want to rather than
be forced to. My work collegues complain Informix is crap - Ingres does
not have this problem!
Jack Parker
"Crap" is a rather severe word. I'm sure that Ingres had its limitations
and features as well.
cheers
j.
Art S. Kagel
> Jack/Art
You're on IBM. Informix uses a 4K pagesize on AIX due to the
inefficiencies of smaller pages on AIX (or greater efficiency
of larger ones?). Anyway, the limit is 2^24 pages which is
32GB on a 2K system or 64GB on a 4K pagesize system like yours.
Art S. Kagel