Hello Folks,

Does anyone have an accurate way to calculate the amount of disk
spaces needed for tables ?

I followed up the method in the section of calculating disk space
from Database Administrator's Guide to do the calculation. It seems
the calculations are not accuate especialy for BTREE struction in our
database(the results of calculations for HEAP are OK). I could get the
pages used by a table at present from iitables and compared them with
the pages required for this table from my calculations, if there were
more than 20% wasted, I reclaimed them by modify <table> to statement,
which should reclaim the lost spaces due to many deletions of this
table. I compared the pages again after modification and found that
there were the same percent of differences. This make me think the
way of calculation is not accurate. Can anyone shed a light on my
issue and have a better and an accurate way to calculate the amount of
disk spaces needed for tables in Ingres.

Thanks,

Scott Liu

Data Analyst
Algonquin College
Ottawa, Canada

Scott wrote:

I don't think you are ever going to get a very accurate calculation
because sometimes the per-row overhead is variable.

Chip Nickolett has a spreadsheet at
http://www.comp-soln.com/var_page.xls that might be of interest.

I spent a fair bit of time fiddling with the calculation a couple of
years ago and decided I'd just treat disks as consumables, and buy more
as-and-when.

--
Roy

UK Ingres User Association Conference 2010 will be on Tuesday June 8 2010
Go to http://www.iua.org.uk/join to get on the mailing list.

On Jul 14, 2009, at 11:51 AM, Roy Hann wrote:

I'm going to steal a jump on Mikey by proclaiming "Disks Are Free". :-)

I understand that the OP is trying to use disk space as a trigger
for modifying, but the trick there is to use space change, not
an absolute calculation. Compute a rows per page across the entire
table, and maintain that value over time; when it goes down
by "enough" you re-modify the table to reconstruct. (here I mean
a fractional average rows-per-page, not an integral actual
rows-per-page.)

If the table is largely an OLTP-style table, with relatively few
rows looked-up per query, space used is pretty much irrelevant
since Disks Are Free. If queries often or even sometimes scan
large chunks of the table, sequential disk layout becomes
relevant, and effective modification is more a function of
filesystem layout policy than space usage. (e.g. for
partitioned tables, set the degree_of_parallelism
config setting to 1, so that modifies are non-parallelized
and result in more sequential allocations. Or, set
filesystem reservation policies to generously large
amounts. Whatever it takes.)


Karl

On Jul 14, 12:17*pm, Karl & Betty Schendel <schen... (AT) kbcomputer (DOT) com>
wrote:
Hello Folks,

Thank you all for your information. Yes, there is no very accurate
method to calculate the size of a table. We are going to modify our
tables' structures in certain interval time to claim lost spaces.
Really appreciate your feedback and valuble information.

Thanks again.

Scott

Have you tried the "table space calculator" within Ingres Visual DBA?



-----Original Message-----
From: info-ingres-bounces (AT) kettleriver...ting (DOT) com [mailto:info-ingres-bounces (AT) kettleriverconsulting (DOT) com] On Behalf Of Scott
Sent: 23 July 2009 15:41
To: info-ingres (AT) kettleriverconsulting (DOT) com
Subject: Re: [Info-Ingres] How to calculate tables' spaces acurately inIngres

On Jul 14, 12:17*pm, Karl & Betty Schendel <schen... (AT) kbcomputer (DOT) com>
wrote:
Hello Folks,

Thank you all for your information. Yes, there is no very accurate
method to calculate the size of a table. We are going to modify our
tables' structures in certain interval time to claim lost spaces.
Really appreciate your feedback and valuble information.

Thanks again.

Scott
_______________________________________________
Info-Ingres mailing list
Info-Ingres (AT) kettleriverconsulting (DOT) com
http://www.kettleriverconsulting.com...fo/info-ingres