Consultor Eletrônico

Status: Verified

GOAL:

Are there any guidelines for the new blocks per cluster setting with Progress OpenEdge 10.X?

GOAL:

Are there any guidelines for Type II Area blocks per cluster?

GOAL:

Are there any guidelines for Type 2 Area blocks per cluster?

FACT(s) (Environment):

All Supported Operating Systems
OpenEdge 10.X

FIX:

The Type II Storage Area architecture was introduced in OpenEdge 10. The difference between Type I Storage Areas (introduced in Progress 9.x) and Type II Storage Areas is the concept of "clusters" per Storage Area. There are three choices for cluster size:
- small (8 databaseblocks),
- medium (64 databaseblocks),
- large (512 databaseblocks).

It is not the focus of this Solution to discuss their creation or migration - Please refer to the OpenEdge Data Management: Database Design or OpenEdge Getting Started: Database Essentials - OpenEdge architecture documentation.

Clusters are best from an overall performance point of view, especially for fast growing tables. Although the initial allocation of a cluster may be considered expensive in the sense that the minimum amount of disk space that can be allocated is one cluster, having that many contiguous blocks improves performance by alleviating object fragmentation and better I/O efficiency. It is ultimately dependent on how dynamic/static the data are and the distribution of database objects across Storage Areas to realise the true benefits.

For example:

Type II Storage Areas have been labelled: "Anti-Social Areas" as the main differentiator between the two structures, is (as this example shows) that each cluster may contain only one database object (ie a table or an index). Whereas Type I Storage Area architecture allows any mix of database objects that are stored in that area, to populate any given database block. Consider the following Type II configuration for an 8KB database:

"Data_Area":10,128;512 . f 1000000

The "Data_Area" uses 512 blocks per cluster; which translates to 4 megabytes allocated per cluster. If in this "Data_Area", there are 500 tables, each with one index, and each table containing a single 100 byte record with a 10 byte key, these objects will initially consume 4,000 megabytes of diskspace, (1000 objects x clustersize), leaving a lot of room for future expansion until the next cluster is needed for that database object. For static data, this could be considered a waste of space as that future expansion is never going to happen.

As general guidelines:

Note: The Schema Area is still a Type I Storage Area, it cannot be changed to a Type II area. As such, we recommend moving existing schema tables and indexes, out of the "Schema Area" to new Storage Areas.

General Best Practices for database layout still apply:

- File system and database enabled for large files
- Placing all indexes that belongs to a spefic table on a separate Storage Area from the Storage area that will hold the data of this same table
- Make the last extent of every area a variable length to allow for unanticipated growth but monitor to ensure new space is added before this extent is needed
- AI/BI files should be on their own disk slice for performance reasons and even better if on another disk controller
- For Type I Storage Areas, it is important to get the records per block correct or risk running out of recids by wasting them. For Type II Storage Areas it is much more important to make the record per block accurately reflect the correct value for the application, not only considering recids but also wasted space.

Rule of Thumb - Cluster size and Records per Block:
- 8 blocks per cluster for index areas with 1 record per block
- 64 blocks per cluster for data areas with 128 records per block for 8K databases allowing about 60 bytes of user data per record

Multi Object Areas:
- Group like object together and same rules as above except for suggesting:
- 16 reco.rd per block for /large/ record tables allowing about 500 bytes of user data per record on 8K databases
- 256