q1: update statistics crash informix , q2: performance when to need to change machine.

hey all. i got 2 questions using IUS 9.14.UC3 first questions: we're using datablade excalibur. we're created this table: create table t_foo ( id_foo serial not null constraint "dfloch".n133_278, nom varchar(255), numero_voie varchar(15), nom_voie varchar(255), nom_quartier varchar(100), adresse2 varchar(255), cp varchar(5), tel varchar(20), email varchar(100), web varchar(200), secteurn1 integer, commentaire "informix".clob, primary key (id_organisme) constraint u133_277 );with the kind of index on the same column. the first index using btree and the second using excalibur. we generated updates statistics request with Art S. Kagel's dostats. this request :UPDATE STATISTICS HIGH FOR TABLE t_foo (nom) crash informix!!!! 02:00:32 Assert Failed: No Exception Hander 02:00:32 Who: Session(62508, informix@pipotron, 28356, 0) Thread(1514016, sqlexec, 0, 0) File: mtex.c Line: 363 02:00:32 Results: Exception Caught. Type: MT_EX_OS, Context: mem 02:00:32 Action: Please notify Informix Technical Support.seems because we're using 2 index on the same columns. but does anybody know really why? second question: on UltraSPARC - II 248MHz 256Mb with IUS 9.14.UC3 we're trying to tune the performance of this machine . We 're got more and more connection. difficult to count the number because it comes from Internet. based on top, bufwait_ratio, onstat -p etc we notice bufwait_ratio keep increasin:1.4680069879528 1.61194255187721 2.01160473306121 2.71271009478005 2.89120052045105 3.41613642008222 4.0130288111384 3.9947363311289 4.56486751753462 5.30708343610557 3.82214714320148 5.02053680360054 5.68746202393749 5.88830558938297 6.36799856860363 6.73750819722825 6.92488916933377 6.92721466365835 6.94676862264538 6.71867811482314 6.74834672779293 6.56881055424866 6.55429661118465 6.48148760167169 6.48709085293811 6.33092974629547 6.27122705775875 6.35293357034056 6.37281967070884 6.62357233817041 6.62732838896883 3.40879584550471 5.97868363925157 values on onstat -p which decrease: dskreads pagreads bufreads %cached dskwrits pagwrits bufwrits %cache 3169886 44935869 1298077691 98.22 4374737 22218616 25091870 82.57 25223341 49339844 1424636291 98.23 4789952 24205113 27142805 82.35 25881795 50420936 1458845523 98.23 4887594 24716522 27753426 82.39 27863736 54743399 1576665204 98.23 5262433 26639761 30196347 82.57 28047338 55101204 1599200522 98.25 5307187 26829747 30341191 82.51 28813523 56749124 1675185568 98.28 5532839 27878084 31150589 82.24 28976315 57045046 1692285473 98.29 5580715 28045280 31304788 82.17 30237638 59896887 1783553169 98.30 5985955 29859002 32803036 81.75 31266019 62246638 1822778293 98.28 6129716 30568319 33646892 81.78 34116355 67097202 1957002805 98.26 6602403 32924758 35884750 81.60 35176653 68674473 1996324916 98.24 6729866 33548629 36603604 81.61 39113256 75137529 2143764399 98.18 7193078 35668577 39357197 81.72 40437614 77085914 2184140234 98.15 7356198 36438095 40547943 81.86 475308 601776 17468271 97.28 38676 106088 260904 85.18 1530405 2234057 63718103 97.60 196118 798288 959171 79.55 top'idle is about 50% and sometimes 0% we're add 1 cleaner (we got now 2 cleaners), add more BUFFER 12000 to 15000 and add more logfiles. of cause this modifications weren't made in one time. but scattered during weeks. but the performance seems degrade again. finally i wonder if I should change our machine!!! INFORMIX-Universal Server Version 9.14.UC4 -- On-Line -- Up 15:40:21 -- 170816 Kbytes Configuration File: /usr/local/informix/etc/onconfig.std #************************************************************************** # # INFORMIX SOFTWARE, INC. # # Title: onconfig.std # Description: INFORMIX-Universal Server Configuration Parameters # #************************************************************************** # Root Dbspace Configuration ROOTNAME rootdbs # Root dbspace nameROOTPATH /usr/local/informix/dbspaces/rootdbs # Path for device containing root dbspace ROOTOFFSET 16 # Offset of root dbspace into device (Kbytes) ROOTSIZE 100000 # Size of root dbspace (Kbytes) # Disk Mirroring Configuration Parameters MIRROR 1 # Mirroring flag (Yes = 1, No = 0) MIRRORPATH /usr/local/informix/dbspaces/m_rootdbs # Path for device containing mirrored root MIRROROFFSET 16 # Offset into mirrored device (Kbytes) # Physical Log Configuration PHYSDBS logsdbs # Location (dbspace) of physical log PHYSFILE 4000 # Physical log file size (Kbytes) # Logical Log Configuration LOGFILES 9 # Number of logical log files LOGSIZE 1500 # Logical log size (Kbytes) # Diagnostics MSGPATH /var/informix/logs/online.log # System message log file path CONSOLE /var/informix/logs/console # System console message path ALARMPROGRAM /usr/local/informix/etc/log_full.sh # Alarm program path # System Archive Tape Device TAPEDEV /var/tmp/informix/tapedev # Tape device path TAPEBLK 16 # Tape block size (Kbytes) TAPESIZE 3993600 # Maximum amount of data to put on tape (Kbytes) # Log Archive Tape Device LTAPEDEV /var/tmp/informix/ltapedev # Log tape device path LTAPEBLK 16 # Log tape block size (Kbytes) LTAPESIZE 3993600 # Max amount of data to put on log tape (Kbytes) # Optical STAGEBLOB # INFORMIX-OnLine/Universal Server staging area # System Configuration SERVERNUM 3 # Unique id corresponding to a OnLineinstance DBSERVERNAME irena # Name of default database server DBSERVERALIASES local3 # List of alternate dbservernames DEADLOCK_TIMEOUT 60 # Max time to wait of lock in distributed env. RESIDENT 1 # Forced residency flag (Yes = 1, No = 0) MULTIPROCESSOR 0 # 0 for single-processor, 1 formulti-processor NUMCPUVPS 1 # Number of user (cpu) vps SINGLE_CPU_VP 1 # If non-zero, limit number of cpu vps toone NOAGE 0 # Process aging AFF_SPROC 0 # Affinity start processor AFF_NPROCS 0 # Affinity number of processors # Shared Memory Parameters LOCKS 8000 # Maximum number of locks BUFFERS 12000 # Maximum number of shared buffers NUMAIOVPS 1 # Number of IO vps PHYSBUFF 64 # Physical log buffer size (Kbytes) LOGBUFF 64 # Logical lo

