IDS Forum

Re: Performance v11.70.fc3

Posted By: Art Kagel
Date: Thursday, 22 September 2011, at 9:18 a.m.

In Response To: Re: Performance v11.70.fc3 (Peter_Logan@spartanstores.com)

Oh, sure, do something completely weird and unconventional like reading the
onconfig.std file! My problem was I did the obvious all these months and
just read the MFM which only says:

default value
1
values 0 = Disable automatic read-ahead requests.
1 = Enable automatic read-ahead requests in the standard mode. The
database server will automatically process read-ahead requests only when
a query waits on I/O.
2 = Enable automatic read-ahead requests in the aggressive mode. The
database server will automatically process read-ahead requests at the start
of the query and continuously through the duration of the query.

Thanks. Now to open a documentation case with IBM! Well, I'll sneak it
into the xC4 Beta reporting because it is still not documented in the .xC4
docs!

Art

Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
Blog: http://informix-myview.blogspot.com/

Disclaimer: Please keep in mind that my own opinions are my own opinions and
do not reflect on my employer, Advanced DataTools, the IIUG, nor any other
organization with which I am associated either explicitly, implicitly, or by
inference. Neither do those opinions reflect those of other individuals
affiliated with any entity with which I am affiliated nor those of the
entities themselves.

On Thu, Sep 22, 2011 at 8:57 AM, Peter_Logan@spartanstores.com <
Peter_Logan@spartanstores.com> wrote:

> Art,
>
> Here is the onconfig.std part that talks about this. v11.70.fc3
>
> #
> # RA_PAGES & RA_THRESHOLD have been replaced with AUTO_READAHEAD.
> #
> # AUTO_READAHEAD mode[,readahead_cnt]
> # mode 0 = Disable (Not recommended)
> # 1 = Passive (Default)
> # 2 = Aggressive (Not recommended)
> # readahead_cnt Optional Range 4-4096
> # readahead_cnt allows for tuning the # of
> # pages that automatic readahead will request
> # to be read ahead. When not set, the default
> # is 128 pages.
> #
> # Notes:
> # The threshold for starting the next readahead request, which
> # use to be known as RA_THRESHOLD is always set to 1/2 of the
> # readahead_cnt. RA_THRESHOLD is deprecated and no longer used.
> #
> # If RA_PAGES & AUTO_READAHEAD are not present in the ONCONFIG file,
> # Informix will default to using an AUTO_READAHEAD setting of 1.
> #
> # If RA_PAGES is present in the ONCONFIG file and AUTO_READAHEAD is
> # not, Informix will set AUTO_READAHEAD to 1,RA_PAGES (passive mode
> # with a readahead_cnt=RA_PAGES).
> #
>
> Peter Logan
> Senior Database Administrator
> Phone: 616/878-8309
>
> From: "Art Kagel" <art.kagel@gmail.com>
> To: ids@iiug.org
> Date: 09/22/2011 08:49 AM
> Subject: Re: Performance v11.70.fc3 [25004]
> Sent by: ids-bounces@iiug.org
>
> Whoa! The second part of AUTO_READAHEAD is not documented! Peter, do you
> have any documentation on that one? John? Scott? Anyone?
>
> You all know I think that readahead is mostly unnecessary today, so if
> this
> is actually tunable, that would be great! I've been fearful that this new
> readahead algorithm is all or nothing.
>
> Art
>
> Art S. Kagel
> Advanced DataTools (www.advancedatatools.com)
> Blog: http://informix-myview.blogspot.com/
>
> Disclaimer: Please keep in mind that my own opinions are my own opinions
> and
> do not reflect on my employer, Advanced DataTools, the IIUG, nor any other
>
> organization with which I am associated either explicitly, implicitly, or
> by
> inference. Neither do those opinions reflect those of other individuals
> affiliated with any entity with which I am affiliated nor those of the
> entities themselves.
>
> On Thu, Sep 22, 2011 at 7:57 AM, Peter_Logan@spartanstores.com <
> Peter_Logan@spartanstores.com> wrote:
>
> > Art is actually correct, if you disable the new feature and go with the
> > old parameter of RA_PAGES, then the engine will use half of that as the
> > threshold ... in our case though .. our performance suffered with this..
>
> > One thing you might want to do is play with the READAHEAD_CNT portion of
>
> > the AUTO_READHEAD parameter ... ie.
> > AUTO_READAHEAD 1,256
> >
> > That sets the number of pages being read ahead. With moving this number
> > up to 256 and 512 I am seeing much better performance .. not as good as
> > before, but way better in my case than taking the default. The box I'm
> > doing this previously had RA_PAGES set to 1024 ...
> >
> > Peter Logan
> > Senior Database Administrator
> > Phone: 616/878-8309
> >
> > From: "Dan Mueller" <Dan.Mueller@trnswrks.com>
> > To: ids@iiug.org
> > Date: 09/22/2011 07:27 AM
> > Subject: RE: Performance v11.70.fc3 [24998]
> > Sent by: ids-bounces@iiug.org
> >
> > According to IBM support just yesterday, RA_PAGES & RA_THRESHHOLS are no
>
> > longer looked by the engine in 11.7.
> >
> > -----Original Message-----
> > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
> Art
> > Kagel
> > Sent: Wednesday, September 21, 2011 6:40 PM
> > To: ids@iiug.org
> > Subject: Re: Performance v11.70.fc3 [24988]
> >
> > Try disabling the new automatic readahead if you have not already and
> add
> > back
> > the older RA_ parameters.
> >
> > Art
> >
> > Art S. Kagel
> > Advanced DataTools (www.advancedatatools.com)
> > Blog: http://informix-myview.blogspot.com/
> >
> > Disclaimer: Please keep in mind that my own opinions are my own opinions
>
> > and
> > do not reflect on my employer, Advanced DataTools, the IIUG, nor any
> other
> >
> > organization with which I am associated either explicitly, implicitly,
> or
> > by
> > inference. Neither do those opinions reflect those of other individuals
> > affiliated with any entity with which I am affiliated nor those of the
> > entities themselves.
> >
> > On Wed, Sep 21, 2011 at 3:36 PM, Peter_Logan@spartanstores.com <
> > Peter_Logan@spartanstores.com> wrote:
> >
> > > IDS: v11.70.fc3x3
> > > OS: Aix 6.1
> > >
> > > We have been running on the above configuration for about a month now.
>
> > > We have had several issues relating to the automatic read ahead, hence
>
> > > the X3 patch. We are also having significant performance issues
> > > especially when working with temp tables. We have a large number of
> > > Micro Strategies reports that build numerous temp tables and then join
>
> > them
> > all at the end.
> > > The time on a large number of these has increased dramatically with
> > > the new release. Prior we were on v11.50.fc7. I moved this database to
>
> > > v11.70.fc2 and things performed as they did in 11.50. I'm guessing
> > > that the issue may have to do with changes made to temp tables and
> > > such. Some of these queries have gone from 70 seconds to 13 minutes.
> > > Not the right direction! Anyway, I'm just asking generally if those
> > > who have moved to this release have seen any similar issues, and if
> > > so, were you able to get things working properly. Stats have been
> > > updated and such so it's not that sort of thing. I have IBM looking
> > > into it, but also wanted to get input from the user community.
> > >
> > > Thanks for any and all responses ...
> > >
> > > Peter Logan
> > > Senior Database Administrator
> > > Phone: 616/878-8309
> > >
> > >
> > >
> > >
> >
> >
> >
> >
>
>
> *******************************************************************************
>
> >
> > > Forum Note: Use "Reply" to post a response in the discussion forum.
> > >
> > >
> >
> > --90e6ba6e8bea0dd12404ad7b4006
> >
> >
> >
> >
>
>
> *******************************************************************************
>
> >
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
> >
> >
> >
>
>
> *******************************************************************************
>
> >
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
> >
> >
> >
>
>
> *******************************************************************************
>
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
> >
>
> --90e6ba6e8bea6146da04ad870f98
>
>
>
> *******************************************************************************
>
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
>
> *******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>

