Extremely Slow

Hello! Art, Michael, Obnoxio, Paul, Thank you all very much for the helpful advices and enthusiasm! We have survived! UPDATE STATISTICS does help - it brings the performance back. Incontrast to what I thought, it looks like that update statistics is mandatory after dbimport. Our critical time has passed. We will adjust later whatever parameters you suggested. I am replying your questions in this posting. If you have time to read... > Can you move your physical and logical logs out to separate spindles? This machine is running in RAID5 with 4 disks. >>BUFFERS 15000>This looks a bit low. What is the output from tbstat -p? >More buffers. Post onstat -p so we can diagnose the problem. RSAM Version 5.10.UC1 -- On-Line -- Up 08:51:38 -- 42144 Kbytes Profile dskreads pagreads bufreads %cached dskwrits pagwrits bufwrits %cached 1403978 3401530 63623914 97.79 14243 46835 350422 95.94 isamtot open start read write rewrite delete commit rollbk 76668903 280475 1176114 36859460 183157 11065 66366 3570773 100 ovtbls ovlock ovuser ovbuff usercpu syscpu numckpts flushes 0 0 0 0 3278.77 683.85 13 89 bufwaits lokwaits lockreqs deadlks dltouts lchwaits ckpwaits compress 18713 2 36942577 0 0 2785 13 2801 >Is there anything /var/adm/messages? No abnormal error or warning. >ARRRGGGG! Only one dbspace and that is root! BAD VERY BAD! Perhaps I am too stuborn. I thought as the machine is equipped with RAID5, there should be no negative impact on performance by putting everything in one single portion of disk space. I also thought that this arrangement avoids the trouble when one portion of space becomes too small while another is too big and wasted. Maybe it's time for me to change my way of thinking. >Post onstat -p, onstat -k, onstat -l, onstat -D, onstat -R, onstat -F and we'll see what we can do. Locks address wtlist owner lklist type tblsnum rowid size 8006dcfc 0 80001d7c 0 S 1000002 202 0 [snip - 310 similar records] 265 active, 100000 total, 8192 hash buckets Physical Logging Buffer bufused bufsize numpages numwrits pages/io P-2 107 256 56 0 0.00% phybegin physize phypos phyused %used 101f8c 4096 1209 619 15.11 Logical Logging Buffer bufused bufsize numrecs numpages numwrits recs/pages pages/io L-1 3 512 154 3 1 51.3 3.0 address number flags uniqid begin size used %used 803fb4fc 1 F------ 0 102f8c 4096 0 0.00% 803fb518 2 F------ 0 103f8c 4096 0 0.00% 803fb534 3 F------ 0 104f8c 4096 0 0.00% 803fb550 4 F------ 0 384db8 4096 0 0.00% 803fb56c 5 F------ 0 385db8 4096 0 0.00% 803fb588 6 U---C-L 6 386e77 4096 3753 91.63% [snip - 30 total loggical files. Only one record has none-zero in "used" column] 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 page Rd page Wr pathname 800556bc 1 1 0 15676 402 /dev/ru2 80055754 2 1 0 9126 0 /dev/ru3 800557ec 3 1 0 9260 112 /dev/p2d1 80055884 4 1 0 0 0 /dev/p2d2 4 active, 8 total 16 buffer LRU queues LRU 0: 69 ( 7.3%) modified of 939 total [snip] LRU 15: 38 ( 4.1%) modified of 923 total 875 dirty, 14991 queued, 15000 total, 16384 hash buckets, 2048 buffer size start clean at 8% dirty, stop at 3% Fg Writes LRU Writes Idle Writes Chunk Writes 0 580 0 0 address flusher snooze state data 80001b3c 0 3 I 0 = 0 80001bcc 1 3 I 0 = 0 80001c5c 2 3 I 0 = 0 80001cec 3 3 I 0 = 0 states: Exit Idle Chunk Lru sar -u 3 5 08:58:22 %usr %sys %wio %idle (-u) 08:58:25 24 7 69 0 08:58:28 37 11 51 0 08:58:31 20 12 68 0 08:58:34 20 12 68 0 08:58:37 43 9 48 0 Average 29 10 61 0 Take care! CN

