Feedback
Did this article resolve your question/issue?

   

Article

Best Practices for Managing Records-Per-Block Settings

Information

 
TitleBest Practices for Managing Records-Per-Block Settings
URL NameP110420
Article Number000127007
EnvironmentProduct: Progress
Product: OpenEdge
Version: All supported versions
OS: All Supported Operating Systems
Question/Problem Description
Best Practices for Managing Records-Per-Block Settings
What is the best way to determine the number of records per block for an area?
What is the limit of records in an area?
Where can I find the Database Statistics tool?
Is there a reason why 256 records for a database block should not be set for all areas?
Steps to Reproduce
Clarifying Information
Error Message
Defect/Enhancement Number
Cause
Resolution
This Article directs Progress Customers to a “Best Practices” White paper and Progress tool that provides important information for optimizing Progress technology and adjusting database structure settings.

It also discusses the maxarea limit and provides examples to demonstrate these settings.


Prior to OpenEdge 10.1B and introduction of 64-bit rowids when used in conjunction with Type II Storage Areas, the Progress Database had a two billion rowid maximum limit per database Storage Area.

The maximum number of database blocks available to a Storage Area is determined by the maximum number of records defined per a block on a Storage Area.
 
Depending on the records per block settings in the database structure, it imposes a maximum limit on the number of blocks for an area and therefore the maximum area size.

That is, the maximum number of blocks per Area is directly related to the records per block setting. Here are some examples:
 
Assuming no fragmentation, if there are 2 billion records with records per block setting of 256, there will be 8,388,608 database blocks available (to hold those 2 billion records).
Calculation: 2 billion records in binary is represented as 1024x1024x1024x2=2,147,483,648 divided by 256 (records per block) equals 8,388,608 database blocks.


Similarly, 2 billion records divided to 128 records per block will have 16,777,216 database blocks available in the Storage Area.

Finally, if we use 1 record per block setting, then
2 billion records would have 2 billion blocks in the Storage Area.

Now, if we multiply the maximum number of blocks in the Area by the database blocksize, this will give us the maximum Storage Area size (to hold those 2 billion records)

The higher records per block setting, the lower Storage Area size.

 
The number of records per block is independent of the actual number of records that get stored in each database block.
 
Depending on the number of records per block defined, the database will contain either “as many” or less records in any one block, but still only 2 billion rowid slots in a Storage Area. The correct value is always a function of actual record size.
 
As an example:
 
If the average record size is 100 bytes, then the ideal records per block setting would be 80 (for 8k data block), so that it will hold exactly 100 x 80 = 8k. Following on this:

setting records-per-block to 1 will cause significant unused space in every data block.
setting records-per-block to 32 will cause ~4.7KB unused space in every data block.
setting records-per-block to 64 will cause ~1.6KB unused space in every data block.
setting records-per-block to 128 will cause ~66bytes unused space in every data block AND 48 unused rowids per data block.
setting records-per-block to 256 will also cause ~66bytes unused space in every data block AND 176 unused rowids per data block.

 
A record fragment also counts against the maximum rowids.
 
Large records that are fragmented will therefore compound the maximum rowid issue by leaving less rowids available for new records.

Here is an example:
 
Assume that a 32K record is being stored in a database using an 8K block size whose Storage Areas have been configured to 256 records per block.
When the 32K record is stored, four database blocks will be used to store the record, so 1024 rowid slots will no longer be available, while only 4 of these rowids are actually used:
The first for the record reference itself and the remaining 3 for the record fragments.
One 32K record = 4 x 8K blocks = 4 x 256 rowid slots.


Determining best records per block setting and evaluating whether the maxarea limit is being reached
 
Our Progress Community Web site has additional information to help determine correct thresholds and settings and evaluate whether the maxarea limit is being reached.
This can be found by searching for "Database Statistics Tool" on https://community.progress.com

 
In this downloadable package you will find instructions on using the tool, interpreting its results and a white paper, "Best Practices for Managing Records-Per-Block Settings".

In the white paper you will find detailed information on how to set records-per-block to achieve optimal performance.

Also, please review our Article 000012254 regarding reserved space. In Progress 9.1E04, OpenEdge 10.0B05, 10.1x and above, the space was reserved in the Storage Area to allow for proper crash recovery handling. This will reduce the total available blocks and rowids below those numbers calculated above.

 
In summary:
 
When using Type I Storage Areas in all current versions, or Type II Storage Areas prior to OpenEdge 10.1B, the number of records per block defined in the database structure counts against the maximum rowids available per Storage Area (2^32-1) limiting the number of database blocks per Storage Area and therefore the Storage Area size, independent of the number of records and record fragments that are physically stored in each database block. When this hard limit is reached, the database can no longer be updated and corrective restructuring operations are needed.
 
Beginning in 10.1B and later versions, Type II Storage Areas can hold 2^64-1 or 9,223,372,036,854,775,808 rowids. Like with 32-bit keys, OpenEdge uses 1 bit of each 64-bit record structure internally, and the rest of the 63-bit structure is used for addressing the rowids. So, the number of records per Type II Storage Area is governed by the maximum area size, rather than the addressability of 32-bit database keys (which is a hard limit on Type I Storage Areas and Type II Storage Areas prior to OpenEdge 10.1B).
 
Those in charge of database design who have assigned a higher number of records per block than required may wish to reconsider this decision, particularly if they are arbitrarily choosing this number. When implementing a new database design or considering a database re-organisation, please review the documentation, talk with your Progress account representative or application partner and discuss on our Progress Communities forum prior to determining the records-per-block settings for the database structure.
Workaround
Notes
Last Modified Date9/25/2018 11:08 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.