Feedback
Did this article resolve your question/issue?

   

Article

How to tune BI I/O performance

« Go Back

Information

 
TitleHow to tune BI I/O performance
URL NameP54984
Article Number000149672
EnvironmentProduct: Progress
Version: 8.x, 9.x
Product: OpenEdge
Version: 10.x, 11.x, 12.x
OS: All supported platforms
Question/Problem Description
How to tune BI I/O performance
How to monitor BI activity
How to provide more BI buffers (-bibufs)
How to increase the BI clustersize
How to increase the Number of BI Clusters
How to increase the BI Block Size
Steps to Reproduce
Clarifying Information
Error Message
Defect Number
Enhancement Number
Cause
Resolution
The following is extracted from Progress v8 documentation, which is also valid for later Progress OpenEdge releases:

Before-image I/O

Before-imaging is always enabled to let PROGRESS recover transactions if the system fails. This mechanism is extremely important for database reliability, but it creates a significant amount of I/O that can affect performance. In addition, before-image I/O is usually the first and most likely cause of I/O bottlenecks. PROGRESS must always record a change in the BI file before it can record a change in the database and after-image (AI) files. If BI activity creates an I/O bottleneck, all other database activities are affected.

Reduce the I/O impact of before-imaging by:
  • Moving the BI file to its own disk
  • Running a before-image writer (BIW) (shared-memory systems only)
  • Providing more BI buffers
  • Increasing the BI cluster size
  • Increasing the BI block size
  • Delaying BI writes
Monitoring BI Activity:

Use Operating System utilities to monitor the amount of I/O activity on the disk where the BI file or files reside. Use the PROMON utility to monitor specific BI activity. Use the R&D Option BI Log Activity:
 
promon > R&D > 2. Activity Displays > 5. BI Log

Look for the following potential problems:
  • Busy buffer waits
  • Empty buffer waits
  • High number of writes per second
  • High number of partial writes
A partial write occurs when PROGRESS must write data to the BI file before the BI buffer is full. This might happen if:
  • An APW attempts to write a DB block whose changes are recorded in a BI buffer that has not been written. Because BI notes must be flushed before the AI note is flushed, the APW writes the data in the BI buffer before the buffer is full so it can do the AI write.
  • An after-image writer (AIW) runs ahead of the BIW. Because BI notes must be flushed before the AI notes can be written, the AIW writes the BI buffer before it is full so it can do the AI write.
  • The Suppress BI File Write (-Mf) parameter's timer expires before the buffer was filled.
Moving the BI File:

Performance can benefit distributing database files across multiple disks. Help balance the before-image I/O load by placing the BI extents on a separate disk.
If unable to use a multi-volume database, move a single-volume BI file to a different disk drive than the database files.  Use the structure description file to define the file locations.

Using a Before-image Writer (Enterprise database licenses only):

The BIW is a background process that continually writes filled BI buffers to disk. Since writes to the BI file occur in the background, client and server processes rarely have to wait for a filled buffer to be written to disk. BIWs are optional, but highly recommended for improving I/O performance.  The PROGRESS server writes current information to the BI file through the current output buffer. When this buffer fills, the server places the buffer on the filled chain. The server then takes a new buffer from the empty chain and uses it as the current output buffer. If no empty buffers are available, the process must wait while a filled buffer is written to disk.  The BIW writes the filled buffers to disk and places them on the empty chain. By clearing out the filled chain, the BIW ensures that a supply of empty buffers is available to client and server processes.

Can run one BIW (Before Image Writer) per database. The BIW must be started manually from command line, via the AdminServer, or via a script.  The BIW process can be started and stopped at any time without shutting down the database.

Providing More BI Buffers (-bibufs):

Can increase the number of before-image buffers in the before-image buffer pool with the Before-image Buffers (-bibufs) startup parameter. Increasing the number of buffers increases the availability of empty buffers to client and server processes. In general, initially set this parameter to 20. Increase it if there are any empty buffer waits in the PROMON > Activity screen or in the PROMON > R&D > BI Log Activity screen.

Increasing the BI Cluster Size:

