Re: OnLine 7.13 performance problems - comments please?

Hi Neil, It appears that some of your queries are going into sequential scans (2 million scans and 874 million locks in 12 hours!) so it looks like there is some heavy DS going on. There are only 22019 commits - your OLTP chaps are probably being drowned by DS. I would suggest that your indexes would need looking into. Of course, the question is which tables, which columns? Well, quite often, it takes just one or a few users to bring the system to its knees (personal experience!) and identifying who they are and what they are doing is the key. I can suggest the following 1. Identify the offending user This can be done using the onstat -u utility. Users who are active have a '-' as their first flag. You can try the following onstat -u | \\ grep -v 'informix' | \\ # Remove user 'informix' activity grep "^.................-" # 18th character is the first flag This should give you a list of user sessions which are active. Run this at intervals of 2 to 3 seconds for about a minute to check whether the same session repeats. If it does, this could be the guy you have to go for. 2. Now, find out what the joker is doing You can use 'onstat -g ses' to get information. Try this shell script which is called with the user as an arguement and run under the bourne shell. : for SESSION in `onstat -u | grep $1 | cut -b 26-34 | sort -r -n ` do onstat -g ses $SESSION | more done This should give you the various sessions of the user, with the offending one being shown first. Among other information, it will give you the actual sql statement being currently executed. Using that you decide one of the following (my list of possible actions) a. Order a summary court martial b. Knock out the DS user to get OLTP off its knees b. Talk to the user and question the idiotic behaviour c. Get into a discussion with the user with the idea of creating a new index. I would also consider the following ONCONFIG changes if your priority is OLTP MAX_PDQPRIORITY 0 DS_TOTAL_MEMORY 5000 DS_MAX_SCANS 10 DS_MAX_QUERIES 2 Your LOGSIZE seems very high also. Besides disadvantages related to backup/restore, your performance could suffer too if you have an automatic backup of logs running - if you have any other data on that disk (even your next log!), there would be severe contention for that disk while your 6 Mb log is being backed up to tape. HTH. Neil Truby wrote: >} >} We're running OnLine 7.13 under Solaris 2.4, on a Sparc 20 with 160M of >} memory. The load is increasing, and the users are complaining about >} performance. >} >} Here's my onstat -p in the 12 hours since I zeroed the stats: >} >} Profile >} dskreads pagreads bufreads %cached dskwrits pagwrits bufwrits %cached >} 1581705 2954933 454881229 99.65 105177 221459 827392 87.29 >} >} isamtot open start read write rewrite delete commit >} rollbk >} 301750703 34728791 54605347 158736271 67734 66525 88369 >} 22019 712 >} >} ovlock ovuserthread ovbuff usercpu syscpu numckpts flushes >} 0 0 0 45736.59 1212.92 73 146 >} >} bufwaits lokwaits lockreqs deadlks dltouts ckpwaits compress seqscans >} 166771 2568 872540367 0 0 137 6094 2058386 >} >} ixda-RA idx-RA da-RA RA-pgsused lchwaits >} 806840 188 397745 1204459 1016 >} >} ..... and here are selected highlights of my onconfig file. My >} explanation to the users is that the machine, which consistently shows >} 0% idle CPU, is simply overwhelmed by the application (which is >} bought-in, so we can't tune it). Any other comments, suggestions or >} opinions would be gratefully received. >} >} Thanks >} Neil >} ---------------------- Rudy Fernandes (ICP) GIC, Kuwait ----------------------