Before-Image File Size Limits
How the .bi file works:
- In versions prior to Progress 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 cannot not exceed 2 gig.
- In Progress 9, Type I Storage Areas were introduced. This allows database bi extents to be treated differently: the sum of the bi extents can exceed 2Gb, and
- Starting Progress 9.1C, Large Files Support for Enterprise Database Licenses was introduced. When largefiles are enabled at the database level, every variable database extent can exceed 2Gb provided the OS supports and user limits allow largefiles.
When a before-image (.bi) file is created initially, 4 bi-clusters are automatically allocated:
- Prior to Progress 9.x the default bicluster size was 16KB,
- Since Progress 9.x it is 512KB (524,288) by default. The bi cluster size can be changed offline with:
$ PROUTIL -C truncate bi -bi <size in KB).
Before-image file clusters are managed as a doubly linked list,
containing forward and backward pointers. When a transaction starts, transaction information is stored in the first cluster. When this cluster is full, transaction notes are then written to the next cluster. Finally, when the initial four clusters are filled with pending transactions, a decision needs to be made to either extend the .bi file or reuse space, if possible:
Why would pending transactions in bicluster 1 sit there for so long?
- 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 first 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, no additional clusters need to be added at this checkpoint.
- Further explanation are on the topics of how transactions are written to a .bi file and database Checkpoints are provided in Articles:
- What is a Checkpoint?
- Why the .bi file grows larger than original bi with roll forward?
A bi file can grow large when for example a schema change is applied to a large database table (1 Gb or more). To address this:
- Starting in Progress 8.2A, "Fast Schema Change" was introduced to make schema changes faster and not use as much .bi space.
- Since Progress 9, "Schema Versioning" was introduced, which only uses .bi space when an index is added as active to or deleted from a database table, but not when fields are added or deleted.
- Schema Versioning: Why V9 schema changes are so fast
Excessive bi file growth only ever occurs when a transaction is started and remains active or open for a long period of time.
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 added when needed at each checkpoint.
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. Typically this scenario would be supported by excessive Checkpoints (LE 1 minute) and alleviated by increasing the bi cluster size.
Transactions have their before-image transaction notes recorded in the bi file so that we can undo during online processing and complete the Redo and Physical and Logical Undo bi recovery. As described above, the bi file is organized in sections called clusters, specifically biclusters.
As each cluster is filled with before-image transaction 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.Long duration transactions are caused by:
- Use of ESQL or ODBC.
- The way in which the ABL application transaction scope is coded.
"A transaction is a unit of work which must complete in its entirety or not at all"
Bi Growth will therefore occur when:
- Data is imported from a file in one transaction, as opposed to importing with one transaction per record
- The scope of the transaction all-or-nothing, one transaction, as opposed to splitting into smaller transactions
- The size of the transaction is a function of the size of the underlying database. As more records are added to tables over time, so the transaction size increases.
- The application code (e.g. triggers) get into a loop that blocks other parts of the code and never completes.
- A transaction is started at login and does not end until logout, possibly extending for the entire day.
- 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, the first step is to identify users running long transactions. For example:
- PROMON > R&D > 1 (Status display) > 4(Processes/client...) > 3(Active transaction).
- A list of users with open active transactions will be presented. A transaction time of over 10 minutes 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 takes so long and cutting this transaction short is the best solution to addressing excessive bi growth once the associated application code responsible has been isolated.
- Detailed troubleshooting advice is provided in Article Suggestions for investigating and identifying the cause of unexpected bi growth
Starting from Progress 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 unless the bistall database startup parameter is also used. This mechanism ensures (unexpected) excessive bi growth can be throttled. The following Article details how these database startup parameters can be configured and managed once the bi threshold is reached by way of example: