RE: Performance v11.70.fc3

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.

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

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.

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. > > > >