fast recovery

Aix 6.1 IDS 11.70.fc3 Had a transaction reach the long transaction HWM after running for about 5 hours...I know .. error in the sql statement to switch the table to raw before doing this mass delete .. anyways ... attempted to onmode -z sessionid .. that resulted in the engine going down ... The system has now been in fast recovery for over 8 hours ... Anyone got any suggestions as to onstats that I can run to make sure it's really doing something, and to get an idea of how much longer... Doesn't seem like much IO is going on .. Thanks for the help .... Peter Logan Database Administrator SpartanNash INC. Office: 616/878-8309 Mobile: 616/304-9672

Try onstat -x That will show where the rollback is and where it has to rollback to in the "begin_logpos" and "current log pos" columns it will also give the "est rb time" I believe it works during fast recovery although I am uncertain if the time estimates will be accurate. George. From: "Peter_Logan@spartanstores.com" <Peter_Logan@spartanstores.com> To: ids@iiug.org, Date: 07/24/2014 06:58 AM Subject: fast recovery [33426] Sent by: ids-bounces@iiug.org Aix 6.1 IDS 11.70.fc3 Had a transaction reach the long transaction HWM after running for about 5 hours...I know .. error in the sql statement to switch the table to raw before doing this mass delete .. anyways ... attempted to onmode -z sessionid .. that resulted in the engine going down ... The system has now been in fast recovery for over 8 hours ... Anyone got any suggestions as to onstats that I can run to make sure it's really doing something, and to get an idea of how much longer... Doesn't seem like much IO is going on .. Thanks for the help .... Peter Logan Database Administrator SpartanNash INC. Office: 616/878-8309 Mobile: 616/304-9672 ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

The only monitoring you can do is to run onstat -D, onstat -g iof, and onstat -u to see IO activity. In the future, besides remembering to alter the table to RAW and inceasing the number of recovery threads in your ONCONFIG file, try using my dbdelete utility for large deletes. It deletes 8192 rows in a transaction and commits. Also, it tends to be faster than just DELETE FROM <table> when there are many rows being deleted using filters on indexed columns. Art Art S. Kagel, Principal Consultant ASK Database Management Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on 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, Jul 24, 2014 at 7:58 AM, Peter_Logan@spartanstores.com < Peter_Logan@spartanstores.com> wrote: > Aix 6.1 > IDS 11.70.fc3 > > Had a transaction reach the long transaction HWM after running for about 5 > hours...I know .. error in the sql statement to switch the table to raw > before doing this mass delete .. anyways ... attempted to onmode -z > sessionid .. that resulted in the engine going down ... > > The system has now been in fast recovery for over 8 hours ... Anyone got > any suggestions as to onstats that I can run to make sure it's really > doing something, and to get an idea of how much longer... Doesn't seem > like much IO is going on .. > > Thanks for the help .... > > Peter Logan > Database Administrator > SpartanNash INC. > Office: 616/878-8309 > Mobile: 616/304-9672 > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001a11349adc69916304feeff8c9

Thanks Art .. Do you have any recommendations for the number of recovery threads? Peter Logan Database Administrator SpartanNash INC. Office: 616/878-8309 Mobile: 616/304-9672 From: "Art Kagel" <art.kagel@gmail.com> To: ids@iiug.org Date: 07/24/2014 08:56 AM Subject: Re: fast recovery [33429] Sent by: ids-bounces@iiug.org The only monitoring you can do is to run onstat -D, onstat -g iof, and onstat -u to see IO activity. In the future, besides remembering to alter the table to RAW and inceasing the number of recovery threads in your ONCONFIG file, try using my dbdelete utility for large deletes. It deletes 8192 rows in a transaction and commits. Also, it tends to be faster than just DELETE FROM <table> when there are many rows being deleted using filters on indexed columns. Art Art S. Kagel, Principal Consultant ASK Database Management Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on 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, Jul 24, 2014 at 7:58 AM, Peter_Logan@spartanstores.com < Peter_Logan@spartanstores.com> wrote: > Aix 6.1 > IDS 11.70.fc3 > > Had a transaction reach the long transaction HWM after running for about 5 > hours...I know .. error in the sql statement to switch the table to raw > before doing this mass delete .. anyways ... attempted to onmode -z > sessionid .. that resulted in the engine going down ... > > The system has now been in fast recovery for over 8 hours ... Anyone got > any suggestions as to onstats that I can run to make sure it's really > doing something, and to get an idea of how much longer... Doesn't seem > like much IO is going on .. > > Thanks for the help .... > > Peter Logan > Database Administrator > SpartanNash INC. > Office: 616/878-8309 > Mobile: 616/304-9672 > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001a11349adc69916304feeff8c9 ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

