High lngspins - IDS 9.30 UC2

Hi All, We have recently upgraded IDS 9.21UC3 to IDS 9.30 UC3 on RHL 7.3 (Kernel - 2.4.18-27.7.xbigmem). Platform - Dell PE6600 - Quard Xeon, dual channel, 8GB RAM - HT. Channel-A has 8 72 GB mirror & stripped and Channel-B has got 8 36 GB mirror & stripped. I have been keeping an eye on the database performance and constantly observing that lngspins are keeps on growing. Within 2 days of running, lngspins gone upto more than 21000. Also notice high seqscans. So far as seqscans are concern, probably they have been there with IDS 9.21 UC3 as well. Could some one pl. advise ? Ofcourse - I am sure gurus like Art, Mark D, Bill, Obnoxio, J Leffler, J Parker, Tsutomu and manu more will respond to it. Details of following onstats are as below: 1. onstat -g glo 2. onstat -p 3. onstat -P 4. onstat -d 5. onstat -c 6. onstat -g iov 7. onstat -D 8. onstat -R 9. onstat -F 10. onstat -g seg [Live:auth->authprog] onstat -g glo Informix Dynamic Server Version 9.30.UC3 -- On-Line -- Up 2 days 18:00:33 -- 1366176 Kbytes MT global info: sessions threads vps lngspins 374 560 31 21430 sched calls thread switches yield 0 yield n yield forever total: 3927615459 560654618 3584329234 1861444 24375989 per sec: 12 12 0 0 6 Virtual processor summary: class vps usercpu syscpu total cpu 3 41233.15 1459.49 42692.64 aio 22 519.91 1952.60 2472.51 lio 1 8.67 44.62 53.29 pio 1 0.60 4.44 5.04 adm 1 4.75 37.75 42.50 soc 2 201.84 1638.10 1839.94 msc 1 68.18 21.72 89.90 total 31 42037.10 5158.72 47195.82 Individual virtual processors: vp pid class usercpu syscpu total 1 29700 cpu 13548.51 626.85 14175.36 2 29701 adm 4.75 37.75 42.50 3 29702 cpu 15078.50 525.08 15603.58 4 29703 cpu 12606.14 307.56 12913.70 5 29704 lio 8.67 44.62 53.29 6 29705 pio 0.60 4.44 5.04 7 29706 aio 39.97 260.47 300.44 8 29707 msc 68.18 21.72 89.90 9 29708 aio 34.73 130.95 165.68 10 29709 aio 31.28 120.36 151.64 11 29710 aio 29.22 111.09 140.31 12 29711 aio 27.27 105.32 132.59 13 29712 aio 26.18 100.10 126.28 14 29713 aio 24.68 89.16 113.84 15 29714 aio 24.03 87.30 111.33 16 29715 aio 22.70 79.68 102.38 17 29716 aio 22.51 77.28 99.79 18 29717 aio 21.54 75.55 97.09 21 29718 aio 20.47 70.15 90.62 19 29719 aio 21.73 72.90 94.63 23 29720 aio 20.04 66.33 86.37 22 29721 aio 19.92 68.04 87.96 25 29722 aio 19.13 64.25 83.38 20 29723 aio 21.04 71.05 92.09 27 29724 aio 18.07 59.51 77.58 24 29725 aio 19.70 64.01 83.71 29 29726 aio 18.55 58.39 76.94 26 29727 aio 18.79 61.72 80.51 28 29728 aio 18.37 59.00 77.37 30 29729 soc 87.05 702.88 789.93 31 29730 soc 114.82 935.60 1050.42 tot 42040.38 5159.23 47199.61 ----------------------------------------------------------- [Live:auth->authprog] onstat -p Informix Dynamic Server Version 9.30.UC3 -- On-Line -- Up 2 days 17:59:01 -- 1366176 Kbytes Profile dskreads pagreads bufreads %cached dskwrits pagwrits bufwrits %cached 43056847 125882631 3355831614 98.72 2009883 11470076 12282452 83.64 isamtot open start read write rewrite delete commit rollbk 2526616592 27179056 150978314 1927954589 3255094 191668 189745 651309 34 gp_read gp_write gp_rewrt gp_del gp_alloc gp_free gp_curs 4278 1252 14560 740 0 0 17 ovlock ovuserthread ovbuff usercpu syscpu numckpts flushes 0 0 0 42021.58 5157.48 246 770 bufwaits lokwaits lockreqs deadlks dltouts ckpwaits compress seqscans 1809524 1 2554621970 0 0 271 546021 1042972 ixda-RA idx-RA da-RA RA-pgsused lchwaits 6932524 1128570 24853036 32904007 3401049 ------------------------------------------------------------ [Live:auth->authprog] onstat -P | tail -5 Percentages: Data 83.06 Btree 15.87 Other 1.07 ------------------------------------------------------------ [Live:auth->authprog] onstat -d Informix Dynamic Server Version 9.30.UC3 -- On-Line -- Up 2 days 18:04:47 -- 1366176 Kbytes Dbspaces address number flags fchunk nchunks flags owner name 788a07d8 1 0x1001 1 1 N informix rootdbs 7986a018 2 0x1 2 1 N informix plogdbs 7986a168 3 0x1 3 1 N informix llogdbs 7986a2b8 4 0x1 4 1 N informix tmpndbs 7986a408 5 0x2001 5 1 N T informix tmptdbs 7986a558 6 0x1 6 16 N informix maindbs 7986a6a8 7 0x8001 22 1 N S informix sndqdbs 7 active, 2047 maximum Chunks address chk/dbs offset size free bpages flags pathname 788a0928 1 1 0 256000 198196 PO- /IFMXDATA/authlive/rootdbs 798430a0 2 2 0 256000 5947 PO- /IFMXDATA/authlive/plogdbs 79843210 3 3 0 512000 69579 PO- /IFMXDATA/authlive/llogdbs 79843380 4 4 0 1024000 1023947 PO- /IFMXDATA/authlive/tmpndbs 798434f0 5 5 0 1024000 1015460 PO- /IFMXDATA/authlive/tmptdbs 79843660 6 6 0 1024000 7 PO- /IFMXDATA/authlive/maindbs01 798437d0 7 6 0 1024000 0 PO- /IFMXDATA/authlive/maindbs02 79843940 8 6 0 1024000 45 PO- /IFMXDATA/authlive/maindbs03 79843ab0 9 6 0 1024000 17 PO- /IFMXDATA/authlive/maindbs04 79843c20 10 6 0 1024000 4 PO- /IFMXDATA/authlive/maindbs05 79843d90 11 6 0 1024000 2 PO- /IFMXDATA/authlive/maindbs06 79869018 12 6 0 1024000 8 PO- /IFMXDATA/authlive/maindbs07 79869188 13 6 0 1024000 12 PO- /IFMXDATA/authlive/maindbs08 798692f8 14 6 0 1024000 99537 PO- /IFMXDATA/authlive/maindbs09 79869468 15 6 0 1024000 540241 PO- /IFMXDATA/

