Re: Tuning IDS 9.30 after migration from IDS7.31

dthacker@omnihotels.com (Dave Thacker) wrote in message news:<554c618d.0308280657.2c8627bd@posting.google.com>... > 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. > <original output snipped> This morning I received a request for more diagnostics.. >>Post an onstat -g seg, onstat -g ntu, onstat -d, onstat -g iof, onstat -gioq >>How much memory can you use for the Informix instance? 2-3GB out of 4GB total. >>How much is on the box? 4GB >>Do your users connect via shared memory or soc? Both. Diag output follows onstat -g seg Informix Dynamic Server Version 9.30.UC5 -- On-Line -- Up 3 days 10:26:39 -- 953968 Kbytes Segment Summary: id key addr size ovhd class blkused blkfree 96469010 1388660737 30000000 632242176 234076 R 154331 25 899940362 1388660738 60000000 335872000 10856 V 49958 32042 71565323 1388660739 80000000 1458176 648 M 330 26 2097168 1388660740 90000000 1458176 648 M 324 32 5373965 1388660741 a0000000 1458176 648 M 324 32 21889036 1388660742 b0000000 1458176 648 M 324 32 1572881 1388660743 c0000000 1458176 648 M 324 32 3801097 1388660744 e0000000 1458176 648 M 324 32 Total: - - 976863232 - - 206239 32253 (* segment locked in memory) onstat -g ntu Informix Dynamic Server Version 9.30.UC5 -- On-Line -- Up 3 days 10:27:54 -- 953968 Kbytes global network information: #netscb connects read write q-free q-limits q-exceed alloc/max 307/ 330 44270 92485769 96145565 9/ 66 170/ 10 0/ 9 114/ 114 Individual thread network information (basic): netscb type thread name sid fd poll reads writes q-nrm q-pvt q-exp 61d1a080 soctcp sqlexec 210567 72 5 89 88 0/ 1 0/ 1 0/ 0 64f72df0 ipcshm sqlexec 210553 0 0 41 50 -10/ 0 0/ 0 0/ 0 60f2f8e0 soctcp sqlexec 210554 103 5 52 52 0/ 1 1/ 1 0/ 0 68e76498 ipcshm sqlexec 210411 0 0 16 20 -4/ 0 0/ 0 0/ 0 6248de18 ipcshm sqlexec 210410 0 0 893 958 -236/ 0 0/ 0 0/ 0 694bbe50 soctcp sqlexec 210295 106 6 202 202 0/ 1 1/ 1 0/ 0 61daa6a0 soctcp sqlexec 210076 94 5 210215 210216 0/ 1 0/ 1 0/ 0 66388790 ipcshm sqlexec 210073 0 0 215 234 -75/ 0 0/ 0 0/ 0 68f06c18 ipcshm sqlexec 210049 0 0 16 20 -1/ 0 0/ 0 0/ 0 6374d0e8 ipcshm sqlexec 210037 0 0 2426 2696 -605/ 0 0/ 0 0/ 0 6609bd68 ipcshm sqlexec 209946 0 0 696 2742 -423/ 0 0/ 0 0/ 0 632db5d0 ipcshm sqlexec 209932 0 0 2886 3002 -1096/ 0 0/ 0 0/ 0 63680580 ipcshm sqlexec 209813 0 0 178 199 -49/ 0 0/ 0 0/ 0 60dc1128 ipcshm sqlexec 209805 0 0 1572 1615 -385/ 0 0/ 0 0/ 0 631bedd8 ipcshm sqlexec 209719 0 0 13562 13726 -5895/ 0 0/ 0 0/ 0 677fce10 soctcp sqlexec 209672 105 5 1530 1530 0/ 1 1/ 1 0/ 0 66fbad50 soctcp sqlexec 209660 89 5 559 559 0/ 1 1/ 1 0/ 0 68e27488 soctcp sqlexec 209656 82 6 713 713 0/ 1 1/ 1 0/ 0 61f94df0 soctcp sqlexec 209653 79 5 710 710 0/ 1 1/ 1 0/ 0 63f7b0a0 soctcp sqlexec 209643 69 5 1390 1390 0/ 1 1/ 1 0/ 0 68f51b20 soctcp sqlexec 209633 60 6 1986 1986 0/ 1 1/ 1 0/ 0 6108f4b0 soctcp sqlexec 209629 62 5 731 731 0/ 1 1/ 1 0/ 0 6375db20 soctcp sqlexec 209626 66 6 330 330 0/ 1 1/ 1 0/ 0 6d3d4518 soctcp sqlexec 209617 61 6 2790 2790 0/ 1 1/ 1 0/ 0 69a32bf8 soctcp sqlexec 209612 59 6 936 936 0/ 1 1/ 1 0/ 0 635860f0 soctcp sqlexec 209604 55 5 1204 1204 0/ 1 1/ 1 0/ 0 62924608 soctcp sqlexec 209598 58 6 701 701 0/ 1 1/ 1 0/ 0 631be5a0 soctcp sqlexec 209592 57 6 829 829 0/ 1 1/ 1 0/ 0 62a74b00 soctcp sqlexec 209588 56 5 835 835 0/ 1 1/ 1 0/ 0 60b50e28 soctcp sqlexec 209578 51 5 958 958 0/ 1 1/ 1 0/ 0 63f81d98 soctcp sqlexec 209574 54 6 806 806 0/ 1 1/ 1 0/ 0 6108fd10 soctcp sqlexec 209565 42 6 698 698 0/ 1 1/ 1 0/ 0 62441218 soctcp sqlexec 209549 48 6 780 780 0/ 1 1/ 1 0/ 0 62a28db8 soctcp sqlexec 209543 50 6 756 756 0/ 1 1/ 1 0/ 0 64e91080 soctcp sqlexec 209541 49 5 917 917 0/ 1 1/ 1 0/ 0 64930520 soctcp sqlexec 209528 38 5 830 830 0/ 1 1/ 1 0/ 0 6202b708 soctcp sqlexec 209526 39 5 708 708 0/ 1 1/ 1 0/ 0 64cd2d48 soctcp sqlexec 209522 24 5 639 639 0/ 1 1/ 1 0/ 0 62981030 soctcp sqlexec 209515 23 6 1303 1303 0/ 1 1/ 1 0/ 0 684cc658 soctcp sqlexec 209514 11 5 911 911 0/ 1 1/ 1 0/ 0 663881f0 soctcp sqlexec 209510 32 6 1437 1437 0/ 1 1/ 1 0/ 0 620b9080 soctcp sqlexec 209501 29 5 833 833 0/ 1 1/ 1 0/ 0 61d3b4d0 soctcp sqlexec 209499 26 6 704 704 0/ 1 1/ 1 0/ 0 60009a58 soctcp sqlexec 209496 17 5 815 815 0/ 1 1/ 1 0/ 0 624dc568 soctcp sqlexec 209484 13 6 1054 1054 0/ 1 1/ 1 0/ 0 6202b030 soctcp sqlexec 209475 18 5 931 931 0/ 1 1/ 1 0/ 0 627bce60 soctcp sqlexec 209464 14 6 827 827 0/ 1 1/ 1 0/ 0 60b40080 soctcp sqlexec 209461 20 6 848 848 0/ 1 1/ 1 0/ 0 6bc59ac8 soctcp sqlexec 209450 10 5 862 862 0/ 1 1/ 1 0/ 0 6252b7a8 ipcshm sqlexec 209298 0 0 1803 1833 -743/ 0 0/ 0 0/ 0 608228a8 soctcp sqlexec 209296 9 5 459 620 0/ 1 1/ 1 0/ 0 6491a3c0 soctcp sqlexec 209282 2 5 12 12 0/ 1 1/ 1 0/ 0 67120df0 ipcshm sqlexec 209027 0 0 5380 5701 -1507/ 0 0/ 0 0/ 0 65f9b5a8 ipcshm sqlexec 208661 0 0 781

All those sequential scans: 6 million from a total of 32 million page reads. Are you *sure* you ran update stats? Also, although you say you could use up to 3GBytes of memory for the database server, you're actually using less than 1 Gbyte, and your buffer cache ("Yes, we speak Oracle!") is only about half a gig in size. Still, your cache rates are pretty good nonetheless .... -- Neil Truby t:01932 724027 Director m:07798 811708 Ardenta Limited e:neil.truby@ardenta.com