Re: tuning questions
Allen Jantzen wrote:
[SNIP]
> I cannot seem to get my onstat -F LRU write / Chunk Write ratio
> any higher than 1:1 or 1.5:1. During the day my checkpoints average
> only 3-4 seconds:
>
> 08:04:01 Checkpoint Completed: duration was 4 seconds.
> 08:09:04 Checkpoint Completed: duration was 1 seconds.
> 08:14:09 Checkpoint Completed: duration was 1 seconds.
> 08:19:16 Checkpoint Completed: duration was 3 seconds.
> 08:34:32 Checkpoint Completed: duration was 1 seconds.>
> but at night when a lot of batch processing is occurring (beginning at
> 3:30 AM for instance) they go up to 30-40 seconds, or even higher:
> 03:15:39 Checkpoint Completed: duration was 1 seconds.
> 03:31:10 Checkpoint Completed: duration was 19 seconds.
> 03:36:44 Checkpoint Completed: duration was 30 seconds.
> 03:42:34 Checkpoint Completed: duration was 47 seconds.
> 03:47:33 Logical Log 5154 Complete.
> 03:47:35 Process exited with return code 133: /bin/sh /bin/sh -c
> /opt/informix/etc/log_full.sh 2 23 "Logical Log 515
See that the drift of checkpoint times exceeds 5 minutes plus the actual
duration? That means some process(s) is(are) in a critical section and
are preventing the checkpoint from continuing. I think that this is
your BIGGEST problem. Someone has coded some HUGE transactions that
are holding critical resources at night. Check those overnight jobs.
Also your ALARMPROGRAM script is failing, no harm but you MAY think that
it is doing something. The default script Informix provides only works
if you have OnBar set up with some storage manager. If not substitute
some other script or program (see mine in utils3_ak for an example).
> Should I make other changes as a result of enabling KAIO?
> Should NUMAIOVPS go down to 1, even w/ cooked dbspaces??
No AIO looks OK. Increase LRUs to 32. Reduce MAX/MIN_LRU_DIRTY to 2/0
or even 1/0. Increase BUFFERS even though your cache %s look good you
may be keeping the I/O subsystem busy thrashing buffers at night. For a
10GB database I'd expect to use ~50,000 buffers. Are you using
singleton disks or worse RAID5? That might explain the poor write
performance? Can you reconfigure your drives into a RAID3 or better
RAID1+0 configuration?
> Would another CPU vp be a good idea? (2 physcial processors)
If you only have 2CPUs adding a second VP does not seem to help.
Art S. Kagel