Progress
Database Administration
Guide and Reference
Before-image I/O
Before-imaging is always enabled to let the database engine 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. The engine 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:
Monitoring BI Activity
Use operating system utilities to monitor the amount of I/O activity on the disk where the BI files reside. Use the PROMON utility to monitor specific BI activity. Use the R&D option BI Log Activity. Figure 14–8 shows a sample display.
Figure 14–8: PROMON BI Log Activity Display
![]()
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 the database engine 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 perform 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 perform the AI write.
- The Suppress BI File Write (-
Mf
) parameter’s timer expires before the buffer is filled.Moving the BI File
The "Disk I/O" section explains the performance benefits of distributing database files across multiple disks. You help balance the before-image I/O load by placing the BI extents on a separate disk.
Using a Before-image Writer
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 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. See "Starting Up and Shutting Down," for instructions on starting and stopping a BIW.
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 parameter 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 the database engine writes data to the BI file, these clusters fill up. When a cluster fills, the engine 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 the engine 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:
Follow these steps to change the cluster size:
- Use the PROSHUT command or the PROMON Shutdown a Database option to shut down the database.
- Enter the following command:
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 16384 are common.
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, the database engine, by default, creates four BI clusters, each of which is 512K. As the engine fills a cluster, the cluster is checkpointed, and the engine writes to the next cluster on the chain. Figure 14–9 illustrates the default BI clusters.
Figure 14–9: BI Clusters At Startup
![]()
In some cases, the database engine cannot write to the next cluster because the next cluster contains an active transaction. When the engine cannot use the next cluster on the chain, it creates a new cluster and begins writing to it. While the engine creates the new cluster, no database update activity can occur, thus impacting database performance. Figure 14–10 illustrates how BI clusters fill over time.
Figure 14–10: BI Clusters Over Time
![]()
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 91,7504 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 the database engine from adding clusters on an as-needed basis. The BI file is truncated:
Follow this step to increase the number of BI clusters:
Increasing the BI Block Size
The database engine reads and writes information to the BI file in blocks. Increasing the size of these blocks allows the engine 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:
- Use the PROSHUT command or the PROMON Shutdown a Database option to shut down the database.
- Enter the following command to change the BI block 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.
For detailed information on this command, see the description of the PROUTIL utility in Database Administration Utilities."
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
When the Delayed BI File Write (-
Mf
) startup parameter is set to zero, use the Group Commit technique to increase performance. This technique assumes that for the benefit of overall performance, each individual transaction can take slightly longer. For example, when a transaction begins to commit and spools its end note to the BI buffer, it waits a short time until one of two things happen: it fills the buffer and is written to disk, or a few other transactions complete and store their end notes in the BI buffer so that a single synchronous write commits all the transactions. Use the Group Delay (-groupdelay
) startup parameter to set the amount of time (milliseconds) the transaction waits.If the Group Commit technique does not provide sufficient improvement, you can improve performance on a busy system by delaying BI file writes with the Delayed BI File Write (-
Mf
) startup parameter.By default, the database engine 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 might incur 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).For more detailed information on the -
Mf
parameter, see Database Startup Parameters."Setting a BI Threshold
When an application performs large schema updates or large transactions, the BI clusters can grow in excess of 2GB. If a crash occurs during such an operation, the recovery process might require twice the amount of disk space as the BI log was using at the time of the crash. Often this space is not available, 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 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 does not want the database to perform an emergency shutdown when the Recovery Log Threshold limit is reached. The Threshold Stall (-
bistall
) startup parameter quiets the database when the recovery log threshold is reached. Instead of an emergency shutdown, the database stalls forward processing until the database administrator intervenes. This provides 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:
- Use the PROQUIET command to enable a database quiet point:
db-name is the name of the database for which you want to adjust the BI threshold.
NOTE: For more information on, and complete syntax for, the PROQUIET command, see Startup and Shutdown Commands."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.
- Adjust the threshold size using the -
bithreshold
parameter:
db-name
Specifies the name of the database for which you want to adjust the BI threshold.
n
Specifies the new value for the threshold.
- Use the PROQUIET command to disable the quiet point:
For more information on, and the complete syntax for, PROQUIET, see Startup and Shutdown Commands."
Copyright © 2004 Progress Software Corporation www.progress.com Voice: (781) 280-4000 Fax: (781) 280-4095 |