RE: Performance Problems with 7.30.UC7XB

Are these page flags set properly if you perform an UPDATE STATISTICS on all tables? The Informix Migration Guide suggests that such an task be undertaken as part of the upgrade. -----Original Message----- From: Dominic Binks [mailto:dominic.binks@aethos.co.uk] Sent: Wednesday, June 30, 1999 09:47 To: informix-list@iiug.org Subject: Re: Performance Problems with 7.30.UC7XB Dominic Binks wrote: > > "Agnew, Doug" wrote: > > > > Phil, > > > > The database has been performing fairly well, at least not exhibiting the > > behavior that we are seeing now. We have just recently changed from > > 3.1h SAP kernel to 3.1I kernel and as a result moved from 7.24... to > > 7.30.UC5 and now 7.30.UC7XB of Informix. Other than setting the > > parameters recommended by SAP, no changes have been made to our existing > > systems. We just bounced the system at 10:30 this morning, saw good > > performance for the first couple of hours, went to lunch, and now the > > read cache rate is down around 80%, the CPU load on the database server > > has gone from 2.xx to over 6.00, the onstat -P shows over 98% of the > > buffer pool dedicated to Btree buffers (the new 'feature' of 7.3x) -- was > > 60% when we went to lunch and users have gone from 'this is better than it > > was' to 'this is killing us'. This persistent degradation cycle (boot > > system to get good performance, wait 12 hours, reboot) has been with us > > ever since we went to 7.3x and we're at the 'clueless' point. > > Doug > > (Opinions here are mine alone -- no one else wants them) > > I know it's not good practice to respond to one's own post but what the heck. This problem does appear to be related to indexes on databases that have been upgraded. It doesn't seem to occur on databases created in 7.3x. What happens in that there are page flags on the index that tell the engine what type of index page it is (branch, root, leaf etc.) Prior to 7.3x these were not used for anything. In 7.3x they are used to determine the priority for the buffer cache page. What we saw was that the index page had a flags value of 0xd0 (meaning it is a leaf node and a branch node). Since the index is large this takes priority over data pages and squeezes data pages out of the cache. These cache pages are never allowed to be used by data pages. How to diagnose this problem: Use onstat -P to see if you buffer cache is heavily skewed to btree pages. onstat -P also tells you how many pages of cache are related to each partnum. Hopefully, you've got detached indexes and these come up as separate entries in onstat -P. Once you have a partnum you can locate the troublesome index. How to fix the problem: Rebuild the troublesome index(es) under 7.30. I guess this would be good advise for any upgrading to 7.30. As a guide any big frequently used index will be a candidate for this problem. Informix have a copy of a backup taken from one of our customer sites to examine this problem and try to work out why it happens and how to resolve it. Hope this helps. Dominic -- Dominic Binks/Systems Engineer/Logica-Aldiscon 400 Park Avenue/Aztec West/Bristol/BS32 4TR/United Kingdom Tel: +44 1454 614455/Fax: +44 1454 620527/Mobile: +44 498 693964 E-mail: dominic.binks@aethos.co.uk

"Bernstein, Rick" wrote: > > Are these page flags set properly if you perform an UPDATE STATISTICS on > all tables? The Informix Migration Guide suggests that such an task > be undertaken as part of the upgrade. > Apparently not as we had run the suggested DROP DISTRIBUTIONS and UPDATE STATISTICS. Also it does not seem to affect all upgraded servers. We have gone from 7.24.UC6 (the version the database was initially created with) to 7.30.UC7, if that's of any relevance. However, we have seen the problem on other sites with different versions of Informix. Dominic -- Dominic Binks/Systems Engineer/Logica-Aldiscon 400 Park Avenue/Aztec West/Bristol/BS32 4TR/United Kingdom Tel: +44 1454 614455/Fax: +44 1454 620527/Mobile: +44 498 693964 E-mail: dominic.binks@aethos.co.uk