Need enlightenment regarding long transactions

Can someone enlighten me on long transactions? This is something completely new to me as I've only been with Informix for 2 years. Any information or examples would prove helpful. Thanks.

If there are open transactions in a logical log then it cannot be reused/overwritten. This means that if there are many long running open transactions, or one or more very large transactions that span many logical log files, there will come a point at which the engine can no longer log transactions to the logical logs because the logs (even if they have been backed up) cannot be reused due to open transaction data. Since a long running transaction can cause many other transactions to encounter the same problem, when the engine fills a percentage of the logs equivalent to the LTXHWM ONCONFIG setting it will block new transactions from starting in the hope the one or more existing transactions will complete freeing up log space. If space does no free up before the LTXEHWM is reached then the oldest transactions (ie the one that begins earliest in the existing logical logs) is forced to rollback in order to free up log space for other transactions. Art Art S. Kagel Oninit (www.oninit.com) IIUG Board of Directors (art@iiug.org) Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Oninit, the IIUG, nor any other organization with which I am associated either explicitly or implicitly. 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 Tue, Oct 20, 2009 at 11:11 AM, Jeremiah Leong <yogensha4ever@gmail.com>wrote: > Can someone enlighten me on long transactions? This is something > completely new to me as I've only been with Informix for 2 years. Any > information or examples would prove helpful. Thanks. > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list >