Great! I don't see anything new, of course the instance was not up very long so perhaps we just missed the problem. I HATE RAID5! Peruse the CDI archive for my various tirades on the subject, but amoung other things RAID5 is cutting your possible write performance in half! RAID10! RAID10! Art S. Kagel CN Liu wrote: > > Hello! Art, Michael, Obnoxio, Paul, > > Thank you all very much for the helpful advices and enthusiasm! We have > survived! > > UPDATE STATISTICS does help - it brings the performance back. In> contrast to what I thought, it looks like that update statistics is > mandatory after dbimport. > > Our critical time has passed. We will adjust later whatever parameters > you suggested. I am replying your questions in this posting. If you have > time to read... > > > Can you move your physical and logical logs out to separate spindles? > > This machine is running in RAID5 with 4 disks. > > >>BUFFERS 15000> >This looks a bit low. What is the output from tbstat -p? > >More buffers. Post onstat -p so we can diagnose the problem. > > RSAM Version 5.10.UC1 -- On-Line -- Up 08:51:38 -- 42144 Kbytes > > Profile > dskreads pagreads bufreads %cached dskwrits pagwrits bufwrits %cached > 1403978 3401530 63623914 97.79 14243 46835 350422 95.94 > > isamtot open start read write rewrite delete commit > rollbk > 76668903 280475 1176114 36859460 183157 11065 66366 3570773 > 100 > > ovtbls ovlock ovuser ovbuff usercpu syscpu numckpts flushes > 0 0 0 0 3278.77 683.85 13 89 > > bufwaits lokwaits lockreqs deadlks dltouts lchwaits ckpwaits compress > 18713 2 36942577 0 0 2785 13 2801 > > >Is there anything /var/adm/messages? > > No abnormal error or warning. > > >ARRRGGGG! Only one dbspace and that is root! BAD VERY BAD! > > Perhaps I am too stuborn. I thought as the machine is equipped with > RAID5, there should be no negative impact on performance by putting > everything in one single portion of disk space. I also thought that this > arrangement avoids the trouble when one portion of space becomes too > small while another is too big and wasted. Maybe it's time for me to > change my way of thinking. > > >Post onstat -p, onstat -k, onstat -l, onstat -D, onstat -R, onstat -F and we'll see what we can do. > > Locks > address wtlist owner lklist type tblsnum rowid size > 8006dcfc 0 80001d7c 0 S 1000002 202 0 > [snip - 310 similar records] > 265 active, 100000 total, 8192 hash buckets > > Physical Logging > Buffer bufused bufsize numpages numwrits pages/io > P-2 107 256 56 0 0.00% > phybegin physize phypos phyused %used > 101f8c 4096 1209 619 15.11 > > Logical Logging > Buffer bufused bufsize numrecs numpages numwrits recs/pages pages/io > L-1 3 512 154 3 1 51.3 3.0 > > address number flags uniqid begin size used %used > 803fb4fc 1 F------ 0 102f8c 4096 0 0.00% > 803fb518 2 F------ 0 103f8c 4096 0 0.00% > 803fb534 3 F------ 0 104f8c 4096 0 0.00% > 803fb550 4 F------ 0 384db8 4096 0 0.00% > 803fb56c 5 F------ 0 385db8 4096 0 0.00% > 803fb588 6 U---C-L 6 386e77 4096 3753 91.63% > [snip - 30 total loggical files. Only one record has none-zero in "used" > column] > > 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 page Rd page Wr pathname > 800556bc 1 1 0 15676 402 /dev/ru2 > 80055754 2 1 0 9126 0 /dev/ru3 > 800557ec 3 1 0 9260 112 /dev/p2d1 > 80055884 4 1 0 0 0 /dev/p2d2 > 4 active, 8 total > > 16 buffer LRU queues > LRU 0: 69 ( 7.3%) modified of 939 total > [snip] > LRU 15: 38 ( 4.1%) modified of 923 total > 875 dirty, 14991 queued, 15000 total, 16384 hash buckets, 2048 buffer > size > start clean at 8% dirty, stop at 3% > > Fg Writes LRU Writes Idle Writes Chunk Writes > 0 580 0 0 > > address flusher snooze state data > 80001b3c 0 3 I 0 = 0 > 80001bcc 1 3 I 0 = 0 > 80001c5c 2 3 I 0 = 0 > 80001cec 3 3 I 0 = 0 > states: Exit Idle Chunk Lru > > sar -u 3 5 > > 08:58:22 %usr %sys %wio %idle (-u) > 08:58:25 24 7 69 0 > 08:58:28 37 11 51 0 > 08:58:31 20 12 68 0 > 08:58:34 20 12 68 0 > 08:58:37 43 9 48 0> > Average 29 10 61 0 > > Take care! > > CN

Great! I don't see anything new, of course the instance was not up very long so perhaps we just missed the problem. I HATE RAID5! Peruse the CDI archive for my various tirades on the subject, but amoung other things RAID5 is cutting your possible write performance in half! RAID10! RAID10! OH, get two small singleton drive, mirror them and place your logs there if nothing else. I also see you are using NONLOGGED database. BAD very bad. A system crash will trash indexes at the very least! I lost much sleep 3 years ago over my predecessor's decision to do that. Art S. Kagel CN Liu wrote: > > Hello! Art, Michael, Obnoxio, Paul, > > Thank you all very much for the helpful advices and enthusiasm! We have > survived! > > UPDATE STATISTICS does help - it brings the performance back. In> contrast to what I thought, it looks like that update statistics is > mandatory after dbimport. > > Our critical time has passed. We will adjust later whatever parameters > you suggested. I am replying your questions in this posting. If you have > time to read... > [SNIP]