Informix

Hi,

As Art said, the info in the online.log message may be an indicator of checkpoint slowness, but you NEED to view onstat -g ckp to have a final diagnostic on what is happening.
A long checkpoint may be acceptable if you have low values (i.e say below 0.5 secs or less) in the Wait Time and Long Time columns columns. Those columns reflect during how long users transactions wait for the checkpoint.
In your case,  you have 5.5, 1.5 12.6 8.8 wait times, which are "too long" and users waiting at those times.
Looking at the Dirty Buffers column, correlating with the Ckpt Time of the same line, you can see that the time is not proportional to the number of of flushed buffers for instance at 09:18:44 it takes 51 sec to flush 61141 buffers with 12.6 secs of WAIT, and at 09:34:00 it takes 26.3 secs with 0.7 sec of waits  time, for about the same amount of buffers. This is not consistant.
60000 buffers is 120 Mb, which is not a lot of data(if your system page size is 2K). It takes 28.5 secs to write, which is slow.

To have an idea of disk performance, check the specs of the disk you have, and you will see that a disk is capable to write much more Megabytes in much less time. Reasons can again be multiple:
- physical disk contention: your onstat -d says that ALL your chunks are located in the same file system, which is not good. Putting all the chunks in the same file system is the worst case situation. Having the physical log on 2 chunks may also not be a very good idea

- if you run on a VM environment, with badly shared CPU and disk resources, this won't help

- you may also have contention on Informix threads, due to the fact that all your chunks are sitting at the same place, or any oher reason. running onstat -z, then twice onstat -g spi with an interval of 10 minutes may point to what is waiting.

- also run onstat -g iof and look at the chunks thruput

- finally run iostat -d (after identifying which disk you are using) and see how the physical disk performs

• Several of the chunks in your data dbspace are showing write service times over 120ms which is completely unacceptable. This is one reason for the long checkpoint times. You should be getting write service times under 10ms on any modern IO subsystem. Could the underlying storage by RAID5 or RAID6? That would be consistent with the performance I'm seeing. 
• The onstat -F output shows 100% of your dirty page flushes happening at checkpoint time. Ideally you should be getting 60% to 75% of your flushes happening at LRU/idle write time. To get there you will have to reduce the lru_min_dirty and lru_max_dirty in the BUFFERPOOL parameter from 50 & 60 down to 5 & 10 then play with it from there. You may ultimately get VERY aggressive and set down to 0.5 & 1.0, but I would start with 5 & 10 and see how that does.
• You have 128 LRU queues but rather few CLEANERS. The number of CLEANERS is fine to support checkpoint time flushing of the number of chunks you have, but now that you will be shifting 2/3 of your flushing to LRU writes you need to support those LRU queues. I would increase CLEANERS to 64 as it is unlikely that all 128 LRU queues will be flushing at the same time.
• Enable the TEMPTAB_NOLOG parameter (set to 1) which will reduce the number of temp tables that are created in the rootdb space.

• Several of the chunks in your data dbspace are showing write service times over 120ms which is completely unacceptable. This is one reason for the long checkpoint times. You should be getting write service times under 10ms on any modern IO subsystem. Could the underlying storage by RAID5 or RAID6? That would be consistent with the performance I'm seeing. 
• The onstat -F output shows 100% of your dirty page flushes happening at checkpoint time. Ideally you should be getting 60% to 75% of your flushes happening at LRU/idle write time. To get there you will have to reduce the lru_min_dirty and lru_max_dirty in the BUFFERPOOL parameter from 50 & 60 down to 5 & 10 then play with it from there. You may ultimately get VERY aggressive and set down to 0.5 & 1.0, but I would start with 5 & 10 and see how that does.
• You have 128 LRU queues but rather few CLEANERS. The number of CLEANERS is fine to support checkpoint time flushing of the number of chunks you have, but now that you will be shifting 2/3 of your flushing to LRU writes you need to support those LRU queues. I would increase CLEANERS to 64 as it is unlikely that all 128 LRU queues will be flushing at the same time.
• Enable the TEMPTAB_NOLOG parameter (set to 1) which will reduce the number of temp tables that are created in the rootdb space.

