Database Administrator's Guide

*_FLUSH_SEC background flush rate reduces contention

In FairCom DB V11.0 and later, the following background flush thread configuration options remove the dirty cache page flushing overhead from the checkpoint logic, and place this important requirement on these new dedicated background threads:

NONTRAN_DATA_FLUSH_SEC
NONTRAN_INDEX_FLUSH_SEC
TRAN_DATA_FLUSH_SEC
TRAN_INDEX_FLUSH_SEC

This change reduces the time that other threads are blocked waiting to acquire the update list mutex and has smoothed out performance curves for high-speed systems. If you would like a more thorough refresher on this change, please review this topic in the V11.0 Update Guide: Controls for Performance AND Safety of Non-Transaction Updates.

Configuring background flush at a given rate

Starting with FairCom DB V11.5, it’s now possible to configure a fixed rate of background flushing of updated data cache pages and index buffers, which provides the ability to further smooth out performance curves, reducing periodic drops in performance. The two parameters that can be specified for the background flushing are:

• msec - The time in milliseconds to defer after flushing, and
• nfls - The number of flushes after which the defer occurs.

With this new change, the keywords now support specifying these new parameters using the syntax rate=<msec>/<nfls>.

For example, to defer for 1 millisecond after every two flushes of updated TRNLOG data cache pages, use this option:

TRAN_DATA_FLUSH_SEC rate=1/2

The rate value sets a maximum speed at which the background flush threads will write updated buffers to disk. The goal is to flush updated pages quickly enough that the total number of updated buffers does not become extremely large (which could cause the number of transaction logs to increase and could cause checkpoints to take more time to build the list of updated pages and their corresponding transaction numbers and write them to disk), without flushing at too fast a rate, which would impact performance. The msec and nfls values can be used to set the maximum rate of flushing. FairCom suggests starting with rate=1/1, which means delay for one millisecond after every buffer flush. In our testing, we have found 1/1, or 1/2 to be the optimal settings.