Progress KB - How to calculate the maximum Storage Area size ?




Feedback
Did this article resolve your question/issue?

   

Article

How to calculate the maximum Storage Area size ?

Information

 
TitleHow to calculate the maximum Storage Area size ?
URL NameP106246
Article Number000147830
EnvironmentProduct: Progress
Product: Openedge
Version: 9.x, 10.x, 11.x, 12.x
OS: All supported platforms
Question/Problem Description
How to calculate the maximum Storage Area size ?
What is the Progress database maximum Storage Area size limit ?
How big can a Type I Storage Area be ?
How to calculate the Maximum Number of Database Blocks Per Storage Area ?
What is the Maximum data storage area size with a database 4k blocksize?
How to determine if an area is reaching Type I storage area size limits?
Steps to Reproduce
Clarifying Information
Error MessageCreation of this extent would exceed the maximum number of blocks for area <Area Number> prostrct add FAILED. (12867)
Defect/Enhancement Number
Cause
Resolution
Maximum data storage area size

The maximum size of a Type II Storage Area is fixed at approximately one Petabyte, when large files are enabled and maximum sizes are governed by the maximum area size rather than the addressability of 64-bit database keys. For further information refer to Article: For any Progress database prior to OpenEdge 10.1B, before the introduction of 64-bit rowids (2^63 rowids) when used in conjunction with Type II Storage Areas, the Progress Database has a two billion rowid (2^31) maximum per database Storage Area which limits the maximum Storage Area size (maxarea) depending on the database blocksize and the number of records-per-block defined. In these versions or 10.1B and later versions that still use Type I Storage Areas, the maximum application Storage Area size is determined by three values:
  1. Maximum number of records / record fragments per Storage Area: 2,147,483,647 == 2^31-1
  2. Number of Records Per Block of the Area as defined in the structure file: dbname.st
  3. Database block size
For further information refer to Article
Irrespective of the Type I or Type II storage area structure
  • Just because the 'records per block' setting allows that many records or record fragments to be housed in a database block, does not mean that that many records can fit in the block and therefore does not mean that many records will be populated in the area. This is also dependent on the blocksize and the record size which leads to 'wasted rowids'.
  • If there are indexes or LOBS as well as tables in the area, blocks that house these objects further reduces the maximum number of records that can be housed in an area
  •  All storage areas have reserved space to guarantee space for REDO crash recovery. For further discussion refer to Article 000012254, Reserved space used for recovery limits user addressable maximum storage area size.  
To calculate the maximum 32-bit rowid Storage Area Size:

1. Calculate the Maximum number of database blocks per Storage Area:
Max database blocks per Storage Area =  
(Maximum number of rowids per storage area) / (records per block)
2. Use the result to calculate the Maximum Storage Area Size:
 
Max area size = 
(Max database blocks per Storage Area) * (Database block size)

Example: A database uses a Type I Storage Area with:
  • 32 records per block
  • 4K (4096 bytes) database block size.
  1. Maximum number of database blocks per Storage Area = 2,147,483,648 / 32 = 67,108,864
  2. Maximum storage area size = 67,108,864 * 4096 = 274,877,906,944 bytes = 256 GB.
The following table provides a Quick Reference lookup of the most common RPB values for areas with a 2,147,483,647 rowid limit:
 
Records per Block
Database Blocksize
1
4
8
16
128
512
1,024
32
64
256
512
64
32
128
256
128
16
64
128
256
8
32
64

To Monitor Area Size: 

For Type II Areas, there is no value in monitoring _AreaStatus or using PROSTRCT STATISTICS because they have multiple RM chains for each database object stored in that area. For further information refer to Article 000029900, VST's to monitor space in a Type II Storage Area?  

The maxblocks (2^31-1)/rpb area limit is only a concern for Type I storage areas. While the ["Schema Area":6,32;1], remains a Type I area, as long as the database does not have any application objects in this area, only the database meta-schema (as it should) it will never get anywhere near the maxblocks limit on a Type I area and is therefore not a concern.

Using VST's to monitor when a Type I area is reaching the maxarea limit:
 
DEFINE VARIABLE vhiwater AS DECIMAL FORMAT ">>,>>>,>>9" LABEL "Hiwater" NO-UNDO. 
DEFINE VARIABLE vTableAreaRPB AS INTEGER NO-UNDO.
DEFINE VARIABLE vBlockSze AS INTEGER NO-UNDO.
DEFINE VARIABLE vmaxareablx AS INTEGER NO-UNDO.

