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

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 But before you do all that, there are some things you can try: 1. Run UPDATE STATISTICS regularly (get my update statistics shell script) 2. Look at fragmentation -- create the table with a large EXTENT SIZE and biggish NEXT SIZE. You can defrag the table on the fly using the ALTER FRAGMENT ... INIT IN ... command. ______________________________________________________ Get Your Private, Free Email at http://www.hotmail.com

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 nameROOTPATH /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 pathSYSALARMPROGRAM /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 on log tape (Kbytes) # Optical STAGEBLOB # Informix Dynamic Server/Optical staging area # System Configuration SERVERNUM 0 # Unique id corresponding to a Dynamic Serverinstance DBSERVERNAME ol_cergyx1 # Name of default database server DBSERVERALIASES ol_cergyx1_shm # 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 1 # 0 for single-processor, 1 formulti-processor NUMCPUVPS 2 # Number of user (cpu) vps SINGLE_CPU_VP 0 # If non-zero, limit number of cpu vps to one NOAGE 0 # Process aging AFF_SPROC 0 # Affinity start processor AFF_NPROCS 0 # Affinity number of processors # Shared Memory Parameters # ol_cergyx_shm LOCKS 20000 # Maximum number of locks BUFFERS 600 # Maximum number of shared buffers NUMAIOVPS 2 # Number of IO vps PHYSBUFF 32 # Physical log buffer size (Kbytes) LOGBUFF 32 # Logical log buffer size (Kbytes)LOGSMAX 40 # Maximum number of logical log files CLEANERS 1 # Number of buffer cleaner processes SHMBASE 0x82000000 # Shared memory base address SHMVIRTSIZE 8000 # 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 8 # Nu