Performance tuning: troubling long checkpoints and other issues

Hello All, Here's some quick history... Recently the number of users has increased to over 500 sessions. All our users are now complaining of poor performance. Our checkpoints are slow to complete (4 seconds and up to 13 seconds during peak activity) and the CPU activity is constantly over 95%. I've been tweaking LRU, CLEANERS, BUFFERS and I'm now more perplexed than ever. This is an OLTP system with about 5 batch jobs that are constantly run during the day. No other processes other than these batch jobs are run on the database server. The system is not swapping and memory used is sitting at 95%. At one point, a number of years ago, I tried kaio but on this SCO platform it actually performed worse; but perhaps I should try it once again. Informix Dynamic Server Version 7.31.UD8 SCO OpenServer 5.0.6 Dual Pentium III Xeon 1GHz, 4GB ram, 12 * 18GB hard drives arranged in RAID 1 pairs (the first pair is dedicated to the O/S, the rest to Informix) Update statistics is run nightly using one of the very good IIUG 4GLprograms. We only have one temp dbspace since we cannot allocate more because of an application incompatibility (it uses table rowids). I've exhausted input from a number of performance tuning books and searches through this newsgroup. Any help would be greatly appreciated... Thanks, Ron -------------------------------------------------- # Root Dbspace Configuration ROOTNAME rootdbs # Root dbspace nameROOTPATH /dev/rdbs_root # Path for device containing root dbspace ROOTOFFSET 0 # Offset of root dbspace into device (Kbytes) ROOTSIZE 1024000 # Size of root dbspace (Kbytes) # Disk Mirroring Configuration Parameters MIRROR 0 # Mirroring flag (Yes = 1, No = 0) MIRRORPATH # Path for device containing mirroredroot MIRROROFFSET 0 # Offset into mirrored device (Kbytes) # Physical Log Configuration PHYSDBS rootdbs # Location (dbspace) of physical log PHYSFILE 240000 # Physical log file size (Kbytes) # Logical Log Configuration LOGFILES 60 # Number of logical log files LOGSIZE 1000 # Logical log size (Kbytes) # Diagnostics MSGPATH /u/informix7/online.log # System message log file path CONSOLE /u/informix7/online.log # System console message path ALARMPROGRAM /u/informix7/etc/log_full.sh # Alarm program path SYSALARMPROGRAM /u/informix7/etc/evidence.sh # System Alarm program path TBLSPACE_STATS 1 # System Archive Tape Device TAPEDEV secondary:/dev/rStp0 # Tape device path TAPEBLK 64 # Tape block size (Kbytes) TAPESIZE 50000000 # Maximum amount of data to put on tape (Kbytes) # Log Archive Tape Device LTAPEDEV /u/informix7/db-logs/logtape # Log tape device path LTAPEBLK 64 # Log tape block size (Kbytes) LTAPESIZE 25000000 # Max amount of data to put on log tape (Kbytes) # Optical STAGEBLOB # Informix Dynamic Server/Optical staging area # System Configuration SERVERNUM 0 # Unique id corresponding to a DynamicServer instance DBSERVERNAME local_on # Name of default database server DBSERVERALIASES primary # List of alternate dbservernames NETTYPE ipcshm,2,50,CPU # Configure poll thread(s) for nettype NETTYPE tlitcp,2,300,NET # Configure poll thread(s) for nettype DEADLOCK_TIMEOUT 60 # Max time to wait of lock indistributed env. RESIDENT 1 # Forced residency flag (Yes = 1, No = 0) MULTIPROCESSOR 1 # 0 for single-processor, 1 formulti-processor NUMCPUVPS 2 # Number of user (cpu) vps SINGLE_CPU_VP 0 # If non-zero, limit number of cpu vpsto one NOAGE 1 # Process aging AFF_SPROC 0 # Affinity start processor AFF_NPROCS 2 # Affinity number of processors # Shared Memory Parameters LOCKS 150000 # Maximum number of locks # Jan 18-06...#BUFFERS 700000 # Maximum number of shared buffers BUFFERS 600000 # Maximum number of shared buffers # END Jan 16-06# Jan 19-06: Try to decrease maxlen in onstat -q ioq and have one # aio io/s in onstat -g iov ~1 #NUMAIOVPS 16 # Number of IO vps NUMAIOVPS 18 # Number of IO vps # END Jan 19-06 PHYSBUFF 32 # Physical log buffer size (Kbytes) LOGBUFF 32 # Logical log buffer size (Kbytes)LOGSMAX 60 # Maximum number of logical log files # Jan 19-06: set CLEANERS >= LRUS #CLEANERS 32 # Number of buffer cleaner processes CLEANERS 127 # Number of buffer cleaner processes # END Jan 19-06 SHMBASE 0x10000000 # Shared memory base address # Jan 18-06#SHMVIRTSIZE 153600 # initial virtual shared memory segment size SHMVIRTSIZE 235520 # initial virtual shared memory segmentsize #SHMADD 8192 # Size of new shared memory segments (Kbytes) SHMADD 16384 # Size of new shared memory segments (Kbytes) # END Jan 18-06 SHMTOTAL 0 # Total shared memory (Kbytes). 0=>unlimited CKPTINTVL 300 # Check point interval (in sec)# Jan 19-06: Pump this up and avoid = 64, 96 to lower bufwaits # and reduce the buffer turnover rate # and potentially lower checkpoints #LRUS 64 # Number of LRU queues LRUS 127 # Number of LRU queues # END Jan 19-06 LRU_MAX_DIRTY 2 # LRU percent dirty begin cleaninglimit LRU_MIN_DIRTY 1 # LRU percent dirty end cleaning limit LTXHWM 50 # Long transaction high water markpercentage LTXEHWM 60 # Long transaction high water mark (exclusive) TXTIMEOUT 0x12c # Transaction timeout (in sec) STACKSIZE 32 # Stack size (Kbytes) # System Page Size # BUFFSIZE - Dynamic Server no longer supports this configuration parameter. # To determine the page size used by Dynamic Server on your platform # see the last line of output from the command, 'onstat -b'. # Recovery Variables # OFF_RECVRY_THREADS: # Number of parallel worker threads during fast recovery or an offline restore. # ON_RECVRY_THREADS: # Number of parallel worker threads during an online restore. OFF_RECVRY_THREADS 10 # Default number of offline workerthreads ON_RECVRY_THREADS 1 # Default number of online workerthreads # Data Replication Variables # DRAUTO: 0 manual, 1 retain type, 2 reverse type DRAUTO 0 # DR automatic switchover DRINTERVAL 30 # DR max time between DR buffer flushes (in sec) DRTIMEOUT 30 # DR n

