Question on Extent Size

Question on Extent Size

Post by Obnoxio Th » Fri, 12 Sep 2003 15:55:04



My guess is that the tables have got too many extents, but they're probably
in their own dbspace, so they're increasing contiguously when new extents
are added. So basically you're OK.
 
 
 

Question on Extent Size

Post by Abraham Ki » Sat, 13 Sep 2003 07:12:31


There are two options....
Fragmented Table :
First extent size = (no. of rows * rowsize)/# of
fragments.
Next extent size = ( .01* First extent size)
Or to be more precise
= ((no of rows expected per month
*4)*rowsize)/#of fragments
this way you only need to rebuild the table once every
quarter.

Non Fragmented table :
First extent size = (no. of rows * rowsize)
Next extent size = ( .01* First extent size)
Or to be more precise
= ((no of rows expected per month
*4)*rowsize)/#of fragments


As a general rule, make sure you don't exceed more
than 8extents per fragment for a table, this helps
performance.
Here is a sql for obtaining the extent report.


select t.tabname,count(*) n_ext
from sysmaster:sysextents e, <"database
name">:systables t
where e.tabname = t.tabname
and dbsname = <"database name">
and t.tabname not matches 'sys*'
group by 1
having count(*) > 1
order by 2 desc;
EOF

Hope it helps.

-Abraham



__________________________________
Do you Yahoo!?
Yahoo! SiteBuilder - Free, easy-to-use web site design software
http://www.yqcomputer.com/
sending to informix-list

 
 
 

Question on Extent Size

Post by kalpanapa » Sat, 13 Sep 2003 13:23:40

i Abraham
Thanks very much for your reply.

As we are setting up a new database Server, i thought if i need to
recreate any of the tables of having more extents.
When i run the same query it does it return any rows, it means all the
tables are having single extent correct me if i am wrong.

And also if i run the dbschema and oncheck the following is the
result .

with dbschema for one table which is everyday growing table
extent size 1208787 next size 120878
with oncheck foll is the output

TBLspace Report for vam2:informix.asset

Physical Address 3deb94
Creation date 09/05/2003 12:45:36
TBLspace Flags 901 Page Locking
TBLspace contains
VARCHARS
TBLspace use 4 bit
bit-maps
Maximum row size 24249
Number of special columns 30
Number of keys 0
Number of extents 1
Current serial value 189122
First extent size 604393
Next extent size 60439
Number of pages allocated 565682
Number of pages used 14737
Number of data pages 14726
Number of rows 51347
Partition partnum 3146009
Partition lockid 3146009

Extents
Logical Page Physical Page Size
0 3491a9 565682

Could anyone suggest me with ref to the above output, do i need
recreate such tables? If so what is the allowable growth of extent
at present it is showing 1208787 next size 120878 .

If i get the approximate extent size i can check rest of the tables
and recreate them in the new server.
Also for first and next extent size cal ,rowsize you mean (Maximum
row size )(24249) to be considered?

Please advise me which are the other areas i need to consider, as i am
importing the whole db to new server dbexport/dbimport.

Any suggestions much appreciated

Thanks
Kalpana


Abraham Kirubakaran < XXXX@XXXXX.COM > wrote in message news:<bjqtle$so2$ XXXX@XXXXX.COM >...
 
 
 

Question on Extent Size

Post by Rajib Sark » Sun, 14 Sep 2003 00:09:38





Hi Kalpana,
What has happened is "extent concatenation" ...when Informix engine tries
to allocate space for the table ( in extents) it first tries to find space
(if available) right next to the current extent of the table ... it looks
like for your table you are in pretty good shape ... Actually, after Online
5 version, the number of extents rule of thumb does not matter too much
...(although its good to have a clean table) ... but in your case you don't
really require to do anything since your table is existing in physically
contiguous space (even though the extent is big) ..

HTH

Thanx much,

Rajib Sarkar
Advisory Software Engineer (RAS)
IBM Data Management Group
Ph : (602)-217-2100
Fax: (602)-217-2100
T/L : 667-2100

As long as you derive inner help and comfort from anything, keep it --
Mahatma Gandhi



kalpanapai@hotmai
l.com To: XXXX@XXXXX.COM
(KalpanaPai) cc:
Sent by: Subject: Re: Question on Extent Size
owner-informix-li
XXXX@XXXXX.COM


09/11/2003 09:23
PM
Please respond to
kalpanapai





Hi Abraham
Thanks very much for your reply.

