Re: Performance Problems with 7.30.UC7XB

"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) > We are currently investigating this problem. We too upgraded from 7.24(UC6 FWIW). The problem is the buffer cache creeps. We weren't able to reproduce this problem on our test system until yesterday, but then we had a production backup to work from. The production backup does reproduce the problem, although we can't put enough load on to get into the high 90% values quickly. But we can get to about 60% in about 1hr. 75% after 12 hours. Yes we too see the high load averages too. We're running on K580/6 and HP-UX 10.20. We've also got the Y2K patch bundle. I'd be interested in hearing which of these facts are similar to your environment. BTW can you work out if there are any predominant indexes in the buffer cache. We found two in our application (the most important indexes on the two biggest tables). If we get any firm results we'll let you know. 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

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

Dominic, So, if I understand correctly, root and branch index pages should be marked med-high or high (for root) and leaf pages (which are far more numerous) should be marked as low like data pages are. In that case, leaf pages would be replaced by data pages fairly regularly, but then they are not needed as frequently as root and branch pages. Doug Dominic Binks wrote in message <377A4A15.59AC0C8F@aethos.co.uk>... >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

Doug Agnew wrote: > > Dominic, > > So, if I understand correctly, root and branch index pages should be marked > med-high or high (for root) and leaf pages (which are far more numerous) > should be marked as low like data pages are. In that case, leaf pages would > be replaced by data pages fairly regularly, but then they are not needed as > frequently as root and branch pages. > > Doug > That is correct. The problem appears to be the the flags on the index page are incorrect and 7.30 uses this to determine the priority of the page. 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

Dominic Binks wrote: > > Doug Agnew wrote: > > > > Dominic, > > > > So, if I understand correctly, root and branch index pages should be marked > > med-high or high (for root) and leaf pages (which are far more numerous) > > should be marked as low like data pages are. In that case, leaf pages would > > be replaced by data pages fairly regularly, but then they are not needed as > > frequently as root and branch pages. > > > > Doug > > > > That is correct. The problem appears to be the the flags on the index > page are incorrect and 7.30 uses this to determine the priority of the > page. Great news Dominick! I will be upgrading several 7.24 servers to 7.31 shortly so this will help me avoid this pain. Thanks for posting. Hopefully after rebuilding the indexes the buffer classing code will operate sanely. We shall see. Art S. Kagel

"Art S. Kagel" wrote: > > Dominic Binks wrote: > > > > Doug Agnew wrote: > > > > > > Dominic, > > > > > > So, if I understand correctly, root and branch index pages should be marked > > > med-high or high (for root) and leaf pages (which are far more numerous) > > > should be marked as low like data pages are. In that case, leaf pages would > > > be replaced by data pages fairly regularly, but then they are not needed as > > > frequently as root and branch pages. > > > > > > Doug > > > > > > > That is correct. The problem appears to be the the flags on the index > > page are incorrect and 7.30 uses this to determine the priority of the > > page. > > Great news Dominick! I will be upgrading several 7.24 servers to 7.31 > shortly so this will help me avoid this pain. Thanks for posting. > Hopefully after rebuilding the indexes the buffer classing code will > operate sanely. We shall see. > > Art S. Kagel We have a small SQL script which helps to identify problem indexes to save you perhaps half an hour or so... 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

Dominic Binks wrote: > > "Art S. Kagel" wrote: > ... all about index and buffers problem in 7.3x > > We have a small SQL script which helps to identify problem indexes to > save you perhaps half an hour or so... > > Dominic As promised the SMI SQL script to locate branch-leaf pages. I guess if the table is small this is ok, if the table is big it is death. I can't tell you if it will work against a 7.2x release as we haven't run it against a 7.2x database. SELECT tabname , pg_partnum, pg_pgnum FROM systabnames t, systabpaghdrs p WHERE t.partnum = p.pg_partnum AND pg_flags = 208 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

Dominic Binks wrote: > > SELECT tabname , pg_partnum, pg_pgnum > FROM systabnames t, systabpaghdrs p > WHERE t.partnum = p.pg_partnum AND pg_flags = 208> Apologies, that should pg_pagenum, not pg_pgnum. 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

Dominic Binks wrote: > > Dominic Binks wrote: > > > > SELECT tabname , pg_partnum, pg_pgnum > > FROM systabnames t, systabpaghdrs p > > WHERE t.partnum = p.pg_partnum AND pg_flags = 208> > > > Apologies, that should pg_pagenum, not pg_pgnum. Thanks Dominic. Yes I have reported how slow the sysmaster page header and bitmap tables are compared to scanning the disk pages themselves ;-{ (I didn't say that did I?) to Informix providing two versions of the same program that runs 100X faster without sysmaster and they came back and said that there was no difference on their test machine! "Does the test machine have 213 chunks of 2GB each?", I asked. "No, where would we get a test machine that big? It had 4 chunks of 100MB each!", they replied. "Oh I said that's the problem! Your test is not large enough to show the problem!", I continued. "Uh huh. So can we close this case since the problem is not reproduceable?", they countered. "Hey!", I said, "Do whatever you want it's not MY product's premier feature (SMI) that is essentially unuseable by my biggest customers!" "Thanks", they answered, "case closed." BTW what output am I looking for from the query above if the indexes will be problematic? Art S. Kagel