Planning for and Predicting Database Growth
Go Up to Developing the Physical Model
Contents
In order to ensure optimal database performance, it is important that you assess your storage needs and plan accordingly. This section will help you to do this and is comprised of the following topics:
- Planning for Table and Index Storage
- Predicting Table Growth
- Predicting Database Growth
- Reporting Database Growth Analysis
Planning for Table and Index Storage
Planning the storage of tables and indexes is best done during the physical design stage in order to improve performance and to streamline data administration tasks. When planning physical storage, carefully consider both the placement and size of tables and indexes. The performance of almost all database applications is I/O bound. To improve I/O throughput, physically separate tables that are frequently joined together. Also, separate tables from their indexes. The objective is to have the database read or write data in parallel as much as possible.
Two key concerns of every database administrator are free space management and data fragmentation. If you do not properly plan for the volume and growth of your tables and indexes, these two administrative issues could severely impact system availability and performance. Therefore, when designing the physical model, consider the initial extent size and logical partition size.
As a starting point, estimate the size of each table and its indexes based on a projected row count. The Capacity Planning tool helps project the size of the databases given certain growth parameters. For databases that let you specify initial extent sizes, such as Oracle, set the initial extent size to the estimated size in order to avoid data fragmentation as the table grows. By keeping tables within a single extent, data access times decreases and table reorganization can be determined.
Once you have determined the placement and sizes of the tables and indexes in your database, estimate the total size requirements of the database in order to avoid running out of free space for the database.
Predicting Table Growth
Capacity planning metrics can be recorded for each table so that proper sizing can be done before the table is implemented in a database.
The Capacity Planning tab of the Table Editor helps predict change using metrics such as table row count, table growth rate, growth type, growth period and table maximum size.
- In the Data Model Explorer, double-click a table in the physical model.
- Click the Capacity Planning tab and then complete it.
Tip: Change the column Avg. Width and Percent Null values to more accurately predict the table growth.
You can later see and edit the growth predictions you made in the Table Editor in the Capacity Planning Options window.
Predicting Database Growth
Using the Capacity Planning utility, you can forecast storage requirements of newly implemented or existing database systems so budgeting or engineering can be accounted for well in advance.
- In the Data Model Explorer, select and then right-click a physical model.
- Click Capacity Planning and then complete the options.
The following describe options that require additional explanation:
- Growth Parameters tab: Specifies overhead elements that contribute to database growth. The amount of overhead required depends on the DBMS platform and the storage options you have selected for the database, such as the minimum percentage of a data block to reserve as free space, and options for logging, auditing, and data recovery. The values provided here are reflected in the Projected Size column of the Growth Analysis tab.
- Row Overhead: Tailors the formulae to account for any overhead used by the chosen DBMS platforms of the physical model to store each record. This number represents the number of bytes of overhead the DBMS may use to store records. This should be set to 0 initially, but you can specify any number of bytes to add on to the row size.
- Table Overhead: Accounts for overhead to store data for a table. This is a multiplier applied to table size. A value of 1.2 corresponds to 20% overhead. The default value of 1 indicates no overhead.
- Index Overhead: Accounts for overhead to store indexes. The number of bytes is added to the indexed column sizes. For example, in Oracle the index overhead is 6 bytes, so 6 is added to the sum of the indexed column sizes.
- Blob Overhead: Accounts for overhead to store BLOB columns. Similar to Table Overhead, this is a multiplier applied to BLOB storage. A value of 1.2 corresponds to 20% overhead. 1 indicates no overhead.
- Blob BlockSize: Enter an estimate of the average size of BLOB columns. This will be used as the average size of each BLOB column.
Note:
- Changes you make for a table on the Sizing Options tab like Starting Amount, or the Data Growth are automatically reflected in the Sizing Estimates'.
- Changes made in the Sizing Options tab and the Growth Parameters tab are reflected on the Growth Analysis tab.
Reporting Database Growth Analysis
From the Capacity Planning Utility you can export the metrics produced in formats such as RTF, HTML, or CSV, so, for example, you can draft your own reports for the growth patterns of a physical model.
- In the Data Model Explorer, select and then right-click a physical model.
- Click Capacity Planning and then complete the Sizing Options and Growth Parameters of the Capacity Planning utility.
- Click the Growth Analysis tab and select the Report Type and Report Options desired.
A preview of the report contents appears in the Output area.