To help prevent this in the future you can also consider lowering you LTXHWM to trigger the rollback sooner. This works if you have a system like mine that should usually not have a transaction that runs over 5 minutes and if there is a big transaction like this it needs to be run during a maintenance window and I can adjust the LTXHWM setting up before hand to allow it. LTXHWM is usually configured way too high IMO. If you know a transaction that spans 10% of your logical logs indicates some malformed SQL then why not adjust LTXHWM down to 10 from the crazy high default setting to avoid 8 hour rollbacks? Doesn't really help with your long fast recovery problem, but can help prevent it in the future. You could try support, I believe they can truncate your logical logs to right before your long transaction starts, but that has the unfortunate side effect of losing all of the work that was done after the longtx started. If you have a way to redo this work and you need the system back ASAP that could be an option. Like Art said you can watch onstat -r 1 -D and onstat -r 1 -g act to see if the engine is actually doing something. If it is then you should eventually get out of fast recovery. Andrew -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Art Kagel Sent: Thursday, July 24, 2014 7:56 AM To: ids@iiug.org Subject: Re: fast recovery [33429] The only monitoring you can do is to run onstat -D, onstat -g iof, and onstat -u to see IO activity. In the future, besides remembering to alter the table to RAW and inceasing the number of recovery threads in your ONCONFIG file, try using my dbdelete utility for large deletes. It deletes 8192 rows in a transaction and commits. Also, it tends to be faster than just DELETE FROM <table> when there are many rows being deleted using filters on indexed columns. Art Art S. Kagel, Principal Consultant ASK Database Management Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on 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, Jul 24, 2014 at 7:58 AM, Peter_Logan@spartanstores.com < Peter_Logan@spartanstores.com> wrote: > Aix 6.1 > IDS 11.70.fc3 > > Had a transaction reach the long transaction HWM after running for > about 5 hours...I know .. error in the sql statement to switch the > table to raw before doing this mass delete .. anyways ... attempted to > onmode -z sessionid .. that resulted in the engine going down ...> > The system has now been in fast recovery for over 8 hours ... Anyone > got any suggestions as to onstats that I can run to make sure it's > really doing something, and to get an idea of how much longer... > Doesn't seem like much IO is going on .. > > Thanks for the help .... > > Peter Logan > Database Administrator > SpartanNash INC. > Office: 616/878-8309 > Mobile: 616/304-9672 > > > > **************************************************************************** *** > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001a11349adc69916304feeff8c9 **************************************************************************** *** Forum Note: Use "Reply" to post a response in the discussion forum.

I vaguely remember Madison Pruet suggesting at least 4 threads per CPU core (or maybe CPU VP - don't remember and haven't found my notes) when I asked him once. Art Art S. Kagel, Principal Consultant ASK Database Management Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on 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, Jul 24, 2014 at 9:17 AM, Peter_Logan@spartanstores.com < Peter_Logan@spartanstores.com> wrote: > Thanks Art .. > > Do you have any recommendations for the number of recovery threads? > > Peter Logan > Database Administrator > SpartanNash INC. > Office: 616/878-8309 > Mobile: 616/304-9672 > > From: "Art Kagel" <art.kagel@gmail.com> > To: ids@iiug.org > Date: 07/24/2014 08:56 AM > Subject: Re: fast recovery [33429] > Sent by: ids-bounces@iiug.org > > The only monitoring you can do is to run onstat -D, onstat -g iof, and > onstat -u to see IO activity.> > In the future, besides remembering to alter the table to RAW and inceasing > > the number of recovery threads in your ONCONFIG file, try using my > dbdelete > utility for large deletes. It deletes 8192 rows in a transaction and > commits. Also, it tends to be faster than just DELETE FROM <table> when > there are many rows being deleted using filters on indexed columns. > > Art > > Art S. Kagel, Principal Consultant > ASK Database Management > > Blog: http://informix-myview.blogspot.com/ > > Disclaimer: Please keep in mind that my own opinions are my own opinions > and do not reflect on 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, Jul 24, 2014 at 7:58 AM, Peter_Logan@spartanstores.com < > Peter_Logan@spartanstores.com> wrote: > > > Aix 6.1 > > IDS 11.70.fc3 > > > > Had a transaction reach the long transaction HWM after running for about > 5 > > hours...I know .. error in the sql statement to switch the table to raw > > before doing this mass delete .. anyways ... attempted to onmode -z > > sessionid .. that resulted in the engine going down ... > > > > The system has now been in fast recovery for over 8 hours ... Anyone got > > > any suggestions as to onstats that I can run to make sure it's really > > doing something, and to get an idea of how much longer... Doesn't seem > > like much IO is going on .. > > > > Thanks for the help .... > > > > Peter Logan > > Database Administrator > > SpartanNash INC. > > Office: 616/878-8309 > > Mobile: 616/304-9672 > > > > > > > > > > > ******************************************************************************* > > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > --001a11349adc69916304feeff8c9 > > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001a11348d4edf443d04fefa069b