18 Jan Basic OpenEdge DB Tuning Part II: BI Cluster Size
Abstract: In an OpenEdge DB the BI Cluster changes (checkpoints) are write intensive and costly. Increase BI Cluster size, so that a cluster needs 5 minutes or longer to fill up.
PART 1 – FUNCTION OF BI FILE
When it comes to writing in an OpenEdge Database, the BI Cluster Size is critical and could be a bottleneck.
The BI (Before Image) file is the transaction log, which allows to do a rollback in case of an error. It works in the following way:
- Copy a record to BI file
- Change record in DB
- Write a note in BI, that everything is OK
The BI file has the highest write activity in an OpenEdge DB.
Hint: When using an Enterprise DB, start Before Image writer (
probiw mydb) and two Asynchronous Page Writers (
proapw mydb) is a must.
The BI file is organized in larger chunks, called cluster. When you start a DB, 4 cluster are created. These 4 cluster have a default size of 512 kB, so the resulting default BI file after DB start is about 2 MB.
These clusters are organized in a logical ring, so writing in DB fills up a cluster, then the active one is closed and the next one is used. Close one cluster and opening the next is called checkpoint.
When a transaction has a longer duration, then the active cluster stays open (and could not be skipped) – so a new cluster must be inserted.
- On very high loaded systems the BI file size can reach several hundred megabyte.
- When the BI file has a stable size (that means every day it reaches the same size), then never truncate it - increasing size after truncate cost time.
This explained, why the BI file is often growing over time. Sometimes a transaction runs for hours and the BI file grows to GB.
Hint: When the BI file of a Workgroup DB reaches the 2 GB limit, you can add multiple 2 GB BI files offline (
prostrct add) to allow recovery during restart.
PART 2 – TUNING BI FILE
The default cluster size of 512 kB is much too small for fast import or many active users. When checkpoints occur too often, the DB is waiting for sync to disc and everything stops or is slow.
Target is, that the time between 2 checkpoints is at least 5 minutes during periods of high activity.
Rule of Thumb: Setting bi cluster size to 20000 (about 20 MB) is a good starting point for almost every production DB. (The size is automatically rounded to a multiple of 16, there are no magic numbers like 2^n)
Where to measure the duration of checkpoints:
- Start promon (promon mydb)
- Choose R&D
- Choose 3. Other displays
- Choose 4. Checkpoints
This image shows checkpoint screen from a DB with 100 active users and a bi cluster size of 16 MB (screen may look different in your OE version).
This is OK: The ‘Len’ (length) is larger then 5 minutes and the number in ‘Flushes’ row are 0.
Rule: When ‘len’ value over the day is below 300 seconds or more than a few flushes occur, increase BI cluster size.
To set the BI cluster size permanently:
- Shut down DB
proutil mydb -C trunc -bi 20000
Also have a look at:
- Promon, 5, ‘Buffs Flushed’ entry
- Per hour uptime there should be only a few dozen flushes
- VST ‘_Checkpoint’ (see below)
- Check current cluster size in promon, 7, ‘Before image cluster size (kb)’
For long time checkpoint monitoring using ‘_Checkpoint’ VST see this KB article
and consider to increase the number of entries in this VST using DB start Parameter
Summary: In an OpenEdge DB the BI Cluster changes (checkpoints) are write intensive and costly. Increase BI Cluster size, so that a cluster needs 5 minutes or longer to fill up.
SUPPLEMENT 1: STOP BI FILE FROM DISC FLOODING
When a transaction hangs or a very large transaction happens, the BI file can grow more or less unlimited.
Problem: When your disc is full with BI, the DB shuts down and due to lack of space rollback can not happen.
Application is down, users are angry, management is near madness… Bad situation…
To avoid this, set the following parameter during start of the DB:
How it works:
-bitholdtells the DB to not grow the BI file beyond 2000 MB.
-bistalltells the DB to not shut down when reaching the threshold, but to freeze any action (this is called stalling the DB).
- Removed the problem or decide the growth is OK ?
proquiet -C bithreshold ncommand to increase threshold
- How to do this is explained in details in this KB article.
How to do this is explained in details in this KB article .
SUPPLEMENT 2: MAKE YOUR DB INCREDIBLY FAST
There may be situations, where you need the DB FAST, for mass importing, updating or Dump/Load a DB.
I know these two options:
- When you have a valid copy / backup of the DB, use the ‘no integrity’ option (
-i DB start parameter). This will speed up your DB a few hundred percent.
Warning: A crash with -i will make the DB invalid and you need to restore it.
- Set BI cluster to 1 GB and use SSD disc(s) for the BI file (and DB if possible) can also help a lot – and is keeping the DB integrity.
For more information on OE DB tuning see Part I: Basic OpenEdge DB Tuning Part I: -B / Cache
For questions send a mail to: *protected email*