/* In the event application objects are still in the Schema Area */
FOR EACH _AreaStatus NO-LOCK WHERE _AreaStatus._AreaStatus-Areanum >= 6 
AND NOT _AreaStatus._AreaStatus-Areaname BEGINS "After Image",

/* For mixed Type I + Type II structures, 
we're only interested in Type I Areas for 2 billion rowid maxarea limits */
FIRST _Area WHERE _Area._Area-number =_AreaStatus._AreaStatus-Areanum 
AND _Area._Area-clustersize = 1 NO-LOCK
BREAK BY _AreaStatus._Areastatus-Areanum.

IF FIRST-OF (_AreaStatus._Areastatus-Areanum)
THEN DO:

ASSIGN /* HWM */
vhiwater = _AreaStatus-Hiwater. 
IF vhiwater = ? THEN vhiwater = 0.0.

ASSIGN /* RPB */
vTableAreaRPB = EXP(2,_Area._Area-recbits).

FIND FIRST _DbStatus. /* Blocksize */
ASSIGN vBlockSze = _DbStatus._DbStatus-DbBlkSize.

ASSIGN vmaxareablx = (EXP(2,31) - 1) / vTableAreaRPB. 
/* * vBlockSze / 1024 / 1024 / 1024. */

DISPLAY 
_Area-name LABEL 'Storage Area' FORMAT "x(32)" vTableAreaRPB vBlockSze SKIP
vmaxareablx LABEL 'MAXAREA' FORMAT "->,>>>,>>>,>>9"
vhiwater LABEL 'HWM' FORMAT "->,>>>,>>>,>>9"
_AreaStatus._AreaStatus-Hiwater * vTableAreaRPB 
         LABEL "Highest RECID" FORMAT "->,>>>,>>>,>>9".

/* Customise Alert */
IF _AreaStatus._AreaStatus-Hiwater / vmaxareablx GE 0.85 THEN DISP "RED ALERT!! ".

END.
END.

Using PROSTRCT STATISTICS to calculate when a Type I area is reaching the maxarea limit:

Run a prostrct statistics report (online or offline) and look at the "Active blocks" and "Records/Block" listed under the "Database Block Usage for Area:" for the area.

Example:
Database Block Usage for Area: History

  Active blocks: 8060928
    Data blocks: 8060924
    Free blocks: 4
   Empty blocks: 121
   Total blocks: 8061049
  Extent blocks: 83
  Records/Block: 256
   Cluster size: 1
Maximum # of blocks (for 2 Billion records) =  2,147,483,648 records / (256 records / block)  =   8,388,608 blocks

Active blocks: -8,060,928 + 8,388,608 = 327, 680 blocks remaining before maxarea is reached.
 
Workaround
Notes

References to Other Documentation:

Progress Database Administration Guide and Reference, Part I Chapter 3: "Progress Database Limits".
Progress Database Administration Guide and Reference, "Table 3: Maximum Application Data Storage Area Size"

Progress Articles: 

000010253, How to find out the space utilization of a database?   
000013241, What to do when you hit the record limit for a Type I area?    
000010408, Best Practices involving the Schema Area.   
000010613, Best Practices for Managing Records-Per-Block Settings   
000012254, Reserved space used for recovery limits user addressable maximum storage area size.  
000020106, Dbanalys gives contradicting Information about the DB size    


 
Last Modified Date2/25/2020 2:27 PM
Attachment 
Files
Disclaimer The origins of the information on this site may be internal or external to Progress Software Corporation (“Progress”). Progress Software Corporation makes all reasonable efforts to verify this information. However, the information provided is for your information only. Progress Software Corporation makes no explicit or implied claims to the validity of this information.

Any sample code provided on this site is not supported under any Progress support program or service. The sample code is provided on an "AS IS" basis. Progress makes no warranties, express or implied, and disclaims all implied warranties including, without limitation, the implied warranties of merchantability or of fitness for a particular purpose. The entire risk arising out of the use or performance of the sample code is borne by the user. In no event shall Progress, its employees, or anyone else involved in the creation, production, or delivery of the code be liable for any damages whatsoever (including, without limitation, damages for loss of business profits, business interruption, loss of business information, or other pecuniary loss) arising out of the use of or inability to use the sample code, even if Progress has been advised of the possibility of such damages.