Re: Btree Percentage

recycle -- shutdown and restart Informix server. Sorry for the confusion. In article <9138qn$ods$1@nnrp1.deja.com>, Norman Erickson Lugtu <elugtu@my-deja.com> wrote: > First of all what is "recycle"? Try running update statistics. > > In article <9137e6$n97$1@nnrp1.deja.com>, > hanna_shaw@my-deja.com wrote: > > Hi, > > > > We have a performance problem in one of our production box for a long > > time. I found the percentage of Btree in the box is really high. Here > > is the tail part of output of 'onstat -P': > > > > Percentages: > > Data 3.38 > > Btree 95.41 > > Other 1.21 > > > > Output of 'onstat -R': > > ... > > # f/m pair total % of length LOW MED_LOW MED_HIGH HIGH > > 0 f 2477 98.7% 2446 0 229 2010 207 > > 1 m 1.3% 31 0 31 0 0 > > 2 f 2476 98.9% 2450 0 240 2010 200 > > 3 m 1.1% 26 0 26 0 0 > > ... > > > > Most are 'MED_HIGH' instead of 'MED_LOW'. I checked the previous > > related emails in the news group and found some suggestion for this > > problem. But none seems to be a solution of our problem. Our current > > Informix version is 7.31.uc4. 'NOAGE' is already set to 1. > > > > I also found that the performance is pretty good everytime when we > > recycle the Informix server. And then it will be worse and worse until > > we recycle it again.(Now we recycle it once a week. We can't recycle > it > > everyday.) We had a batch job running every night. After recycle, it > > just takes minutes. But before recycle, it even couldn't finish in 24 > > hours and blocks all other jobs. > > > > Does anyone know how to solve the performance problem? > > > > Thanks in advance. > > > > Sent via Deja.com http://www.deja.com/ > > Before you buy. > > > > Sent via Deja.com http://www.deja.com/ > Before you buy. > Sent via Deja.com http://www.deja.com/ Before you buy.

From the great Art Kagel: "Unless you are manually setting many indexes RESIDENT it is normal for Btree to be around 10-20% of buffers. There was a bug in 7.24 which caused all index pages to be marked as index nodes, even leaf pages, which did not affect 7.24 at all. The buggy code was fixed in 7.30, except for the index build code in oncheck (if you have early versions of 7.30 DO NOT LET oncheck build indexes). This misidentification of page types causes 7.3x to classify ALL index pages as MED-HIGH priority (data pages are MEDIUM and index leaves are supposed to be LOW) so that over time 40% or more of your buffers are taken up by index pages starving out data pages and causing buffer thrashing. This is the bug that was referred to. If you have this bug you must drop and recreate ALL indexes created by 7.24 or oncheck." Hope this helps... Zandy In article <913b2o$qep$1@nnrp1.deja.com>, hanna_shaw@my-deja.com wrote: > recycle -- shutdown and restart Informix server. Sorry for the > confusion. > > In article <9138qn$ods$1@nnrp1.deja.com>, > Norman Erickson Lugtu <elugtu@my-deja.com> wrote: > > First of all what is "recycle"? Try running update statistics. > > > > In article <9137e6$n97$1@nnrp1.deja.com>, > > hanna_shaw@my-deja.com wrote: > > > Hi, > > > > > > We have a performance problem in one of our production box for a > long > > > time. I found the percentage of Btree in the box is really high. > Here > > > is the tail part of output of 'onstat -P': > > > > > > Percentages: > > > Data 3.38 > > > Btree 95.41 > > > Other 1.21 > > > > > > Output of 'onstat -R': > > > ... > > > # f/m pair total % of length LOW MED_LOW MED_HIGH HIGH > > > 0 f 2477 98.7% 2446 0 229 2010 207 > > > 1 m 1.3% 31 0 31 0 0 > > > 2 f 2476 98.9% 2450 0 240 2010 200 > > > 3 m 1.1% 26 0 26 0 0 > > > ... > > > > > > Most are 'MED_HIGH' instead of 'MED_LOW'. I checked the previous > > > related emails in the news group and found some suggestion for this > > > problem. But none seems to be a solution of our problem. Our current > > > Informix version is 7.31.uc4. 'NOAGE' is already set to 1. > > > > > > I also found that the performance is pretty good everytime when we > > > recycle the Informix server. And then it will be worse and worse > until > > > we recycle it again.(Now we recycle it once a week. We can't recycle > > it > > > everyday.) We had a batch job running every night. After recycle, it > > > just takes minutes. But before recycle, it even couldn't finish in > 24 > > > hours and blocks all other jobs. > > > > > > Does anyone know how to solve the performance problem? > > > > > > Thanks in advance. > > > > > > Sent via Deja.com http://www.deja.com/ > > > Before you buy. > > > > > > > Sent via Deja.com http://www.deja.com/ > > Before you buy. > > > > Sent via Deja.com http://www.deja.com/ > Before you buy. > Sent via Deja.com http://www.deja.com/ Before you buy.

