Re: Informix halts for no obvious reason ?!

Milen Metodiev wrote: > > Hi, folks. > > This is the umpteenth time this happens, so I wonder > if anyone can help. Even a few hints will be more than > welcome. > > When the system is busy enough ( 20-30 users of Informix ) > and people start issuing "heavy" queries, Informix falls into > a strange deadlock. It looks like one process is in a critical > section, and won't leave it. Informix waits for this process > to get out of its critical section, so that it can complete > the pending checkpoint. In the end, all users end up waiting > for this checkpoint, which never occurs. > > It happens to random users, using random programs, i.e. there > is no any consistency about this behaviour. > > Here is some information about our version of Informix, etc. [SNIP] > RSAM Version 5.01.UD2 -- On-Line (CKPT REQ) -- Up 01:14:09 -- 20288 Kbytes 1) Upgrade to 5.08 or later. Why? Tbtape for 5.0x versions earlier than 5.08, 7.1x versions before 7.14 and 7.2x versions before 7.21 will MISS PAGES during an ONLINE archive if there is update activity to the database! Only quiescent archives are reliable! Also 5.05 and later has a MUCH faster networking protocol if you have any network clients (even on the same machine). Version 5.07 fixed a bug permitting a table to grow too big so that page addresses wrap and overwrite older pages. Vers 5.02, 5.04 and 5.06 fixed different bugs in tbcheck. All in all you need a new version. > > Message Log File: /usr/runtime/online.log > ted > 08:10:22 Checkpoint Completed > 08:10:27 Checkpoint Completed > 08:14:11 Checkpoint Completed > 08:14:13 Checkpoint Completed > 08:14:16 Checkpoint Completed > 08:14:22 Checkpoint Completed > 08:14:26 Checkpoint Completed > 08:14:30 Checkpoint Completed > 08:14:34 Checkpoint Completed > 08:15:43 Checkpoint Completed > 08:17:30 Checkpoint Completed > 08:18:18 Checkpoint Completed > 08:18:35 Checkpoint Completed > 08:19:53 Checkpoint Completed > 08:25:04 Checkpoint Completed > 08:28:10 Checkpoint Completed > Configuration File: /usr/runtime/etc/tbconfig [SNIP] > > # Physical Log Configuration > > PHYSDBS rootdbs # Name of dbspace that contains physical log > PHYSFILE 1000 # Physical log file size (Kbytes) 2) Increase your PHYSFILE size. You have a CKPTINTVL of 5 Minutes but you are actually checkpointing every 10 seconds to 1 minute because your Physical log keeps filling (Checkpoint is triggered at 75% physical log usage). This means that a large % of the system's time is being spent waiting for checkpoints. Try values between 4000 and 20000. Start at 4000 and keep increasing until checkpoints happen at 5 minute intervals except at the busiest update times (then you want the safety of a more frequent checkpoint anyway just not TOO frequent and not all day long). [SNIP] > CLEANERS 1 # Number of page-cleaner processes 3) Checkpoints are taking too long because you only have one page cleaner to clean 8 LRU queues. THE MANUAL IS WRONG about having one Cleaner per disk and the desirability of ChunkWrites. Make that one Cleaner per LRU and look for 50-75% of the writes reported in tbstat -F to be LRU and Idle writes. You might also try 16 LRUs and 16 Cleaners. Minimizing LRU writes will free up resources for other applications but minimizing Chunk writes will maximize server performance. The LRU_..._DIRTY parameters also affect this see below. > SHMBASE 0x0 # Shared memory base address > CKPTINTVL 300 # Checkpoint interval (in seconds) > LRUS 8 # Number of LRU queues > LRU_MAX_DIRTY 60 # LRU modified begin-cleaning limit (percent) > LRU_MIN_DIRTY 50 # LRU modified end-cleaning limit (percent) 4) Reduce LRU_MAX_DIRTY and LRU_MIN_DIRTY to 10 and 5 respectively and play with them from there. This will minimize the number of dirty buffers to be cleaned at checkpoint time and speed your checkpoints (we run 2 and 0 here) by shifting the writes from Checkpoint time Chunk Writes to Idle Writes and LRU Writes. > LTXHWM 65 # Long TX high-water mark (percent) > LTXEHWM 75 # Long TX exclusive high-water mark (percent) [SNIP]> 07:57:46 Checkpoint Completed > 07:57:50 Checkpoint Completed > 07:57:54 Checkpoint Completed > 07:58:54 Checkpoint Completed > 07:58:55 Level 0 Archive Completed > 08:00:26 Logical Log 124 Backed Up > 08:04:17 Checkpoint Completed > 08:09:36 Checkpoint Completed Art S. Kagel