[Info-Ingres] page_size on iietabs
Martin Bowes
Hi Everyone,
I have a table with 4 long varchar columns. The table itself has only 793613 rows on 142675 pages, but one of its supporting iietabs has just reached the magical 8.3million rows (1 row per page). And so now an insert into the table fails as the blob table cannot be extended.
If this were an ordinary table I would simply expand the page size from 2k to 8k and buy myself some more time. But I can't seem to do that with the iietab. I was hoping the page size would be copied from the base table when creating the blob table but this doesn't appear to be the case and its stuck on 2k pages.
Is there a configuration parameter, or modify option that allows me to specify the page size on the iietabs?
Would making the base table partitioned help the iietab?
Ingres version is: II 9.0.4 (a64.lnx/105)NPTL + patch12707
Martin Bowes
Martin Bowes
Hi Everyone,
I've answered my own question: the Config parameter: blob_etab_page_size.
Marty
Laframboise André
blob_etab_structure
From: info-ingr...@kettleriverconsulting.com [mailto:info-ingr...@kettleriverconsulting.com] On Behalf Of Martin Bowes
Sent: May 7, 2008 9:00 AM
To: Ingres and related product discussion forum
Martin Bowes
Hi Andre,
Dropping the original and recreating would take at least two hours.
I'm lucky enough to have an agreement with the owner to take the database offline at the moment and connect with +U and modify the catalog directly. I'm not sure how long that will take, but it should be less.
BTW. Expanding to 8k will probably buy me another year before I'm in this problem again … on this column. Can I make the etab a partitioned table?
Marty
Karl & Betty Schendel
On May 7, 2008, at 9:51 AM, Martin Bowes wrote:
> BTW. Expanding to 8k will probably buy me another year before I'm
> in this problem again … on this column. Can I make the etab a
> partitioned table?
>
>
>
No.
But etabs are supposed to accumulate if one fills up. Obviously that
is not
working, which would be a bug.
Also, I think I recall discovering that etabs on a partitioned base
table aren't quite right -- each partition gets its own etab, but
all the blob data goes into the partitioned master's etab, which
certainly wasn't what I had in mind originally. It's been
a while since I poked around with blobs in a partitioned
table, though.
You might want to go to 16K pages, or larger, instead of 8.
Karl
Laframboise André
To: Ingres and related product discussion forum
Subject: RE: [Info-Ingres] page_size on iietabs
Hi Andre,
Dropping the original and recreating would take at least two hours.
I'm lucky enough to have an agreement with the owner to take the database offline at the moment and connect with +U and modify the catalog directly. I'm not sure how long that will take, but it should be less.
BTW. Expanding to 8k will probably buy me another year before I'm in this problem again … on this column. Can I make the etab a partitioned table?
Marty
Martin Bowes
Having finally beaten the existing problem into submission and set up
triggers, monitors etc to check for new cases...
How would the iietabs accumulate? I'm looking at the iiextended_relation
catalog and its keyed on the etab_base. So I'm assuming here that if the
max number of pages was breached that I would see a new iietab created
to hold the spill, and a new entry in iiextended_relation with the same
etab_base, etab_attid but a different etab_extension. Yes?
BTW. What is etab_type for?
I'm currently trying to build a testcase for this. And am loading
8388600 rows into a simple table with a blob column. I can then add
extra row - which will cause an extra page to be loaded onto the iietab
and see what happens as we break the magic 8388608 mark.
I'll play with partitioned tables afterwards.
Marty
-----Original Message-----
From: info-ingr...@kettleriverconsulting.com
[mailto:info-ingr...@kettleriverconsulting.com] On Behalf Of Karl
& Betty Schendel
Sent: 07 May 2008 15:31
To: Ingres and related product discussion forum
Subject: Re: [Info-Ingres] page_size on iietabs
On May 7, 2008, at 9:51 AM, Martin Bowes wrote:
> BTW. Expanding to 8k will probably buy me another year before I'm
> in this problem again ... on this column. Can I make the etab a
> partitioned table?
>
>
>
No.
But etabs are supposed to accumulate if one fills up. Obviously that
is not
working, which would be a bug.
Also, I think I recall discovering that etabs on a partitioned base
table aren't quite right -- each partition gets its own etab, but
all the blob data goes into the partitioned master's etab, which
certainly wasn't what I had in mind originally. It's been
a while since I poked around with blobs in a partitioned
table, though.
You might want to go to 16K pages, or larger, instead of 8.
Karl
_______________________________________________
Info-Ingres mailing list
Info-...@kettleriverconsulting.com
http://www.kettleriverconsulting.com/mailman/listinfo/info-ingres
Martin Bowes
Oh this is fun!
I've just worked out that:
1. The system does auto generate a new iietab when the data source was a
copy. I'm not so sure about inserts, I'll play with those later.
2. Any subsequent attempt to insert into the base table will succeed,
the data going to the new iietab....BUT,
The first attempt in the SESSION (not transaction) to insert will
generate the following errors in the errlog.
E_DM92BF_DM1P_TABLE_TOO_BIG Error extending table past maximum table
size of , Database *** ERslookup() ERROR: Missing or bad parameter for
this message. ***
E_DM92CB_DM1P_ERROR_INFO An error occurred while using the Space
Management Scheme on table: iietab_137_138, database: bowtest
E_DM92DC_DM1P_EXTEND Error extending a file.
E_DM92CB_DM1P_ERROR_INFO An error occurred while using the Space
Management Scheme on table: iietab_137_138, database: bowtest
E_DM92DC_DM1P_EXTEND Error extending a file.
E_DM92D0_DM1P_GETFREE Error allocating a page from free list.
E_DM9257_DM1B_FINDDATA Error occurred finding disk space for a new
record.
E_DM0138_TOO_MANY_PAGES An attempt was made to grow a file to a
size larger than a TID can handle.