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.
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.
This explained, why the BI file is often growing over time. Sometimes a transaction runs for hours and the BI file grows to GB.
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.
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:
- The -bithold tells the DB to not grow the BI file beyond 2000 MB.
- The -bistall tells 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.
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:
- 1. 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.
- 2. 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