The BI file is organized into clusters on disk. As PROGRESS writes data to the BI file, these clusters fill up. When a cluster fills, PROGRESS must ensure that all modified database buffer blocks referenced by notes in that cluster are written to disk. This is known as a checkpoint. Checkpointing reduces recovery time and lets PROGRESS reuse BI disk space. Raising the BI cluster size increases the interval between checkpoints.  Raising the BI cluster size can reduce the I/O overhead of writing modified database buffers to disk. It also lets you defer writes and collect more changes before writing a block; this lets you write multiple changes with the same write.  Larger cluster sizes generally increase performance. They also have drawbacks:
  • Increased disk space usage for the BI file.
  • Longer crash recovery periods.
  • Longer checkpoint times. (Run APWs to eliminate this drawback.)
To determine the best before-image cluster size:

Refer to Article  How to determine an optimum before-image (BI) file cluster size  

Follow these steps to change the cluster size:
  1. Use the PROSHUT utility or the PROMON Shutdown a Database option to shut down the database.
  2. Enter the following command:
$   proutil db-name -C truncate bi -bi <size>
  • Specify the new cluster size in kilobytes.
  • The number must be a multiple of 16 in the range 16 to 262128 (16K-256MB). The default cluster size is 512K. 
  • The BI block size can also be changed at the same time with this command.  For more information, see the "Increasing the BI Block Size" section below.

Increasing the Number of BI Clusters:

When creating a new database or truncating the bi of an existing database, PROGRESS, by default, creates four BI clusters, each of which is 512K. As PROGRESS fills a cluster, the cluster is checkpointed, and PROGRESS writes to the next cluster on the chain.  In some cases, PROGRESS cannot write to the next cluster because the next cluster contains an active transaction. When PROGRESS cannot use the next cluster on the chain, it creates a new cluster and begins writing to it.  While PROGRESS creates the new cluster, no database update activity can occur, thus impacting database performance.

The BI clusters typically grow to their optimal number over time.  To calculate the current number of BI clusters for a database by dividing the BI physical file size by the BI cluster size. For example, a database BI file with a BI cluster size of 128K and a physical size of 917504 has 7 BI clusters.  Whenever the BI file is truncated, consider growing the number of BI clusters to its optimal size before restarting the database, thus preventing PROGRESS from adding clusters on an as needed basis. The BI file is truncated:
  • Automatically by PROGRESS when you start after-imaging (RFUTIL AIMAGE BEGIN).
  • Automatically by PROGRESS when you perform an index rebuild (PROUTIL IDXBUILD).
  • Manually (PROUTIL TRUNCATE BI).
Follow these steps to increase the number of BI clusters:
$   proutil db-name -C bigrow <n>
  • For n, specify the number of BI clusters to create for the specified database.
  • PROGRESS creates four BI clusters by default.
  • For example if specifying a BIGROW value of 9, PROGRESS creates an additional 9 BI file clusters for a total of 13 clusters.

Increasing the BI Block Size:

PROGRESS reads and writes information to the BI file in blocks. Increasing the size of these blocks allows PROGRESS to read and write more data at one time. This can reduce I/O rates on disks where the BI files are located.  The default BI block size (8K) is sufficient for applications with low transaction rates.  If performance monitoring indicates that BI writes are a performance bottleneck, and the platform's I/O subsystem can take advantage of larger writes, increasing the BI block size might improve performance.

Follow these steps to change the BI block size:
  1. Use the PROSHUT utility or the PROMON Shutdown a Database option to shut down the database.
  2. Enter the following command to change the BI block size.
$   proutil db-name -C truncate bi -biblocksize <size>
  • For size, specify the new BI block size in kilobytes.
  • Valid values are 0, 1, 2, 4, 8, and 16. 
  • Can also change the BI cluster size with this command at the same time.  For more information, see the "Increasing the BI Cluster Size" section.  
  • Typically, if changing the BI cluster size and block size, then change the AI block size as well. After-imaging has to be disabled to change the AI blocksize. Refer to Article  How to improve After Imaging (AI) performance

Delaying BI Writes:

