Re: Tuning advice.

Yeh, I know about ol_ch_mars_a, Oh for more disk, or some free weekends to attempt a re-org! Looking at the onstat -g dic most lists are full but with unreferenced tables, I'll try upping this! I'll try the LRU_MAX_DIRTY 2 as well, doesn't this tend to reduce cached writes? I'm wary of increasing BUFFERS too much because of leaving enough memory for the applications, this box runs boths Apps and Server. swapinfo output Kb Kb Kb PCT START/ Kb TYPE AVAIL USED FREE USED LIMIT RESERVE PRI NAME dev 524288 65168 459120 12% 0 - 1 /dev/vg00/lvol2 dev 524288 66124 458164 13% 0 - 1 /dev/vg04/lv_swap2 reserve - 602220 -602220 memory 397140 334944 62196 84% sar -w HP-UX hpk200 B.10.20 U 9000/819 09/20/00 00:00:00 swpin/s bswin/s swpot/s bswot/s pswch/s 01:00:00 0.00 0.0 0.00 0.0 1051 02:00:00 0.00 0.0 0.00 0.0 661 03:00:00 0.00 0.0 0.00 0.0 432 04:00:00 0.00 0.0 0.00 0.0 76 05:00:00 0.00 0.0 0.00 0.0 72 06:00:00 0.00 0.0 0.00 0.0 408 07:00:00 0.00 0.0 0.00 0.0 74 08:00:01 0.00 0.0 0.00 0.0 78 08:20:01 0.00 0.0 0.00 0.0 79 08:40:01 0.00 0.0 0.00 0.0 735 09:00:00 0.00 0.0 0.00 0.0 427 09:20:01 0.78 0.0 0.81 0.1 1215 09:40:01 0.97 0.0 1.03 0.2 1586 10:00:00 0.95 0.0 0.99 0.4 1223 10:20:00 0.98 0.0 1.01 0.3 999 10:40:00 0.94 0.1 0.96 0.8 767 11:00:00 0.97 0.0 0.99 0.3 407 11:20:00 0.97 0.1 0.97 0.2 519 11:40:00 0.98 0.1 0.99 0.3 700 12:00:00 1.00 0.1 1.00 0.1 440 12:20:00 0.99 0.1 0.99 0.2 501 12:40:00 0.99 0.1 1.00 0.1 409 13:00:00 1.00 0.0 0.98 0.0 344 13:20:00 1.00 0.0 0.99 0.0 333 13:40:01 1.00 0.1 1.01 0.1 263 14:00:01 1.00 0.0 1.02 0.1 243 14:20:01 0.99 0.0 1.01 0.1 317 14:40:01 1.00 0.1 1.01 0.1 352 15:00:00 1.00 0.1 0.98 0.0 778 15:20:01 0.98 0.1 1.01 0.2 502 15:40:00 0.99 0.1 0.98 0.1 1298 16:00:01 1.00 0.1 0.97 0.0 797 16:20:01 1.00 0.0 1.00 0.0 211 16:40:01 1.00 0.0 0.99 0.0 206 17:00:00 0.98 0.1 1.00 0.1 430 Average 0.46 0.0 0.46 0.1 483 onstat -g seg Informix Dynamic Server Version 7.30.UC7 -- On-Line -- Up 4 days 23:02:22 -- 298184 Kbytes Segment Summary: id key addr size ovhd class blkused blkfree 521 1381451778 c0bda000 2711552 652 M 325 6 520 1381451777 d1235000 202932224 5572 R* 24765 7 (shared) 1381451777 dd3bd000 102408192 2172 V 6884 5617 Total: - - 308051968 - - 31974 5630 (* segment locked in memory) I need to read up on interpreting this onfo :o( but this gives an idea of how the memory is working at present. Thanks. -- Tony Flaherty Snr. A/P, Informix DBA, HpUx Admin, Gimmi a broom! MFS Ltd. Madison Pruet wrote in message <39C8C202.37844AA8@informix.com>... >Well, you do need more buffers. Generally the read cache rate needs to be >pretty close to 99%. You might be able to reduce your checkpoint times even >more by setting LRU_MAX_DIRTY to 2. > >You also have skewed usage of the chunks. From your sample ol_ch_mars_a is >accounting for a rather large percentage of your activity. You might want to >consider moving some of the tables in there to othere chunks. This might help >your checkpoint times also. > >I would consider setting DD_HASHSIZE to a much larger value. I don't know where >all of your IO is coming from, but if it is dictionary information, this would >help. If oninit is only taking 50% of the system when it is overloaded, that >implies that you are blocking quite a bit on IO completion. Increasing buffers >would help. > >I'm sure that some other folks that frequent the news group will have some other >ideas and/or suggestions. I'll be interested to see what they suggest. > > > >Tony Flaherty wrote: [snipped my original post] >> -- >> Tony Flaherty >> Snr. A/P, Informix DBA, HpUx Admin, Gimmi a broom! >> MFS Ltd. > >-- >Madison Pruet > >=========================================== >Enterprise Replication Product Developement >Dallas, Texas >Informix Software >=========================================== > >

Ok - if the dictionary information tends to be unreferenced, then that implies that you are not using prepared statements. By not preparing the statements you can run into dictionary thrashing and not be aware of it. I'd still consider increasing the dictionary size, possibably large enough so that you can get an idea as to how large the dictionary would need to be if all of the statements were prepared and loaded into memory. It might be a good stragety to increase the size of the chains until they did not reach max length, just to get that information. M.P. Tony Flaherty wrote: > Yeh, I know about ol_ch_mars_a, Oh for more disk, or some free weekends to > attempt a re-org! > > Looking at the onstat -g dic most lists are full but with unreferenced > tables, I'll try upping this! > > I'll try the LRU_MAX_DIRTY 2 as well, doesn't this tend to reduce cached > writes? > > I'm wary of increasing BUFFERS too much because of leaving enough memory for > the applications, this box runs boths Apps and Server. > > swapinfo output > > Kb Kb Kb PCT START/ Kb > TYPE AVAIL USED FREE USED LIMIT RESERVE PRI NAME > dev 524288 65168 459120 12% 0 - 1 /dev/vg00/lvol2 > dev 524288 66124 458164 13% 0 - 1 > /dev/vg04/lv_swap2 > reserve - 602220 -602220 > memory 397140 334944 62196 84% > > sar -w > > HP-UX hpk200 B.10.20 U 9000/819 09/20/00 > > 00:00:00 swpin/s bswin/s swpot/s bswot/s pswch/s > 01:00:00 0.00 0.0 0.00 0.0 1051 > 02:00:00 0.00 0.0 0.00 0.0 661 > 03:00:00 0.00 0.0 0.00 0.0 432 > 04:00:00 0.00 0.0 0.00 0.0 76 > 05:00:00 0.00 0.0 0.00 0.0 72 > 06:00:00 0.00 0.0 0.00 0.0 408 > 07:00:00 0.00 0.0 0.00 0.0 74 > 08:00:01 0.00 0.0 0.00 0.0 78 > 08:20:01 0.00 0.0 0.00 0.0 79 > 08:40:01 0.00 0.0 0.00 0.0 735 > 09:00:00 0.00 0.0 0.00 0.0 427 > 09:20:01 0.78 0.0 0.81 0.1 1215 > 09:40:01 0.97 0.0 1.03 0.2 1586 > 10:00:00 0.95 0.0 0.99 0.4 1223 > 10:20:00 0.98 0.0 1.01 0.3 999 > 10:40:00 0.94 0.1 0.96 0.8 767 > 11:00:00 0.97 0.0 0.99 0.3 407 > 11:20:00 0.97 0.1 0.97 0.2 519 > 11:40:00 0.98 0.1 0.99 0.3 700 > 12:00:00 1.00 0.1 1.00 0.1 440 > 12:20:00 0.99 0.1 0.99 0.2 501 > 12:40:00 0.99 0.1 1.00 0.1 409 > 13:00:00 1.00 0.0 0.98 0.0 344 > 13:20:00 1.00 0.0 0.99 0.0 333 > 13:40:01 1.00 0.1 1.01 0.1 263 > 14:00:01 1.00 0.0 1.02 0.1 243 > 14:20:01 0.99 0.0 1.01 0.1 317 > 14:40:01 1.00 0.1 1.01 0.1 352 > 15:00:00 1.00 0.1 0.98 0.0 778 > 15:20:01 0.98 0.1 1.01 0.2 502 > 15:40:00 0.99 0.1 0.98 0.1 1298 > 16:00:01 1.00 0.1 0.97 0.0 797 > 16:20:01 1.00 0.0 1.00 0.0 211 > 16:40:01 1.00 0.0 0.99 0.0 206 > 17:00:00 0.98 0.1 1.00 0.1 430> > Average 0.46 0.0 0.46 0.1 483 > > onstat -g seg> > Informix Dynamic Server Version 7.30.UC7 -- On-Line -- Up 4 days > 23:02:22 -- 298184 Kbytes > > Segment Summary: > id key addr size ovhd class blkused blkfree > 521 1381451778 c0bda000 2711552 652 M 325 6 > 520 1381451777 d1235000 202932224 5572 R* 24765 7 > (shared) 1381451777 dd3bd000 102408192 2172 V 6884 5617 > Total: - - 308051968 - - 31974 5630 > > (* segment locked in memory) > > I need to read up on interpreting this onfo :o( but this gives an idea of > how the memory is working at present. > > Thanks. > > -- > Tony Flaherty > Snr. A/P, Informix DBA, HpUx Admin, Gimmi a broom! > MFS Ltd. > > Madison Pruet wrote in message <39C8C202.37844AA8@informix.com>... > >Well, you do need more buffers. Generally the read cache rate needs to be > >pretty close to 99%. You might be able to reduce your checkpoint times > even > >more by setting LRU_MAX_DIRTY to 2. > > > >You also have skewed usage of the chunks. From your sample ol_ch_mars_a is > >accounting for a rather large percentage of your activity. You might want > to > >consider moving some of the tables in there to othere chunks. This might > help > >your checkpoint times also. > > > >I would consider setting DD_HASHSIZE to a much larger value. I don't know > where > >all of your IO is coming from, but if it is dictionary information, this > would > >help. If oninit is only taking 50% of the system when it is overloaded, > that > >implies that you are blocking quite a bit on IO completion. Increasing > buffers > >would help. > > > >I'm sure that some other folks that frequent the news group will have some > other > >ideas and/or suggestions. I'll be interested to see what they suggest. > > > > > > > >Tony Flaherty wrote: > > [snipped my original post] > > >> -- > >> Tony Flaherty > >> Snr. A/P, Informix DBA, HpUx Admin, Gimmi a broom! > >> MFS Ltd. > > > >-- > >Madison Pruet > > > >=========================================== > >Enterprise Replication Product Developement > >Dallas, Texas > >Informix Software > >=========================================== > > > > -- Madison Pruet =========================================== Enterprise Replication Product Developement Dallas, Texas Informix Software ===========================================