RE: partition buffer summary - Btree percentage

Would someone help me with the syntax here. I have been through the manuals and tried it a bunch of ways, but it still isn't working. I need to disable the primary key. Here is the create table: create table "eadmin".ad_prauth ( ad_product char(5) not null , ad_prodtype char(5) not null , ad_platform char(2) not null , ad_feature char(5), ad_key integer not null , primary key (ad_key) constraint "eadmin".ad_prauth ); Thanks, Dianne -----Original Message----- From: Art S. Kagel [mailto:kagel@bloomberg.net] Sent: September 30,1999 8:59 AM To: informix-list@iiug.org Subject: Re: partition buffer summary - Btree percentage If your primary key indexes are are manually created so that you have access to their names you should just be able to disable and reenable them so that they rebuild themselves in place. If the indexnames are invisible (ie do not show up separately in dbschema and have weird names in dbaccess) then yes you will have to drop and readd the constraints. Same for all constraint indexes. Art S. Kagel dianne.pendleton@autodesk.com wrote: > > Regarding the rebuilding of indexes to help the situation, would I also have > to drop and recreate any primary keys that are on the tables from the sql? > > Thanks, Dianne > > -----Original Message----- > From: Doug Agnew [mailto:dagnew@charlottepipe.com] > Sent: September 29,1999 11:42 AM > To: informix-list@iiug.org > Subject: Re: partition buffer summary - Btree percentage > > Dianne, > > Yep, it is cumulative. The longer the server is up, the worse it gets > (until it hits 98+%). With SAP, we were bouncing the server about every 12 > hours. > > We're running 7.30.UC7XK, which allows you to turn off the buffer > prioritization and getting ready to start running UC7XK1, which is SUPPOSED > to fix the problem. > > Thanks, > Doug > dianne.pendleton@autodesk.com wrote in message > <7stl0t$6uj$1@news.xmission.com>... > > > >Hi all. We recently upgraded from 7.23.UC1 to 7.31.UC2, HP-UX 10.20. > >Performance was okay for about 3 days then it got bad. onstat -P showed a > >high Btree # (95-99%), onstat -R showed a lot of Medium-High's. Both of > >these results could point to the 115327 bug described here. We called > >Informix and they had us increase our LRUS, CLEANERS, and NUMAIOVPS. We > >bounced the database yesterday at noon to put them into effect. Everything > >ran good all afternoon and evening until this morning. Our update > >statistics scripts ran early this morning. > >Does anyone know if this bug 115327 has a cumulative effect? In other > >words, I theorize that if I bounce the database now, whatever has > >accumulated will be cleared out and we should be good for a while. We will > >not run any update statistics scripts for now to keep it as clean as > >possible. This is only a temporary solution until Informix gets back to us > >with patch information for the bug. Comments, anyone? I would appreciate > >the feedback while we struggle through this mess. > >(Yes, I am aware of the sql to run and rebuilding the indexes, but that > will > >take a lot of time so I'm looking at it from a different angle.) > > > >Thanks, Dianne > > > >-----Original Message----- > >From: plsmith@my-deja.com [mailto:plsmith@my-deja.com] > >Sent: September 21,1999 11:40 PM > >To: informix-list@iiug.org > >Subject: Re: partition buffer summary - Btree percentage > > > > > >Doug, good luck with the stress tests - I would be interested to hear > >how it goes. The version I have is 7.30.UC7XM and seems ok so far. > > > >I think the interim 7.30 release with the fix is 7.30.UC10 - which isn't > >out yet. > > > >Pete > >Logica > > > > > >In article <6uPF3.2929$9W2.5801@news2.mco>, > > "Doug Agnew" <dagnew@charlottepipe.com> wrote: > >> It is sorta fixed in 7.30UC7 --- we are using version 7.30UC7XK1, and > >it > >> seems to be okay there (at least our small test system didn't show the > >> buffer problem). Will probably be stressing it this week as we > >upgrade > >> SAP.... > >> > >> Doug > >> > >> Art S. Kagel wrote in message <37E7BA3B.DC191AA1@bloomberg.net>... > >> >I believe it is fixed in 7.31UC3 and 7.30UC7, I relocated my desk and > >> >cannot find the note on the subject so I'm going from memory here. I > >do > >> >not think there is a fix contemplated for 7.2x so upgrades MAY have > >to > >> >rebuild indexes to eliminate the problem. > >> > > >> >Art S. Kagel > >> > > >> >"Carlson@WHSmith" wrote: > >> >> > >> >> Any idea in which version this bug is fixed? > >> >> > >> >> John Carlson > >> >> Informix DBA > >> >> WHSmith USA > >> >> > >> >> plsmith@my-deja.com wrote: > >> >> > > >> >> > Vardan > >> >> > > >> >> > You have almost certainly run into bug no. 115327 and you should > >be > >> >> > able to request a patch to fix it. > >> >> > It is a bug in 7.2x and 7.3x whereby btree pages can be generated > >with > >> >> > an invalid page type. This page type is then handled incorrectly > >by the > >> >> > new 7.30 buffer priority management. > >> >> > Dropping and rebuilding affected indexes is a reasonable > >workaround, > >> but > >> >> > the problme could re-occur depending on the habits of you > >application. > >> >> > The invalid page types are generated when all the rows are > >deleted from > >> >> > a table thus compressing the index down to a single page. When > >rows are > >> >> > inserted an invalid page type is then copied through all the leaf > >> nodes. > >> >> > If you look at onstat -R you will see that the majority of pages > >are in > >> >> > the medium-high category, whereas data pages are medium-low. Leaf > >node > >> >> > pages should also be medium low, but because of the invalid page > >type > >> >> > they are placed in the medium-high category, thus squeezing out > >the > >> data > >> >> > pages. If you install a patch you should see a reversal of these > >> >> > percentages and an increase in your read cache. > >> >> > If you talk to Informix tech support refer them to case No. > >861366 > >> which > >> >> > contains a reproducable example. > >> >> > I have a small script which will identify invalid pages types > >(sql > >> >> > courtesy of John Miller, thanks ). > >> >> > > >> >> > email me if you would like a copy. > >> >> > > >> >> > hth > >> >> > Pete Smith > >> >> > Logica > >> >> > > >> >> > In article <7s5n3g$mq7$1@nnrp1.deja.com>, > >> >> > Vardan Aroustamian <vaar@geocities.com> wrote: > >> >> > > Hello, > >> >> > > > >> >> > > I'm new to SAP environment and would like to get some input > >> >> > > from experienced users. > >> >> > > > >> >> > > Anyone knows of some "usual" percentage for Data vs. Btree > >> >> > > pages in the buffer pool (from onstat -P output). > >> >> > > > >> >> > > Currently I have > >> >> > > > >> >> > > Percentages: > >> >> > > Data 20.09 > >> >> > > Btree 77.75 > >> >> > > Other

