Tuning IDS 9.30 after migration from IDS7.31
We've recently migrated from 7.31UC5 to 9.30UC5. Our platform is AIX 4.3.3.
The hardware is an S7A with twelve CPU's. KAIO is enabled. The machine runs
both the database and 4gl, c, and perl applications that connect to the
database. There are also many remote applications that connect to the
database via tcp/ip. The application is heavily OLTP, there is little DSS
work running on the box. After the upgrade, we've experienced a 50% increase
in the response time to one of our key applications. We'd been running 7.3x
for a long time and had our tuning down pretty well, we kept database
structure and applications static while the upgrade took place, so my feeling
is that I'm just not tuned properly. I'll throw this before the group with as
many onstat commands as I can remember being requested on previous cases.
Please take a look and see if you can see any fatal flaws.
onstat -p
Informix Dynamic Server Version 9.30.UC5 -- On-Line -- Up 1 days 08:38:03
-- 656064 Kbytes
Profile
dskreads pagreads bufreads %cached dskwrits pagwrits bufwrits %cached
28865936 93631064 2578920259 98.88 2598876 4834789 93582633 97.22
isamtot open start read write rewrite delete commit rollbk
2404240073 460155135 822591939 3739597979 23441317 8416780 1827861 9905906
23548
gp_read gp_write gp_rewrt gp_del gp_alloc gp_free gp_curs
3 0 0 512 0 0 2
ovlock ovuserthread ovbuff usercpu syscpu numckpts flushes
0 0 0 426713.11 18645.66 393 1180
bufwaits lokwaits lockreqs deadlks dltouts ckpwaits compress seqscans
2648099 2022 1005907466 0 0 4995 1791147 23076308
ixda-RA idx-RA da-RA RA-pgsused lchwaits
9036937 5626052 6259658 20662466 22087820
onstat -F
Informix Dynamic Server Version 9.30.UC5 -- On-Line -- Up 1 days 08:39:38
-- 656064 Kbytes
Fg Writes LRU Writes Chunk Writes
0 1998605 240072
onstat -g sch
Informix Dynamic Server Version 9.30.UC5 -- On-Line -- Up 1 days 08:40:18
-- 656064 Kbytes
VP Scheduler Statistics:
vp pid class semops busy waits spins/wait
1 135068 cpu 132 135 996
2 135830 adm 0 0 0
3 61052 cpu 27 27 1001
4 133752 cpu 43 48 988
5 96058 cpu 2 2 1001
6 43498 cpu 3 3 1001
7 139086 cpu 7 11 853
8 139592 cpu 1392213 2962099 937
9 136256 cpu 1240775 2648711 940
10 134210 cpu 1088541 2325560 941
11 137068 cpu 933363 1998043 943
12 127748 cpu 813999 1744390 943
13 136030 lio 2 0 0
14 130992 pio 2 0 0
15 50146 aio 58499 0 0
16 38812 msc 375292 0 0
17 130032 aio 2 0 0
18 133492 soc 6 7 916
19 90572 soc 25439 25478 999
Thread Migration Statistics:
vp pid class steal-at steal-sc idlvp-at idlvp-sc inl-polls Q-ln
1 135068 cpu 20584429 3588202 2171495 1664941 55257055 0
2 135830 adm 0 0 681862 279219 0 0
3 61052 cpu 15002034 3063731 1807358 1341054 45156176 0
4 133752 cpu 14980961 3009531 1622717 1168421 40887577 0
5 96058 cpu 12056388 2631360 1362152 929201 32621420 0
6 43498 cpu 26840243 3443645 1471151 990603 47119278 0
7 139086 cpu 62929952 4675110 1763831 1286217 73475948 0
8 139592 cpu 5379718 1485900 991435 565593 0 0
9 136256 cpu 4811055 1389237 937340 494590 0 0
10 134210 cpu 4266127 1301289 867937 421491 0 0
11 137068 cpu 3715208 1185099 804718 350699 0 0
12 127748 cpu 3276955 1084533 759402 290843 0 0
13 136030 lio 0 0 0 0 0 0
14 130992 pio 0 0 0 0 0 0
15 50146 aio 0 0 3 3 0 0
16 38812 msc 0 0 4911 3967 0 0
17 130032 aio 0 0 0 0 0 0
18 133492 soc 0 0 349346 295575 0 0
19 90572 soc 0 0 346697 286818 0 0
onstat -g iov
AIO I/O vps:class/vp s io/s totalops dskread dskwrite dskcopy wakeups io/wup errors
kio 0 s 20.8 2444889 2172696 272193 0 4491036 0.5 0
kio 1 s 26.1 3072660 2785881 286779 0 5531465 0.6 0
kio 2 s 25.9 3049670 2764354 285316 0 5628213 0.5 0
kio 3 s 16.5 1947778 1687573 260205 0 3632065 0.5 0
kio 4 i 7.2 853419 681510 171909 0 1507722 0.6 0
kio 5 i 15.6 1833049 1542135 290914 0 3438105 0.5 0
kio 6 i 8.3 977813 795775 182038 0 1731357 0.6 0
kio 7 s 18.2 2139052 1806986 332066 0 3889432 0.5 0
kio 8 i 9.2 1085954 911267 174687 0 1927755 0.6 0
kio 9 i 6.3 739738 578086 161652 0 1297059 0.6 0
kio 10 i 5.5 643843 490966 152877 0 1122811 0.6 0
msc 0 i 3.3 385601 0 0 0 375842 1.0 0
aio 0 i 0.5 58496 19470 12 0 58497 1.0 0
aio 1 i 0.0 0 0 0 0 1 0.0 0
pio 0 i 0.0 0 0 0 0 1 0.0 0
lio 0 i 0.0 0 0 0 0 1 0.0 0
onstat -g glo
Informix Dynamic Server Version 9.30.UC5 -- On-Line -- Up 1 days 08:45:59
-- 656064 Kbytes
MT global info:
sessions threads vps lngspins
234 376 19 21771587
sched calls thread switches yield 0 yield n yield forever
total: 1778972188 746753004 1233938296 7694681 44174563
per sec: 49024 8099 43172 41 488
Virtual processor summary:
class vps usercpu syscpu total
cpu 11 424884.60 13345.03 438229.63
aio 2 7.32 10.96 18.28
lio 1 1.24 3.70 4.94
pio 1 1.06 3.44 4.50
adm 1 13.40 18.77 32.17
soc 2 620.70 4850.09 5470.79
msc 1 3006.30 476.64 3482.94
total 19 428534.62 18708.63 447243.25
Individual virtual processors:
vp pid class usercpu syscpu total
1 135068 cpu 51305.98 1586.91 52892.89
2 135830 adm 13.40 18.77 32.17
3 61052 cpu 40597.39 1295.37 41892.76
4 133752 cpu 36562.12 1143.56 37705.68
5 96058 cpu 27033.3
dthacker@omnihotels.com (Dave Thacker) wrote in message news:<554c618d.0308280657.2c8627bd@posting.google.com>... original message snipped.... I've been asked for some more info. Did you run update statistics? Yes, but we're going to revisit our methods, as they may not have been granular enough. What type of upgrade was this? We did an in-place upgrade. Dave Thacker
On 28 Aug 2003 11:06:41 -0700, dthacker@omnihotels.com (Dave Thacker)
wrote:
>dthacker@omnihotels.com (Dave Thacker) wrote in message news:<554c618d.0308280657.2c8627bd@posting.google.com>...
>original message snipped....
>
>I've been asked for some more info.
>
>Did you run update statistics?
>Yes, but we're going to revisit our methods, as they may not have been
>granular enough.
>
>What type of upgrade was this?
>We did an in-place upgrade.
>
Are you able to see what SQL is active and snag a copy into dbaccess?
You could then run with 'set explain on' and see the query plan . . .