Proper sizing of the primary recovery area, also known as the before-image file, is important to your overall database system. This area is responsible for the recoverability of your database on an ongoing basis. Because the primary recovery area is written to frequently, if it is on a slow disk update performance will be affected. The size of this area varies depending on both the length of transactions and the activity on your system.

The primary recovery area is made up of clusters, which are tunable in size. When records are modified, notes are written to this area. If a problem occurs, or if the user decides to "undo" the changes, this area is used to ensure that no partial updates occur.

For example, assume you want to modify all of the records in a table to increase a value by 10 percent. You want this to happen in an all-or-nothing fashion because you can not determine which records are modified if the process terminates abnormally. In this case, you have one large transaction that modifies all of the records. If a problem occurs during the transaction, all of the modifications are rolled back to the original values. Why is this important? If you have several of these processes running simultaneously, the primary recovery area can grow substantially.

The structure of the area is a linked list of clusters. The smaller the cluster size the more frequent the checkpoints occur. A checkpoint is a synchronization point between memory and disk. While there is a potential performance benefit from infrequent checkpoints, this must be tempered with the amount of time it takes to recover the database. Large cluster sizes can also increase database startup and shutdown time when the database needs to back out incomplete transactions or perform crash recovery.

The best way to determine the before-image cluster size is to monitor the database at the time of the day when the most updates to the database are being made, and review the duration of your checkpoints throughout the week.

Ideally, checkpoints should not happen more than once every two minutes. If you are checkpointing more often, you should increase your before-image cluster size. This does not mean you should decrease the cluster size if it is happening less frequently. The default of 512KB is fine for smaller systems with low update volume, but a value of 1024KB to 4096KB is best for most other systems. The cluster size can be modified from small (8KB) to large (greater than 256 MB).

The cluster size influences the frequency of the checkpoints for the database. As users fill up a cluster with notes, they are also modifying shared memory. The page writers (APWs) are constantly scanning memory, looking for modified buffers to write to disk. At the first checkpoint, all of the modified buffers are put in a queue to be written prior to the next checkpoint. The buffers on the modified buffer queue are written by the page writers at a higher priority than other buffers. If all of the buffers on the queue are written prior to the next checkpoint, it is time to schedule the current modified buffers. If all of the buffers are not written, then you must write all of the previously scheduled buffers first, and then schedule the currently modified buffers. If you are checkpointing at the proper frequency and you are still flushing buffers at checkpoint, you should add an additional APW and monitor further. If adding the APW helps but does not eliminate the problem, add one more. If adding the APW does not help, look for a bottleneck on the disks.

The format of the primary recovery area has been discussed, but its size has not. There is no formula for determining the proper size because the size of the area is dependent on the application. Progress Software Corporation recommends that you isolate this area from other portions of the database for performance reasons. If you only have one database, you can isolate this area to a single disk (mirrored pair), as the writes to this area are sequential and would benefit from being placed on a single nonstriped disk. If you have several databases, you might want to store your primary recovery areas on a stripe set (RAID 10) to increase throughput.