[beksalur]

our average checkpoint duration time in Informýx Online 7.31 is 9-10 seconds. Do you think it is normal? What can be done to tune it to normal duration times(say less than 5 minutes)?

our configuration file;

BUFFERS 400000
PHYSBUFF 1024
LOGBUFF 128
CLEANERS 128
PHYSFILE 490000
LOGFILES 80
LOGSIZE 25000
LRU_MAX_DIRTY 3 #(%)
LRU_MAX_DIRTY 2 #(%)

with checkpoint time 3 minutes.

Platform:
HP-UX B.11.00 U 9000/800

Thanks in advance.

[Roelwe]

Now, you forgot a very important parameter: How many LRU queues do you have?

[beksalur]

LRU 127

[Roelwe]

Than the only thing you can do is:
LRU_MAX_DIRTY 2
LRU_MIN_DIRTY 1

You can always decrease the amount of BUFFERS. Or move some data around. Check with 'onstat -D', 'onstat -g ioq', 'onstat -g iof' what dbspaces cause the long checkpoints.

Check with sysmaster:sysptprof which tables are heavily accessed. Try fragmentation on the bigger tables....

[beksalur]

Mr. Roelwe here is our onconfig file:

Configuration File: /data_1/informix/etc/onconfig
#************************************************* *************************
#
# INFORMIX SOFTWARE, INC.
#
# Title: onconfig.std
# Description: Informix Dynamic Server Configuration Parameters
#
#************************************************* *************************

# Root Dbspace Configuration

ROOTNAME rootdbs # Root dbspace name
ROOTPATH /data_1/IFMXDATA/phydev4/chunk4_1 #19.11.2000 chunk4_3 idi
ROOTOFFSET 100 # Offset of root dbspace into device (Kbytes)
ROOTSIZE 999900

# Disk Mirroring Configuration Parameters

MIRROR 0 # Mirroring flag (Yes = 1, No = 0)
MIRRORPATH # Path for device containing mirrored root
MIRROROFFSET 0 # Offset into mirrored device (Kbytes)

# Physical Log Configuration

PHYSDBS plogdbs # Location (dbspace) of physical log
PHYSFILE 490000 # Physical log file size (Kbytes)

# Logical Log Configuration

LOGFILES 80 # Number of logical log files
LOGSIZE 25000 # Logical log size (Kbytes)


RESIDENT 1 # Forced residency flag (Yes = 1, No = 0)

MULTIPROCESSOR 1 # 0 for single-processor, 1 for multi-processor
NUMCPUVPS 3 # Number of user (cpu) vps
SINGLE_CPU_VP 0

NOAGE 0 # Process aging
AFF_SPROC 0 # Affinity start processor
AFF_NPROCS 0 # Affinity number of processors

# Shared Memory Parameters

LOCKS 500000

BUFFERS 460000 # Maximum number of shared buffers
NUMAIOVPS 40 # Number of IO vps
PHYSBUFF 1024 # Physical log buffer size (Kbytes)
LOGBUFF 128 # Logical log buffer size (Kbytes)
LOGSMAX 120 # Maximum number of logical log files
CLEANERS 127 # Number of buffer cleaner processes
#CLEANERS 4 # Number of buffer cleaner processes
SHMBASE 0x0 # Shared memory base address
SHMVIRTSIZE 1100000 # initial virtual shared memory segment size
SHMADD 64000 # Size of new shared memory segments (Kbytes)
SHMTOTAL 0 # Total shared memory (Kbytes). 0=>unlimited
CKPTINTVL 180 #300 # Check point interval (in sec)
LRUS 127 # 512 #127 # Number of LRU queues
LRU_MAX_DIRTY 2 #10 # LRU percent dirty begin cleaning limit
LRU_MIN_DIRTY 1 #5 # LRU percent dirty end cleaning limit
LTXHWM 50 # Long transaction high water mark percentage
LTXEHWM 60 # Long transaction high water mark (exclusive)
TXTIMEOUT 0x12c # Transaction timeout (in sec)
STACKSIZE 32 # Stack size (Kbytes)


OFF_RECVRY_THREADS 10 # Default number of offline worker threads
ON_RECVRY_THREADS 1 # Default number of online worker threads

# Data Replication Variables
# DRAUTO: 0 manual, 1 retain type, 2 reverse type
DRAUTO 0 # DR automatic switchover
DRINTERVAL 30 # DR max time between DR buffer flushes (in sec)
DRTIMEOUT 30 # DR network timeout (in sec)
DRLOSTFOUND /usr/informix/etc/dr.lostfound # DR lost+found file path

# CDR Variables
CDR_LOGBUFFERS 2048 # size of log reading buffer pool (Kbytes)
CDR_EVALTHREADS 1,2 # evaluator threads (per-cpu-vp,additional)
CDR_DSLOCKWAIT 5 # DS lockwait timeout (seconds)
CDR_QUEUEMEM 4096 # Maximum amount of memory for any CDR queue (Kbytes)
CDR_LOGDELTA 30 # % of log space allowed in queue memory
CDR_NUMCONNECT 16 # Expected connections per server
CDR_NIFRETRY 300 # Connection retry (seconds)
CDR_NIFCOMPRESS 0 # Link level compression (-1 never, 0 none, 9 max)
DBSPACETEMP tempdbs # Default temp dbspaces


FILLFACTOR 90 # Fill factor for building indexes

# method for Dynamic Server to use when determining current time
USEOSTIME 0 # 0: use internal time(fast), 1: get time from OS(slow)

# Parallel Database Queries (pdq)
MAX_PDQPRIORITY 100 # Maximum allowed pdqpriority
DS_MAX_QUERIES # Maximum number of decision support queries
DS_TOTAL_MEMORY # Decision support memory (Kbytes)
DS_MAX_SCANS 1048576 # Maximum number of decision support scans
DATASKIP off # List of dbspaces to skip

Thanks in advance.

[Roelwe]

Config looks ok to me.
How many processors are in the machine?
How many chunks do you have?

I really think the long checkpoint duration can be due to the disks that cannot follow.
onstat -g ioq
shows the queue per chunk. This maxlen parameter should not exceed 25.