Before Image Concepts
No matter how many BI extents you have created for your database the BI is treated as one logical entity. The BI consists of a number of clusters and each cluster is made up of a number of blocks.
Every time a BI cluster is filled (no more free blocks) OpenEdge executes a database checkpoint. A checkpoint forces a sync between memory buffered copies of database and BI blocks with the actual physical blocks on disk.
In an ideal situation checkpoints should happen no closer than 2-3 minutes apart and no changed blocks should exist that must be written (flushed) to disk. Depending on the level of write activity in your database reaching the ideal can range from very easy to nearly impossible. Most applications will trend much more heavily towards the easy side of the scale.
Before You Begin
There are a number of easy steps to take to see immediate performance improvements. Properly tuning some of these values will of course take more research but simply enabling these options will show an improvement over the defaults.
BI buffers are used to allow more work to happen in memory before being written to disk. The number of BI buffers is determined by the -bibufs startup parameter and each buffer uses one BI block size of memory.
A reasonable starting point is to make sure the -bibufs parameter is set to at least 200. The default value of 20 is far too low for most databases.
You can monitor your database during peak times to determine if you could benefit from more BI buffers by using ProTop or promon to look at the busy buffer waits and no buffers available metrics. Note that some small number of buffer waits are likely to exist in high volume applications so the goal is not to eliminate them completely but to keep the numbers as low as possible.
Make sure that you are starting a BI writer (BIW) for your database. The BIW will help offload some of the BI related activity from the rest of the OpenEdge database clients. To enable a BIW for your database you can check the appropriate box in OpenEdge Explorer/Management or add the following command to your database startup script:
APWs (Asynchronous page writers
You can start one or more APWs per database to assist with writes to the database files. Without the appropriate number of APWs enabled the individual OpenEdge clients must share the responsibility for writing to the database.
To enable one or more APWs for your database you can specify the desired number in OpenEdge Explorer/Management or add the following command to your database startup script (once per APW):
Why Is My BI Growing?
This OpenEdge KB discuss the concept in much more detail but here is a high level explanation.
If your database only has transactions open for a short period of time the BI clusters will be reused and the BI files should stay close to the same size. If you have transactions open for extended periods of time those clusters will not be reused and more clusters will be added to the BI files.
The root cause is that OpenEdge treats the BI as a serial list of clusters instead of loosely linked blocks like other databases do. As long as one transaction remains open in a cluster none of the subsequent clusters can be reused.
Monitor your database for transactions that have been open for more than a short period of time and correct the associated code. If you don't have access to source code you will need to contact your application provider so they can correct the issue.
You can disconnect the user from the database using proshut or promon but you need to be aware of any transaction rollback that may occur. Because the code is obviously flawed in regard to transaction scope you should be aware that you might end up with inconsistent application data. OpenEdge will make sure the database is transactionally consistent but the application might be using multiple transactions to accomplish one business transaction.
In Between Checkpoints
During the time between checkpoints OpenEdge clients can modify database blocks and APWs can scan for changed blocks to write to disk. If there is not enough time between checkpoints for the APWs to write the changed blocks you will end up flushing buffers at the start of the checkpoint.
When buffers are flushed at checkpoint all write activity pauses until the checkpoint is complete. This is most often noticed by the application "hanging" on a semi-regular schedule. You can monitor the most recent checkpoints and historical BI totals by using ProTop or promon.
How records are modified
- A transaction is opened on the database.
- The buffer pool block(s) that contain the record are locked. If a block is locked by another process the client will retry a number of times (-spin) to lock the block and then sleep before trying again.
- A BI buffer is locked. If all of the BI buffers are locked the client must wait until one becomes available. If filled buffers are available the client will flush that buffer to disk.
- Changes are written to the buffer pool block and the BI buffer.
- The locks on the BI buffer and buffer pool are released.
- Repeat this process (from step 2) until all records for the transaction are modified.
- The transaction is marked complete.
What the APWs do
- APWs scan the database buffer pools for modified blocks.
- If modified blocks are found the APW attempts to lock the specific block just like a client would for updates or reads.
- The APW writes the modified block to disk.
- This process will completed until the APW times out or reaches a limit of blocks.
- The APW goes to sleep for a period of time before the cycle starts again.
What the BIW does
- The BIW scans the BI buffer pool for modified blocks.
- If modified blocks are found the BIW attempts to lock the specific block.
- The BIW writes the modified block to the appropriate BI cluster and block.
- This process will completed until the BIW times out or reaches a limit of blocks.
- The BIW goes to sleep for a period of time before the cycle starts again.
When The Checkpoint Happens
When there are no more free blocks in a cluster the database will trigger a checkpoint. All database writes are paused for the duration of the checkpoint in order to guarantee database consistency.
If you have no modified buffers or very few modified buffers, properly tuned disks and proper OS sync settings this process should happen very quickly. Otherwise the process of writing checkpoint data can cause a noticeable pause in your application.
Identifying the root issue is crucial because simply increasing the number of APWs or increasing the cluster size can actually make the problem worse if you have an underlying disk or sync issue.
Controlling Checkpoint Length
The length of a checkpoint is directly related to the BI cluster size. Increasing the cluster size allows more data to be written before a checkpoint must occur. This also increases the amount of time between checkpoints, allowing the APWs more time to write changed database blocks to disk.
Your BI block size should always be set to 16 (16K), smaller values will degrade performance. Valid values for the BI cluster size range from 16 (16K) to 262128 (just under 256MB). You can alter the BI cluster size when the database is offline by using the proutil command below:
proutil YourDB -C truncate bi -bi ClusterSizeInKB -biblocksize 16