• Several of the chunks in your data dbspace are showing write service times over 120ms which is completely unacceptable. This is one reason for the long checkpoint times. You should be getting write service times under 10ms on any modern IO subsystem. Could the underlying storage by RAID5 or RAID6? That would be consistent with the performance I'm seeing. 
• The onstat -F output shows 100% of your dirty page flushes happening at checkpoint time. Ideally you should be getting 60% to 75% of your flushes happening at LRU/idle write time. To get there you will have to reduce the lru_min_dirty and lru_max_dirty in the BUFFERPOOL parameter from 50 & 60 down to 5 & 10 then play with it from there. You may ultimately get VERY aggressive and set down to 0.5 & 1.0, but I would start with 5 & 10 and see how that does.
• You have 128 LRU queues but rather few CLEANERS. The number of CLEANERS is fine to support checkpoint time flushing of the number of chunks you have, but now that you will be shifting 2/3 of your flushing to LRU writes you need to support those LRU queues. I would increase CLEANERS to 64 as it is unlikely that all 128 LRU queues will be flushing at the same time.
• Enable the TEMPTAB_NOLOG parameter (set to 1) which will reduce the number of temp tables that are created in the rootdb space.

• Several of the chunks in your data dbspace are showing write service times over 120ms which is completely unacceptable. This is one reason for the long checkpoint times. You should be getting write service times under 10ms on any modern IO subsystem. Could the underlying storage by RAID5 or RAID6? That would be consistent with the performance I'm seeing. 
• The onstat -F output shows 100% of your dirty page flushes happening at checkpoint time. Ideally you should be getting 60% to 75% of your flushes happening at LRU/idle write time. To get there you will have to reduce the lru_min_dirty and lru_max_dirty in the BUFFERPOOL parameter from 50 & 60 down to 5 & 10 then play with it from there. You may ultimately get VERY aggressive and set down to 0.5 & 1.0, but I would start with 5 & 10 and see how that does.
• You have 128 LRU queues but rather few CLEANERS. The number of CLEANERS is fine to support checkpoint time flushing of the number of chunks you have, but now that you will be shifting 2/3 of your flushing to LRU writes you need to support those LRU queues. I would increase CLEANERS to 64 as it is unlikely that all 128 LRU queues will be flushing at the same time.
• Enable the TEMPTAB_NOLOG parameter (set to 1) which will reduce the number of temp tables that are created in the rootdb space.

• Several of the chunks in your data dbspace are showing write service times over 120ms which is completely unacceptable. This is one reason for the long checkpoint times. You should be getting write service times under 10ms on any modern IO subsystem. Could the underlying storage by RAID5 or RAID6? That would be consistent with the performance I'm seeing. 
• The onstat -F output shows 100% of your dirty page flushes happening at checkpoint time. Ideally you should be getting 60% to 75% of your flushes happening at LRU/idle write time. To get there you will have to reduce the lru_min_dirty and lru_max_dirty in the BUFFERPOOL parameter from 50 & 60 down to 5 & 10 then play with it from there. You may ultimately get VERY aggressive and set down to 0.5 & 1.0, but I would start with 5 & 10 and see how that does.
• You have 128 LRU queues but rather few CLEANERS. The number of CLEANERS is fine to support checkpoint time flushing of the number of chunks you have, but now that you will be shifting 2/3 of your flushing to LRU writes you need to support those LRU queues. I would increase CLEANERS to 64 as it is unlikely that all 128 LRU queues will be flushing at the same time.
• Enable the TEMPTAB_NOLOG parameter (set to 1) which will reduce the number of temp tables that are created in the rootdb space.