Extremely Slow

Dear Informix Gurus, It's almost 21:00 now. We have signed maintenance contract with local Informix Inc., but their engineers have all been off duty and can not help me until tomorrow. Our engine is RSAM Version 5.10.UC1 upon SCO OpenServer 5.04 with 512MB RAM. Last night our database corrupted due to frequent power off/on (We hadn't known that the UPS had been dead). Luckily, we successfully inintialized the dbspaces and dbimported the database from backup. When initializing the dbspace, I created 3 local log files each of 8MB (It had been 60 X 1KB=60KB total before the corruption). The number and size of logical files are the only parameters I changed after database corruption. We use raw device for dbspaces. The dbimport took about 5 hours to finish re-building 4 GB database. There were about only 5 users logged in during dbimporting. We happily went home at 22:00 last night after having rebuilt the database. This morning everyone came back to the office to work. The number of users logged in to run 4GL increased. (I did not checked the number of logins that time. Usually 60 logins were the maxmimum). Then, the machine became slower and slower, and finally almost was as slow as dead. sar -u shows about 98%wio and 0%idle. It took more than 5 miniutes to login to OS. The company virtually stopped operating and my boss shouted to kill me. We rebooted the OS this noon and provided the services again at 14:00. In the beginning, the speed was still slow but at least it was able to provide services. Then, the machine started to crawl again since about 16:30. The local Informix engineer told us to do 2 things: (1) increasing number of logicl files to at least 6 and (2) updating statistics. Now we have done these (the number of logical files is now 20 = 160MB total, and I will increase it to 60 files later) as told by Informix. Please note 2 things: (1) This machine was working fine before database corruption. The size and number of logical files were the only parameters I changed after database corruption. We made no modification on this hardware and OS after data corruption. (2) I have experienced this same problem on a newly installed testing hardware (PIII 450 + 512MB SDRAM + Mylex RAID card + 4*Quantium Atlas 10K + 0.5GB Informix logical log space) - it works fast for 1 or 2 users processing big tables but starts to crawl when all people came in running 4GL. tbconfig: PHYSDBS rootdbs PHYSFILE 8192 LOGFILES 20 LOGSIZE 8192USERS 200 TRANSACTIONS 200 LOCKS 100000 BUFFERS 15000TBLSPACES 400 CHUNKS 8 DBSPACES 8 PHYSBUFF 512 LOGBUFF 1024LOGSMAX 60 CLEANERS 4 SHMBASE 0x0 CKPTINTVL 900BUFFSIZE 2048 LRUS 16 LRU_MAX_DIRTY 8 LRU_MIN_DIRTY 3 LTXHWM 40 LTXEHWM 50DYNSHMSZ 0 GTRID_CMP_SZ 32 TXTIMEOUT 300SPINCNT 0 /etc/conf/cf.d/mtune: * Semaphore Parameters SEMMAP 10 10 100 SEMMNI 4096 300 300 SEMMNS 4096 60 300 SEMMNU 30 10 100 SEMMSL 25 25 60 SEMOPM 10 10 10 SEMUME 10 10 10 SEMVMX 32767 32767 32767 SEMAEM 16384 16384 16384 * Shared Memory Parameters SHMMAX 60000000 131072 2147483647 SHMMIN 1 1 1 SHMMNI 255 100 2000 tbstat -d: RSAM Version 5.10.UC1 -- On-Line -- Up 02:57:02 -- 42144 Kbytes Dbspaces address number flags fchunk nchunks flags owner name 80055b7c 1 1 1 4 N informix rootdbs 1 active, 8 total Chunks address chk/dbs offset size free bpages flags pathname 800556bc 1 1 0 1000000 4 PO- /dev/ru2 80055754 2 1 0 1000000 2 PO- /dev/ru3 800557ec 3 1 0 1000000 386697 PO- /dev/p2d1 80055884 4 1 0 1000000 999997 PO- /dev/p2d2 4 active, 8 total I much worrry about the outcome when tommorrow comes. Can someone give me any suggestions so that I can try once this machine starts to crawl again? Thank you all in advance! CN

