Re: High lngspins - IDS 9.30 UC2
Hmm, well my first inclination would be to recommend that you update
statistics, if you have not done so already.
You may well be right that the sequential scans were an issue before the
upgrade, but I would not rule out that the optimizer is making different
[not always good] choices.
If update stats does not reduce your scans I would concentrate on figuring
out which sql's are at the heart of the issue. Chances are that you are
going to need to review your indexes. The good news is that you will
probably find only a handful of problem queries.
As for the long spins, take a look at the output of onstat -g spi. This will
tell you more about the source of the long spins. I would suggest you sort
the output based on num loops. If memory serves right, the ones with the
high counts are going to be generating the long spins.
This can be cross referenced to onstat -g wmx. The list of mutexes that the
system is waiting on. Assuming the problem is happening at the time of
investigation that is.
Mark
----- Original Message -----
From: "Hari Gupta" <hariog@yahoo.com>
To: <informix-list@iiug.org>
Sent: Thursday, October 16, 2003 03:52
Subject: 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 info