Hari Gupta wrote: > We have recently upgraded IDS 9.21UC3 to IDS 9.30 UC3 on RHL 7.3 > (Kernel - 2.4.18-27.7.xbigmem). Platform - Dell PE6600 - Quard Xeon, > dual channel, 8GB RAM - HT. Channel-A has 8 72 GB mirror & stripped > and Channel-B has got 8 36 GB mirror & stripped. On the same machine? > I have been keeping an eye on the database performance and constantly > observing that lngspins are keeps on growing. Within 2 days of > running, > lngspins gone upto more than 21000. Also notice high seqscans. Often, this is associated with an underconfiguration of the NETTYPE, but I can't really see a problem unless your engine is very busy? > So far as seqscans are concern, probably they have been there with > IDS 9.21 UC3 as well. UPDATE STATISTICS? Or missing indexes? > [Live:auth->authprog] onstat -g glo > > Informix Dynamic Server Version 9.30.UC3 -- On-Line -- Up 2 days > 18:00:33 -- 1366176 Kbytes > > MT global info: > sessions threads vps lngspins > 374 560 31 21430 > > sched calls thread switches yield 0 yield n yield > forever > total: 3927615459 560654618 3584329234 1861444 > 24375989 > per sec: 12 12 0 0 6 > > [Live:auth->authprog] onstat -p > > Informix Dynamic Server Version 9.30.UC3 -- On-Line -- Up 2 days > 17:59:01 -- 1366176 Kbytes > > Profile > dskreads pagreads bufreads %cached dskwrits pagwrits bufwrits %cached > 43056847 125882631 3355831614 98.72 2009883 11470076 12282452 83.64 > > isamtot open start read write rewrite delete commit > rollbk > 2526616592 27179056 150978314 1927954589 3255094 191668 189745 > 651309 34 > > gp_read gp_write gp_rewrt gp_del gp_alloc gp_free gp_curs > 4278 1252 14560 740 0 0 17 > > ovlock ovuserthread ovbuff usercpu syscpu numckpts flushes > 0 0 0 42021.58 5157.48 246 770 > > bufwaits lokwaits lockreqs deadlks dltouts ckpwaits compress > seqscans > 1809524 1 2554621970 0 0 271 546021 > 1042972 > > ixda-RA idx-RA da-RA RA-pgsused lchwaits > 6932524 1128570 24853036 32904007 3401049 > > ------------------------------------------------------------ > > [Live:auth->authprog] onstat -P | tail -5 > Percentages: > Data 83.06 > Btree 15.87 > Other 1.07 > > > ------------------------------------------------------------ > --------------------------------------------------------------------------------------------- > > [Live:auth->authprog] onstat -c > > Informix Dynamic Server Version 9.30.UC3 -- On-Line -- Up 2 days > 18:05:45 -- 1366176 Kbytes > > Configuration File: /usr/informix/etc/onconfig.authlive > #************************************************************************** > # > # INFORMIX SOFTWARE, INC. > # > # Title: onconfig.authlive.normalrunning > # Description: Informix Dynamic Server Configuration Parameters > # Use this as the base configuration file for a quad > # hyper-threaded P4 server with 2 gig or more of ram > # > #************************************************************************** > > # Root Dbspace Configuration > > ROOTNAME rootdbs # Root dbspace name> ROOTPATH /IFMXDATA/authlive/rootdbs > # Path for device containing root > dbspace > ROOTOFFSET 0 # Offset of root dbspace into device > (Kbytes) > ROOTSIZE 512000 # 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 plogdbs # Location (dbspace) of physical log > PHYSFILE 500000 # Physical log file size (Kbytes)> > # Logical Log Configuration > > LOGFILES 60 # Number of logical log files > LOGSIZE 16384 # Logical log size (Kbytes)> > # Diagnostics > > MSGPATH /var/log/informix/authlive.log # System message log > file path > CONSOLE /dev/console # System console message path > ALARMPROGRAM /usr/informix/etc/no_log.sh # Alarm program path > TBLSPACE_STATS 1 # Maintain tblspace statistics> > # System Archive Tape Device > > #TAPEDEV /dev/null # Tape device path > TAPEDEV /dev/st0 # Tape device path > TAPEBLK 736 # Tape block size (Kbytes) > TAPESIZE 102400000 # Maximum amount of data to put on > tape (Kbytes)> > # Log Archive Tape Device > > #LTAPEDEV /dev/null # Log tape device path > LTAPEDEV /dev/st0 # Log tape device path > LTAPEBLK 512 # Log tape block size (Kbytes) > LTAPESIZE 20971520 # Max amount of data to put on log > tape (Kbytes)> #LTAPESIZE 10240000 # Max amount of data to put on log > tape (Kbytes) > > # Optical > > STAGEBLOB # Informix Dynamic Server staging area > > # System Configuration > > SERVERNUM 5 # Unique id corresponding to a OnLine> instance > DBSERVERNAME authlive # Name of default database server > DBSERVERALIASES authlive_shm # List of alternate dbservernames > NETTYPE soctcp,2,150,NET # Configure poll thread(s) for> nettype > NETTYPE ipcshm,2,300,CPU # Configure poll thread(s) for> nettype > DEADLOCK_TIMEOUT 60 # Max time to wait of lock in> distributed env. > RESIDENT -1 # Forced residency flag (Yes = 1, No = > 0) > > MULTIPROCESSOR 1 # 0 for single-processor, 1 for> multi-processor > NUMCPUVPS 3 # Number of user (cpu) vps > SINGLE_CPU_VP 0 # If non-zero, limit number of cpu vps> to one > > NOAGE 1 # Process aging > AFF_SPROC 0 # Affinity start processor > AFF_NPROCS 0 # Affinity number of processors> > # Shared Memory Parameters > > LOCKS 100000 # Maximum number of locks > BUFFERS 400000 # Maximum number of shared buffers > NUMAIOVPS 22 # Number of IO vps > PHYSBUFF 256 # Physical log buffer size (Kbytes) > LOGBUFF 256 # Logical log buffer size (Kbytes) > CLEANERS 128 # Number of buffer cleaner processes > SHMBASE 0x44000000L # Shared memory base address > SHMVIRTSIZE 500000 # initial virtual shared memory> segment size > SHMADD 100000 # Size of new shared memory segments > (Kbytes)> ### SHMVIRTSIZE 1000000 # initial virtual shared memory > se