RE: Informix 7.31.FC7 Performance Problems
The solution to "rebuild all indexes" is not good news for SAP sites
with 22,000 tables and 26,000 indexes in each Informix instance.
-----Original Message-----
From: Art S. Kagel [mailto:kagel@bloomberg.net]
Sent: Wednesday, December 20, 2000 15:49
To: informix-list@iiug.org
Subject: Re: Informix 7.31.FC7 Performance Problems
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
"Bernstein, Rick" wrote:
>
> The solution to "rebuild all indexes" is not good news for SAP sites
> with 22,000 tables and 26,000 indexes in each Informix instance.
Hey, nor for PeopleSoft nor for my P&L history database with 600GB of data
to reindex in 120 tables with multiple indexes on each. NOLRUPRIO does the
trick making the LRUS behave the same as they did in 7.30.
Art S. Kagel
> -----Original Message-----
> From: Art S. Kagel [mailto:kagel@bloomberg.net]
> Sent: Wednesday, December 20, 2000 15:49
> To: informix-list@iiug.org
> Subject: Re: Informix 7.31.FC7 Performance Problems
>
> 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
We run 7.31UC6 here, and had better luck set LRUAGE=1 that NOLRUPRIO.
The NOLRUPRIO seemed to cause us to have excessive foreground writes.
--
Jay Aymond
Community Coffee
Art S. Kagel <kagel@bloomberg.net> wrote in message
news:3A4266B5.477FBBE2@bloomberg.net...
> "Bernstein, Rick" wrote:
> >
> > The solution to "rebuild all indexes" is not good news for SAP sites
> > with 22,000 tables and 26,000 indexes in each Informix instance.
>
> Hey, nor for PeopleSoft nor for my P&L history database with 600GB of data
> to reindex in 120 tables with multiple indexes on each. NOLRUPRIO does
the
> trick making the LRUS behave the same as they did in 7.30.
>
> Art S. Kagel
>
> > -----Original Message-----
> > From: Art S. Kagel [mailto:kagel@bloomberg.net]
> > Sent: Wednesday, December 20, 2000 15:49
> > To: informix-list@iiug.org
> > Subject: Re: Informix 7.31.FC7 Performance Problems
> >
> > 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