Jim You appear to have IO issues with the large IO wait in sar. Similarly you get a large queue length on your tempdbs. There is another chunk that reached a long queue length. You have 4Gb Ram yet only 1.5Gb is assigned to Informix. What is using the rest (2.5Gb) and what is that doing to the server? In your ONCONFIG someone looks like they reduced BUFFERS from 700000 to 600000 - why? It looks like your application is short of BUFFERS - causing the excessive IO. Cacheing is very low and buffer waits are high. BTR is far to high. Is your IO relatively balanced between your disk pairs? What are your Informix users requesting of Informix (onstat -u). Are there issues with the application? MW -----Original Message----- From: informix-list-bounces@iiug.org [mailto:informix-list-bounces@iiug.org] On Behalf Of Jim Kenedy Sent: Friday, 20 January 2006 9:12 a.m. To: informix-list@iiug.org Subject: Performance tuning: troubling long checkpoints and other issues Hello All, Here's some quick history... Recently the number of users has increased to over 500 sessions. All our users are now complaining of poor performance. Our checkpoints are slow to complete (4 seconds and up to 13 seconds during peak activity) and the CPU activity is constantly over 95%. I've been tweaking LRU, CLEANERS, BUFFERS and I'm now more perplexed than ever. This is an OLTP system with about 5 batch jobs that are constantly run during the day. No other processes other than these batch jobs are run on the database server. The system is not swapping and memory used is sitting at 95%. At one point, a number of years ago, I tried kaio but on this SCO platform it actually performed worse; but perhaps I should try it once again. Informix Dynamic Server Version 7.31.UD8 SCO OpenServer 5.0.6 Dual Pentium III Xeon 1GHz, 4GB ram, 12 * 18GB hard drives arranged in RAID 1 pairs (the first pair is dedicated to the O/S, the rest to Informix) Update statistics is run nightly using one of the very good IIUG 4GLprograms. We only have one temp dbspace since we cannot allocate more because of an application incompatibility (it uses table rowids). I've exhausted input from a number of performance tuning books and searches through this newsgroup. Any help would be greatly appreciated... Thanks, Ron -------------------------------------------------- # Root Dbspace Configuration ROOTNAME rootdbs # Root dbspace nameROOTPATH /dev/rdbs_root # Path for device containing root dbspace ROOTOFFSET 0 # Offset of root dbspace into device (Kbytes) ROOTSIZE 1024000 # Size of root dbspace (Kbytes) # Disk Mirroring Configuration Parameters MIRROR 0 # Mirroring flag (Yes = 1, No = 0) MIRRORPATH # Path for device containing mirroredroot MIRROROFFSET 0 # Offset into mirrored device (Kbytes) # Physical Log Configuration PHYSDBS rootdbs # Location (dbspace) of physical log PHYSFILE 240000 # Physical log file size (Kbytes) # Logical Log Configuration LOGFILES 60 # Number of logical log files LOGSIZE 1000 # Logical log size (Kbytes) # Diagnostics MSGPATH /u/informix7/online.log # System message log file path CONSOLE /u/informix7/online.log # System console message path ALARMPROGRAM /u/informix7/etc/log_full.sh # Alarm program path SYSALARMPROGRAM /u/informix7/etc/evidence.sh # System Alarm program path TBLSPACE_STATS 1 # System Archive Tape Device TAPEDEV secondary:/dev/rStp0 # Tape device path TAPEBLK 64 # Tape block size (Kbytes) TAPESIZE 50000000 # Maximum amount of data to put on tape (Kbytes) # Log Archive Tape Device LTAPEDEV /u/informix7/db-logs/logtape # Log tape device path LTAPEBLK 64 # Log tape block size (Kbytes) LTAPESIZE 25000000 # Max amount of data to put on log tape (Kbytes) # Optical STAGEBLOB # Informix Dynamic Server/Optical staging area # System Configuration SERVERNUM 0 # Unique id corresponding to a DynamicServer instance DBSERVERNAME local_on # Name of default database server DBSERVERALIASES primary # List of alternate dbservernames NETTYPE ipcshm,2,50,CPU # Configure poll thread(s) for nettype NETTYPE tlitcp,2,300,NET # Configure poll thread(s) for nettype DEADLOCK_TIMEOUT 60 # Max time to wait of lock indistributed env. RESIDENT 1 # Forced residency flag (Yes = 1, No = 0) MULTIPROCESSOR 1 # 0 for single-processor, 1 formulti-processor NUMCPUVPS 2 # Number of user (cpu) vps SINGLE_CPU_VP 0 # If non-zero, limit number of cpu vpsto one NOAGE 1 # Process aging AFF_SPROC 0 # Affinity start processor AFF_NPROCS 2 # Affinity number of processors # Shared Memory Parameters LOCKS 150000 # Maximum number of locks # Jan 18-06...#BUFFERS 700000 # Maximum number of shared buffers BUFFERS 600000 # Maximum number of shared buffers # END Jan 16-06# Jan 19-06: Try to decrease maxlen in onstat -q ioq and have one # aio io/s in onstat -g iov ~1 #NUMAIOVPS 16 # Number of IO vps NUMAIOVPS 18 # Number of IO vps # END Jan 19-06 PHYSBUFF 32 # Physical log buffer size (Kbytes) LOGBUFF 32 # Logical log buffer size (Kbytes)LOGSMAX 60 # Maximum number of logical log files # Jan 19-06: set CLEANERS >= LRUS #CLEANERS 32 # Number of buffer cleaner processes CLEANERS 127 # Number of buffer cleaner processes # END Jan 19-06 SHMBASE 0x10000000 # Shared memory base address # Jan 18-06#SHMVIRTSIZE 153600 # initial virtual shared memory segment size SHMVIRTSIZE 235520 # initial virtual shared memory segmentsize #SHMADD 8192 # Size of new shared memory segments (Kbytes) SHMADD 16384 # Size of new shared memory segments (Kbytes) # END Jan 18-06 SHMTOTAL 0 # Total shared memory (Kbytes). 0=>unlimited CKPTINTVL 300 # Check point interval (in sec)# Jan 19-06: Pump this up and avoid = 64, 96 to lower bufwaits # and reduce the buffer turnover rate # and potentially lower checkpoints #LRUS 64 # Number of LRU queues LRUS 127 # Number of LRU queues # END Jan 19-06 LRU_MAX_DIRTY 2 # LRU percent dirty begin cleaninglimit LRU_MIN_DIRTY 1 # LRU percent dirty end cleaning limit LTXHWM 50 # Long transaction high water markpercentage LTXEHWM 60 # Long transaction high water mark (exclusive)TXTI