Thanks for the information. But it looks like that it's not the situation we have. When we first built our production server, the Informix version was 7.30.uc3. Then we upgraded to the current version - - 7.31.uc4. We never used 'oncheck' to rebuild our indexes. We also never set indexes resident. Any information else? In article <913c0v$re2$1@nnrp1.deja.com>, Lyzander Marantal <zandy@my-deja.com> wrote: > From the great Art Kagel: > > "Unless you are manually setting many indexes RESIDENT it is normal for > Btree to be around 10-20% of buffers. There was a bug in 7.24 which > caused all index pages to be marked as index nodes, even leaf pages, > which did not affect 7.24 at all. The buggy code was fixed in 7.30, > except for the index build code in oncheck (if you have early versions > of 7.30 DO NOT LET oncheck build indexes). This misidentification of > page types causes 7.3x to classify ALL index pages as MED-HIGH priority > (data pages are MEDIUM and index leaves are supposed to be LOW) so that > over time 40% or more of your buffers are taken up by index pages > starving out data pages and causing buffer thrashing. This is the bug > that was referred to. If you have this bug you must drop and recreate > ALL indexes created by 7.24 or oncheck." > > Hope this helps... > Zandy > > In article <913b2o$qep$1@nnrp1.deja.com>, > hanna_shaw@my-deja.com wrote: > > recycle -- shutdown and restart Informix server. Sorry for the > > confusion. > > > > In article <9138qn$ods$1@nnrp1.deja.com>, > > Norman Erickson Lugtu <elugtu@my-deja.com> wrote: > > > First of all what is "recycle"? Try running update statistics. > > > > > > In article <9137e6$n97$1@nnrp1.deja.com>, > > > hanna_shaw@my-deja.com wrote: > > > > Hi, > > > > > > > > We have a performance problem in one of our production box for a > > long > > > > time. I found the percentage of Btree in the box is really high. > > Here > > > > is the tail part of output of 'onstat -P': > > > > > > > > Percentages: > > > > Data 3.38 > > > > Btree 95.41 > > > > Other 1.21 > > > > > > > > Output of 'onstat -R': > > > > ... > > > > # f/m pair total % of length LOW MED_LOW MED_HIGH > HIGH > > > > 0 f 2477 98.7% 2446 0 229 2010 > 207 > > > > 1 m 1.3% 31 0 31 > 0 0 > > > > 2 f 2476 98.9% 2450 0 240 2010 > 200 > > > > 3 m 1.1% 26 0 26 > 0 0 > > > > ... > > > > > > > > Most are 'MED_HIGH' instead of 'MED_LOW'. I checked the previous > > > > related emails in the news group and found some suggestion for > this > > > > problem. But none seems to be a solution of our problem. Our > current > > > > Informix version is 7.31.uc4. 'NOAGE' is already set to 1. > > > > > > > > I also found that the performance is pretty good everytime when we > > > > recycle the Informix server. And then it will be worse and worse > > until > > > > we recycle it again.(Now we recycle it once a week. We can't > recycle > > > it > > > > everyday.) We had a batch job running every night. After recycle, > it > > > > just takes minutes. But before recycle, it even couldn't finish in > > 24 > > > > hours and blocks all other jobs. > > > > > > > > Does anyone know how to solve the performance problem? > > > > > > > > Thanks in advance. > > > > > > > > Sent via Deja.com http://www.deja.com/ > > > > Before you buy. > > > > > > > > > > Sent via Deja.com http://www.deja.com/ > > > Before you buy. > > > > > > > Sent via Deja.com http://www.deja.com/ > > Before you buy. > > > > Sent via Deja.com http://www.deja.com/ > Before you buy. > Sent via Deja.com http://www.deja.com/ Before you buy.