Re: Major performance problems with Informix Online

Hello, > Siemens Nixdorf RM600 containing > 8 RISC Processors > 900MB RAM > 80GB Disk Space That's nice but how is this diskspace used? RAID0, RAID1, RAID 5. That fastest way is either using no RAID at and using Informix mirroring or using RAID 1. How many dbspaces do you have and how are they spread accross the discs? Spread your dbspaces accoss your disks and try to stick to the rule: 1 dbspace = 1 chunk = 1 Unix device. Move the logical logs and the physical log out of the rootdbspace to separate disks. Use seperate dbspaces for indexes and data. Create the dbspaces in round robin fashion. Eg. Disk 1 Disk 2 Disk 3 root(1) llogs(2) physlog(3) dbs1(3) dbs2(4) dbs3(5) idx1(6) idx2(7) idx3(8) tmp1(9) tmp2(10) tmp3(11) > Approximately 60-80 users. > It's running a Patient Administration System > >Below is a copy of the online configurations (there are two on our box >and i'm not sure which is being used.) > >onconfig.camis_l: > >#************************************************************************** ># ># INFORMIX SOFTWARE, INC. ># ># Title: onconfig.camis_l ># Description: INFORMIX-OnLine Configuration Parameters ># ># Created on 7 March 1996 ># >#************************************************************************** > ># Root Dbspace Configuration > >ROOTNAME rootdbs # Root dbspace name >ROOTPATH /home/local/dev/camis_l_onraw010s7> # Path for device containing root >dbspace Are you sure this is a raw device? >ROOTOFFSET 0 # Offset of root dbspace into device >(Kbytes) >ROOTSIZE 20000 # 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 camis_phy01 # Location (dbspace) of physical log >PHYSFILE 249894 # Physical log file size (Kbytes)> ># Logical Log Configuration > >LOGFILES 30 # Number of logical log files >LOGSIZE 20000 # Logical log size (Kbytes) If your DB size is relative to your disksize, this is not enough. You need approx. 20% of your effictive data. For 1 GB of data, use 200 MB of ll's, say 10 of 20 MB each. > ># Diagnostics > >MSGPATH /home/informix/grimpas_camis_online.log > # System message log file path >CONSOLE /dev/console # System console message path >ALARMPROGRAM /opt/lib/informix/etc/log_full.sh # Alarm program path> ># System Archive Tape Device > >TAPEDEV /dev/null # Tape device path What the heck???? Don't you people perforn backups? >TAPEBLK 64 # Tape block size (Kbytes) >TAPESIZE 4500000 # Maximum amount of data to put on >tape (Kbytes)> ># Log Archive Tape Device > >LTAPEDEV naj # Log tape device path >LTAPEBLK 64 # Log tape block size (Kbytes) >LTAPESIZE 4500000 # Max amount of data to put on log >tape (Kbytes)> ># Optical > >STAGEBLOB # INFORMIX-OnLine/Optical staging area > > ># System Configuration > >SERVERNUM 50 # Unique id corresponding to a OnLine>instance >DBSERVERNAME grimpascamis_l_shm # Name of default database server >DBSERVERALIASES grimpascamis_l_tcp # List of alternate dbservernames >NETTYPE ipcshm,1,200,CPU # Override sqlhosts nettype>parameters >NETTYPE tlitcp,1,200,NET # Override sqlhosts nettype>parameters >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 6 # Number of user (cpu) vps Try to decrease the number of CPU VP's. Too much is no good. Start out with 2 or 3. >SINGLE_CPU_VP 0 # If non-zero, limit number of cpu vps>to one > >NOAGE 0 # Process aging I believe Sinix supports this feature, turn it on. >AFF_SPROC 0 # Affinity start processor >AFF_NPROCS 0 # Affinity number of processors If all fails, bind VP's to real CPU's. > ># Shared Memory Parameters > >LOCKS 10000 # Maximum number of locks That's not very much for such a large DB. >BUFFERS 20000 # Maximum number of shared buffers You have 900 MB, use it! >NUMAIOVPS 12 # Number of IO vps Deacrease this: on a Siemens, KAIO is supported, 2 or 3 AIOVP's should do it! >PHYSBUFF 32 # Physical log buffer size (Kbytes) >LOGBUFF 32 # Logical log buffer size (Kbytes) use onstat -l to make sure the no. of pages per io is more then 75% of the available number of pages. >LOGSMAX 100 # Maximum number of logical log files >CLEANERS 9 # Number of buffer cleaner processes 1 cleaner per physical disk! >SHMBASE 0x20000000 # Shared memory base address >SHMVIRTSIZE 16384 # initial virtual shared memory>segment size >SHMADD 16384 # Size of new shared memory segments >(Kbytes) >SHMTOTAL 0 # Total shared memory (Kbytes). >0=>unlimited >CKPTINTVL 300 # Check point interval (in sec) >LRUS 4 # Number of LRU queues You could increase this one. Play with it. >LRU_MAX_DIRTY 3 # LRU percent dirty begin cleaning>limit >LRU_MIN_DIRTY 1 # LRU percent dirty end cleaning limit Jees, the server is flushing all the time. Try 30 for MAX and 20 for MIN. Check with onstat -F to make sure that the writes are mostly LRU writes, CHUNCK write are OK if your checkpoints take less than 1 second. >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 - OnLine no longer supports this configuration parameter. ># To determine the page size used by OnLine 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. > >O