Re: Poor Performance with 7.31.uc6

From: "Ty O'Kelly" <tokelly@maxor.invalid.com> > >We have a database that was upgraded from 7.23.uc5 to 7.31.uc6 a couple >of weeks ago and we seem to be experiencing some performance issues. >This is an OLTP system that processes insurance claims. Claims that >would have taken a few seconds to process are now taking several >minutes. I am concerned we have had an index go bad, but I won't be >able to check that until later when I can bring the system down. I know >I need examine configuration options as well. The poor performance has >not been a constant problem. It is possible that the performance >decreases with the amount of time the server has been up, as we had to >cycle it last week and everything seemed fine until the past couple of >days. After the upgrade I performed the "Update Statistics" as noted in >the documentation, but I have seen a post from Clown suggesting "UPDATE >STATISTICS DROP DISTRIBUTIONS". Would this make a significant >difference? Using recent posts as a guide I am including output from Of course not! I made the suggestion purely out of spite. >onstat -c, onstat -p and onstat -P | tail -5, onstat -u | tail-2, onstat>-F and onstat -R. None of the onconfig values have been changed since >the upgrade, and these values were setup by the previous DBA, so I don't >know why specific values were chosen. I know the last bufwaits ratio I >calculated was much higher than Art's recommended 7%. I appreciate any >input! >onstat -c:> >RESIDENT 0 # Forced residency flag (Yes = 1, No Try -1 ># Shared Memory Parameters > >LOCKS 100000 # Maximum number of locks >BUFFERS 80000 # Maximum number of shared buffers Judging by your cache hit ratio, you really need more buffers. >NUMAIOVPS 1 # Number of IO vps Try 2 >CLEANERS 14 # Number of buffer cleaner processes Try 127 >LRUS 8 # Number of LRU queues Try 127 ># Read Ahead Variables >RA_PAGES 64 # Number of pages to attempt to read>ahead >RA_THRESHOLD 60 # Number of pages left before next group Try 32/28 >DBSPACETEMP dbtemp # Default temp dbspaces You should have at least 3. >onstat -p:> >Informix Dynamic Server Version 7.31.UC6 -- On-Line (Prim) -- Up 7 >days 03:16:33 -- 588512 Kbytes > >Profile >dskreads pagreads bufreads %cached dskwrits pagwrits bufwrits %cached >202836401 45424197 562375502 63.93 12293974 11046860 47649089 74.20 63%? Way too low. >onstat -P | tail -5:> >Percentages: >Data 6.77 >Btree 92.40 >Other 0.82 This looks ugly. What is the output of onstat -B | grep " d0 " | wc -l ? >onstat -R: It really looks like you've run into the LRU priority scheduling bug? I could have sworn this was fixed. Can you try dropping and recreating your indexes, as well as USLDD? ________________________________________________________________________ Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com

Obnoxio The Clown wrote: > From: "Ty O'Kelly" <tokelly@maxor.invalid.com> > >After the upgrade I performed the "Update Statistics" as noted in > >the documentation, but I have seen a post from Clown suggesting "UPDATE > >STATISTICS DROP DISTRIBUTIONS". Would this make a significant > >difference? Using recent posts as a guide I am including output from > > Of course not! I made the suggestion purely out of spite. Ok, let me rephrase the question. Will the performance increase be significant enough to justify my staying up late for however many nights it will take to do this, since we typically do this sort of thing when the user load is light? Of course, if I wind up having to drop and recreate the all indexes this might not really matter... > >onstat -P | tail -5:> > > >Percentages: > >Data 6.77 > >Btree 92.40 > >Other 0.82 > > This looks ugly. What is the output of > > onstat -B | grep " d0 " | wc -l 0 > It really looks like you've run into the LRU priority scheduling bug? I > could have sworn this was fixed. Can you try dropping and recreating your > indexes, as well as USLDD? I may not have much choice but to drop and recreate indexes. Sounds like I will get to enjoy quite a few late nights. What is USLDD? I don't find a reference to that abbreviation anywhere. Thanks, Ty -- Ty O'Kelly DBA tokelly@maxor.invalid.com 806-324-5521