Call us: +49 40 30 68 03 – 0 | E-mail:
Feel free to contact us or follow us
 

Basic OpenEdge DB Tuning Part II: BI Cluster Size

18 Jan Basic OpenEdge DB Tuning Part II: BI Cluster Size

Posted at 15:28h in Database tuning by

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.

 

blog_openedge_tuning_bi_cluster_thumb

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.

blog_openedge_tuning_bi_cluster_img2

Hint:

  • 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.
blog_openedge_tuning_bi_cluster_img3

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:

 

        1. Start promon (promon mydb)
        2. Choose R&D
        3. Choose other displays
        4. Choose 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.

blog_openedge_tuning_bi_cluster_img4
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:

        1. Shut down DB
        2. run: 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
'-numcheckpointstats'

 

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:

      • -bithold 2000
      • -bistall

 

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 ?
      • Use proquiet -C bithreshold n command 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:

 

        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

Klaus Erichen, IAP GmbH, Germany, 11 Jan 2018

 

For questions send a mail to: