upgrade to IDS 7.30 UC2-1
After upgrading to IDS 7.3x on DG/UX, the engine became very slow, with onstat -P showing Btree buffer usage climbing to 50-95% and heavy dirty-buffer/checkpoint activity. Respondents identified it as a known defect (bug 115327, plus 111681): index pages are incorrectly shrunk by the btree cleaners so both leaf and node flags get set, pushing excess pages into the MED_HIGH priority class. Fixes were listed in 7.31UC2XA/7.31UC4, 7.30xC10 and 9.20UC1; as an interim workaround, rebuild indexes created under 7.2x or on tables with many deletes.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
After we upgraded to version 7.30 UC2-1 IDS running under DG/UX 4.20, the engine seems to run like a dog , nothing else has changed. it seems that after running update statistics it is even worse . ONSTAT - R Informix Dynamic Server Version 7.31.UC2 -- On-Line (CKPT REQ) -- Up 20 days 0 2:22:15 -- 190512 Kbytes Blocked:CKPT 8 buffer LRU queue pairs priority levels # f/m pair total % of length LOW MED_LOW MED_HIGH HIGH 0 f 3123 76.5% 2390 0 974 1357 59 1 m 23.5% 733 0 636 97 0 2 f 3123 76.9% 2403 0 947 1402 54 3 m 23.1% 720 0 639 81 0 4 f 3123 77.4% 2416 0 973 1397 46 5 m 22.6% 707 0 601 106 0 6 f 3122 75.6% 2360 0 952 1349 59 7 m 24.4% 762 0 676 86 0 8 f 3122 82.3% 2568 0 1193 1327 48 9 m 17.7% 554 0 473 81 0 10 f 3122 76.4% 2386 0 964 1370 52 11 m 23.6% 736 0 636 100 0 12 F 3123 77.6% 2422 0 1010 1340 72 13 m 22.4% 701 0 618 83 0 14 f 3123 79.4% 2480 0 1085 1334 61 15 m 20.6% 643 0 548 95 0 5556 dirty, 24981 queued, 25000 total, 32768 hash buckets, 2048 buffer size start clean at 60% (of pair total) dirty, or 1875 buffs dirty, stop at 50% 0 priority downgrades, 0 priority upgrades ONSTAT -P ......SNIP Totals: 25000 13282 10250 1468 0 2479 Percentages: Data 41.00 Btree 53.13 <<<< this one gets up to 95% at times Other 5.87 We are thankful for any clues Ralf Peters
> > Percentages: > Data 41.00 > Btree 53.13 <<<< this one gets up to 95% at times This is the problem. This bug is fixed in 7.31UC4 or in 7.30UC10. You need to upgrade once again... :) ------------------------------------------------- With best regards, Yuri Dovgart SAP R/3, Informix technical consultant, Informix Certified Professional, Senior System Consultant System Architecture and High Availability Systems, 'Telecominvest' company Email y_dovgart@tci.ukrtel.net ICQ 39284285 > Other 5.87 > > > We are thankful for any clues > > Ralf Peters > >
> > Percentages: > Data 41.00 > Btree 53.13 <<<< this one gets up to 95% at times This is the problem. This bug is fixed in 7.31UC4 or in 7.30UC10. You need to upgrade once again... :) ------------------------------------------------- With best regards, Yuri Dovgart SAP R/3, Informix technical consultant, Informix Certified Professional, Senior System Consultant System Architecture and High Availability Systems, 'Telecominvest' company Email y_dovgart@tci.ukrtel.net ICQ 39284285 > Other 5.87 > > > We are thankful for any clues > > Ralf Peters > >
> > Percentages: > Data 41.00 > Btree 53.13 <<<< this one gets up to 95% at times This is the problem. This bug is fixed in 7.31UC4 or in 7.30UC10. You need to upgrade once again... :) ------------------------------------------------- With best regards, Yuri Dovgart SAP R/3, Informix technical consultant, Informix Certified Professional, Senior System Consultant System Architecture and High Availability Systems, 'Telecominvest' company Email y_dovgart@tci.ukrtel.net ICQ 39284285 > Other 5.87 > > > We are thankful for any clues > > Ralf Peters > >
> > 7.31UC2XA > 7.31UC4 > 7.32 (not out yet) > 9.20UC1 (IDS.2000/Foundation.2000) 7.30TC10 too. > > As a temporary fix you can rebuild any indexes that were build by 7.2x > versions (which did not set the flags properly to begin with since they > were not used for much back then) and any indexes on tables that have has > substantial deletes since the last index rebuild (it is index compression > of pages emptied by deletes that still causes the problem, the BTREE > cleaner threads mis-set the flags on the newly compressed index pages). > > Ultimately you will have to upgrade again. > > Art S. Kagel > > Ralf Peters wrote: > > > > After we upgraded to version 7.30 UC2-1 IDS running under DG/UX 4.20, the > > engine seems to run like a dog , nothing else has changed. it seems that > > after running update statistics it is even worse . > > > > ONSTAT - R > > Informix Dynamic Server Version 7.31.UC2 -- On-Line (CKPT REQ) -- Up 20 > > days 0 > > 2:22:15 -- 190512 Kbytes > > Blocked:CKPT > > > > 8 buffer LRU queue pairs priority levels > > # f/m pair total % of length LOW MED_LOW MED_HIGH HIGH > > 0 f 3123 76.5% 2390 0 974 1357 59 > > 1 m 23.5% 733 0 636 97 0 > > 2 f 3123 76.9% 2403 0 947 1402 54 > > 3 m 23.1% 720 0 639 81 0 > > 4 f 3123 77.4% 2416 0 973 1397 46 > > 5 m 22.6% 707 0 601 106 0 > > 6 f 3122 75.6% 2360 0 952 1349 59 > > 7 m 24.4% 762 0 676 86 0 > > 8 f 3122 82.3% 2568 0 1193 1327 48 > > 9 m 17.7% 554 0 473 81 0 > > 10 f 3122 76.4% 2386 0 964 1370 52 > > 11 m 23.6% 736 0 636 100 0 > > 12 F 3123 77.6% 2422 0 1010 1340 72 > > 13 m 22.4% 701 0 618 83 0 > > 14 f 3123 79.4% 2480 0 1085 1334 61 > > 15 m 20.6% 643 0 548 95 0 > > 5556 dirty, 24981 queued, 25000 total, 32768 hash buckets, 2048 buffer size > > start clean at 60% (of pair total) dirty, or 1875 buffs dirty, stop at 50% > > 0 priority downgrades, 0 priority upgrades > > > > ONSTAT -P > > ......SNIP > > > > Totals: 25000 13282 10250 1468 0 2479 > > > > Percentages: > > Data 41.00 > > Btree 53.13 <<<< this one gets up to 95% at times > > Other 5.87 > > > > We are thankful for any clues > > > > Ralf Peters
You are being hit with bug #115327: INDEX PAGES CAN BE INCORRECTLY SHRUNK SETTING BOTH LEAF AND NODE FLAGS WHICH WILL CAUSE EXCESS PAGES TO BE PLACED IN THE MED_HIGH PRIORITY CLASS... This is fixed in: 7.31UC2XA 7.31UC4 7.32 (not out yet) 9.20UC1 (IDS.2000/Foundation.2000) As a temporary fix you can rebuild any indexes that were build by 7.2x versions (which did not set the flags properly to begin with since they were not used for much back then) and any indexes on tables that have has substantial deletes since the last index rebuild (it is index compression of pages emptied by deletes that still causes the problem, the BTREE cleaner threads mis-set the flags on the newly compressed index pages). Ultimately you will have to upgrade again. Art S. Kagel Ralf Peters wrote: > > After we upgraded to version 7.30 UC2-1 IDS running under DG/UX 4.20, the > engine seems to run like a dog , nothing else has changed. it seems that > after running update statistics it is even worse . > > ONSTAT - R > Informix Dynamic Server Version 7.31.UC2 -- On-Line (CKPT REQ) -- Up 20 > days 0 > 2:22:15 -- 190512 Kbytes > Blocked:CKPT > > 8 buffer LRU queue pairs priority levels > # f/m pair total % of length LOW MED_LOW MED_HIGH HIGH > 0 f 3123 76.5% 2390 0 974 1357 59 > 1 m 23.5% 733 0 636 97 0 > 2 f 3123 76.9% 2403 0 947 1402 54 > 3 m 23.1% 720 0 639 81 0 > 4 f 3123 77.4% 2416 0 973 1397 46 > 5 m 22.6% 707 0 601 106 0 > 6 f 3122 75.6% 2360 0 952 1349 59 > 7 m 24.4% 762 0 676 86 0 > 8 f 3122 82.3% 2568 0 1193 1327 48 > 9 m 17.7% 554 0 473 81 0 > 10 f 3122 76.4% 2386 0 964 1370 52 > 11 m 23.6% 736 0 636 100 0 > 12 F 3123 77.6% 2422 0 1010 1340 72 > 13 m 22.4% 701 0 618 83 0 > 14 f 3123 79.4% 2480 0 1085 1334 61 > 15 m 20.6% 643 0 548 95 0 > 5556 dirty, 24981 queued, 25000 total, 32768 hash buckets, 2048 buffer size > start clean at 60% (of pair total) dirty, or 1875 buffs dirty, stop at 50% > 0 priority downgrades, 0 priority upgrades > > ONSTAT -P > ......SNIP > > Totals: 25000 13282 10250 1468 0 2479 > > Percentages: > Data 41.00 > Btree 53.13 <<<< this one gets up to 95% at times > Other 5.87 > > We are thankful for any clues > > Ralf Peters
Art S. Kagel <kagel@bloomberg.net> wrote in message news:382B116D.CC19C39A@bloomberg.net... > Yuri Dovgart wrote: > > > > > > > > 7.31UC2XA > > > 7.31UC4 > > > 7.32 (not out yet) > > > 9.20UC1 (IDS.2000/Foundation.2000) > > > > 7.30TC10 too. > > Isn't 7.30 kind of irrelevant to this bug though, it does not do buffer > priority, that is a 7.31 feature? I suppose it fixes the marking of the > index page flags by the BTREE Cleaners which will help if one eventually > upgrades to a post 7.30 version. As far as I understand we are talking about bugs 115327 and 111681. BOTH bugs are fixed in 7.31TC4 or 7.30TC10. Or I understand something wrong ? I found problem with Btree percentages on 2 SAP sites running 7.30TC7XB. Or we told about different things ? As far as I understand this problem appears since 7.30 release, in wich buffer management was changed. ------------------------------------------------- With best regards, Yuri Dovgart SAP R/3, Informix technical consultant, Informix Certified Professional, Senior System Consultant System Architecture and High Availability Systems, 'Telecominvest' company Email y_dovgart@tci.ukrtel.net ICQ 39284285 > > Art S. Kagel
Yuri Dovgart wrote: > > > > > 7.31UC2XA > > 7.31UC4 > > 7.32 (not out yet) > > 9.20UC1 (IDS.2000/Foundation.2000) > > 7.30TC10 too. Isn't 7.30 kind of irrelevant to this bug though, it does not do buffer priority, that is a 7.31 feature? I suppose it fixes the marking of the index page flags by the BTREE Cleaners which will help if one eventually upgrades to a post 7.30 version. Art S. Kagel
No, you are right Yuri. I was thinking of the Buffer Priority Aging which was added in 7.31 which is related but another thing. Sorry to confuse the issue. I have no 7.30's still around so I could not check this out. Apologies to all for wasting bandwidth. Art S. Kagel Yuri Dovgart wrote: > > Art S. Kagel <kagel@bloomberg.net> wrote in message > news:382B116D.CC19C39A@bloomberg.net... > > Yuri Dovgart wrote: > > > > > > > > > > > 7.31UC2XA > > > > 7.31UC4 > > > > 7.32 (not out yet) > > > > 9.20UC1 (IDS.2000/Foundation.2000) > > > > > > 7.30TC10 too. > > > > Isn't 7.30 kind of irrelevant to this bug though, it does not do buffer > > priority, that is a 7.31 feature? I suppose it fixes the marking of the > > index page flags by the BTREE Cleaners which will help if one eventually > > upgrades to a post 7.30 version. > > As far as I understand we are talking about bugs 115327 and 111681. BOTH > bugs are fixed in 7.31TC4 or 7.30TC10. Or I understand something wrong ? I > found problem with Btree percentages on 2 SAP sites running 7.30TC7XB. Or we > told about different things ? As far as I understand this problem appears > since 7.30 release, in wich buffer management was changed. > > ------------------------------------------------- > With best regards, Yuri Dovgart > SAP R/3, Informix technical consultant, > Informix Certified Professional, > Senior System Consultant > System Architecture and High Availability Systems, > 'Telecominvest' company > Email y_dovgart@tci.ukrtel.net > ICQ 39284285 > > > > > Art S. Kagel