Kbase P111065: FAQ - 'Best Practices' For Managing Records-Per-Block Settings
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
17/10/2008 |
|
Status: Verified
GOAL:
FAQ - Best Practices For Managing Records-Per-Block Settings
FACT(s) (Environment):
All Supported Operating Systems
OpenEdge 10.x
Progress 9.x
FIX:
Why are we proactively communicating about the 2.1 billion records-per-block maximum, per database area?
This limit is not new, and has been discussed frequently over the life of Progress Version 9 and OpenEdge Release 10. However, because of the continued growth in database size due to regulatory changes as well as business growth, and the fact that not all customers are aware of the limits, we feel we it is prudent to draw additional attention to it, Recently, we have seen a few customers experience unplanned downtime when reaching the maximum. Compounding the issue is a common misconception about how the maximum number of records per area is calculated. Therefore, we are proactively informing and educating customers so that potential issues can be avoided.
What happens if you reach the 2.1 billion records-per-block area maximum?
Your database will perform an abnormal shutdown. Recovering from the error may be time consuming.
Can the issue be avoided?
Yes, appropriate configuration planning and proactive management of databases can avoid the issue completely.
Who is most likely to hit this issue?
Customers who have large tables and have configured the database storage areas so that each block can hold up to 256 records could reach the limit. See Progress Knowledge Solution P110420 for more details.
To be specific, the setting of the ?maximum records per block? (for example 256) parameter for the storage area is what will be counted against the maximum number of addressable rows for the area. This is independent of the actual number of records in the block. If you have large records, then you may only have ten records in a block, but 256 would be counted against the maximum. Also note that a record fragment counts against the record maximum. Large records that are fragmented, may therefore compound the issue.
Note that the maximum number of records in a storage area is independent of database block size, but the probability of encountering the limit is a bit higher with block sizes below 8192 bytes.
When did Progress know about this issue?
It has often been discussed in technical sessions at user conferences and other settings. It has always been a potential, but highly improbable, issue. With the ongoing growth in average database size, it is now becoming more probable. As a result, this is now a subject that we believe all Progress Database Administrators should be more aware of and should take steps to avoid.
What steps can be taken to avoid the issue?
1. Identify the storage areas that might be close to the maximums. (See solution P110420 for details of a tool to help in this task)
2. Determine the optimal records-per-block for that area.
Then either:
a) Purge data from the area to temporarily reduce the count of records within the area until you can plan for proper migration of the data to an area that has the optimal records-per-block for those tables.
b) Use the tablemove option in proutil to move data to a new area with a more optimal definition of records-per-block
c) Dump and load the data to a new area that has the most optimal records-per-block for the tables impacted.
Contact Technical Support if you need more information or guidance.
If I hit the maximum what can be done?
1. Go to backup. Then restructure your database as detailed above.
or
2. G.o to backup and roll forward to a point in time before the maximum was reached. Then restructure your database as detailed above. Contact Technical Support if you need more information or guidance
Are there other maximums to be concerned about?
In addition to the maximum number of rows per area, there is a related limit: the maximum number of data blocks in a storage area. Both limits are affected by the setting of the area?s record-per-block. While the maximum for the number of records in an area is 2.1 billion, the maximum number of data blocks varies depending on the setting of records-per-block.
If you choose a records-per-block setting of 256, then the maximum number of database blocks for the storage area is 8,388,608. If you halve the setting of 128 records-per-block, then the maximum number of blocks in the area will be approximately doubled, to 16,777,216. If you choose a setting of 1 record-per-block the limit, will be approximately 2.1 billion blocks.
Note that the correct value is a function of actual record size. If the average record size is 50 bytes, setting records-per-block to 1 will cause significant unused space in every data block. Please see P110420 for more information.
Where are database maximums documented?
Database maximums are documented, for Release 10, in the ?OpenEdge Data Management: Database Design Guide? in the chapter titled ?OpenEdge RDBMS Limits?. For Version 9, database maximums are documented in the ?Database Administration Guide and Reference? Manual, in the chapter titled ?Database Limits?.
Is Progress considering changing the 2 billion record maximum sometime in the near future?
In the short term Progress will address the issue such that the database handles this condition more gracefully. Longer term the maximum will be removed.
Are there services from Progress that can help identify best recommendations for creating a database structure and how to organize data in areas?
Technical Support is always available to assist customers with questions regarding database maximums, tools and administration, and recovery questions. However, the most effective way to plan database health is through a consulting engagement..