Hi Art,
one clarification, as this seems to be a slight, yet not uncommon misconception: DYNAMIC_LOGS in fact is meant to allow transaction rollbacks to complete, rather than prevent 'long transaction' rollbacks in the first place, i.e. this only will kick in when you're already approaching the end of your log space (rather than approaching LTXHWM). To have this working properly, you also should have your individual logical logs big enough, so even a super-fast transaction (rollback) cannot fill one of them in under two seconds (so the server has time to detect the dire situation and react).
Regarding "end of your log space", this in fact normally would be the log file preceding the one holding the BEGIN of your oldest still open transaction. With Enterprise Replication being part of the picture, another hard stop, equally prone to blocking the instance or, optionally, to dynamic log addition (s. CDR_MAX_DYNAMIC_LOGS, CDR_LOG_LAG_ACTION), would be the log file containing ER's 'replay position' - just for completeness sake ;-)
------------------------------
Andreas Legner
------------------------------
Original Message:
Sent: Wed April 29, 2020 09:42 AM
From: Art Kagel
Subject: Long Running Purge Script
Saradhi:
See my responses below:
So, IDS is behaving well. But I wonder on a few elements not clear:
1. Let us say there 40 Logical Logs of size 30000 KB.
Am I correct to say that the total LOG SIZE is 40x30 = 1.2GB?
Yes. Correct.
2. So, a transaction cannot exceed this high value of 1.2 GB? Or how does IDS keep track of these limits?
Almost. There always has to be logical log space to log running transactions. The logs are used round robin so the oldest log is overwritten by the next one. However, if there is an open transaction in that log it cannot be overwritten so everything would have to stop because even a transaction that was about to close could not write its COMMIT record to the logs. In order to prevent that from happening when the percentage of total log space that is spanned by open transactions reaches the LTXHWM percent the oldest open transaction is stopped and automatically rolled back. Of course that rollback writes some data to the logs and other transactions also continue to write to the logs. Also the 2nd oldest transaction may also be blocking the same log file as the one that is rolling back so that may not be enough to free up log space. So, if the percent of log space that is spanned by open transactions reaches the LTXEHWM percent then new transactions will be blocked and all open transactions are rolled back.
So, for example, if LTXHWM is set to 70 then when 70% of the logs contain open transactions then the rollback of the oldest starts. If LTXEHWM is set to 80 then when 80% of the logs contain open transactions all transactions that have not committed are rolled back. So, depending on what the settings for these parameters are you will get far less than 1.2GB of logs used before a rollback. Remember that it isn't that single oldest transaction's log records that are counted but all log records written since the beginning of the log containing that oldest transaction's BEGIN WORK record that are counted. So, for example, if an interactive program that is poorly written does BEGIN WORK and the user goes home for the day without committing it or rolling it back that begins the countdown and even though it may have written only a few dozen bytes to the logs it will be the oldest open transaction and that transaction will span all of the active logs until LTXHWM is reached and it is rolled back releasing all of that log space for reuse.
3. When does DYNAMIC LOGS comes into picture?
If you are set to DYNAMIC LOGS then the engine will automatically add new logs when you get to the LTXHWM instead of rolling transactions back. Of course that will eventually consume all of the free space in the ROOTDBS (or other dbspace if you specify one) then we are back to the scenario above in #2.
4. How does LTXHWM (70%) and LTXEHWM (80%) play a role in this situation?
See #2.
5. When LTAPEDEV=/dev/null, and all the 40 log files are full, if the transaction continues, then there does IDS recording the logical logs?
There are two criteria that govern whether a log file can be reused/overwritten: 1) it has been backed up (or at least marked as backed up because you have LTAPEDEV set to /dev/null), and 2) there are no open transactions contained in that log (see #2 again). If neither applies then the server will either add dynamic logs if so configured or it will just stop processing any "transactions". Some SELECTs may continue but nothing that needs log space.
6. Why does not the IDS before execution estimate the quantum of change a transaction attempts to make and tally that size with the LOG SIZE and say sorry to the user instead of entering into a vert long transaction scenario, and then abort at the end!?
This sounds like a good idea. However, haven't you ever done a SET EXPLAIN on a query and noticed that the estimated rows had no relationship in reality to the actual rows processed? Should the engine refuse a DELETE of 10 rows because the data distributions cause it to estimate that there are 30,000 rows that would be deleted? On top of that, as I pointed out in #2 a tiny transaction that is LOOONNGG but not large can still span all of the logs so ...
Art
Art S. Kagel, President and Principal Consultant
ASK Database Management
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.
Original Message------
Thank you Art, Mark and Andreas for your insights.
The job today got aborted (a big sigh) with error message 458.
Now, I have advised my application team to break the entire task into multiple steps, and at each stage minimize the scope of each delete, do a commit as frequently as possible.
So, IDS is behaving well. But I wonder on a few elements not clear:
1. Let us say there 40 Logical Logs of size 30000 KB.
Am I correct to say that the total LOG SIZE is 40x30 = 1.2GB?
2. So, a transaction cannot exceed this high value of 1.2 GB? Or how does IDS keep track of these limits?
3. When does DYNAMIC LOGS comes into picture?
4. How does LTXHWM (70%) and LTXEHWM (80%) play a role in this situation?
5. When LTAPEDEV=/dev/null, and all the 40 log files are full, if the transaction continues, then there does IDS recording the logical logs?
Lastly, a wishful idea:
6. Why does not the IDS before execution estimate the quantum of change a transaction attempts to make and tally that size with the LOG SIZE and say sorry to the user instead of entering into a vert long transaction scenario, and then abort at the end!?
Once again, thank you all, have a nice day.
#Informix