My bad, the server only has 2Gb of Ram, which I now recall to be the max for this platform. BUFFERS was decreased because the system began to swap since the number of user sessions has increased. We do have one set of disks that is running hot. They were all nicely balanced a year ago. I am working on trying to spread that load. Below are some disk stats. Regarding the application, we are currently investigating sequential scans. I am pretty sure that there are a few that are contributing to the high BTR. Other than checking the sequential scans and further balancing the disks which we are in the process of doing, is there something else in our configuration that we should be looking at? I wasn't sure if you were asking to see the output from onstat -u, if so, it is further below. ------------------------------ sar -d 00:00:00 device %busy avque r+w/s blks/s avwait avserv (-d) 08:00:02 Sdsk-0 0.65 1.00 6.10 19.80 0.00 1.06 Sdsk-1 0.16 1.00 4.57 47.55 0.00 0.35 Sdsk-2 4.42 1.00 7.17 40.44 0.00 6.17 Sdsk-3 9.36 1.00 12.80 194.18 0.00 7.31 Sdsk-4 1.58 1.00 16.89 68.81 0.00 0.93 Sdsk-5 5.48 1.00 10.81 150.01 0.00 5.07 08:30:01 Sdsk-0 42.19 1.00 12.61 202.93 0.00 33.46 Sdsk-1 22.85 1.00 168.80 6588.51 0.00 1.35 Sdsk-2 7.95 1.00 22.36 181.06 0.00 3.56 Sdsk-3 100.00 1.00 294.14 1181.13 0.00 3.53 Sdsk-4 13.94 1.00 48.65 338.34 0.00 2.87 Sdsk-5 11.02 1.00 112.59 2916.36 0.00 0.98 09:00:04 Sdsk-0 4.76 1.00 7.53 34.26 0.00 6.32 Sdsk-1 36.71 1.00 207.94 8105.15 0.00 1.77 Sdsk-2 23.59 1.00 52.32 543.41 0.00 4.51 Sdsk-3 100.00 1.00 423.05 2259.31 0.00 3.95 Sdsk-4 18.16 1.00 69.88 833.38 0.00 2.60 Sdsk-5 6.26 1.00 83.67 2465.78 0.00 0.75 09:30:01 Sdsk-0 99.06 1.00 26.88 399.70 0.00 36.85 Sdsk-1 6.48 1.00 59.53 2334.88 0.00 1.09 Sdsk-2 22.50 1.00 44.43 599.65 0.00 5.06 Sdsk-3 100.00 1.00 393.27 2048.82 0.00 3.83 Sdsk-4 12.17 1.00 36.83 257.62 0.00 3.30 Sdsk-5 4.88 1.00 9.37 70.70 0.00 5.20 10:00:04 Sdsk-0 0.97 1.00 8.97 27.20 0.00 1.08 Sdsk-1 1.20 1.00 22.16 262.40 0.00 0.54 Sdsk-2 12.42 1.00 52.43 748.80 0.00 2.37 Sdsk-3 59.97 1.00 248.96 1069.24 0.00 2.41 Sdsk-4 61.34 1.00 256.71 1058.33 0.00 2.39 Sdsk-5 5.49 1.00 8.63 76.98 0.00 6.36 -------------------------------------------- Userthreads address flags sessid user tty wait tout locks nreads nwrites 5e45e018 ---P--D 1 informix - 0 0 0 320 1261 5e45e518 ---P--F 0 informix - 0 0 0 0 229537 5e45ea18 ---P--F 0 informix - 0 0 0 0 25344 5e45ef18 ---P--F 0 informix - 0 0 0 0 228654 5e45f418 ---P--F 0 informix - 0 0 0 0 9080 5e45f918 ---P--F 0 informix - 0 0 0 0 11756 5e45fe18 ---P--F 0 informix - 0 0 0 0 12773 5e460318 ---P--F 0 informix - 0 0 0 0 114684 5e460818 ---P--F 0 informix - 0 0 0 0 80952 5e460d18 ---P--F 0 informix - 0 0 0 0 47867 5e461218 ---P--F 0 informix - 0 0 0 0 15156 5e461718 ---P--F 0 informix - 0 0 0 0 16809 5e461c18 ---P--F 0 informix - 0 0 0 0 2196 5e462118 ---P--F 0 informix - 0 0 0 0 7239 5e462618 ---P--F 0 informix - 0 0 0 0 12577 5e462b18 ---P--F 0 informix - 0 0 0 0 11629 5e463018 ---P--F 0 informix - 0 0 0 0 10573 5e463518 ---P--F 0 informix - 0 0 0 0 63121 5e463a18 ---P--F 0 informix - 0 0 0 0 38056 5e463f18 ---P--F 0 informix - 0 0 0 0 10634 5e464418 ---P--F 0 informix - 0 0 0 0 555 5e464918 ---P--F 0 informix - 0 0 0 0 163 5e464e18 ---P--F 0 informix - 0 0 0 0 558 5e465318 ---P--F 0 informix - 0 0 0 0 4528 5e465818 ---P--F 0 informix - 0 0 0 0 112 5e465d18 ---P--F 0 informix - 0 0 0 0 5435 5e466218 ---P--F 0 informix - 0 0 0 0 156 5e466718 ---P--F 0 informix - 0 0 0 0 98 5e466c18 ---P--F 0 informix - 0 0 0 0 15577 5e467118 ---P--F 0 informix - 0 0 0 0 7833 5e467618 ---P--F 0 informix - 0 0 0 0 4624 5e467b18 ---P--F 0 informix - 0 0 0 0 5332 5e468018 ---P--F 0 informix - 0 0 0 0 9809 5e468518 ---P--F 0 informix - 0 0 0 0 157 5e468a18 ---P--F 0 informix - 0 0 0 0 1132 5e468f18 ---P--F 0 informix - 0 0 0 0 219 5e469418 ---P--F 0 informix - 0 0 0 0 110 5e469918 ---P--F 0 informix - 0 0 0 0 842 5e469e18 ---P--F 0 informix - 0 0 0 0 15976 5e46a318 ---P--F 0 informix - 0 0 0 0 146 5e46a818 ---P--F 0 informix - 0 0 0 0 97 5e46ad18 ---P--F 0 informix - 0 0 0 0 99 5e46b218 ---P--F 0 informix - 0 0 0 0 448 5e46b718 ---P--F 0 informix - 0 0 0 0 9117 5e46bc18 ---P--F 0 informix - 0 0 0 0 20203 5e46c118 ---P--F 0 informix - 0 0 0 0 96 5e46c618 ---P--F 0 informix - 0 0 0 0 8317 5e46cb18 ---P--F 0 informix - 0 0 0 0 104 5e46d018 ---P--F 0 informix - 0 0 0 0 22050 5e46d518 ---P--F 0 informix - 0 0 0 0 5898 5e46da18 ---P--F 0 informix - 0 0 0 0 24097 5e46df18 ---P--F 0 informix - 0 0 0 0 20685 5e46e418 ---P--