In article <7t0neh$q0$1@news.xmission.com>, dianne.pendleton@autodesk.com wrote: > > Would someone help me with the syntax here. I have been through the manuals > and tried it a bunch of ways, but it still isn't working. > I need to disable the primary key. > > Here is the create table: > > create table "eadmin".ad_prauth > ( > ad_product char(5) not null , > ad_prodtype char(5) not null , > ad_platform char(2) not null , > ad_feature char(5), > ad_key integer not null , > primary key (ad_key) constraint "eadmin".ad_prauth > ); > > Thanks, Dianne set constraints for "eadmin".ad_prauth disabled; Regards, Vardan > > -----Original Message----- > From: Art S. Kagel [mailto:kagel@bloomberg.net] > Sent: September 30,1999 8:59 AM > To: informix-list@iiug.org > Subject: Re: partition buffer summary - Btree percentage > > If your primary key indexes are are manually created so that you have > access to their names you should just be able to disable and reenable them > so that they rebuild themselves in place. If the indexnames are invisible > (ie do not show up separately in dbschema and have weird names in dbaccess) > then yes you will have to drop and readd the constraints. Same for all > constraint indexes. > > Art S. Kagel > > dianne.pendleton@autodesk.com wrote: > > > > Regarding the rebuilding of indexes to help the situation, would I also > have > > to drop and recreate any primary keys that are on the tables from the sql? > > > > Thanks, Dianne <snip> -- -- Vardan Aroustamian vaar@geocities.com Sent via Deja.com http://www.deja.com/ Before you buy.

Sorry, I didn't post full answer. In article <7t0u6q$ssj$1@nnrp1.deja.com>, Vardan Aroustamian <vaar@geocities.com> wrote: > In article <7t0neh$q0$1@news.xmission.com>, > dianne.pendleton@autodesk.com wrote: > > > > Would someone help me with the syntax here. I have been through the > manuals > > and tried it a bunch of ways, but it still isn't working. > > I need to disable the primary key. > > > > Here is the create table: > > > > create table "eadmin".ad_prauth > > ( > > ad_product char(5) not null , > > ad_prodtype char(5) not null , > > ad_platform char(2) not null , > > ad_feature char(5), > > ad_key integer not null , > > primary key (ad_key) constraint "eadmin".ad_prauth > > ); > > > > Thanks, Dianne > Full answer should be: > set constraints for "eadmin".ad_prauth disabled; or set constraints "eadmin".ad_prauth disabled; or you can create table with disabled constraints create table "eadmin".ad_prauth ( ad_product char(5) not null , ad_prodtype char(5) not null , ad_platform char(2) not null , ad_feature char(5), ad_key integer not null , primary key (ad_key) constraint "eadmin".ad_prauth disabled ); Vardan > > Regards, > > Vardan > > > > > -----Original Message----- > > From: Art S. Kagel [mailto:kagel@bloomberg.net] > > Sent: September 30,1999 8:59 AM > > To: informix-list@iiug.org > > Subject: Re: partition buffer summary - Btree percentage > > > > If your primary key indexes are are manually created so that you have > > access to their names you should just be able to disable and reenable > them > > so that they rebuild themselves in place. If the indexnames are > invisible > > (ie do not show up separately in dbschema and have weird names in > dbaccess) > > then yes you will have to drop and readd the constraints. Same for > all > > constraint indexes. > > > > Art S. Kagel > > > > dianne.pendleton@autodesk.com wrote: > > > > > > Regarding the rebuilding of indexes to help the situation, would I > also > > have > > > to drop and recreate any primary keys that are on the tables from > the sql? > > > > > > Thanks, Dianne > > <snip> > > -- > -- > Vardan Aroustamian > vaar@geocities.com > > Sent via Deja.com http://www.deja.com/ > Before you buy. > -- -- Vardan Aroustamian vaar@geocities.com Sent via Deja.com http://www.deja.com/ Before you buy.