How to use the -bithold -bistall and proquiet bithreshold utilities
While -bithold -bistall can be used with WorkGroup and Enterprise databases, being able to raise the before-image threshold online requires the PROQUIET feature which is only available with an Enterprise Database License and an OpenEdge Replication Plus license is required to run PROQUIET against a target replication enabled database.
When used with a non-Enterprise license, never-the-less the database can still be started with -bithold set to at a value (MB) to prevent the bi file growing unattended. Together with -bistall, the database will stall when it reaches this size in order that further information can be gathered as detailed below in order to isolate suspects. However the database will need to be shutdown and restarted which would undo the large transaction(s) which will most likely need additional bi space to be added before doing so. While this is not ideal for production systems at least this halts and isolates at the time until the errant application code has been isolated.
This Article demonstrates how the use of the proquiet bithreshold command interacts with the database startup parameters -bithold -bistall through the use of the following example scenario:
"A running transaction causes expansion of the before-image (BI) file - you hope fits into 500 MB of bi space, but are not sure that it will."
The database is started by setting a 500 MB before-image (bi) threshold (-bithold) that will stop the bi growing when the bi cluster chain reaches this size and
All OLTP database activity stalls (-bistall) when the threshold is reached:
$ proserve dbname -bithold 500 -bistall
The transaction starts and the bi chain grows.
Before the bi threshold is reached
A Warning message will be written to the database lg file indicating that the bi file is approaching the established threshold (500MB):
BI File size has grown to within 90 percent of the threshold value of 500.0 MBytes. (9240) [message (6559) in version 8]
At this point the bi threshold can pro-actively be bumped up online with the "bithreshold" command before the current "bithold" value is reached. In a production situation this allows time for investigation of whatever transactions are causing the unexpected BI growth, while still containing the maximum size the bi file can grow to. When raising the bi threshold, consider carefully that a quiet point is first being established explicitly to allow the bi threshold to be changed and that the database is stalled while raising the bi threshold until the quiet point is again explicitly released allowing transaction processing to ensue (before) the system stalls again when the new bi threshold is reached. In practice, all three commands should appear in a script to ensure that they occur as quickly as possible with as little impact to the online system as possible.
The following commands will allow the bi threshold to expand to 800 MB, before the 500MB bi threshold is reached:
$ proquiet dbname -C enable
$ proquiet dbname -C bithreshold 800
$ proquiet dbname -C disable
The following log (.lg) file entries confirm the action:
QUIET 31: Quiet point request login by root on <tty>. (5569)
QUIET 31: BI File Threshold size has been changed from 500.0 MBytes, to 800.0 MBytes. (9242)
QUIET 31: Logout by root on <tty>. (453)
Usr 45: Forward processing continuing. (6561)
In Version 8 the messages are:
BROKER 0: Quiet point has been enabled by the broker.(5583)
QUIET 2: BI Threshold value has changed to 838860800, was 524288000 . (6556)
BROKER 0: Quiet point has been disabled by the broker.(5584)
Once the quiet point has been released, transaction processing resumes and the bi file continues to expand, the same 90% of threshold warning will be issued eventually. If not pro-actively raised beforehand, the bi threshold is reached:
Once the bi threshold is reached:
- The following log (.lg) file entries are written:
Usr 45: BI File size has grown to within 90 percent of the threshold value of 800.0 MBytes. (9240) [(6558) in version 8]
Usr 45: BI File Threshold size of 800.0 MBytes has been reached. (9239)
Usr 45: Forward processing stalled until database administrator intervention. (6560)
- All transaction updates are suspended as the database has now been placed in a quiet point. It cannot be disabled until a decision is made to either:
- Shut the database down. When the database is next accessed bi roll back recovery takes place which will undo all open transactions, OR
- Raise the bi threshold further.
- Without the -bistall parameter (ie only the -bithold specified at database startup), the database will automatically shut down.
- With the -bistall parameter, while the database is stalled:
- Users trying to disconnect cannot do so until the quiet point has been released by raising the bithreshold value.
- Users trying to connect are allowed to do so, but their session will be pending until the stall is released.
- This quiet point can only be released by raising the bi threshold further or shutting down the database, it cannot be disabled with "proquiet -C disable".
Raising the bi threshold once bithold is reached
Continuing our example, assume that we know that while this transaction is long-running, it does infrequent updates during its long run and that you are confident you can afford to go to 1.2GB. Having reached the current -bithold value (800 MB), the BI threshold could then be further raised above 1GB to 1.2GB. Immediately after this command is issued, transaction processing will continue. It is not necessary to disable the quiet point.
$ proquiet dbname -C bithreshold 1200
The following. lg entries confirm the action:
QUIET 31: Quiet point request login by root on /dev/ttyp10. (5569)
QUIET 31: BI File Threshold size has been changed from 1000.0 MBytes, to 1200.0 MBytes. (9242)
QUIET 31: Logout by root on /dev/ttyp10. (453)
Usr 45: Forward processing continuing. (6561)
Notice that raising the bi threshold once the bi threshold has been reached, differs to the first part of our example where we pro-actively raise the bi threshold before the current limit is reached having been alerted that the bi file is currently at 90% of the value. This stall (-bistall) causes an implicit quiet point and the (only) command allowed at this stage apart from "proshut dbname -by" is: proquiet dbname -C bithreshold <new bithold value>.
At this point in the example, our long transaction completes bi clusters are released for re-use and transaction processing proceeds. The BI cluster chain is (say) 1.1GB with the current bi threshold set at 1.2 GB.
Database is shut down when bithold is reached
If instead of raising the bi threshold from 800MB to 1.2GB it was decided to issue a shutdown of the database (say, the long transaction was not completing), or the database was not started with -bistall.
- [recommended] Truncate the bi file, ensuring enough bi space for the recovery. Usually twice the current bi size, which is known in this case by the bithreshold value last set. Once bi recovery completes, start the database with the -bistall -bithold parameters
- [alternatively] Start the database, ensuring enough bi space for the recovery, but without the -bitstall and -bithold database startup parameters. If the database is started with the current -bistall value, crash recovery will exceed the existing database startup bithold value (500 MB in this example) in the Undo phase of crash recovery with messages similar to:
BROKER 0: BI file size is greater than the BI file threshold size. (6566)
BROKER 0: You must truncate the BI file or increase the BI threshold size. (6567)
BROKER ** This process terminated with exit code 1. (8619)
Further Considerations when using -bithold
- Although -bistall and -bithold can be specified on replication-enabled target databases and are honoured, the -bithreshold cannot be raised online against the target replication enabled database. The replication target database will first need to be shutdown and either started with a higher -bithold value or preferably none at all. There is no need to add the -bistall parameter to the replication-enabled target database startup parameters, as bi growth is tethered from the source database end.
- For Progress 8.3 databases, it is dangerous to allow the value to go beyond 1GB because crash recovery may not be possible due to the need to expand the bi log beyond the 2GB bi limit during the Physical and Logical Undo operations. When raising the bithreshold above 1GB in this version, don't raise it higher than at least 2 bi-cluster size below 2GB and take care that all open transactions have completed before shutting the database down. When starting the database with a bi threshold larger than 1GB in version 8, a warning message will alert to this fact:
SERVER : Before Image Threshold is greater or equal to 1GB.
SERVER : Crash recovery may not be successful in the event of a crash.
- In Progress 9 and later, although the sum of the bi files can exceed 2GB:
- New bi extents cannot be added before the unused variable bi extent is removed
- To remove extents requires the bi file to be truncated first.
- When the bi file is truncated, remaining bi space may be exhausted during roll back recovery (Physical and Logical undo). In this crashed state,
- More bi extents can be added without having to remove the variable bi extent.
- Large files can be enabled on the database allowing the variable extent to grow beyond 2GB. The filesystem, Progress/OpenEdge version and installed database license supports it.
- The -bistall database startup parameter should be used only if there is constant management of the underlying system so that if there is a stall it can be handled immediately. Typically OpenEdge Management or 3rd party enterprise monitoring tools are configured to monitor the database .lg file for the 90% and stalled messages and trigger appropriate next actions.