As we are setting up a new database Server, i thought if i need to
recreate any of the tables of having more extents.
When i run the same query it does it return any rows, it means all the
tables are having single extent correct me if i am wrong.

And also if i run the dbschema and oncheck the following is the
result .

with dbschema for one table which is everyday growing table
extent size 1208787 next size 120878
with oncheck foll is the output

TBLspace Report for vam2:informix.asset

Physical Address 3deb94
Creation date 09/05/2003 12:45:36
TBLspace Flags 901 Page Locking
 
 
 

Question on Extent Size

Post by Rajib Sark » Sun, 14 Sep 2003 00:09:38





Hi Kalpana,
What has happened is "extent concatenation" ...when Informix engine tries
to allocate space for the table ( in extents) it first tries to find space
(if available) right next to the current extent of the table ... it looks
like for your table you are in pretty good shape ... Actually, after Online
5 version, the number of extents rule of thumb does not matter too much
..(although its good to have a clean table) ... but in your case you don't
really require to do anything since your table is existing in physically
contiguous space (even though the extent is big) ..

HTH

Thanx much,

Rajib Sarkar
Advisory Software Engineer (RAS)
IBM Data Management Group
Ph : (602)-217-2100
Fax: (602)-217-2100
T/L : 667-2100

As long as you derive inner help and comfort from anything, keep it --
Mahatma Gandhi



kalpanapai@hotmai
l.com To: XXXX@XXXXX.COM
(KalpanaPai) cc:
Sent by: Subject: Re: Question on Extent Size
owner-informix-li
XXXX@XXXXX.COM


09/11/2003 09:23
PM
Please respond to
kalpanapai





Hi Abraham
Thanks very much for your reply.

As we are setting up a new database Server, i thought if i need to
recreate any of the tables of having more extents.
When i run the same query it does it return any rows, it means all the
tables are having single extent correct me if i am wrong.

And also if i run the dbschema and oncheck the following is the
result .

with dbschema for one table which is everyday growing table
extent size 1208787 next size 120878
with oncheck foll is the output

TBLspace Report for vam2:informix.asset

Physical Address 3deb94
Creation date 09/05/2003 12:45:36
TBLspace Flags 901 Page Locking
 
 
 

Question on Extent Size

Post by Abraham Ki » Thu, 18 Sep 2003 05:13:27

Oops !! did not look at the fact that you have less
than 8 extents per table.
Rajib, thanks for pointing it.

Just keep in mind , that the numbers you see from
dbschema is in Kbytes and the ones you see in the
Oncheck output is in pages.
All that you give to the engine is in Kbytes and all
that you get back from the engine is in Pages.

-Abraham.

--- Rajib Sarkar < XXXX@XXXXX.COM > wrote:
=== message truncated ===


__________________________________
Do you Yahoo!?
Yahoo! SiteBuilder - Free, easy-to-use web site design software
http://sitebuilder.yahoo.com
sending to informix-list
 
 
 

Question on Extent Size

Post by Mark D. St » Thu, 18 Sep 2003 05:39:50


The 8 extents general rule applies to OnLine up to and including version 5.
From version 6, all extents are cached, so there is no real drop in
performance in terms of tracking extents after 8 extents. However, 1 extent
per fragment is best. If you get any were near 200 then you run the risk of
running out and getting errors. Any where in between and it depends on the
type of user access as to the affect on performance.

Cheers,
--
Mark.

+----------------------------------------------------------+-----------+
| Mark D. Stock mailto: XXXX@XXXXX.COM |//////// /|
| Mydas Solutions Ltd http://www.yqcomputer.com/ |///// / //|
| +-----------------------------------+//// / ///|
| |We value your comments, which have |/// / ////|
| |been recorded and automatically |// / /////|
| |emailed back to us for our records.|/ ////////|
+----------------------+-----------------------------------+-----------+

sending to informix-list
 
 
 

Question on Extent Size

Post by malcolm.ii » Thu, 18 Sep 2003 23:46:26


I still keep to the 8 extents rule when calculating database sizing unless
there is a good reason not to. I have observed that the system can take an
appreciable time to add or even extend an extent and if that happens too
frequently it is noticeable.

regards

Malcolm
----- Original Message -----
From: "Mark D. Stock" < XXXX@XXXXX.COM >
To: "Abraham Kirubakaran" < XXXX@XXXXX.COM >
Cc: "KalpanaPai" < XXXX@XXXXX.COM >; < XXXX@XXXXX.COM >
Sent: Tuesday, September 16, 2003 9:39 PM
Subject: Re: Question on Extent Size



5.
extent
of

sending to informix-list