Consultor Eletrônico

Status: Verified

GOAL:

How to use the -bithold -bistall and proquiet bithreshold utilities

GOAL:

Managing exceptional before-image (BI) growth

GOAL:

How to keep the bi file from growing beyond the 2 Gig limit

GOAL:

How to prevent the before image file (.bi) from hitting the 2 GB progress limit

GOAL:

How to monitor bi growth by implementing startup parameters -bithold -bistall and proquiet bithreshold

GOAL:

Implementation of -bithold -bistall and the proquiet bithreshold parameters

FACT(s) (Environment):

Progress 8.x
Progress 9.x
OpenEdge 10.x
All Supported Operating Systems

FIX:

There are some documentation inconsistencies and resulting confusion concerning: -bithold -bistall database startup parameters and the proquiet bithreshold command.

This solution summarises how they are supposed to be used through the use of the following example scenario:

"a transaction is running that causes expansion of the before-image (BI) file - you hope fits into 1GB of bi space, but are not sure that it will."

The following database start up parameters, starts the database by setting a 500MB before-image (bi) threshold (-bithold) that will make the bi stop growing and all database activity stall (-bistall) when the threshold is reached:

$ proserve dbname -bithold 500 -bistall

Before this threshold is reached, a warning message similar to the following will appear that indicates 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 Administrator can bump up the threshold online with the "bithreshold" parameter, to allow for investigation of whatever is causing the unexpected BI growth (before) the system stalls again when the new bi threshold is reached. In this example, assume that we expect to have to go above 500MB and are hoping that the update finishes before too long. A reasonable course of action would be to bump the threshold to 800 MB. 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 results:

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 message numbers are different:
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)

Notice the -C syntax on "proquiet" and no dash on "bithreshold" command. Also consider carefully that a quiet point is being established here to allow the threshold to be changed and that the system is stalled while raising the bi threshold.

In practice, all three commands should appear in a script to make sure that they occur as quickly as possible with as little impact to the online system as possible.

If processing continues and the bi file continues to expand, the same 90% warning will be issued eventually and once the bi size reaches the 800 MB threshold value, the f.ollowing message appears in the .lg file:

Usr 45: BI File size has grown to within 90 percent of the threshold value of 800.0 MBytes. (9240) [message (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)

The database has been placed in a quiet point and it cannot be explicitly disabled. At this point, all updates to the database will be suspended until the threshold is raised or the database is shutdown, at which time bi roll back recovery takes place when the database is next accessed.

It is worth noting at this point:
- bistall 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. Some sites configure 3rd party enterprise monitoring tools to watch for both the .lg file for the 90% and stalled messages or Fathom Management product.
- without the -bistall parameter (ie only the -bithold specified), the database will automatically shut down once the bithold value is reached.
- Users trying to disconnect cannot do so until the quiet point has been released
- Users trying to connect are allowed to do so, but their session will be pending, until the stall is released. If left unattended, user slots (-n) could eventually run out and no further connections will be available.
- This quiet point can only be released by raising the bi threshold further or shutting down the database.
- although -bistall and -bithold can be specified on replication-enabled target datatabases and are honoured, the -bithreshold cannot be raised online against the target replication enabled database. The target will need to be shutdown and either started with a higher -bithold value or none at all. There is therefore no need to add the -bistall parameter to replication-enabled target database startup parameters.

In this scenario, you may have reached the safety point where you might be unwilling to allow any more bi log to be used, in which case the database should be shut down.

Always bear in mind, that a ballpark figure of at least twice the current bi size could be needed for successful roll back recovery, should the current open transactions fail to complete in time. For Progress Version 8.3 users, it is dangerous to allow the value to go beyond 1GB because crash recovery may not be possible (due to a need to expand the bi log beyond 2GB during the Physical and Logical undo operations). When starting off with a bi threshold larger than 1GB, a warning message will appear, similar to:

$ proserve dbname -bithold 1000 -bistall

SERVER : Before Image Threshold is greater or equal to 1GB.
SERVER : Crash recovery may not be successful in the event of a crash.

In version 9 and beyond, although the sum of the bi files can exceed 2GB, new bi exents cannot be added before the last VARIABLE bi extent is removed, which implies a truncation of the bi beforehand - which inturn means that existing bi space may be exhausted during roll back recovery (Physical and Logical undo). The only option in this case may be to stop the database and enable large files if the filesystem and Progress version supports it.

For example, assume that we know your transaction is long-running, that it does infrequent updates during its long run and that you are completely confident you can go to 1.2GB and not take any risk. The BI threshold could .then be raised above 1GB to 1.2GB with the command:

$ proquiet dbname -C bithreshold 1200

with the following .lg results:

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)

Note that immediately after this command is issued, processing is continued. Also note that, contrary to some statements in some of the documentation, it is not necessary (in fact not allowed) to enable and disable a quiet point when you adjust the bi threshold once the database is stalled (ie the -bithold has been reached). The stall itself causes an implicit quiet point and the (only) command allowed is:

$ proquiet dbname -C bithreshold <new bithold value>

At this point in the example, the long transaction completes. The BI file stands at, say, 1.1GB with a threshold at 1.2 GB.
While there were reasons to allow the BI file to grow above 1GB, an offline "PROUTIL dbname -C truncate bi" command to push the value back down below 1GB should be scheduled at some time in the near future. Since the database startup script has -bithold 500, the BI threshold will be reset to a safe 500MB value once the database is restarted.

However, if it were decided to issue an emergency shutdown of the database (say, the long transaction was not completing), then either:
a.) [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).
or
b.) start the database, ensuring enough bi space for the recovery (as in option "a" above), but without the -bitstall and -bithold parameters.
Otherwise it is very likely that the crash recovery will exceed the bithold value 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).