On a busy system, one can significantly improve performance by delaying BI file writes with the Delayed BI File Write (-Mf) startup parameter.
By default, PROGRESS writes the last BI block to disk at the end of each transaction. This write guarantees that the completed transaction is recorded permanently in the database. On a system with little update activity, this extra BI write is very important and adds no performance overhead. On a busy system however, the BI write is less important (the BI block will be written to disk very soon anyway) and has a significant performance penalty.  Set the -Mf parameter to delay BI writes at transaction commit time. When -Mf is set to a positive value, the last BI record is guaranteed to be written to disk within the specified number of seconds. The record is written sooner if the user logs out or the system shuts down.

Note:  Suppressing the last BI write does not reduce database integrity. However, if there is a system failure, the last few completed transactions can be lost (never actually written to the BI file).

Setting a BI Threshold:

When an application performs large schema updates or uses undisciplined programming practices, the BI clusters can grow in excess of 1GB. If a crash occurs during such an operation, the recovery process requires twice the amount of disk space as the recovery log was using at the time of the crash. Often this is not possible, leaving the database in an unusable state.

Using the Recovery Log Threshold (-bithold) startup parameter sets the maximum size to which BI files can grow. Once the threshold is reached, the database performs an emergency shutdown. This mechanism ensures that there will be enough disk space to perform database recovery. All messages associated with the threshold are logged in the database log (.lg) file. These messages include:
  • The value of the threshold.
  • Warning message if the threshold is set above 1000MB.
  • Warning message when recovery log files are extended.
  • Message that a database shutdown is occurring because the threshold has been reached.
The recommended range is to set -bithold between 3% and 100% of the largest possible recovery log file size, rounded to the nearest cluster boundary. If the threshold is set above 1000MB, PROGRESS issues a warning message to the display and the database log (.lg) file. The system will check the total amount of BI clusters in use each time a new cluster is marked as used. If the No Crash Protection (-i) is set, the recovery log threshold parameter is set to the default (none) and cannot be overridden.

Enabling Threshold Stall:

Often a database administrator will not want the database to perform an emergency shutdown when the Recovery Log Threshold is set. The Threshold Stall (-bistall) startup parameter quiets the database when the recovery log threshold is reached. Instead of an emergency shutdown, the database will stall forward processing until the database administrator intervenes. This will provide the database administrator the options of shutting down the database, making more disk space available, and increasing the threshold amount. A message is added to the database log (.lg) file stating that the threshold stall is enabled.

Using PROQUIET to Adjust the BI Threshold:

Can adjust the value of the threshold by providing a valid threshold value for the PROQUIET command. The value can be increased above the current value or reduced to a value of one cluster larger than the recovery log file size at the time the PROQUIET command is issued.  Follow these steps to adjust the BI threshold:
  1. Use the PROQUIET command to enable a database "quiet point."
$   proquiet db-name enable
During a database quiet processing point all file write activity to the database is stopped. Any processes that attempt to start a transaction while the quiet point is enabled must wait until you disable the database quiet processing point.
  1. Adjust the threshold size using the -bithreshold parameter:
$   proquiet db-name -bithreshold n
n is the new value for the threshold.
  1. Use the PROQUIET command to disable the quiet point:
$   proquiet db-name disable

BI Performance Improvements: 

Upgrade to OpenEdge 11.3.3 or later where performance improvements on extending the bi file were enhanced on Solaris and AIX platforms. Refer to Article:
In OpenEdge 11.7.6 (when available) and 11.2.1, all supported platforms are enhanced to use this new mechanism. Refer to Article:
Workaround
Notes
Last Modified Date11/20/2020 7:33 AM
Attachment 
Files
Disclaimer The origins of the information on this site may be internal or external to Progress Software Corporation (“Progress”). Progress Software Corporation makes all reasonable efforts to verify this information. However, the information provided is for your information only. Progress Software Corporation makes no explicit or implied claims to the validity of this information.

Any sample code provided on this site is not supported under any Progress support program or service. The sample code is provided on an "AS IS" basis. Progress makes no warranties, express or implied, and disclaims all implied warranties including, without limitation, the implied warranties of merchantability or of fitness for a particular purpose. The entire risk arising out of the use or performance of the sample code is borne by the user. In no event shall Progress, its employees, or anyone else involved in the creation, production, or delivery of the code be liable for any damages whatsoever (including, without limitation, damages for loss of business profits, business interruption, loss of business information, or other pecuniary loss) arising out of the use of or inability to use the sample code, even if Progress has been advised of the possibility of such damages.