Kbase P110420: Best Practices for Managing Records-Per-Block Settings
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
10/06/2010 |
|
Status: Verified
GOAL:
Best Practices for Managing Records-Per-Block Settings
GOAL:
What is the best way to determine the number of records per block for an area?
GOAL:
What is the limit of records in an area?
GOAL:
Where can I find the Database Statistics tool?
GOAL:
Is there a reason why 256 records for a database block should not be set for all areas?
FACT(s) (Environment):
All Supported Operating Systems
Progress/OpenEdge Product Family
FIX:
This solution directs Progress Customers to a ?Best Practices White paper? and available Progress tool that provides important information on optimizing Progress technology and adjusting database settings.
The Progress Database has a two billion record maximum on a database storage area. To be specific, the number of records defined for each block will be counted against the maximum.
This is independent of the actual number of records stored in the database block.
Furthermore the maximum number of database blocks stored in a storage area is determined by the maximum number of records defined per a block on a storage area.
Depending on the number of records per block defined, the database will contain either more or less records in any one block, but still only 2 billion records in a storage area.
How you slice the records imposes a similar maximum on the number of blocks for an area.
If you have 2 billion records and use a records per block setting of 256 then you have only 8,388,608 blocks to hold 2 billion records.
If you cut the same 2 billion into 128 records per block 16,777,216 blocks will be available.
Ultimately if you use 1 record per block you could have 2 billion blocks in a storage area.
That is the block maximums and it is directly related to the setting of records per block.
Also note that a record fragment counts against the record maximum. Large records that are fragmented, will therefore compound the issue.
As an example: Assume that a 32K record is being stored into a database using an 8K block size whose storage area?s have been configured to 256 records per a block. When the 32K record is stored, four database blocks will be used to store the record and 1024 records slots will be used. One 32K record = (4) 8K blocks = 1024 record slots (if records per block was defined as 256).
Those in charge of database design who have assigned high number of records per a block such as 256 may wish to reconsider this decision especially if they are arbitrarily choosing this number. If you are implementing a new database or application and expect large records in the system, please review the documentation, or talk with your Progress account representative or partner prior to determining the records-per-block settings.
The Progress Technical Support and the PSDN Web sites have additional information that will help you to determine the correct thresholds and settings. This can be found by searching for "Database Statistics Tool" on PSDN (http://www.psdn.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 review the solution below regarding reserved space. In 9.1E04, 10.0B05 and 10.1x, space was reserved in the storage area to allow for proper crash recovery handling. This will reduce the total available blocks and records below those numbers calculated above.
Prior to 10.1B, both Type I and Type II storage areas have a limit of 2 billion records.
Beginning in 10.1B and later versions, Type II storage areas can hold 9,223,372,036,854,775,808 records. Progress / OpenEdge uses 1 bit of each 64-bit record structure internally, the rest of the 63-bit structure is used for addressing the records.