RE: PUT cursor very very very slow ... but it works with no error ! WHY

Just a few ideas. Only #1 seems likely to address the problem you described, but the others suggestions would help rule out things that kind of tug at the mind as being an issue. 1. Looking at your B-Tree percentage from onstat -P it looks like you are running into a bug which causes B-Tree pages to hog BUFFERS. I am not sure of this because I dont know what type of effect 600 BUFFERS will have on a system. This might be the reason for your slow inserts. The only solution I know of for this is to drop your indexes when you clear the table or upgrade 2. If you have more than 1 disk separating rootdbs, physlog and logical logs can add to performance. From your config >TBLSPACE_STATS 1This is gathering statistics, not a problem if you look at them, but disabling it mighthelp performance >BUFFERS 600 # Maximum number of shared buffersThis seems really small. Even though you have a good cache rate there is a lot of contention for your BUFFERS. I think upping this to just 6000 would make a big difference. It looks like you have a good bit of contention for BUFFERS. >NUMAIOVPS 2 # Number of IO vpsI think you might get some advantage by increasing this by 1 or 2. >CLEANERS 1 # Number of buffer cleaner processesI would up this to 8 as well. >LRU_MAX_DIRTY 60 # LRU percent dirty begin cleaning limit >LRU_MIN_DIRTY 50 # LRU percent dirty end cleaning limitYou might end up lowering these to make your checkpoints a reasonable time Hope this end up helpsing some, Will >===== Original Message From Laurent COLLIGNON <lcollignon@imsii.fr> ===== >Obnoxio The Clown a 'crit : > >> From: Laurent COLLIGNON <lcollignon@imsii.fr> >> > >> >SCO OpenServer 3.2V5.0.5 >> >IDS 7.30 UC2 >> > >> >HP 2 CPU - 512 MB - 2 mirrored HD of 9 GB each >> >IDS: chunks in raw devices - 1 database of about 2 GB (370 tables) >> >----------------- >> > >> >App: pay-roll >> > >> >When computing for a few salaries, an CURSOR FOR INSERT takes more than >> >5 minutes to insert a single row in an approximately empty table !!! It >> >finally succeeds but it's far too long for such an insert. >> >There are many other Inserts, Deletes and Updates in other tables and >> >they're done in less than a 1/10 second. >> > >> >oncheck -cI doesn't report anything but takes more than a minute to >> >check ... 1000 rows ! >> >oncheck -pT report 37 extents for a global size of 14000 pages (I>> >suppose it shows that the table is sometimes filled with thousands of >> >rows which are deleted later) >> >I drop and recreate indexes before recomputing the same job: it works >> >fine for a few hours only >> > >> >Last chance: I dropped indexes again, renamed the table to an unused >> >name and recreated the table to load my 1000 rows back. By this way I >> >try to isolate the physical space allocated by the old table. >> > >> >Now, every SELECT and oncheck is ok and takes no time to perform on the >> >new table (as I could expect it of course) but when real jobs will have >> >to be performed, this table will be filled and flushed many times (it >> >wil take some weeks). What if the same problem arise ? >> > >> >I also think that Unix can't report any trouble in case of HD damage >> >because of the raw device choice but can Informix report something ? >> >I found nothing in online.log except from Checkpoint every five minutes >> >and I noticed that they need from 4 to 6 or 7 seconds each when my >> >program is blocking on my long INSERT statements and it's unusual >> >because they are usually completed in 0 or 1 second. >> >> Whew! That's quite a problem set. For background, can you please post (to >> the list) output from the following: >> >> onstat -p >> onstat -c >> onstat -d >> onstat -D >> onstat -g seg >> onstat -g iof >> onstat -g iov >> onstat -m >> onstat -u | tail -2 >> onstat -P | tail -5 >> onstat -F >> onstat -R>> > >Ok, here's the result ... >onstat -p> >Informix Dynamic Server Version 7.30.UC2 -- On-Line -- Up 6 days 03:13:57 -- >36864 Kbytes > >Profile >dskreads pagreads bufreads %cached dskwrits pagwrits bufwrits %cached >10335630 1531627 226746268 95.44 240971 958358 6967603 96.54 > >isamtot open start read write rewrite delete commit rollbk > >312664723 59315502 25507841 59658370 1528257 65788 32811 4518 27 > >gp_read gp_write gp_rewrt gp_del gp_alloc gp_free gp_curs >0 0 0 0 0 0 0 > >ovlock ovuserthread ovbuff usercpu syscpu numckpts flushes >2828 0 649 8403.30 2588.08 699 3970 > >bufwaits lokwaits lockreqs deadlks dltouts ckpwaits compress seqscans >3471135 0 120407377 0 0 340 53822 6137124 > >ixda-RA idx-RA da-RA RA-pgsused lchwaits >1408723 303390 1403608 2942544 655659 > >onstat -c> >Informix Dynamic Server Version 7.30.UC2 -- On-Line -- Up 6 days 03:13:57 -- >36864 Kbytes > >Configuration File: /usr1/ids/etc/onconfig >#************************************************************************** ># ># INFORMIX SOFTWARE, INC. ># ># Title: onconfig.std ># Description: Informix Dynamic Server Configuration Parameters ># >#************************************************************************** > ># Root Dbspace Configuration > >ROOTNAME rootdbs # Root dbspace name>ROOTPATH /dev/ifmx_sys # Path for device containing root dbspace >ROOTOFFSET 0 # Offset of root dbspace into device (Kbytes) >ROOTSIZE 30000 # 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 # Location (dbspace) of physical log >PHYSFILE 1000 # Physical log file size (Kbytes)> ># Logical Log Configuration > >LOGFILES 20 # Number of logical log files >LOGSIZE 500 # Logical log size (Kbytes)> ># Diagnostics > >MSGPATH /usr1/ids/online.log # System message log file path >CONSOLE /dev/console # System console message path >ALARMPROGRAM /usr1/ids/etc/log_full.sh # Alarm program path>SYSALARMPROGRAM /usr1/ids/etc/evidence.sh # System Alarm program path >TBLSPACE_STATS 1> ># System Archive Tape Device > >#TAPEDEV /dev/rStp0 # Tape device path >TAPEDEV /dev/null >TAPEBLK 16 # Tape block size (Kbytes) >TAPESIZE 4000000 # Maximum amount of data to put on tape >(Kbytes)> ># Log Archive Tape Device > >LTAPEDEV /dev/null # Log tape device path >LTAPEBLK 16 # Log tape block size (Kbytes) >LTAPESIZE 4000000 # Max amount of data to put