How to tune BI I/O performance

por | 3 enero, 2014

The following is extracted from the v8 documentation.

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.
You can 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. You help balance the before-image I/O load by placing the BI extents on a separate disk.
If you cannot use a multi-volume database, you can move a single-volume BI file to a different disk drive than the database file. You might also move the BI files to allow more room for other database files. Use the structure description file to define the file locations.

Using a Before-image Writer (Shared-memory Systems 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.
You can only run one BIW per database. You must manually start the BIW, but you can start and stop the BIW process at any time without shutting down the database.

Providing More BI Buffers
You 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 param.eter to 20. Increase it if there are any empty buffer waits in the PROMON Activity screen or in the 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. However, they also have significant drawbacks:

– Increased disk space usage for the BI file.
– Longer crash recovery periods.
– Longer checkpoint times. (Run APWs to eliminate this drawback.)

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

For 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. Cluster sizes from 512 to 4096 are the most beneficial.
You can also change the BI block size with this command. You might want to do so at this time. For more information, see the “Increasing the BI Block Size” section.

Increasing the Number of BI Clusters
When you create a new database or truncate 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. You can 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, you should 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

1 Enter the following command: proutil db-name -C bigrow n

For n, specify the number of BI clusters that you want to create for the specified database. PROGRESS creates four BI clusters by default. If you specify 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. However, if performance monitoring indicates that BI writes are a performance bottleneck, and your 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. If you have a single AI file and after-imaging is enabled when you enter this command, you must use the After-image Filename (-a) parameter to specify the AI filename.
You can also change the BI cluster size with this command. You might want to do so at this time. For more information, see the “Increasing the BI Cluster Size” section.
Typically, if you change the BI cluster size and block size, you should also change the AI block size. For information, see the “Increasing the AI Block Size” section.

Delaying BI Writes
On a busy system, you 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 proces.sing 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
You 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.

2 Adjust the threshold size using the -bithreshold parameter:

proquiet db-name -bithreshold n

n is the new value for the threshold.

3 Use the PROQUIET command to disable the quiet point:

proquiet db-name disable

See also How can the -bithold -bistall and the proquiet bithreshold be implemented?.

 

PROUTIL TRUNCATE BI qualifier
Performs the following three functions:
• Uses the information in the before-image (BI) files to bring the database and after-image
(AI) files up to date, waits to verify that the information has been successfully written to
the disk, then truncates the before-image file to its original length.
• Sets the BI cluster size using the Before-image Cluster Size (-bi) parameter.
• Sets the BI block size using the Before-image Block Size (-biblocksize) parameter.
Syntax
Parameters db-name
Specifies the database you are using.
-G n
Specifies the number of seconds the TRUNCATE BI qualifier waits after bringing the
database and AI files up to date and before truncating the BI file. The default wait period
is 60 seconds. You might specify a shorter period for practice or test purposes only.
Caution: Do not decrease the value for any significant database, because a system crash
could damage the database if the BI file is truncated before the writes to the
database and AI files are flushed from the operating system buffer cache.
-bi size
Specifies the size of the cluster in kilobytes. The number must be a multiple of 16 ranging
from 16 to 262,128 (16K to 256MB). The default cluster size is 524K. If you use a value
that is not a multiple of 16, PROUTIL rounds the value up to the next multiple of 16.
-biblocksize size
Specifies the size of the BI blocks in each buffer in kilobytes. The valid values are 1, 2, 4,
8, and 16. The default -biblocksize is 8K. A value of zero (0) tells PROUTIL to use the
default block size. The block size cannot be smaller than the database block size.
Notes • The Before-image Block Size (-biblocksize) parameter changes the BI block size so that
the database engine reads and writes the blocks as one block.
• Use the PROSTRCT STATISTICS qualifier to display the block size for a database.
• If you change the BI block size or cluster size before backing up a database, when you
restore the database, the blocks and clusters will be the size you specified before the
backup.
proutil db-name -C truncate bi { [ -G n ] | -bi size | -biblocksize size }
PROUTIL Utility
21–106
• PROUTIL reads all the BI blocks according to the size of the first block it reads. For
example, if the first BI block is 8K, PROUTIL reads and writes each block on an 8K
boundary.
• For