Do you use many stored procedures? If so, try using different PC_HASHSIZE and PC_POOLSIZE config. parameters. Currently you don't have them in onconfig, so they have default values which are too small for so many users if there are many stored procedures or UDR's in use. "Jim Kenedy" <JimKenedy@gmail.com> wrote in message news:1137707263.759211.319630@o13g2000cwo.googlegroups.com... > My bad, the server only has 2Gb of Ram, which I now recall to be the > max for this platform. BUFFERS was decreased because the system began > to swap since the number of user sessions has increased. > > We do have one set of disks that is running hot. They were all nicely > balanced a year ago. I am working on trying to spread that load. > Below are some disk stats. > > Regarding the application, we are currently investigating sequential > scans. I am pretty sure that there are a few that are contributing to > the high BTR. > > Other than checking the sequential scans and further balancing the > disks which we are in the process of doing, is there something else in > our configuration that we should be looking at? > > I wasn't sure if you were asking to see the output from onstat -u, if > so, it is further below. > ------------------------------ > sar -d > 00:00:00 device %busy avque r+w/s blks/s avwait > avserv (-d) > 08:00:02 Sdsk-0 0.65 1.00 6.10 19.80 0.00 > 1.06 > Sdsk-1 0.16 1.00 4.57 47.55 0.00 > 0.35 > Sdsk-2 4.42 1.00 7.17 40.44 0.00 > 6.17 > Sdsk-3 9.36 1.00 12.80 194.18 0.00 > 7.31 > Sdsk-4 1.58 1.00 16.89 68.81 0.00 > 0.93 > Sdsk-5 5.48 1.00 10.81 150.01 0.00 > 5.07> > 08:30:01 Sdsk-0 42.19 1.00 12.61 202.93 0.00 > 33.46 > Sdsk-1 22.85 1.00 168.80 6588.51 0.00 > 1.35 > Sdsk-2 7.95 1.00 22.36 181.06 0.00 > 3.56 > Sdsk-3 100.00 1.00 294.14 1181.13 0.00 > 3.53 > Sdsk-4 13.94 1.00 48.65 338.34 0.00 > 2.87 > Sdsk-5 11.02 1.00 112.59 2916.36 0.00 > 0.98 > > 09:00:04 Sdsk-0 4.76 1.00 7.53 34.26 0.00 > 6.32 > Sdsk-1 36.71 1.00 207.94 8105.15 0.00 > 1.77 > Sdsk-2 23.59 1.00 52.32 543.41 0.00 > 4.51 > Sdsk-3 100.00 1.00 423.05 2259.31 0.00 > 3.95 > Sdsk-4 18.16 1.00 69.88 833.38 0.00 > 2.60 > Sdsk-5 6.26 1.00 83.67 2465.78 0.00 > 0.75 > > 09:30:01 Sdsk-0 99.06 1.00 26.88 399.70 0.00 > 36.85 > Sdsk-1 6.48 1.00 59.53 2334.88 0.00 > 1.09 > Sdsk-2 22.50 1.00 44.43 599.65 0.00 > 5.06 > Sdsk-3 100.00 1.00 393.27 2048.82 0.00 > 3.83 > Sdsk-4 12.17 1.00 36.83 257.62 0.00 > 3.30 > Sdsk-5 4.88 1.00 9.37 70.70 0.00 > 5.20 > > 10:00:04 Sdsk-0 0.97 1.00 8.97 27.20 0.00 > 1.08 > Sdsk-1 1.20 1.00 22.16 262.40 0.00 > 0.54 > Sdsk-2 12.42 1.00 52.43 748.80 0.00 > 2.37 > Sdsk-3 59.97 1.00 248.96 1069.24 0.00 > 2.41 > Sdsk-4 61.34 1.00 256.71 1058.33 0.00 > 2.39 > Sdsk-5 5.49 1.00 8.63 76.98 0.00 > 6.36 > > -------------------------------------------- > Userthreads > address flags sessid user tty wait tout locks nreads > nwrites > 5e45e018 ---P--D 1 informix - 0 0 0 320 > 1261 > 5e45e518 ---P--F 0 informix - 0 0 0 0 > 229537 > 5e45ea18 ---P--F 0 informix - 0 0 0 0 > 25344 > 5e45ef18 ---P--F 0 informix - 0 0 0 0 > 228654 > 5e45f418 ---P--F 0 informix - 0 0 0 0 > 9080 > 5e45f918 ---P--F 0 informix - 0 0 0 0 > 11756 > 5e45fe18 ---P--F 0 informix - 0 0 0 0 > 12773 > 5e460318 ---P--F 0 informix - 0 0 0 0 > 114684 > 5e460818 ---P--F 0 informix - 0 0 0 0 > 80952 > 5e460d18 ---P--F 0 informix - 0 0 0 0 > 47867 > 5e461218 ---P--F 0 informix - 0 0 0 0 > 15156 > 5e461718 ---P--F 0 informix - 0 0 0 0 > 16809 > 5e461c18 ---P--F 0 informix - 0 0 0 0 > 2196 > 5e462118 ---P--F 0 informix - 0 0 0 0 > 7239 > 5e462618 ---P--F 0 informix - 0 0 0 0 > 12577 > 5e462b18 ---P--F 0 informix - 0 0 0 0 > 11629 > 5e463018 ---P--F 0 informix - 0 0 0 0 > 10573 > 5e463518 ---P--F 0 informix - 0 0 0 0 > 63121 > 5e463a18 ---P--F 0 informix - 0 0 0 0 > 38056 > 5e463f18 ---P--F 0 informix - 0 0 0 0 > 10634 > 5e464418 ---P--F 0 informix - 0 0 0 0 > 555 > 5e464918 ---P--F 0 informix - 0 0 0 0 > 163 > 5e464e18 ---P--F 0 informix - 0 0 0 0 > 558 > 5e465318 ---P--F 0 informix - 0 0 0 0 > 4528 > 5e465818 ---P--F 0 informix - 0 0 0 0 > 112 > 5e465d18 ---P--F 0 informix - 0 0 0 0 > 5435 > 5e466218 ---P--F 0 informix - 0 0 0 0 > 156 > 5e466718 ---P--F 0 informix - 0 0 0 0 > 98 > 5e466c18 ---P--F 0 informix - 0 0 0 0 > 15577 > 5e467118 ---P--F 0 informix - 0 0 0 0 > 7833 > 5e467618 ---P--F 0 informix - 0 0 0 0 > 4624 > 5e467b18 ---P--F 0 informix - 0 0 0 0 > 5332 > 5e468018 ---P--F 0 informix - 0 0 0 0 > 9809 > 5e468518 ---P--F 0 informix - 0 0 0 0 > 157 > 5e468a18 ---P--F 0 informix - 0 0 0 0 > 1132 > 5e468f18 ---P--F 0 informix - 0 0 0 0 > 219 > 5e469418 ---P--F 0 informix - 0 0 0 0 > 110 > 5e469918 ---P--F 0 informix - 0 0 0 0 > 842 > 5e469e18 ---P--F 0 informix - 0 0 0 0 > 15976 > 5e46a318 ---P--F 0 informix - 0 0 0 0 > 146 > 5e46a818 ---P--F 0 informix - 0 0 0 0 > 97 > 5e46ad18 ---P--F 0 informix - 0 0 0 0 > 99 > 5e46b218 ---P--F 0 informix - 0 0 0 0 > 448 > 5e46b718 ---P--F 0 informix -

