Re: Searches get slow after a while

Re: | Fellow Informix'ers, | | We have a database server application using Informix 5.0 Online | on an IBM RS6000 340. When the application comes up, the response time is | good, but we see a deterioration after a while. Searches seem to slow down | and the CPU usage seems to go to 100% (sar report). Restarting the | application | does not seem to help. We need to reboot the machine completely to regain | speed. | | Could someone point out what seems to be the problem? This machine | is configured with 64MB of memory and has paging space in excess of 128M. | Are there any other tests/utilities I could run to get a clearer picture | of what is going on? | Perhaps you could run the following command before starting the application as well as during the application's slowdown to monitor CPU busy-ness: sar -q timeslice repetitions This will give you more of an idea of the relative CPU usage before and after starting the application. We find on an HP 9000/G30 that if the CPU run-queue size gets above 3, then we have something to worry about. Other useful tools (on our machines anyway) are : sar -d : this will give you an idea of the relative amounts of activity on your disks. sar -w : this will tell you whether swapping is occurring - if it is, then this indicates that either your system doesn't have enough memory for your applications, or your applications use (waste ?) too much memory for your system. tbstat -p : If you decide that it is indeed the Informix appplication that is slowing the machine down, then running tbstat -p at regular intervals will give you an idea of how many reads (Disk or buffered) are being performed by the engine. We have occasionally discovered engines that are doing as many as 3 000 buffered reads per second on average, which pointed us in the direction of optimizer problems and/or index and/or application problems. Running tbstat -u can then help identify which users/processes are guilty of doing large amounts of reads/writes tbstat -p can also help identify whether you have too few buffers allocated to your engine. (ovbuff will be significantly greater than 0) If you have system accounting on your machine, perhap you can switch this on and then run a report sorting by CPU usage - this should help you identify CPU 'hogs'. Another thing to watch out for are the number of times processes are run - you may find background processes running without sleeps, for example. PS: Have you always had this problem ? If not, what changes have you made to the system ? If you upgraded from Online 4.1 for example, this may point to optimizer changes causing sequential searches and/or sort merges that can potentially slow the whole machine down. Hope this helps, Richard Ridley DHL Asia Pacific