Reduced Flushing of Updated Data and Index Cache Pages

The c-tree Server follows a buffer aging strategy, which ensures updated cache pages for transaction-controlled files are eventually flushed to disk. The factors that affect when a buffer is flushed include the number of times a buffer has been updated and the number of checkpoints that have occurred since the buffer was last flushed. This section describes ways to tune the c-tree Server's buffer aging strategy to avoid unnecessary flushing of updated buffers for transaction-controlled files.

TRANSACTION_FLUSH Server Configuration Keyword

The TRANSACTION_FLUSH server configuration keyword controls the aging of updated buffers based on the number of times a buffer has been updated since it was last flushed.

TRANSACTION_FLUSH <num_updates> sets the maximum number of updates made to a data or index cache page before it is flushed. The default value is 100. Increasing this value reduces repeated flushing of updated cache pages that may occur in a system that maintains a high transaction rate with a pattern involving frequently updating the same buffers.

CHECKPOINT_FLUSH Server Configuration Keyword

The CHECKPOINT_FLUSH server configuration keyword controls the aging of updated buffers based on the number of checkpoints that have occurred since the buffer was last flushed.

CHECKPOINT_FLUSH <num_chkpnts> sets the maximum number of checkpoints to be written before a data or index cache page holding an image for a transaction controlled file is flushed. The default value is 2. Increasing this value avoids repeated flushing of updated cache pages that may occur in a system that maintains high transaction rates. When CHECKPOINT_FLUSH is increased, the c-tree Server automatically detects the reliance on previous transaction logs and increases the active log count as needed provided that the FIXED_LOG_SIZE server configuration keyword is not enabled.

The following formula estimates the number of logs required to support unwritten updated cache pages:

Let:

CPF = CHECKPOINT_FLUSH value (defaults to 2)

CPL = # of checkpoints per log (typically 3 and no less than 3)

MNL = minimum # of logs to support old pages

Then:

MNL = ((CPF + CPL - 1) / CPL) + 2, where integer division is used

Note: The c-tree Server does not use an actual formula to determine the number of logs to retain. It detects the actual "recovery vulnerability" state of the data to determine what logs are required should a need for recovery occur. This can vary depending upon transaction volumes, and the number of "dirty" cache pages retained in the logs that have not been flushed to the data and index files on disk yet.

Example

CPF=2,  CPL=3  => MNL = 3 (but the server enforces a minimum of 4)

CPF=19, CPL=3 => MNL = 9 active transaction logs