What are database extentsExtents are disk files that store physical blocks of database objects. Extents make it possible for an OpenEdge database to extend across more than one file system or physical volume. The limits on the number of extents that can be added to a database are: maxarea 32000, maximum extents per area 1024.
Types of extents: 1. Fixed-Length extents (f)
- Control how much disk space each extent uses by defining the size of the extent in the database structure (.st) file in KiloBytes.
- Blocks are pre-allocated and pre-formatted specifically for the database when they are created.
- Once created, the fixed space on disk is always available to the database
- Once a fixed-length extent is filled, the next available extent is used.
2. Variable-length extents
- The initial allocation for a Variable length extent is 16 x database-blocksize
- They do not have a predefined maximum size and can continue to grow until they use all available space on disk or until they reach the limits of Progress, the file system's limit on file size or user limits or available disk space
- There can be only one variable extent defined per Before-Image and Data Area and it must be the last extent in the area, except for After-Image extents (.ai) where more than one ai extent may be a variable extent as each AI file is assigned an area number.
- A variable extent becomes a fixed extent after a new extent is added. Refer to Article What happens to the variable-length extent when a new extent is added
- Typically, a variable-length extent is used as an overflow file when all fixed-length extents have been exhausted. When the variable extent used this leads to database extents not being uniformly sized when new extents are added. Refer to Article Are there any problems with database extents not being uniformly sized?
3. Fixed-Variable extents (v)
Advantages and DisadvantagesDetermining which extent type to use, depends business requirements and perspective.
Both Fixed and Variable database extents can only be larger than 2 GB when
largefiles are enabled with an Enterprise Database license, the OS is enabled for largefiles and user quota/limits allow. Restoring a production database to Development environments needs extra considerations. For further information refer to Article
How do prorest and procopy work with Large File Support? In the past, the disadvantage of a Variable extent is that Progress needs to communicate with the Operating System every time when it needs additional disk space, which could degrade performance. With modern filesystems, variable size extents work quite well for all but the most unusual situations, like non-journalled filesystems. Inode indirection is not an item of concern for journalled file systems. File indirection is an issue only in ufs filesystems and derivatives. UFS is basically only used these days on special occasions; all modern filesystems are either derivatives of the Berkely Fast Filesystem or are journalled, which means that there is no large scaling in access time with file size as there is with UFS. In other words, only in special cases will you have a situation where large variable files are much slower than many small files.
While Variable extents may provide easier file management, working with very large files need additional considerations when:
- During the management of a database outside of production, extents need to be backed up or moved across filesystems or servers. Extents move “faster” in smaller sizes and are more manageable in so far as if an FTP fails you don’t have to start all over again.
- Unexpected variable extent growth hits available disk space, Progress or OS limits. As a general recommendation, limiting data extents below 2 GB guards against reaching some forgotten OS/user/filesystem limit when the database is in production. These can usually be overridden / reconfigured after the fact, but time consuming and involve production downtime. Fixed extents also gives more control on managing the extents themselves and transferring between environments when needed.
The only real advantage of fixed extents over variable extents today, is that the space is pre-allocated and therefore always available to the database. The perceived disadvantage of fixed extents is periodic full extent management. As more fixed extents are added to the database, the total number of file handles being consumed may become an issue, using fewer larger extents rather than more smaller controls
file handle consumption. Each self-service user consumes one file handle per database extent, plus a few. If there are hundreds of users, default kernel configuration of per-user and total system-wide file handle usage for all OS's except Windows and AIX become a limitation.
Having variable extents does not negate monitoring extent sizes. When variable extents are used and disk space is not available due to something else temporarily using the filesystem allocated to the database, or unattended variable extent growth results in dbdown situations which take longer to recover from. Full extent management provides more control and less downtime than (unattended) Large file management.
General RecommendationAs a (very) general recommendation, which applies to both Progress 9.1x and OpenEdge 10.1x, 11.x DB structures which underline the use of the extent "properties" (i.e. the block size, record per block and clustering from OpenEdge 10).
- Always spend some time in defining the best combination: "area - table", assuming you agree on the approach one big table = one separate area, regardless of the number or type of extents.
- For small databases (< 30 GB) use 2 GB extents or all variable extents. Otherwise use 4Gb or 8Gb fixed extents with largefiles enabled and 2GB or variable extents for areas with little growth forecast.
- Periodically run PROSTRCT STATISTICS to monitor extent growth.