--20cf303bfbc49708fa04ad8786c8

Messages In This Thread

• Performance v11.70.fc3
  Peter_Logan@spartanstores.com -- Wednesday, 21 September 2011, at 3:36 p.m.
  • Re: Performance v11.70.fc3
    Jack Parker -- Wednesday, 21 September 2011, at 3:56 p.m.
    • RE: Performance v11.70.fc3
      Dan Mueller -- Thursday, 22 September 2011, at 7:21 a.m.
      • RE: Performance v11.70.fc3
        Peter_Logan@spartanstores.com -- Thursday, 22 September 2011, at 7:52 a.m.
  • Re: Performance v11.70.fc3
    Fernando Nunes -- Wednesday, 21 September 2011, at 5:39 p.m.
  • Re: Performance v11.70.fc3
    Art Kagel -- Wednesday, 21 September 2011, at 6:39 p.m.
    • Re: Performance v11.70.fc3
      Peter_Logan@spartanstores.com -- Wednesday, 21 September 2011, at 6:45 p.m.
    • RE: Performance v11.70.fc3
      Dan Mueller -- Thursday, 22 September 2011, at 7:23 a.m.
      • RE: Performance v11.70.fc3
        Peter_Logan@spartanstores.com -- Thursday, 22 September 2011, at 7:57 a.m.
        • Re: Performance v11.70.fc3
          Art Kagel -- Thursday, 22 September 2011, at 8:45 a.m.
          • Re: Performance v11.70.fc3
            Peter_Logan@spartanstores.com -- Thursday, 22 September 2011, at 8:57 a.m.
            • Re: Performance v11.70.fc3
              Art Kagel -- Thursday, 22 September 2011, at 9:18 a.m.
      • Re: Performance v11.70.fc3
        Art Kagel -- Thursday, 22 September 2011, at 8:40 a.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.