Performance v11.70.fc3
After upgrading from IDS 11.50.FC7 to 11.70.FC3 on AIX 6.1, Peter Logan saw big slowdowns (70s to 13 minutes) in MicroStrategy reports that build and join many temp tables; 11.70.FC2 had performed fine. Suggestions included checking update statistics and comparing query plans, and disabling the new AUTO_READAHEAD (though another poster noted RA_PAGES/RA_THRESHOLD are ignored in 11.70). Peter had already disabled it and got an IBM patch for the read-ahead problem (others hit APAR IC78269), but the temp-table slowdown was still open, suspected to be an optimizer defect, with a test case sent to IBM — no resolution recorded.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
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
Peter, We are in the process of upgrading our software from 10.TC10 to = 11.70FC3. =46rom Windows 2003 to 2008, from 32bit to 64 and on new = hardware. I'm in the middle of migration efforts. To date I have been = very impressed with performance, but with that much changing it's hard = to point a finger at where the benefit is actually coming from. We have not started doing anything with TEMP tables yet, so I can't = comment. cheers j. On Sep 21, 2011, at 3:36 PM, Peter_Logan@spartanstores.com wrote: > IDS: v11.70.fc3x3=20 > OS: Aix 6.1=20 >=20 > We have been running on the above configuration for about a month now. = We=20 > have had several issues relating to the automatic read ahead, hence = the X3=20 > patch. We are also having significant performance issues especially = when=20 > working with temp tables. We have a large number of Micro Strategies=20= > reports that build numerous temp tables and then join them all at the = end.=20 > The time on a large number of these has increased dramatically with = the=20 > new release. Prior we were on v11.50.fc7. I moved this database to=20 > v11.70.fc2 and things performed as they did in 11.50. I'm guessing = that=20 > the issue may have to do with changes made to temp tables and such. = Some=20 > of these queries have gone from 70 seconds to 13 minutes. Not the = right=20 > direction! Anyway, I'm just asking generally if those who have moved = to=20 > this release have seen any similar issues, and if so, were you able to = get=20 > things working properly. Stats have been updated and such so it's not=20= > that sort of thing. I have IBM looking into it, but also wanted to get=20= > input from the user community.=20 >=20 > Thanks for any and all responses ...=20 >=20 > Peter Logan=20 > Senior Database Administrator=20 > Phone: 616/878-8309=20 >=20 >=20 > = **************************************************************************= *****=20 > Forum Note: Use "Reply" to post a response in the discussion forum.=20= >=20
I've upgraded a customer from 10 to 11.70.FC3 around 1 month ago. We had two performance issues but none could be mapped to an engine problem: 1- lack of update statistics 2- a query plan inside a procedure used an index on a date column when another index was much better. The catch: The query uses variables and these are not available at proc plan calculation time It would help to compare the query plans. Did you provide them to IBM? I don't recall seeing anything that matches your description.... Regards. On Wed, Sep 21, 2011 at 8: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. > > -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently... --0016e64805dc3f9cc404ad7a67ea
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
Did that .. In working with IBM we may have encountered a defect with the optimizer .. sending them a test case ... Peter Logan Senior Database Administrator Phone: 616/878-8309 From: "Art Kagel" <art.kagel@gmail.com> To: ids@iiug.org Date: 09/21/2011 06:40 PM Subject: Re: Performance v11.70.fc3 [24988] Sent by: ids-bounces@iiug.org 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.
We upgraded to 11.70. FC3 and immediately ran into APAR IC78269. This forces us to turn off AUTO_READAHEAD which has killed the performance on anything using read ahead. We are currently waiting on a patch for that APAR however are considering reverting to 11.50.FC5 in the meantime. -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Jack Parker Sent: Wednesday, September 21, 2011 3:56 PM To: ids@iiug.org X5=EBVSu Subject: Re: Performance v11.70.fc3 [24985] Peter, We are in the process of upgrading our software from 10.TC10 to = 11.70FC3. =46rom Windows 2003 to 2008, from 32bit to 64 and on new = hardware. I'm in the middle of migration efforts. To date I have been = very impressed with performance, but with that much changing it's hard = to point a finger at where the benefit is actually coming from. We have not started doing anything with TEMP tables yet, so I can't = comment. cheers j. On Sep 21, 2011, at 3:36 PM, Peter_Logan@spartanstores.com wrote: > IDS: v11.70.fc3x3=20 > OS: Aix 6.1=20 >=20 > We have been running on the above configuration for about a month now. >= We=20 > have had several issues relating to the automatic read ahead, hence = the X3=20 > patch. We are also having significant performance issues especially = when=20 > working with temp tables. We have a large number of Micro > Strategies=20= > reports that build numerous temp tables and then join them all at the > = end.=20 > The time on a large number of these has increased dramatically with = the=20 > new release. Prior we were on v11.50.fc7. I moved this database to=20 > v11.70.fc2 and things performed as they did in 11.50. I'm guessing = that=20 > the issue may have to do with changes made to temp tables and such. = Some=20 > of these queries have gone from 70 seconds to 13 minutes. Not the = right=20 > direction! Anyway, I'm just asking generally if those who have moved = to=20 > this release have seen any similar issues, and if so, were you able to > = get=20 > things working properly. Stats have been updated and such so it's > not=20= > that sort of thing. I have IBM looking into it, but also wanted to > get=20= > input from the user community.=20 >=20 > Thanks for any and all responses ...=20 >=20 > Peter Logan=20 > Senior Database Administrator=20 > Phone: 616/878-8309=20 >=20 >=20 > = **************************************************************************= *****=20 > Forum Note: Use "Reply" to post a response in the discussion forum.=20= >=20 ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.
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.
I also considered reverting, but we have a bunch of partitioned tables which had new fragments added to them, so I would have had to drop and recreate them cause it wouldn't revert them...way too much work. IBM got us a patch that fixed our RA issue ... 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:25 AM Subject: RE: Performance v11.70.fc3 [24997] Sent by: ids-bounces@iiug.org We upgraded to 11.70. FC3 and immediately ran into APAR IC78269. This forces us to turn off AUTO_READAHEAD which has killed the performance on anything using read ahead. We are currently waiting on a patch for that APAR however are considering reverting to 11.50.FC5 in the meantime. -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Jack Parker Sent: Wednesday, September 21, 2011 3:56 PM To: ids@iiug.org X5=EBVSu Subject: Re: Performance v11.70.fc3 [24985] Peter, We are in the process of upgrading our software from 10.TC10 to = 11.70FC3. =46rom Windows 2003 to 2008, from 32bit to 64 and on new = hardware. I'm in the middle of migration efforts. To date I have been = very impressed with performance, but with that much changing it's hard = to point a finger at where the benefit is actually coming from. We have not started doing anything with TEMP tables yet, so I can't = comment. cheers j. On Sep 21, 2011, at 3:36 PM, Peter_Logan@spartanstores.com wrote: > IDS: v11.70.fc3x3=20 > OS: Aix 6.1=20 >=20 > We have been running on the above configuration for about a month now. >= We=20 > have had several issues relating to the automatic read ahead, hence = the X3=20 > patch. We are also having significant performance issues especially = when=20 > working with temp tables. We have a large number of Micro > Strategies=20= > reports that build numerous temp tables and then join them all at the > = end.=20 > The time on a large number of these has increased dramatically with = the=20 > new release. Prior we were on v11.50.fc7. I moved this database to=20 > v11.70.fc2 and things performed as they did in 11.50. I'm guessing = that=20 > the issue may have to do with changes made to temp tables and such. = Some=20 > of these queries have gone from 70 seconds to 13 minutes. Not the = right=20 > direction! Anyway, I'm just asking generally if those who have moved = to=20 > this release have seen any similar issues, and if so, were you able to > = get=20 > things working properly. Stats have been updated and such so it's > not=20= > that sort of thing. I have IBM looking into it, but also wanted to > get=20= > input from the user community.=20 >=20 > Thanks for any and all responses ...=20 >=20 > Peter Logan=20 > Senior Database Administrator=20 > Phone: 616/878-8309=20 >=20 >=20 > = **************************************************************************= *****=20 > Forum Note: Use "Reply" to post a response in the discussion forum.=20= >=20 ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum. ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.