Non-blocking checkpoints in Informix Dynamic Server

Tune Informix Dynamic Server to take advantage of non-blocking checkpoints and RTO_SERVER_RESTART in an online transaction processing environment

Return to article

Monitoring checkpoint performance

Table 1. Monitoring checkpoints with onstat -ckp
AUTO_CKPT=On RTO_SERVER_RESTART=60 Estimated recovery time 7 seconds
Critical SectionsDskfluPhysical LogAvgLogical LogAvg
IntervalClock TimeTriggerLSNTotal TimeFlush TimeBlock Time# WaitsCkpt TimeWait TimeLong Time# Dirty Buffers/SecTotal Pages/SecTotal Pages/Sec
118:41:36Startup1:f80.00.00.000.00.00.0443010
218:41:49Admin1:11c12cc0.30.20.010.00.00.02884288419661634549379
318:42:21Llog8:1882.32.02.010.02.02.014438738831810654422181
418:42:44*User10:19c0180.00.00.010.00.00.039395362120412816
518:46:21RTO13:188197196.90.0190.00.00.015838180414025329213017516286
Max Plog pages/wcMax Llog pages/secMax Dskflush TimeAvg Dskflush pages/secAvg Dirty pages/secBlocked Time
2954116019778021870


Some of the following messages might also be displayed along with the before-mentioned performance advisory information. They are intended to provide suggestions that would lead to better performance.

Physical log is too small for bufferpool size. 
System performance may be less than optimal.
Increase physical log size to at least ##Kb.
Physical log is too small for optimal performance.
Increase the physical log size to at least $ldKb.
Logical log space is too small for optimal performance.
Increase the total size of the logical log space to at least ## Kb.
Physical log is too small for bufferpool size. 
System performance may be less than optimal.
Increase physical log size to at least ##Kb
Transaction blocking has taken place. The physical log is too small.
Please increase the size of the physical log to ##Kb
Ledger for onstat -ckp
ItemUnitsDescription
AUTO_CKPTSOn/OffDisplays if automatic checkpoints feature is on or off
RTO_SERVER_RESTARTSecondsDisplays the RTO policy. 0=RTO policy is off.
Estimated recovery timeSecondsThis is the estimated time it would take the IDS server to perform fast recovery.
IntervalNumberCheckpoint interval id
Clock TimeWall clock timeThis is the wall clock time that the checkpoint occurred
TriggerTextThere are several events that can trigger a checkpoint. The most common are RTO (trying to maintain the Recovery Time Objective policy), Plog (physical log is 75% full) or Llog (running out of logical log resources). If there is a * in front of the event, that indicated the checkpoint was a transaction blocking checkpoint.
LSNLog positionLog position of checkpoint
Total TimeSecondsTotal checkpoint duration from request time to checkpoint completion
Flush TimeSecondsTime to flush bufferpools
Block TimeSecondsTransaction blocking time
# WaitsNumberNumber of transactions that blocked waiting for checkpoint
Ckpt TimeSecondsThis is the amount of time it takes for all transactions to recognize a checkpoint has been requested
Wait TimeSecondsAverage time thread waited for checkpoint
Long TimeSecondsLongest amount of time a transaction waited for checkpoint
# Dirty BuffersNumberNumber of buffers flushed to disk during checkpoint processing
Dskflu/SecNumberNumber of buffers flushed to disk per sec during checkpoint processing
Plog Total PagesNumberTotal number of pages physically logged during the checkpoint interval
Plog Avg/SecNumberAverage rate of physical log activity during the checkpoint interval
Llog Total PagesNumberTotal number of pages logically logged during the checkpoint interval
Llog Avg/SecNumberAverage rate of logical log activity during the checkpoint interval
Table 2. Monitoring checkpoints with sysmaster
syscheckpoint
Column NameColumn TypeDescription
intvlintCheckpoint interval id
typechar(12)Blocking or Non-blocking
callerchar(10)There are several events that can trigger a checkpoint. The most common are RTO (trying to maintain the Recovery Time Objective policy), Plog (physical log is 75% full) or Llog (running out of logical log resources). If there is a * in front of the event, that indicated the checkpoint was a transaction blocking checkpoint.
clock_timeintThis is the wall clock time that the checkpoint occurred. See UNIX localtime() function.
crit_timefloatThis is the amount of time it takes for all transactions to recognize a checkpoint has been requested
flush_timefloatTime taken to flush all the bufferpools
cp_timefloatTotal checkpoint duration from request time to checkpoint completion
n_dirty_buffsintNumber of buffers flushed to disk during checkpoint processing
plogs_per_secintAverage rate of physical log activity during the checkpoint interval
llogs_per_secintAverage rate of logical log activity during the checkpoint interval
dskflush_per_secintNumber of buffers flushed to disk per sec during checkpoint processing
ckpt_logidintLog position of checkpoint
ckpt_logposintLog position of checkpoint
physusedintTotal number of pages physically logged during the checkpoint interval
logusedintTotal number of pages logically logged during the checkpoint interval
n_crit_waitsintNumber of transactions that blocked waiting for checkpoint
tot_crit_waitfloatTime accumulated by all transactions waiting on checkpoint
longest_crit_waitfloatLongest amount of time a transaction waited for checkpoint
block_timefloatTransaction blocking time
sysckptinfo
Column NameColumn TypeDescription
ckpt_statusint0x0011=A checkpoint was blocked because of the physical log running out of resources. 0x0020=A checkpoint was blocked because of the logical log running out of resources. 0x0040=A checkpoint was blocked because of long transactions. 0x1000=Physical log is too small. 0x2000=Logical log space is too small. 0x4000=The physical log is too small for RTO
plogs_per_SintAverage rate of physical logging activity
llogs_per_SintAverage rate of logical logging activity
dskF_per_SintAverage rate of pages flushed to disk
longest_dskFintLongest duration of time that it took to flush the bufferpool to disk during checkpoint processing
dirty_pgs_SintAverage rate of pages being modified
sug_plog_sizeintSuggested physical log size
sug_llog_szintSuggested logical log space size
ras_plog_spintRate at which fast recovery can restore the physical log
ras_llog_spintRate at which fast recovery can replay the logical log
boottimeintTime it takes for the IDS server to boot shared memory and open chunks
auto-ckptsint1=On,0=Off
auto_lruint1=On,0=Off
cur_intvlintCurrent checkpoint interval id
boottimeint1=On,0=Off

Return to article