OPTCOMPIND 2 # To hint the optimizer for oltp do not set it to 2 set it to 0 instead!!! Superboer. Jim Kenedy wrote: > Hello All, > Here's some quick history... Recently the number of users has > increased to over 500 sessions. All our users are now complaining of > poor performance. Our checkpoints are slow to complete (4 seconds and > up to 13 seconds during peak activity) and the CPU activity is > constantly over 95%. I've been tweaking LRU, CLEANERS, BUFFERS and > I'm now more perplexed than ever. > > This is an OLTP system with about 5 batch jobs that are constantly run > during the day. No other processes other than these batch jobs are run > on the database server. The system is not swapping and memory used is > sitting at 95%. At one point, a number of years ago, I tried kaio but > on this SCO platform it actually performed worse; but perhaps I should > try it once again. > > Informix Dynamic Server Version 7.31.UD8 > SCO OpenServer 5.0.6 > Dual Pentium III Xeon 1GHz, 4GB ram, 12 * 18GB hard drives arranged in > RAID 1 pairs (the first pair is dedicated to the O/S, the rest to > Informix) > > Update statistics is run nightly using one of the very good IIUG 4GL> programs. > We only have one temp dbspace since we cannot allocate more because of > an application incompatibility (it uses table rowids). > > I've exhausted input from a number of performance tuning books and > searches through this newsgroup. > > Any help would be greatly appreciated... > > Thanks, > Ron > > -------------------------------------------------- > # Root Dbspace Configuration > ROOTNAME rootdbs # Root dbspace name> ROOTPATH /dev/rdbs_root # Path for device containing root > dbspace > ROOTOFFSET 0 # Offset of root dbspace into device > (Kbytes) > ROOTSIZE 1024000 # Size of root dbspace (Kbytes)> > # Disk Mirroring Configuration Parameters > MIRROR 0 # Mirroring flag (Yes = 1, No = 0) > MIRRORPATH # Path for device containing mirrored> root > MIRROROFFSET 0 # Offset into mirrored device (Kbytes)> > # Physical Log Configuration > PHYSDBS rootdbs # Location (dbspace) of physical log > PHYSFILE 240000 # Physical log file size (Kbytes)> > # Logical Log Configuration > LOGFILES 60 # Number of logical log files > LOGSIZE 1000 # Logical log size (Kbytes)> > # Diagnostics > MSGPATH /u/informix7/online.log # System message log file path > CONSOLE /u/informix7/online.log # System console message path > ALARMPROGRAM /u/informix7/etc/log_full.sh # Alarm program path > SYSALARMPROGRAM /u/informix7/etc/evidence.sh # System Alarm program > path > TBLSPACE_STATS 1> > # System Archive Tape Device > TAPEDEV secondary:/dev/rStp0 # Tape device path > TAPEBLK 64 # Tape block size (Kbytes) > TAPESIZE 50000000 # Maximum amount of data to put on tape > (Kbytes)> > # Log Archive Tape Device > LTAPEDEV /u/informix7/db-logs/logtape # Log tape device path > LTAPEBLK 64 # Log tape block size (Kbytes) > LTAPESIZE 25000000 # Max amount of data to put on log tape > (Kbytes)> > # Optical > STAGEBLOB # Informix Dynamic Server/Optical > staging area > > # System Configuration > SERVERNUM 0 # Unique id corresponding to a Dynamic> Server instance > DBSERVERNAME local_on # Name of default database server > DBSERVERALIASES primary # List of alternate dbservernames > NETTYPE ipcshm,2,50,CPU # Configure poll thread(s) for nettype > NETTYPE tlitcp,2,300,NET # Configure poll thread(s) for nettype > DEADLOCK_TIMEOUT 60 # Max time to wait of lock in> distributed env. > RESIDENT 1 # Forced residency flag (Yes = 1, No = > 0) > > MULTIPROCESSOR 1 # 0 for single-processor, 1 for> multi-processor > NUMCPUVPS 2 # Number of user (cpu) vps > SINGLE_CPU_VP 0 # If non-zero, limit number of cpu vps> to one > > NOAGE 1 # Process aging > AFF_SPROC 0 # Affinity start processor > AFF_NPROCS 2 # Affinity number of processors> > # Shared Memory Parameters > LOCKS 150000 # Maximum number of locks > # Jan 18-06...> #BUFFERS 700000 # Maximum number of shared buffers > BUFFERS 600000 # Maximum number of shared buffers > # END Jan 16-06> # Jan 19-06: Try to decrease maxlen in onstat -q ioq and have one > # aio io/s in onstat -g iov ~1 > #NUMAIOVPS 16 # Number of IO vps > NUMAIOVPS 18 # Number of IO vps > # END Jan 19-06 > PHYSBUFF 32 # Physical log buffer size (Kbytes) > LOGBUFF 32 # Logical log buffer size (Kbytes)> LOGSMAX 60 # Maximum number of logical log files > # Jan 19-06: set CLEANERS >= LRUS > #CLEANERS 32 # Number of buffer cleaner processes > CLEANERS 127 # Number of buffer cleaner processes > # END Jan 19-06 > SHMBASE 0x10000000 # Shared memory base address > # Jan 18-06> #SHMVIRTSIZE 153600 # initial virtual shared memory > segment size > SHMVIRTSIZE 235520 # initial virtual shared memory segment> size > #SHMADD 8192 # Size of new shared memory segments > (Kbytes) > SHMADD 16384 # Size of new shared memory segments > (Kbytes) > # END Jan 18-06 > SHMTOTAL 0 # Total shared memory (Kbytes). > 0=>unlimited > CKPTINTVL 300 # Check point interval (in sec)> # Jan 19-06: Pump this up and avoid = 64, 96 to lower bufwaits > # and reduce the buffer turnover rate > # and potentially lower checkpoints > #LRUS 64 # Number of LRU queues > LRUS 127 # Number of LRU queues > # END Jan 19-06 > LRU_MAX_DIRTY 2 # LRU percent dirty begin cleaning> limit > LRU_MIN_DIRTY 1 # LRU percent dirty end cleaning limit > LTXHWM 50 # Long transaction high water mark> percentage > LTXEHWM 60 # Long transaction high water mark > (exclusive) > TXTIMEOUT 0x12c # Transaction timeout (in sec) > STACKSIZE 32 # Stack size (Kbytes)> > # System Page Size > # BUFFSIZE - Dynamic Server no longer supports this configuration > parameter. > # To determine the page size used by Dynamic Server on your > platform > # see the last line of output from the command, 'onstat -b'. > > # Recovery Variables > # OFF_RECVRY_THREADS: > # Number of parallel worker threads during fast recovery or an offline > restore. > # ON_RECVRY_THREADS: > # Number of parallel worker threads during an