CN Liu wrote: > > Dear Informix Gurus, > [SNIP] > Last night our database corrupted due to frequent power off/on (We > hadn't known that the UPS had been dead). Luckily, we successfully > inintialized the dbspaces and dbimported the database from backup. When > initializing the dbspace, I created 3 local log files each of 8MB (It > had been 60 X 1KB=60KB total before the corruption). The number and size > of logical files are the only parameters I changed after database > corruption. We use raw device for dbspaces. > > The dbimport took about 5 hours to finish re-building 4 GB database. > There were about only 5 users logged in during dbimporting. We happily > went home at 22:00 last night after having rebuilt the database. > > This morning everyone came back to the office to work. The number of > users logged in to run 4GL increased. (I did not checked the number of > logins that time. Usually 60 logins were the maxmimum). Then, the > machine became slower and slower, and finally almost was as slow as > dead. sar -u shows about 98%wio and 0%idle. It took more than 5 miniutes > to login to OS. The company virtually stopped operating and my boss > shouted to kill me. [SNIP] > tbconfig: > > PHYSDBS rootdbs The physical and logical logs MUST be in a separate dbspace from the rootdbs, as must the databases, on separate disks from data and rootdbs. This is CRITICAL to good performance even more so in 5.xx than it is in 7.xx! [SNIP] > BUFFERS 15000 More buffers. Post onstat -p so we can diagnose the problem. [SNIP] > CHUNKS 8 > DBSPACES 8 Increase these, it is highly likely that you will eventually need more than 8 dbspaces and chunks and then you will have to bounce the engine to be able to add the ninth one. I'd set these to the 5.xx max of 64 each it costs almost no memory to do so. > PHYSBUFF 512 That's a very large physical log buffer which puts much data at risk of loss! 32-64 is usually quite sufficient. > LOGBUFF 1024 Same here. Unless you are using unbuffered logging, in which case you do not need a large logical log buffer anyway, you are risking too much data with so large a logical log buffer. 32-64K is usually good enough also note that if you use unbuffered logging you are wasting log space since the buffer is flushed whenever a transaction completes and the entire 1024K of buffer of written to the logfile even though only a small portion may actually be used. Use onstat -k to look at the typical portions of the buffer that is being used and ajust accordingly. BTW there are three logical log buffers so your buffers are using 3MB of memory! [SNIP] > CLEANERS 4 CLEANERS should be >= LRUS so make this at least 16 for best flush performance. [SNP] > tbstat -d: > > RSAM Version 5.10.UC1 -- On-Line -- Up 02:57:02 -- 42144 Kbytes > > Dbspaces > address number flags fchunk nchunks flags owner name > 80055b7c 1 1 1 4 N informix rootdbs ARRRGGGG! Only one dbspace and that is root! BAD VERY BAD! You should have AT LEAST three, and preferably four, dbspaces: - rootdbs where there should be NO DATA AT ALL! Used for configuration information, reserved pages, sorting, temp tables etc. - logdbs - for physical and logical logs. This MUST be on a separate disk drive from rootdbs and the data dbspaces. Ideally you should have two log dbspaces a physlogdbs and a loglogdbs again on two separate drives, but it update volume is low you can get away with one. - Data dbspaces, one or more. More than one will allow you to load balance your data tables across multiple drives by placing different tables in different dbspaces built from chunks on separate drives. > 1 active, 8 total > > Chunks > address chk/dbs offset size free bpages flags pathname > 800556bc 1 1 0 1000000 4 PO- /dev/ru2 > 80055754 2 1 0 1000000 2 PO- /dev/ru3 > 800557ec 3 1 0 1000000 386697 PO- /dev/p2d1 > 80055884 4 1 0 1000000 999997 PO- /dev/p2d2 DO NOT USE actual device names. Set up a directory somewhere, like under the Informix home (though in OL5.xx you will get more chunks if you use a directory like /d) and place symbolic links there pointing to the actual chunks in /dev then use the link names for chunk paths. This will give you added flexibility in the event of a crash to replace a bad drive with another drive by relinking the symbolic links. > 4 active, 8 total Post onstat -p, onstat -k, onstat -l, onstat -D, onstat -R, onstat -F and we'll see what we can do. Art S. Kagel

In article <7srlna$97o$1@news.xmission.com>, CN Liu <cn@mail.sinyih.com.tw> writes > >Dear Informix Gurus, > >It's almost 21:00 now. We have signed maintenance contract with local >Informix Inc., but their engineers have all been off duty and can not >help me until tomorrow. > >Our engine is RSAM Version 5.10.UC1 upon SCO OpenServer 5.04 with 512MB >RAM. > >Last night our database corrupted due to frequent power off/on (We >hadn't known that the UPS had been dead). Luckily, we successfully >inintialized the dbspaces and dbimported the database from backup. When >initializing the dbspace, I created 3 local log files each of 8MB (It >had been 60 X 1KB=60KB total before the corruption). The number and size >of logical files are the only parameters I changed after database >corruption. We use raw device for dbspaces. > >The dbimport took about 5 hours to finish re-building 4 GB database. >There were about only 5 users logged in during dbimporting. We happily >went home at 22:00 last night after having rebuilt the database. > >This morning everyone came back to the office to work. The number of >users logged in to run 4GL increased. (I did not checked the number of >logins that time. Usually 60 logins were the maxmimum). Then, the >machine became slower and slower, and finally almost was as slow as >dead. sar -u shows about 98%wio and 0%idle. It took more than 5 miniutes >to login to OS. The company virtually stopped operating and my boss >shouted to kill me. > >We rebooted the OS this noon and provided the services again at 14:00. >In the beginning, the speed was still slow but at least it was able to >provide services. Then, the machine started to crawl again since about >16:30. > >The local Informix engineer told us to do 2 things: (1) increasing >number of logicl files to at least 6 and (2) updating statistics. Now we >have done these (the number of logical files is now 20 = 160MB total, >and I will increase it to 60 files later) as told by Informix. > >Please note 2 things: >(1) This machine was working fine before database corruption. The size >and number of logical files were the only parameters I changed after >database corruption. We made no modification on this hardware and OS >after data corruption. > >(2) I have experienced this same problem on a newly installed testing >hardware (PIII 450 + 512MB SDRAM + Mylex RAID card + 4*Quantium Atlas >10K + 0.5GB Informix logical log space) - it works fast for 1 or 2 users >processing big tables but starts to crawl when all people came in >running 4GL. > Get the 4Gl to do an SET EXPLAIN ON and look at the sqexplain.out generated. Are indexes being used? Have you done an update statistics AFTER dbimporting?? >tbconfig: > >PHYSDBS rootdbs >PHYSFILE 8192 >LOGFILES 20 >LOGSIZE 8192>USERS 200 >TRANSACTIONS 200 >LOCKS 100000 >BUFFERS 15000 INCREASE this will help the most. >TBLSPACES 400 >CHUNKS 8 >DBSPACES 8 >PHYSBUFF 512 >LOGBUFF 1024 Reduce these to 32 if you are using unbuffered logging. >LOGSMAX 60 >CLEANERS 4 >SHMBASE 0x0 >CKPTINTVL 900>BUFFSIZE 2048 >LRUS 16 >LRU_MAX_DIRTY 8 >LRU_MIN_DIRTY 3 >LTXHWM 40 >LTXEHWM 50>DYNSHMSZ 0 >GTRID_CMP_SZ 32 >TXTIMEOUT 300>SPINCNT 0 Isn't the default 300 under Online 5? Why did you change it? > >/etc/conf/cf.d/mtune: > >* Semaphore Parameters >SEMMAP 10 10 100 >SEMMNI 4096 300 300 >SEMMNS 4096 60 300 >SEMMNU 30 10 100 >SEMMSL 25 25 60 >SEMOPM 10 10 10 >SEMUME 10 10 10 >SEMVMX 32767 32767 32767 >SEMAEM 16384 16384 16384 >* Shared Memory Parameters >SHMMAX 60000000 131072 2147483647 >SHMMIN 1 1 1 >SHMMNI 255 100 2000 > >tbstat -d: > >RSAM Version 5.10.UC1 -- On-Line -- Up 02:57:02 -- 42144 Kbytes > >Dbspaces >address number flags fchunk nchunks flags owner name >80055b7c 1 1 1 4 N informix rootdbs > 1 active, 8 total > >Chunks >address chk/dbs offset size free bpages flags pathname >800556bc 1 1 0 1000000 4 PO- /dev/ru2 >80055754 2 1 0 1000000 2 PO- /dev/ru3 >800557ec 3 1 0 1000000 386697 PO- /dev/p2d1 >80055884 4 1 0 1000000 999997 PO- /dev/p2d2 > 4 active, 8 total > >I much worrry about the outcome when tommorrow comes. Can someone give >me any suggestions so that I can try once this machine starts to crawl >again? > Monitor the sql against the engine by using SET EXPLAIN ON. Check indexes are being used... >Thank you all in advance! > >CN -- David Williams