Informix Performance

I have the following configuration: 2 CPU's, 1GB of memory, IDS 7.2, data is on 4 HDD's mirrored to another 4 HDD's. My problem is the following: I think that I have not tuned instance of IDS. With sar output I can see that the system is spending much time in wio% (10%) while the cpu user portion utiliation is around 2-3%. Checkpoints occur on regular basis determined by CKPTINTL=300 in onconfig. LRUS = 16/CLEANERS = 16/LRU_MAX_DIRTY = 2/LRU_MIN_DIRTY = 1. But the duration of the checkpoints is too long (2 to 5 s!). This server holds the transactions meaning there are few very big tables in it. Buffer waits are too long (6).onstat -p shows 65 - 70 % write cache hit ratio and 92% read cache ratio, which is not so well I presume?! Buffers are set to 100000. Now, few of the suggestions were to increase the BUFFER variable but we have "full utilized" memory - there is no more space to allocate. Now the first thing which came in my mind is to add more buffers but I would like your's opinion on this also. Dejan.

"DEJAN STOJCEVSKI" <dejan.stojcevski@cosmofon.com.mk> wrote on 11/08/2004 02:43:45 AM: > I have the following configuration: > 2 CPU's, 1GB of memory, IDS 7.2, data is on 4 HDD's mirrored to > another 4 HDD's. > My problem is the following: > I think that I have not tuned instance of IDS. With sar output I can > see that the system is spending much time in wio% (10%) while the > cpu user portion utiliation is around 2-3%. Checkpoints occur on > regular basis determined by CKPTINTL=300 in onconfig. LRUS = > 16/CLEANERS = 16/LRU_MAX_DIRTY = 2/LRU_MIN_DIRTY = 1. But the > duration of the checkpoints is too long (2 to 5 s!). This server > holds the transactions meaning there are few very big tables in it. > Buffer waits are too long (6).onstat -p shows 65 - 70 % write cache > hit ratio and 92% read cache ratio, which is not so well I presume?! > Buffers are set to 100000. Now, few of the suggestions were to > increase the BUFFER variable but we have "full utilized" memory - > there is no more space to allocate. Now the first thing which came > in my mind is to add more buffers but I would like your's opinion onthis also. The first obvious comment is 'IDS 7.2 should not be used in general - time to upgrade'. Which version of 7.2x are you using (and don't answer that if it isn't 7.24 - you've got huge problems). What platform? When are you planning to upgrade? Who is doing the mirroring - IDS or the operating system (or hardware)? If it isn't IDS, are you using RAID 5? If so, don't - http://www.baarf.com/ From the statistics given, it sounds like you'd benefit from more buffers (your read cache ratio should be much higher; the write cache ration may not be as critical if you do, say, 20 reads for every write). You could look at whether you have enough LRUS; you'd benefit from IDS 9.40's fractional values for LRU_{MIN,MAX}_DIRTY, though 100,000 buffers is not a vast population. Do you have enough row-level locking? 2-5 seconds is not too bad for a checkpoint, but is not good enough. There are tricks available in more recent versions of IDS (such as 'onmode -B') that could be used. If the system has 10% wio and 2-3% user, what proportion of the time is in sys and what proportion of the time is idle? I'll hazard a guess - sys ~ 10%, idle ~75%? However, you need to watch to ensure that you aren't suffering from swapping (paging) for processes. Is your shared memory resident? How much? What connection types are you using? Etc. -- Jonathan Leffler (jleffler@us.ibm.com) STSM, Informix Database Engineering, IBM Data Management 4100 Bohannon Drive, Menlo Park, CA 94025 Tel: +1 650-926-6921 Tie-Line: 630-6921 "I don't suffer from insanity; I enjoy every minute of it!"

Firstly there is too little information for us to work with. IDS 7.2??? is no longer supported. Upgrade to 7.31 or 9.40. What is the OS? 100000 BUFFERS is that 200Mb or 400Mb? (Cacheing is very low). I would guess that you have insufficient BUFFERS and insufficient RAM for your application and database. What else is running on the server? onstat -c onstat -p onstat -d onstat -m onstat -g iof onstat -g segjust for starters. MW > -----Original Message----- > From: forum.subscriber@iiug.org [mailto:forum.subscriber@iiug.org]On > Behalf Of DEJAN STOJC.... > Sent: Monday, 8 November 2004 11:44 p.m. > To: ids@iiug.org > Subject: Informix Performance [3639] > > > I have the following configuration: > 2 CPU's, 1GB of memory, IDS 7.2, data is on 4 HDD's mirrored to > another 4 HDD's. > My problem is the following: > I think that I have not tuned instance of IDS. With sar output I > can see that the system is spending much time in wio% (10%) while > the cpu user portion utiliation is around 2-3%. Checkpoints occur > on regular basis determined by CKPTINTL=300 in onconfig. LRUS = > 16/CLEANERS = 16/LRU_MAX_DIRTY = 2/LRU_MIN_DIRTY = 1. But the > duration of the checkpoints is too long (2 to 5 s!). This server > holds the transactions meaning there are few very big tables in > it. Buffer waits are too long (6).onstat -p shows 65 - 70 % write > cache hit ratio and 92% read cache ratio, which is not so well I presume?! > Buffers are set to 100000. Now, few of the suggestions were to > increase the BUFFER variable but we have "full utilized" memory - > there is no more space to allocate. Now the first thing which > came in my mind is to add more buffers but I would like your's > opinion on this also. > Dejan. >