Kbase 15124: Why is my .bi file growing so large ?
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
28/04/2011 |
|
Status: Verified
GOAL:
Why is my .bi file growing so large ?
GOAL:
Why is my .bi file hitting the 2Gb limit ?
GOAL:
How to troubleshoot an excessive Before Image file growth ?
GOAL:
How can I stop the BI file from growing excessively ?
FACT(s) (Environment):
Progress 8.x
Progress 9.x
OpenEdge 10.x
All Supported Operating Systems
FIX:
How the .bi file works.
When a new .bi file is created, it is automatically allocated 4 clusters (Prior to Progress 9.x the default cluster size was 16KB. It is now 512KB by default).
The .bi file clusters are managed as a doubly linked list (i.e., it contains forward and backward pointers). When a transaction is started, the information is stored in the first cluster. When this cluster is full, it then moves to the next cluster. Finally, when all four clusters are filled with pending transactions, a decision needs to be made to either extent the .bi file or reuse space, if possible.
If the first cluster has no active transactions, then this cluster is reused, marked as the "last" cluster, thus making cluster 2 the "first" cluster in the .bi file for the next "extend or reuse" decision.
However, if the cluster still contains incomplete transactions, then this cluster cannot be re-used. When this happens, the .bi file grows by another cluster - in this example, cluster 5. When cluster 5 is full, the same decision process is exercised again.
If cluster 1 still contains pending transactions, then the .bi file is again extended. Eventually, the transactions in the first cluster will be committed, and will then be available for re-use, so no additional clusters need to be added at this checkpoint.
So why would the pending transactions in cluster 1 sit there for so long?
One simple example is that the user who initiated the transaction walks away from the desk while the application is waiting for user input needed to complete the transaction. Since this cluster can never be "skipped over" (it is managed as a doubly linked list), the .bi file will continue to grow as new clusters are needed.
Sometimes there is no way to avoid the .bi file being extended; there just isn't enough time to commit transactions and make the cluster available before it is needed again by other transactions.
Sometimes a .bi file can grow extremely large when a schema change is done on a large database (1 Gb or more).
To address this, starting in 8.2A Progress, "Fast Schema Change" was introduced to make schema changes faster and not use as much .bi space. In version 9, "Schema Versioning" was introduced, which only uses .bi space in case an index is added to or deleted from a database table, but not when fields are added or deleted.
Please bear in mind that Progress versions prior to 9 limited the size of the entire bi file to no more than 2 gig, no matter how the bi extents were physically arranged. In other words, the sum of the bi extent sizes could not exceed 2 gig. In version 9 the bi extents are treated differently: the sum of the bi extents can exceed 2Gb, and starting version 9.1C (with the introduction of Large Files Support), each extent can exceed 2Gb if largefiles are enabled at database level and the OS supports largefiles.
Excessive Bi file growth typically occurs when a transaction is started and remains active or open for a long period of time.
This is because the Bi file is organized in sections called clusters.
As each cluster is filled with Bi notes, the Bi file is extended to provide more clusters. To minimize Bi file growth, clusters are re-used, but only when all Transactions to which the Bi notes belong, are committed to the database and closed.
As Bi notes are written serially to the Bi file then clusters can only be re-used serially. This means it only takes one active transaction to remain open for Progress to keep adding new clusters as other users open and close transactions.
There are two main causes of long duration transactions:
Use of ESQL or ODBC.
The way in which the application is coded. "A transaction is a unit of work which must complete in its entirety or not at all"
* Sometimes data is imported from a file in one trans.action, best to do it as one transaction per record.
* Sometimes the scope of the transaction is too big. This can be split into smaller transactions
* Sometimes the size of the transaction is a function of the size of the underlying database. As the Database gets bigger so does the transaction.
* Sometimes a transaction is started at login and does not end until logout, possibly extending for the entire day.
* Sometimes users start a transaction and then leave to do something else, or they wait for the next caller to ring.
In case of excessive Bi growth, start promon; R&D; 1(Status display); 4(Processes/client...); 3(Active transaction). You should see a list of users with open active transactions. A transaction time of over 10 min would be unusual.
Users who have open transactions can be traced using promon as above and asked about those time spans.
Finding these users, knowing exactly why their transaction take so long and cutting this transaction short is often the best solution to fixing excessive Bi growth.
Starting from Version 8.3, using the Recovery log Threshold (-bithold) startup parameter sets the maximum size to which BI files can grow.
Once the threshold is reached, the database performs an emergency shut down. This mechanism ensures that there will be enough disk space to perform database recovery..