Re: Major performance problems with Informix Online

In article <61gtoo$g6o$1@news3.Belgium.EU.net>, Koen Van der Elst <koen.vanderelst@advalvas.be> writes > >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? > It is probably a link to 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. > So if it crashes just as a logical log fills up and before it is backed up to tape you lose 20Mb worth of transactions?!!! >> >># 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. > No! More is better. >>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! > That's Online 5, under Online 7 cleaners do not write the data they merely place it on aio/kaio queues. 1 per CPU under 7. >>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. > Again 1 per CPU. >>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. > Agreed, >>LTXHWM 50 # Long transaction high water mark>>percenta