c-treeACE V11.0 Update Guide

Monitoring Delayed Durability Performance

SNAPSHOT Statistics

SNAPSHOT statistics data collection related to transaction log flushing have been updated as follows:

The LOG FLUSH REQUESTS includes a “data cache” value which is the number of log flushes instigated by the Delayed Durability requirement that no data image will go to disk until the log entries associated with the image have been flushed to disk. It includes a “# checks if ...” that shows the number of times a data cache write operation checked to see if a log flush was required.
A new section, DATA CACHE LOG FLUSH REQUEST DETAILS, breaks down the data cache flush requests into the operations that trigger the flush.

Below are excerpts from three different SNAPSHOT.FCS files:

The first using DELAYED_DURABILITY On, which sets FORCE_LOGIDX ON
The second with DELAYED_DURABILITY OFF and FORCE_LOGIDX ON
The third without DELAYED_DURABILITY OFF and FORCE_LOGIDX OFF

The same single-threaded application program was run that adds 100,000 ISAM records, each with three indices, and each add is in a transaction.

Recall that when an index has a file mode with ctLOGIDX enabled, additional transaction log entries permit automatic recovery to repair a damaged index at the site of the damage instead of rebuilding the entire index. ctLOGIDX affects the log flushing dynamics.

SNAPSHOT statistics can be output with the ctstat Statistics Utility -text option.


DELAYED_DURABILITY, which sets FORCE_LOGIDX ON
LOG FLUSH REQUESTS checkpoint/endlog/commit/abort tran: 127 begin tran: 518 LOGIDX option: 20909 file 1st update: 20 replication: 0 data cache: 7 TOTAL: 21581	¬ New statistic
# checks if cache write forces log flush: 1609	¬ New statistic
DATA CACHE LOG FLUSH REQUEST DETAILS cache aging: 0 updated page to be re-assigned: 0 flush on file close: 3 checkpoint: 0 CTFLUSH: 0 ctrbktfls(): 0 other: 4	¬ New section breaks down data cache value above
DELAYED_DURABILITY OFF and FORCE_LOGIDX ON
LOG FLUSH REQUESTS checkpoint/endlog/commit/abort tran: 100137 begin tran: 561 LOGIDX option: 20846 file 1st update: 20 replication: 0 data cache: 0 TOTAL: 121564 # checks if cache write forces log flush: 0 DATA CACHE LOG FLUSH REQUEST DETAILS cache aging: 0 updated page to be re-assigned: 0 flush on file close: 0 checkpoint: 0 CTFLUSH: 0 ctrbktfls(): 0 other: 0
DELAYED_DURABILITY OFF and FORCE_LOGIDX OFF
LOG FLUSH REQUESTS checkpoint/endlog/commit/abort tran: 100135 begin tran: 17207 LOGIDX option: 0 file 1st update: 20 replication: 0 data cache: 0 TOTAL: 117362 # checks if cache write forces log flush: 0 DATA CACHE LOG FLUSH REQUEST DETAILS cache aging: 0 updated page to be re-assigned: 0 flush on file close: 0 checkpoint: 0 CTFLUSH: 0 ctrbktfls(): 0 other: 0

Comparison

Comparing the three different SNAPSHOT files reveals the following:

The total number of log flushes is significantly lower with Delayed Durability on.
The reduction in total log flushes is primarily from eliminating the requirement to ensure log entries are on disk before a transaction commit can return.
LOGIDX causes a significant number of log flushes. However, when LOGIDX is not used, other flushing requirements imposed by indices still generate a comparable number of log flushes related to TRANBEG entries getting flushed.
The data cache impact on the number log flushes is quite small.