Informix 7.31.FC7 Performance Problems

We have IDS 7.31.FC7 running on an HP N-class server. Performance degrades over time after a restart. Also, over time the ratio of Btree to Data goes up, up, up. The database was built from scratch on "IDS 7.31.FC2" and was recently upgraded to FC7 in an attempt to solve this problem but this didn't seem to help at all. Rebooting reguarly is difficult due to the fact we have many long running "batch-like" programs. The next thing we will try is to set "LRUAGE=1" (and restart) and I'm hopeful about this setting. We also have more physical memory on its way (2GB to make it a total of 7GB) which will allow a significant increase in BUFFERS from the current setting of 200000 (400MB). After reading recent (and not so recent) posts on this "bug" (large number of btree buffers set to MED_HIGH and HIGH) in 7.3, I'm a little confused as the conclusion seemed to be that this only happens on databases that have been upgraded from previous versions or could it be that it is just worse in the upgrade scenario? Is it also possible that the (new?) buffer prioritization scheme just doesn't work well in certain scenarios? The application in this case is medium sized Baan system. Any advice would be greatly appreciated as this is really hurting us and let me know if any other information is needed. Chris Pulenzas. chrisp@disus.com Here are some stats from the system: $ onstat -p Informix Dynamic Server Version 7.31.FC7 -- On-Line -- Up 9 days 22:47:17 -- 1909512 Kbytes Profile dskreads pagreads bufreads %cached dskwrits pagwrits bufwrits %cached 421423131 101115920 17217612901 97.55 44640545 24728196 162330465 72.50 isamtot open start read write rewrite delete commit rollbk 16652850681 11205042 531843657 13065751046 21319666 46881957 5288104 8179165 864 gp_read gp_write gp_rewrt gp_del gp_alloc gp_free gp_curs 0 0 0 0 0 0 0 ovlock ovuserthread ovbuff usercpu syscpu numckpts flushes 0 0 0 468394.41 258853.89 2718 5436 bufwaits lokwaits lockreqs deadlks dltouts ckpwaits compress seqscans 82001182 1480 1144763245 2 0 11717 2140003 410317 ixda-RA idx-RA da-RA RA-pgsused lchwaits 182854983 6843081 234065 176815765 39838226 $ onstat -P | tail 24117380 11 11 0 0 0 0 24117381 162 162 0 0 0 0 Totals: 200000 192737 4365 2898 0 2957 Percentages: Data 2.18 Btree 96.37 Other 1.45 $ onstat -R | head Informix Dynamic Server Version 7.31.FC7 -- On-Line -- Up 9 days 22:25:13 -- 1909512 Kbytes 32 buffer LRU queue pairs priority levels # f/m pair total % of length LOW MED_LOW MED_HIGH HIGH 0 f 6229 98.9% 6163 0 163 5119 881 1 m 1.1% 66 0 66 0 0 2 f 6245 98.9% 6179 0 180 5125 874 3 m 1.1% 66 0 64 2 0 4 f 6218 99.0% 6155 0 140 5153 862

The last of the bugs related to the index node misidentification was not eradicated until ?C6X4 (and ?C7) so your indexes were likely created with the wrong page flags or they became bad overtime as nodes split and the two new nodes were mismarked (that last bug). Rebuild all indexes and the problem goes away. Until you can do that you have to turn buffer prioritization off. Set: NOLRUPRIO=1 in the environment and restart. Art S. Kagel disus wrote: > > We have IDS 7.31.FC7 running on an HP N-class server. Performance degrades > over time after a restart. Also, over time the ratio of Btree to Data goes > up, up, up. The database was built from scratch on "IDS 7.31.FC2" and was > recently upgraded to FC7 in an attempt to solve this problem but this didn't > seem to help at all. Rebooting reguarly is difficult due to the fact we > have many long running "batch-like" programs. > > The next thing we will try is to set "LRUAGE=1" (and restart) and I'm > hopeful about this setting. We also have more physical memory on its way > (2GB to make it a total of 7GB) which will allow a significant increase in > BUFFERS from the current setting of 200000 (400MB). > > After reading recent (and not so recent) posts on this "bug" (large number > of btree buffers set to MED_HIGH and HIGH) in 7.3, I'm a little confused as > the conclusion seemed to be that this only happens on databases that have > been upgraded from previous versions or could it be that it is just worse in > the upgrade scenario? Is it also possible that the (new?) buffer > prioritization scheme just doesn't work well in certain scenarios? The > application in this case is medium sized Baan system. > > Any advice would be greatly appreciated as this is really hurting us and let > me know if any other information is needed. > > Chris Pulenzas. > chrisp@disus.com > > Here are some stats from the system: > > $ onstat -p> > Informix Dynamic Server Version 7.31.FC7 -- On-Line -- Up 9 days > 22:47:17 -- 1909512 Kbytes > > Profile > dskreads pagreads bufreads %cached dskwrits pagwrits bufwrits %cached > 421423131 101115920 17217612901 97.55 44640545 24728196 162330465 72.50 > > isamtot open start read write rewrite delete commit > rollbk > 16652850681 11205042 531843657 13065751046 21319666 46881957 5288104 > 8179165 864 > > gp_read gp_write gp_rewrt gp_del gp_alloc gp_free gp_curs > 0 0 0 0 0 0 0 > > ovlock ovuserthread ovbuff usercpu syscpu numckpts flushes > 0 0 0 468394.41 258853.89 2718 5436 > > bufwaits lokwaits lockreqs deadlks dltouts ckpwaits compress seqscans > 82001182 1480 1144763245 2 0 11717 2140003 410317 > > ixda-RA idx-RA da-RA RA-pgsused lchwaits > 182854983 6843081 234065 176815765 39838226 > > $ onstat -P | tail > 24117380 11 11 0 0 0 0 > 24117381 162 162 0 0 0 0> > Totals: 200000 192737 4365 2898 0 2957 > > Percentages: > Data 2.18 > Btree 96.37 > Other 1.45 > > $ onstat -R | head> > Informix Dynamic Server Version 7.31.FC7 -- On-Line -- Up 9 days > 22:25:13 -- 1909512 Kbytes > > 32 buffer LRU queue pairs priority levels > # f/m pair total % of length LOW MED_LOW MED_HIGH HIGH > 0 f 6229 98.9% 6163 0 163 5119 881 > 1 m 1.1% 66 0 66 0 0 > 2 f 6245 98.9% 6179 0 180 5125 874 > 3 m 1.1% 66 0 64 2 0 > 4 f 6218 99.0% 6155 0 140 5153 862