Re: Page count and extent count limits
See replies inline below:
Art
Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
IIUG Board of Directors (art@iiug.org)
See you at the 2010 IIUG Informix Conference
April 25-28, 2010
Overland Park (Kansas City), KS
www.iiug.org/conf
Disclaimer: Please keep in mind that my own opinions are my own opinions and
do not reflect on my employer, Advanced DataTools, the IIUG, nor any other
organization with which I am associated either explicitly, implicitly, or by
inference. Neither do those opinions reflect those of other individuals
affiliated with any entity with which I am affiliated nor those of the
entities themselves.
On Wed, May 5, 2010 at 4:48 AM, ANDREW LEMIN <a_lemin@hotmail.com> wrote:
> Hello all,
> Thank you for all your help on my recent questions.
> I got exactly what I needed in the end thanks to all your help :)
>
> I am now looking into writing a stored procedure to check page limits.
>
> We had a customers production database crash when one of the fragmented
> tables' fragments page count hit the page limit.
>
> IDS apparently has a page limit of 16,777,215 pages per 'table
> fragment'/'table (no fragments)'.
> Is this correct and what else does this limit apply to?
>
Actually the page limit is 16,775,134 officially. It applies to all
partitions, so unfragmented tables and indexes, and each fragment of tables
and indexes separately.
>
> Can anyone provide any advice on how I can query sysmaster etc, to find
> page
> counts for tables/table fragments etc which are getting close to this
> limit?
>
select dbsname, tabname, partnum, nextns as num_extents, nptotal as
num_pages, npused as used_pages
from systabnames as st, sysptnhdr as sp
where st.partnum = sp.partnum
order by 1, 2, 3;
>
> Secondly,
> What is the maximum number of extents per table supported by IDS?
> I have this so far;
> Max_Extents_For_A_Table <= (table pagesize - ((4 * number of columns in a
> table) + (8 * number of BLOB and VARCHAR columns + 136) + (12 * number of
> indices) + (4 * number of columns in the indices) + 84))
>
That's almost the calculation. There should be a '/4' at the end there IB
since each extent entry has to be the location of the extent entry. Roughly
it works out to about 200 extents for a table on a 2K page with no special
columns and a reasonable number of columns and keys
>
> Is there a more elegant or simple way of changing the 'extent' and 'next
> extent' declarations for a table and apply those new sizes without having
> to
> create a copy of the new table, copy the data across from original to new,
> drop the original table, and finally rename the new table to the original
> tables name?
>
ALTER TABLE mytable MODIFY EXTENT SIZE 10000;
ALTER TABLE mytable MODIFY NEXT SIZE 1000;
ALTER FRAGMENT ON TABLE mytable INIT IN somedbspace;
ALTER FRAGMENT ON TABLE mytable INIT FRAGMENT BY ....;
That is currently the fastest way to reorg a table. If you have limited
logging space ALTER the table to RAW before the ALTER FRAGMENT...INIT... and
ALTER it back to STANDARD after and take an archive for fastest processing.
You can INIT the table into the same dbspace or fragment scheme that it
already occupies. As long as there is enough contiguous free space in each
of the named dbspaces the new table will have only one or a couple of
extents per dbspace. Since this copy is done extent by extent you don't
need free space for a second copy of the entire table either, though where
there is less contiguous free space the table will end up with more extents
after the reorg than the ideal.
>
> Are there any other limits that we should be warned of? Our customer
> databases
> are starting to grow quite considerably and as such we are feeling a
> learning
> curve for managing very large IDS databases.
>
Not really any practical limits: 21 million databases. 2^31 pages/chunk.
32766 chunks. 2047 dbspaces/blobspaces/sbspaces. ~128 Petabytes total
storage. All of the limits are in the release notes in
$INFORMIXDIR/release/*/0333/*relnotes*.txt
>
> Thank you very much for your kind help and advice.
> Regards, Andy.
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--00504502b1f89bd1550485d745c7