Table size limits

George Wang  wrote:
> In ingres 6.4/06 on HP/UNIX, Ingres has table size limit at about 8,000,000
> pages. Dose anyone know what will happen after the table size reach the
> limit? Ingres will give error or overwrite the date in existed pages? I can
> see the tid is negative value after table over about 4 million pages.

George,

The maximum number of pages in a table is the same for
Ingres 6, OpenIngres (1.x/2.0) and Ingres II.  It is 2^23 pages
(8,388,608) and is limited by the TID design.  I have never seen
a table that large but would expect one of two possible 
outcomes:  Ingres would detect the "overflow" condition and
generate an error (the preferred way to handle this, IMO); 
The page numbers would "roll over", overwriting existing data
pages (definitely a bad thing).  

You could ask Tech Support about this.  They could take
a look at the source code and let you know what is supposed
to happen.

If you have the time and disk space I would recommend 
running a test (regardless of what TS tells you) and then let 
us know what happens.  Make a copy of your 4 million page
table and modify it with a very low fillfactor.  That should 
create the condition that you need.

Chip Nickolett           ChipN@Comp-Soln.com
Comprehensive Consulting Solutions, Inc.   (www.Comp-Soln.com)
Phone:  262-544-9954     Fax:  262-544-1236




From: "BARKER, Tim" 
To: yuan@cs.mu.oz.au
Subject: Update for your "Table size limits" document
Date: Mon, 17 Apr 2000 13:11:13 +0100

Hi William,

I recently read your page on "Table size limits", where there is speculation
about what would happen if the magic 8,388,606 page boundary was exceeded.
Well a customer site recently hit this limit and it wasn't pretty.  Here are
the details:

A customer site recently hit the magic 8,388,606 page limit, much to their
(and my) dismay.  They're running Ingres 6.4/05 on OpenVMS.  I should also
mention that the table in this case was HASH, with a single integer4
key-column and no secondary indexes..  Fortunately, Ingres does not wrap
around the file and attempt to write over existing information.  Instead the
following line appears in the errlog.log file:

      E_DMF010_DM1R_NO_PAGE     Expected page not given.

The DBMS server promptly dies (although it does write out an iicrash.log
file) and takes any other DBMS servers sharing the DMF cache with it.  From
that point onwards the table seems to be virtually useless until recovered
from a checkpoint.  Table scans process indefinitely, as do key matches.
Only tid matches seem to work correctly.  Insert and Update statements
result in the crash reoccurring.

The physical VMS file seemed to have 6 VMS blocks (that's 3kb) more than the
maximum Ingres limit

There was no obvious corruption of any rows, although with 16Gb hash table
and no certainty of where the last row was written to, I couldn't rule it
out.  The archiving and purge activity then took the best part of a
fortnight to complete!

I hope this information will help people diagnose this problem in future.
_____________________________
Tim Barker
Senior TSS Consultant
Sema Group Telecoms
Tel:  +44 (0) 207 830 1778
Fax: +44 (0) 207 830 1799
Ingres Q & A
Back to William's Home Page
Email William