Calculating Disk Space

This chapter discusses how to calculate the disk space needed for the various files and operations of an Ingres installation.

An Ingres installation requires disk space for storing the system executables as well as for data tables. Disk space is also used during the execution of many commands. Adequate resources are needed for the user account of the installation owner, for the DBA, and for the end users to ensure that the installation is free of problems due to inadequate disk space.

Space Requirements for Tables

This section defines terms applicable to page size and gives calculations for estimating the amount of disk space needed for tables. These are approximations—your table can be much larger, depending on compression and the size of key values.

The calculations are based on newly modified tables. Using the number of rows in the table to determine table size becomes less accurate after data has been deleted or added.

VDBA provides a calculation tool that allows you to calculate disk space requirements for any storage structure quickly and easily. For procedures, see online help.

Calculate Space Requirements for Heap Tables

Note: If rows in the table span pages, use the procedure in Calculate Space Requirements When Rows Span Pages instead.

Use the following procedure to determine the amount of space needed to store the data in a heap table:

1. Create the table.
2. Determine the number of rows that fit on a page.

   select tups_per_page from iitables where table_name = 'tablename';

3. Determine the total number of pages needed if the table is a heap.

   total_heap_pages = num_rows / tups_per_page

Calculate Space Requirements for Hash Tables

Note: If rows in the table span pages, use the procedure in Calculate Space Requirements When Rows Span Pages instead.

Follow these steps to determine the amount of space needed to store the data in a hash table.

1. Create the table and modify it to hash.
2. Determine the number of rows that fit on a page, adjusted for the data page fillfactor to be used.

   select tups_per_page * table_dfillpct/100 from iitables where table_name = 'tablename';

3. Determine the total number of pages needed for a hash table.

   total_hash_pages = (num_rows/(tups_per_page)

   Note: Because hashing does not guarantee an equal distribution of rows, the actual number of pages required can be greater than calculated above.

Calculate Space Requirements for ISAM Tables

Follow these steps to determine the amount of space needed to store the data in an ISAM table:

1. Create the table and modify it to ISAM.
2. Determine the number of rows that fit on a page (adjusted for data page fillfactor) and the number of keys that fit on an index page.

   select tups_per_page * table_dfillpct/100, keys_per_page from iitables where table_name = 'tablename';

3. Determine the number of data pages needed for the table:

   data_pages = (num_rows / tups_per_page)

   Note: When rows span pages, determine the number of data pages using the calculation in Calculate Space Requirements When Rows Span Pages instead.

4. Determine the number of index pages needed for the table:

   index_pages = data_pages / keys_per_page

   Note: When rows span pages, use the following calculation instead:

   index-pages = num_rows / keys_per_page

5. Determine the total number of pages needed for the table. The total includes data pages and index pages. The total number of allocated pages in an ISAM table is never less than keys_per_page.

   total_isam_pages = data_pages + index_pages

   if (total_isam_pages < keys_per_page)

   total_isam_pages = keys_per_page

Calculate Space Requirements for B-tree Tables

Follow these steps to determine the amount of space needed to store the data in a B-tree table:

1. Create the table and modify it to B-tree.
2. Determine the number of rows that fit on a page, the number of keys that fit on an index page, and the number of keys that fit on a leaf page (adjusted by the appropriate fillfactors):

   select tups_per_page * table_dfillpct/100, keys_per_page * table_ifillpct/100, keys_per_leaf * table_lfillpct/100 from iitables where table_name = 'tablename';

3. Determine the number of leaf pages needed. Save the remainder of the division because it is used later:

   leaf_pages = (num_rows/keys_per_leaf)

   remainder = modulo (num_rows / (keys_per_leaf)

4. Determine the number of data pages needed.

   data_pages = leaf_pages * (keys_per_leaf / tups_per_page)

   Note: When rows span pages, determine the number of data pages using the calculation in Calculate Space Requirements When Rows Span Pages instead.

5. If the remainder from Step 3 is greater than 0, adjust the number of leaf and data pages:

   a. leaf_pages = leaf_pages + 1

   b. Round the division up to the nearest integer:

   data_pages = data_pages + (remainder / tups_per_page)

   Note: When rows span pages, Step 5b does not apply.

6. Determine the number of sprig pages.

   sprig_pages: The number of index pages that have leaf pages as their next lower level:

   a. If leaf_pages <= keys_per_page, then sprig_pages = 0

   b. Otherwise, calculate as follows, and round up to the nearest integer:

   sprig_pages = (leaf_pages / keys_per_page)

7. Determine the number of index pages.

   index_pages: The number of index pages that are not sprig pages. This is done iteratively. Do the following if sprig_pages > keys_per_page:

   x = sprig_pages
   do
      {
      x = x / keys_per_page
      index_pages = index_pages + x
      }
   while (x > keys_per_page>

8. Determine the total space required. The total includes data pages, leaf pages, sprig pages, and index pages.

   total_btree_pages = data_pages + leaf_pages + sprig_pages + index_pages

Calculate Space Requirements When Rows Span Pages

Follow these steps to determine the amount of space needed to store the data in a table with rows that span pages:

1. Determine the number of pages per row, as follows:

   pages_per_row = row_size / max row size

   where max_row_size is the maximum row size for the table, as shown in Maximum Row Size Per Page Size.

   Round up to the nearest integer.

2. Determine the number of data pages needed for the table, as follows:

   data_pages = num_rows * pages_per_row

Maximum Row Size Per Page Size

Table rows span pages if the row size is greater than the maximum row size for the table page size, as shown in this table:

Space Requirements for Compressed Tables

Table size for compressed tables is not possible to determine by an algorithm because the number of fields that can be compressed, and what percentage they can be compressed, differ for every table.

To get any sort of estimate, you must guess the amount by which each record, on the average, can be compressed. Use this estimated record width to determine the size of the table as if it were uncompressed, using the rules set forth above.

Tracking of Used and Free Pages

The DBMS space management handles used and free page tracking. A table uses a combination of a single free header page (H) and one or more free map pages (M) to track free and used pages.

In VDBA, to view the graphical display of the pages, select a table and select the Pages tab.

Each free map page can track 16,000 pages, recording whether the page is free or used. As tables are allowed to grow past 16,000 pages, there can be more than one free map page in a table.

Free map pages are tracked by the free header page, whose location is recorded in the system catalog entries for the table.

Free header and free map pages are additional pages required for each table page count.

Note: All tables in the database can grow by a minimum of two pages. One free map page is added per 16,000 pages.

Note: For B-tree tables, all empty disassociated data pages and any pages on the old free list are marked as "used." The only way to reclaim this space is to select Shrink B-tree Index in the Modify Table Structure dialog in VDBA, or use the modify table to B-tree statement in SQL. For more information, see the SQL Reference Guide.

Calculation of Allocated Table Size

VDBA automatically calculates table size based on the number of allocated pages. Using VDBA, select a table and select the Pages tab to view the pages property sheet.

Alternatively, the allocated_pages field in the iitables standard catalog can be used to calculate table size based on the number of allocated pages. You can calculate:

• The size of the table on disk as:

  iitables.allocated_pages * PageSize

• The number of free pages left in the table as:

  iitables.allocated_pages – iitables.number_pages