Art Kagel wrote: > If there are open transactions in a logical log then it cannot be > reused/overwritten. This means that if there are many long running open > transactions, or one or more very large transactions that span many > logical log files, there will come a point at which the engine can no > longer log transactions to the logical logs because the logs (even if > they have been backed up) cannot be reused due to open transaction > data. Since a long running transaction can cause many other > transactions to encounter the same problem, when the engine fills a > percentage of the logs equivalent to the LTXHWM ONCONFIG setting it will > block new transactions from starting in the hope the one or more > existing transactions will complete freeing up log space. If space does > no free up before the LTXEHWM is reached then the oldest transactions > (ie the one that begins earliest in the existing logical logs) is forced > to rollback in order to free up log space for other transactions. > > Art > > Art S. Kagel > Oninit (www.oninit.com <http://www.oninit.com>) > IIUG Board of Directors (art@iiug.org <mailto:art@iiug.org>) > > Disclaimer: Please keep in mind that my own opinions are my own opinions > and do not reflect on my employer, Oninit, the IIUG, nor any other > organization with which I am associated either explicitly or implicitly. > 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 Tue, Oct 20, 2009 at 11:11 AM, Jeremiah Leong > <yogensha4ever@gmail.com <mailto:yogensha4ever@gmail.com>> wrote: > > Can someone enlighten me on long transactions? This is something > completely new to me as I've only been with Informix for 2 years. Any > information or examples would prove helpful. Thanks. > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org <mailto:Informix-list@iiug.org> > http://www.iiug.org/mailman/listinfo/informix-list > > Hi... I didn't quite understand the explanation about LTXEHWM. This is the acronym for Long Transaction Exclusive High Water Mark. If a transaction that was considered long transaction at the point of LTXHWM, is still rolling back at the point defined by LTXEHWM, than all engine transactions will get blocked, leaving the remaining logical log space for the Long TX rollback. This is something you need to avoid because it will cause severe unavailability. In older versions, if the rest (above LTXEHWM) was not enough to complete the long tx rollback than your engine would need technical support intervention. You should calculate the space needed for you longest transactions, and make sure the space represented by LTXHWM can accomodate that. I usually setup LTXEHWM with about 100 - LTXHWM. This should mean that even if the percentage represented by LTXHWM is completely filled by the long tx, than if the engine reached LTXEHWM than it would have sufficient logical log space to complete the rollback. But in current versions you can add more logical logs (or even configure the engine to do so automatically) In version 11.50 you can change these two parameters dinamically using onmode -wm/-wf. This can be a great feature if you want to have certainvalues for your usual activities, but need to do something unusual. In the future, I would like to see more session controlable values... Regards.

> Can someone enlighten me on long transactions? This is something > completely new to me as I've only been with Informix for 2 years. Any > information or examples would prove helpful. Thanks. Have you actually suffered from some? The default settings in the config file are notoriously small.

Warning: This goes waaaay beyond the scope of Jeremiah's question. Much more enlightenment than originally requested. Andrew Clarke wrote: >> Jeremiah's post: >> Can someone enlighten me on long transactions? This is something >> completely new to me as I've only been with Informix for 2 years. Any >> information or examples would prove helpful. Thanks. > > Have you actually suffered from some? The default settings in the > config file are notoriously small. > Andrew, Interesting you should say that. I am accustomed to finding the default settings at LTXHWM=70, LTXEHWM=80. If you have several instances of a rogue application, this is woefully high and is still likely to allow the logs to fill while rolling back one or more of them, before any of them reach the LTXEHWM. When I taught the classes (and for every server I have ever configured) I preached setting these parameters at 40 and 45%. Kinda low, it seems. My reasoning: No matter how many rogue transactions you have, the logs will never fill up. (Assuming you *are* backing up logs, of course.) For suppose TX1 hits the HWM and starts to roll back, then TX2 hits the HWM while TX1 is already rolling back. Then TX2 will start rolling back as well. However, while writing rollback records for both transactions, TX1 is likely to hit the EHWM first. (It makes no difference which hits the EHWM first.) Then the rollback for TX2 will be suspended (along with all other transaction activity) while the TX1 rollback completes. No matter how many rogue transaction TX3-n are running, all will be suspended while that TX1 rollback proceeds. In the above scenario of many rogues, had the EHWM been set higher than 50%, a situation could occur (quite unlikely - famous last words) where many rogues can gang up and fill the logs with their fresh (not rollback) records before the next checkpoint as the rollback of TX1 is being processed. Even this is not foolproof, as the following war story shows: I mention the checkpoint in this context because [I have empirical reason to believe] the span of each transaction is detected at each checkpoint. Here's my experience with that: My user had requested a 30-minute checkpoint interval because he would be running massive loads. I had configured a 2GB physical log for him, as well as *my* standard 40/45 for LTXHWM/LTXEHWM. 15 minutes into the load, the 6GB of log space was full. Wo' hoppen to my mathematically rigorous scheme? Well, you see, there was no checkpoint in all the time the load was running. Because the physical log was so huge, it did not hit the 75% threshold to trigger a checkpoint. Hence, the backed-up logs did not get freed up, since that kind of behavior is executed at checkpoint time. After Advanced Support dialed in and reset the logical logs, I set the checkpoint interval down to 5 minutes and added a comment to keep it that way. -- Jacob S.

Hi there. I think you should be more specific about what you want to know. A good introduction is the Admin Guide to Informix (If you have a docs CD there is it, if not you can find it - depending of IDS Version you're using now) chapters about logical logs Talking about long transaction, usually you should study about configuration parameters LTXHWM and LTXEHWM (controls automatic rollback when DBMS run out logical log space, but usually you shouldn't change them without a clear idea about how they works and what you want to achieve) DYNAMIC_LOGS (to allow IDS to add new logical logs when they are needed, but this is available since version 9.4, I think) and of course how to allocate logical logs and estimate proper size. I hope this helps Omar Muñoz --- On Tue, 10/20/09, Jeremiah Leong <yogensha4ever@gmail.com> wrote: > From: Jeremiah Leong <yogensha4ever@gmail.com> > Subject: Need enlightenment regarding long transactions > To: informix-list@iiug.org > Date: Tuesday, October 20, 2009, 10:11 AM > Can someone enlighten me on long > transactions? This is something > completely new to me as I've only been with Informix for 2 > years. Any > information or examples would prove helpful. Thanks. > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list >

On 21 Oct, 07:59, Beau Nanaz <SpamnTr...@yahoo.com> wrote: > Warning: This goes waaaay beyond the scope of Jeremiah's question. Much > more enlightenment than originally requested. > > Andrew Clarke wrote: > >  >> Jeremiah's post: >  >> Can someone enlighten me on long transactions? This is something >  >> completely new to me as I've only been with Informix for 2 years. Any >  >> information or examples would prove helpful. Thanks. >  > >  > Have you actually suffered from some? The default settings in the >  > config file are notoriously small. >  > > > Andrew, > Interesting you should say that.  I am accustomed to finding the default > settings at LTXHWM=70, LTXEHWM=80.  If you have several instances of a > rogue application, this is woefully high and is still likely to allow > the logs to fill while rolling back one or more of them, before any of > them reach the LTXEHWM. > > When I taught the classes (and for every server I have ever configured) > I preached setting these parameters at 40 and 45%.  Kinda low, it seems. > > My reasoning: > No matter how many rogue transactions you have, the logs will never fill > up. (Assuming you *are* backing up logs, of course.)  For suppose TX1 > hits the HWM and starts to roll back, then TX2 hits the HWM while TX1 is > already rolling back.  Then TX2 will start rolling back as well. > However, while writing rollback records for both transactions, TX1 is > likely to hit the EHWM first. (It makes no difference which hits the > EHWM first.) Then the rollback for TX2 will be suspended (along with all > other transaction activity) while the TX1 rollback completes.  No matter > how many rogue transaction TX3-n are running, all will be suspended > while that TX1 rollback proceeds. > > In the above scenario of many rogues, had the EHWM been set higher than > 50%, a situation could occur (quite unlikely - famous last words) where > many rogues can gang up and fill the logs with their fresh (not > rollback) records before the next checkpoint as the rollback of TX1 is > being processed. > > Even this is not foolproof, as the following war story shows: > > I mention the checkpoint in this context because [I have empirical > reason to believe] the span of each transaction is detected at each > checkpoint.  Here's my experience with that: > > My user had requested a 30-minute checkpoint interval because he would > be running massive loads.  I had configured a 2GB physical log for him, > as well as *my* standard 40/45 for LTXHWM/LTXEHWM. > > 15 minutes into the load, the 6GB of log space was full. > > Wo' hoppen to my mathematically rigorous scheme? > > Well, you see, there was no checkpoint in all the time the load was > running. Because the physical log was so huge, it did not hit the 75% > threshold to trigger a checkpoint.  Hence, the backed-up logs did not > get freed up, since that kind of behavior is executed at checkpoint time. > > After Advanced Support dialed in and reset the logical logs, I set the > checkpoint interval down to 5 minutes and added a comment to keep it > that way. > > -- Jacob S. Are you sure these are LTXHWM/LTXEHWM is not checked at log switch time rather than checkpoint time? We need Madison/Marco to comment here! David.