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 structure is the concept of "clusters" per Storage Area. There are three choices for cluster size:
- small (8 database blocks),
- medium (64 database blocks),
- large (512 database blocks).
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 or a LOB). Whereas Type I Storage Area architecture allows any mix of database objects that are stored in that area as long as they are the same database object, to populate any given database block. Consider the following Type II configuration for an 8KB database:
"Data_Area":10,128;512 . f 1000000The "Data_Area" uses 512 blocks per cluster; which translates to 4 megabytes allocated per cluster (512 x 8KB). 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 disk space, (1000 objects x cluster size), 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:The Schema Area is still a Type I Storage Area, it cannot be changed to a Type II area. As such, moving existing schema tables and indexes, out of the "Schema Area" to new Storage Areas is recommended.
General Best Practices for database layout still apply:
- File system and database enabled for large files (with Enterprise Database Licenses)
- Placing all indexes that belong to a specific table in a separate Storage Area from the Storage Area that will hold data of the associated table.
- Make the last extent of every Storage Area a variable length to allow for unanticipated growth, but monitor to ensure new fixed extent space is added before this variable 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 in database blocks.
Rule of Thumb - Cluster size and Records per Block:
Multi Object Areas:
- Group like objects together and same rules as above except for suggesting:
- 16 record per block for large record size tables allowing about 500 bytes of user data per record on 8K databases.
- 256 record per block for small record size tables allowing about 30 bytes of user data per record on 8K databases.
Fast Growing Tables:
- Separate out to their own index and data Type II Storage Areas.
- Increase the index cluster size to 64 from 8, still 1 record per block.
- 512 blocks per cluster for fast growing table and 64 record per block because fast growing tables don’t want to waste rowids - ~100 bytes of user data.
Databases that are small or not used much and where performance is not very important at all don't need to use Type II areas. For any production database of any consequence use Type II areas, even if performance is not a main concern - maintenance operations will be faster like dumping without indexes by using the table scan feature for example. Although some operations in early OpenEdge 10 releases have been marginally improved, future releases will take more advantage of the clustered space in Type II areas. For example, it is anticipated that a table or index will be able to be deleted in close to zero time in a Type II area. These enhancements will be announced as they are implemented in the OpenEdge Release Notes.