I do not know the answer to the first question except to suggest that you have dostats write a script and see what happens if you run the script -vs- running dostats live. Does the engine crash with the script? If not does it crash if you run dostats again or was that a one-time thing? On the second question the worst bufwaits ratio you show is about 6.9 and as I stated anything under 7 is just fine. However that is running pretty close to the cuff. So I looked at your config. You say you have many users but you only have 12 LRUS, this is the cause of any excess bufwaits. Increase LRUS to at least 32 or try going for broke at 127 or 128. You may see bufwait percentages around 3.0. Other items: 12000 buffers is not much unless the database is very small and all transactions and queries are against very few rows. Also keep CLEANERS the same as LRUS for best performance. Art S. Kagel georges Chaleunsinh wrote: > > hey all. > > i got 2 questions > using IUS 9.14.UC3 > > first questions: > we're using datablade excalibur. > we're created this table: > > create table t_foo > ( > id_foo serial not null constraint "dfloch".n133_278, > nom varchar(255), > numero_voie varchar(15), > nom_voie varchar(255), > nom_quartier varchar(100), > adresse2 varchar(255), > cp varchar(5), > tel varchar(20), > email varchar(100), > web varchar(200), > secteurn1 integer, > commentaire "informix".clob, > primary key (id_organisme) constraint u133_277 > );> with the kind of index on the same column. the first index using btree and > the second using excalibur. > we generated updates statistics request with Art S. Kagel's dostats. > > this request :UPDATE STATISTICS HIGH FOR TABLE t_foo (nom) crash > informix!!!! > > 02:00:32 Assert Failed: No Exception Hander > 02:00:32 Who: Session(62508, informix@pipotron, 28356, 0) > Thread(1514016, sqlexec, 0, 0) > File: mtex.c Line: 363 > 02:00:32 Results: Exception Caught. Type: MT_EX_OS, Context: mem > 02:00:32 Action: Please notify Informix Technical Support.> seems because we're using 2 index on the same columns. > > but does anybody know really why? > > second question: > > on UltraSPARC - II 248MHz 256Mb with IUS 9.14.UC3 > we're trying to tune the performance of this machine . We 're got more and > more connection. difficult to > count the number because it comes from Internet. > > based on top, bufwait_ratio, onstat -p etc we notice > > bufwait_ratio keep increasin:1.4680069879528 > 1.61194255187721 > 2.01160473306121 > 2.71271009478005 > 2.89120052045105 > 3.41613642008222 > 4.0130288111384 > 3.9947363311289 > 4.56486751753462 > 5.30708343610557 > 3.82214714320148 > 5.02053680360054 > 5.68746202393749 > 5.88830558938297 > 6.36799856860363 > 6.73750819722825 > 6.92488916933377 > 6.92721466365835 > 6.94676862264538 > 6.71867811482314 > 6.74834672779293 > 6.56881055424866 > 6.55429661118465 > 6.48148760167169 > 6.48709085293811 > 6.33092974629547 > 6.27122705775875 > 6.35293357034056 > 6.37281967070884 > 6.62357233817041 > 6.62732838896883 > 3.40879584550471 > 5.97868363925157 > > values on onstat -p which decrease: > dskreads pagreads bufreads %cached dskwrits pagwrits bufwrits %cache > 3169886 44935869 1298077691 98.22 4374737 22218616 25091870 82.57 > 25223341 49339844 1424636291 98.23 4789952 24205113 27142805 82.35 > 25881795 50420936 1458845523 98.23 4887594 24716522 27753426 82.39 > 27863736 54743399 1576665204 98.23 5262433 26639761 30196347 82.57 > 28047338 55101204 1599200522 98.25 5307187 26829747 30341191 82.51 > 28813523 56749124 1675185568 98.28 5532839 27878084 31150589 82.24 > 28976315 57045046 1692285473 98.29 5580715 28045280 31304788 82.17 > 30237638 59896887 1783553169 98.30 5985955 29859002 32803036 81.75 > 31266019 62246638 1822778293 98.28 6129716 30568319 33646892 81.78 > 34116355 67097202 1957002805 98.26 6602403 32924758 35884750 81.60 > 35176653 68674473 1996324916 98.24 6729866 33548629 36603604 81.61 > 39113256 75137529 2143764399 98.18 7193078 35668577 39357197 81.72 > 40437614 77085914 2184140234 98.15 7356198 36438095 40547943 81.86 > 475308 601776 17468271 97.28 38676 106088 260904 85.18 > 1530405 2234057 63718103 97.60 196118 798288 959171 79.55 > > top'idle is about 50% and sometimes 0% > > we're add 1 cleaner (we got now 2 cleaners), add more BUFFER 12000 to 15000 > and add more logfiles. > of cause this modifications weren't made in one time. but scattered during > weeks. > but the performance seems degrade again. > > finally i wonder if I should change our machine!!! > > INFORMIX-Universal Server Version 9.14.UC4 -- On-Line -- Up 15:40:21 -- > 170816 Kbytes > > Configuration File: /usr/local/informix/etc/onconfig.std > #************************************************************************** > # > # INFORMIX SOFTWARE, INC. > # > # Title: onconfig.std > # Description: INFORMIX-Universal Server Configuration Parameters > # > #************************************************************************** > > # Root Dbspace Configuration > > ROOTNAME rootdbs # Root dbspace name> ROOTPATH /usr/local/informix/dbspaces/rootdbs > # Path for device containing root dbspace > ROOTOFFSET 16 # Offset of root dbspace into device > (Kbytes) > ROOTSIZE 100000 # Size of root dbspace (Kbytes)> > # Disk Mirroring Configuration Parameters > > MIRROR 1 # Mirroring flag (Yes = 1, No = 0) > MIRRORPATH /usr/local/informix/dbspaces/m_rootdbs > # Path for device containing mirrored root > MIRROROFFSET 16 # Offset into mirrored device (Kbytes)> > # Physical Log Configuration > > PHYSDBS logsdbs # Location (dbspace) of physical log > PHYSFILE 4000 # Physical log file size (Kbytes)> > # Logical Log Configuration > > LOGFILES 9 # Number of logical log files > LOGSIZE 1500 # Logical log size (Kbytes)> > # Diagnostics > > MSGPATH /var/informix/logs/online.log # System message log file path > CONSOLE /var/informix/logs/console # System console message path > ALARMPROGRAM /usr/local/informix/etc/log_full.sh # Alarm program path > > # System Archive Tape Device > > TAPEDEV /var/tmp/informix/tapedev # Tape device path > TAPEBLK 16 # Tape block size (Kbytes) > TAPESIZE 3993600 # Maximum amount of data to put on tape > (Kbytes)> > # Log Archive Tape Device > > LTAPEDEV /var/tmp/informix/ltapedev # Log tape device path > LTAPEBLK 16 # Log tape block size (Kbytes) > LTAPESIZE 3993600 # Max amount of data to put on log tape > (Kbytes)@@NL@