Re: Informix UC7.41 - slow

Geezer From The Freezer wrote: OK, I see several problems off the bat, though this is not a fair run unless you really are ONLY doing unloads from this server. Anyway: - 23% of your queries include a sequential scan! You have some indexing or update statistics issues. - BR is 600%, anything over 7% is slow over 10% is death 600% is BEYOND LRU contention, though I suspect that that's part of it, you are just waiting for data. More BUFFERS might help, 90% of your buffers are used up by two tables. - One of these two busy tables likely lives on chunk rnc1 and the other on rnc2, but I can't tell that their not interleaved on both. Anyway, rnc1 is being HAMMERED. Onstat -g iof shows that chunk is getting >484 io/sec sent to it that amounts to 968KB/sec which is more than three times the total throughput of a SCSI Ultra-320 controller channel and almost 5 times the maximum sustainable throughput of a 15,000 RPM SCSI drive! You need to spread those IOs across at least 8 spindles and at least 4 SCSI channels or the equivalent. Don't think this blade server's going to be able to do that! - To satisfy all those backed up requests your 16 AIO VPs are overworked, you could keep 24 busy right now and if you ever get enough IO bandwidth connected you'll need closer to 32 AIO VPs to keep up. - Meanwhile, increase LRUS and CLEANERS from defaults to 37 each (DO NOT USE 32 whatever you do!) - Try increasing BUFFERS again to 100,000 to try to reduce the IO load by keeping more data in memory - Make sure that UPDATE STATISTICS are being run to the recommended levels from the Performance Guide (or run my dostats utility which automates them). - Take a look at the queries being run (onstat -g sql) and see if you can add indexes. Then table with partnum 3146051 has only 56 index pages in the buffer cache versus 21825 data pages and unless all the active keys on that table are 1/2byte long (2020 / (21825 / 56)) there are VERRRYY few index pages in memory for that many data pages. Reducing sequential scans will go a long way to reducing the required IO load. Art S. Kagel > "Art S. Kagel" wrote: > >>Geezer From The Freezer wrote: >> >>>TBP wrote: >>> >>> >>>>Geezer From The Freezer wrote: >>>> >>>> >>>>>Hi, >>>>> >>>>>My Informix installation seems to be running quite slowly. It's running on a Sun >>>>>Blade >>>>>1000 with 2 x 750Mhz processors and 2Gb RAM. >>>>> >>>>>Here is the onconfig file - anything obviously wrong or tuneable? >>>> >>>>Yes :D >>>> >>>>No such version as UC7.41, but presumably this is something like 7.31.UC7?? >>> >>> >>>oops. 7.31.UC5 even, my mistake :D >> >>OK, you're on the right track now. Repost your ONCONFIG info (so we don't >>have to hunt down the original post) along with ALL of the following and >>someone will help: >> >>Time since startup or since onstat -z was run if later. Output from: >>onstat -p >>onstat -d >>onstat -P >>onstat -D >>onstat -m >>onstat -F >>onstat -g glo >>onstat -g iov >>onstat -g iof >>onstat -g rea>> >>Art S. Kagel > > > ok ran onstat -z to remove stuff - ran some simple export commands from the > application running > over informix. Here is more info - > > onconfig file > > # Root Dbspace Configuration > > ROOTNAME rootdbs # Root dbspace name > ROOTPATH /usr/informix/data/data1> # Path for device containing root dbspace > ROOTOFFSET 0 # Offset of root dbspace into device (Kbytes) > ROOTSIZE 200000 # 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 # Physical log file size (Kbytes) > PHYSFILE 32768 # Physical log file size (Kbytes)> > # Logical Log Configuration > > LOGFILES 45 # Number of logical log files > LOGSIZE 2048 # Logical log size (Kbytes)> > # Diagnostics > > MSGPATH /usr/informix/online.log > # System message log file path > CONSOLE /usr/informix/console.log > # System console message path > ALARMPROGRAM /usr/informix/etc/log_full.sh # Alarm program path > SYSALARMPROGRAM /usr/informix/etc/evidence.sh # System Alarm program path > TBLSPACE_STATS 1> > # System Archive Tape Device > > TAPEDEV /dev/null # Tape device path > TAPEBLK 256 # Tape block size (Kbytes) > TAPESIZE 128000 # Maximum amount of data to put on tape (Kbytes)> > # Log Archive Tape Device > > LTAPEDEV /dev/null # Log tape device path > LTAPEBLK 256 # Log tape block size (Kbytes) > LTAPESIZE 128000 # Max amount of data to put on log tape (Kbytes)> > # Optical > > STAGEBLOB > > # System Configuration > > SERVERNUM 0 # Unique id corresponding to a Dynamic Server> instance > DBSERVERNAME mlgw333 # Name of default database server > DBSERVERALIASES # List of alternate dbservernames > DEADLOCK_TIMEOUT 60 # Max time to wait of lock in distributed env. > RESIDENT 0 # Forced residency flag (Yes = 1, No = 0) > > MULTIPROCESSOR 0 # 0 for single-processor, 1 for multi-processor > NUMCPUVPS 1 # Number of user (cpu) vps > SINGLE_CPU_VP 0 # If non-zero, limit number of cpu vps to one > > NOAGE 1 > AFF_SPROC 0 # Affinity start processor > AFF_NPROCS 0 # Affinity number of processors> > # Shared Memory Parameters > > LOCKS 550000 # Maximum number of locks > BUFFERS 50000 # Maximum number of shared buffers > NUMAIOVPS # Number of IO vps > PHYSBUFF 1024 # Size of root dbspace (Kbytes) > LOGBUFF 1024 # Logical log buffer size (Kbytes)> LOGSMAX 200 # Maximum number of logical log files > CLEANERS 16 # Number of buffer cleaner processes > SHMBASE 0xa000000 # Shared memory base address > SHMVIRTSIZE 16384 # initial virtual shared memory segment size > SHMADD 8192 # Size of new shared memory segments (Kbytes) > SHMTOTAL 0 # Total shared memory (Kbytes). 0=>unlimited > CKPTINTVL 300 # Check point interval (in sec) > LRUS 16 # Number of LRU queues > 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