Original Message:
Sent: Wed April 05, 2023 09:48 AM
From: mark collins
Subject: Logical Logs - Flags and backups
I don't know the specific passage in the manual that mentions quiescent backups, but you can place the instance in quiescent mode via the 'onmode -s' command. The '-s' leaves existing sessions connected and allows them to continue until the user ends the session. If you're impatient, you can use the 'onmode -u' to kill all attached sessions and switch to quiescent mode.
------------------------------
mark collins
Original Message:
Sent: Tue April 04, 2023 07:11 PM
From: Mark Clayton
Subject: Logical Logs - Flags and backups
Hi Mark, thanks for the reply and further info. Will review that area in a bit more detail in the future as it raises some interesting points.
One other thing. I noted in the manual there is an option for a quiescent backup, but the manual is lite in terms of how this gets set etc? Any useful references for understanding this better?
Cheers
Mark
------------------------------
Mark Clayton
Original Message:
Sent: Mon April 03, 2023 10:31 AM
From: mark collins
Subject: Logical Logs - Flags and backups
Mark,
The DYNAMIC_LOGS value of 2 is good, but make certain that you've got plenty of space left in your rootdbs, as that's where the extra logical logs will be created. The LTXHWM/LTXEHWM values of 70/80 are (I believe) the defaults. So long as your application is doing small transactions, it probably is OK, but that's something you have to check. Loading large amounts of data without intermediate COMMITs might require tweaking those default values. Even with tweaking, you may have situations where you don't have enough logical log space. In one of my test instances, I don't have enough space to do a dbimport of a particular database because of one table. I don't have any more disk allocated to that instance, so I can't add logical logs. For that one, I just import the database as an unlogged database, then use ondblog to change the database to logged and run a level 0 archive. I also could probably find something in Art's utilities to do the import with periodic commits, but I've not looked into it yet.
I wanted to expand on one point from my previous post. The LTXHWM/LTXEHWM values are percentages of logical logs, not of the logical log pages. The engine does not care that the start of a transaction is near the very end of a certain logical log. The entire logical log is prevented from being reused so long as there is an open transaction inside that logical log. So whether the BEGIN WORK record was the third item written to a log file or next-to-last thing written, it's the same effect - that log file cannot be reused until the transaction is committed or rolled back.
With that in mind, it is better to have a lot of smaller logical logs than a few large ones. If you were to allocate 1 GB of space to logical logs, it would be better to have one hundred 10 MB logical logs than to have ten 100 MB logs.
------------------------------
mark collins
Original Message:
Sent: Fri March 31, 2023 09:21 PM
From: Mark Clayton
Subject: Logical Logs - Flags and backups
Hi Mark, thanks for the further info and reply. That long transaction rollback thing is hard to get my head around but the explanation helped clarify. And I certainly take your point on qualifying the "we'll never run out of disk space" comment. I've got a task to make sure we have more robust monitoring on processes and disk space etc. so that's scenario is a good thing for us to be aware of.
In terms of settings we have:
DYNAMIC_LOGS 2
LTXHWM 70
LTXEHWM 80
I'm not sure if 70 / 80 are quite high values though as could burn a lot of spare disk before reaching them?
Cheers
Mark
------------------------------
Mark Clayton
Original Message:
Sent: Fri March 31, 2023 07:27 PM
From: mark collins
Subject: Logical Logs - Flags and backups
Hi Mark,
One statement in your post above has not been addressed - "With this setting would it be safe to also assume that we would not run out of log space etc. as backups are continuous and would just continue to cycle around in sequence?"
The answer is a qualified "Yes". The qualification has to do with what's called a Long Transaction. A long transaction is not based on time, or even by how many updates it actually performs, but on the percentage of available logical logs that a given transaction spans. This is controlled by the onconfig parameters LTXHWM and LTXEHWM. If a transaction takes up too much of the available logical logs, the system will start to worry, not being certain that the transaction will finish before it fills up the logical logs. As I said earlier, the transaction in question may not be doing that much update activity. It may be that someone is in dbaccess and did a BEGIN WORK, and updated a single row, but did not do a COMMIT WORK, but got distracted, went to lunch, got called to a meeting, whatever. That transaction is still open, and the logical log in which it started (as well as any later logical logs) cannot be released for reuse until that transaction is closed by either a COMMIT or a ROLLBACK WORK.
For example, say your system has 20 logical logs, and LTXHWM is set to 50, and LTXEHWM is set to 60. If the current logical log number is 445 at the time that a transaction starts, that means you have 20 logical logs available (#445 - 464). If that transaction has not done a COMMIT or ROLLBACK prior to the logical logs reaching the 50% mark (#455), the engine will force it to do a ROLLBACK, and you will see a message in your online.log file saying "Long transaction aborted". The second parameter (LTXEHWM) is a bit of a failsafe. As the system-initiated ROLLBACK is in progress, it is writing additional records to the logical logs. And if you've got multiple users on the system, they may be running other transactions, which also are writing to the logical logs. When the system sees that you have reached 60% full (12 logs into your 20 available because LTXEHWM is set to 60, so at logical log 457), the system will suspend all of the other update activity so that the ROLLBACK has exclusive use of the remaining logical log space, hoping that it can complete the ROLLBACK before it runs out of space.
In the old days, you could fill up the logical logs and the system would just hang, as there was no more space to write in the logical logs. Worse, if you tried to add a new dbspace or chunk, or simply add a new logical log in an existing dbspace, the system needed to write to the logical log to record the fact that you had added this, but since there was no space, it could not do so. They have added onconfig parameters (DYNAMIC_LOGS, AUTO_LLOGS, possibly others) to help you avoid this situation, but since you are new, you'll need to review those to confirm that they are in fact in place.
------------------------------
mark collins
Original Message:
Sent: Thu March 30, 2023 04:30 PM
From: Mark Clayton
Subject: Logical Logs - Flags and backups
HI Mike (and Art), thanks for the replies and information - really appreciate that. Checking Alarmprogram looks like there is an entry BACKUPLOGS=Y as well as BACKUP_CMD="ontape -a -d". So the answer would be continuous backup is set and logs are being backed once used then, aye? With this setting would it be safe to also assume that we would not run out of log space etc. as backups are continuous and would just continue to cycle around in sequence?
# PUBLIC SECTION : CONFIGURATION VARIABLES
#
# ########################################
BACKUPLOGS=Y
Then at night we also run the ontape -s L 0 to create a Level 0 file (which we use for restores to test) as well as then a full backup of the logs again. But this is not essential given we're running continuous log backups anyway?
many thanks!
Mark
ps. Can confirm we don't have LTAPEDEV set to /dev/null either.
------------------------------
Mark Clayton
Original Message:
Sent: Thu March 30, 2023 09:48 AM
From: Mike Walker
Subject: Logical Logs - Flags and backups
Hi Mark,
The "B" in the 3rd position means that the logical log has been backed up, which is good!
The log backup will be triggered by the script referenced in your onconfig file with the parameter ALARMPROGRAM. It is called each time a logical log is filled or logical log is switched. The other possibility is that LTAPEDEV is set to /dev/null (definitely not recommended) in which case the logical log will not be backed up, yet the flag will still show that it has.
The logical log will switch when full, but there are other operations that will trigger a log switch and may be part of your restart process or a manual switch (onmode -l) and may be part of your ontape backup process also, which is why you see some logical logs used but less than 100% used. If you look at your Informix log file (MSGPATH), you will see a record there of each time a logical log is backed up and you may be able to see from that some event around when the particular log backup was performed.
03/30/23 08:30:49 Logical Log 5375644 Complete, timestamp: 0xd1531780.
03/30/23 08:30:49 Logical Log 5375644 - Backup Started
03/30/23 08:30:50 Logical Log 5375644 - Backup Completed
------------------------------
Mike Walker
xDB Systems, Inc
www.xdbsystems.com
Original Message:
Sent: Wed March 29, 2023 11:38 PM
From: Mark Clayton
Subject: Logical Logs - Flags and backups
Hi, total newbie here but needing some guidance re: logical logs and what the onstat flags mean, so would appreciate anything to help improve our understanding. Been reading up on what we can, but informix DBA knowledge is a bit thin in this neck of the woods, hence the forum question.
below is a snip from onstat -l. Informix is 12.10 FC13
My understanding is current Logical Log in use is 47, but was expecting logs immediately prior (46, 45 etc) to on be flagged as U------ (used, ready for backup). With the status U-B----, does that means they're being backed up as they are used, or am I not understanding this correctly? if they are backing up as used would that be a setting in Alarmprogram.sh then? But then we also have a nightly ontape -s L 0 which clears the logging disk space before that slowly builds again during the day. In the full Log list there appear to be no logs with just a U or F flag (maybe immediately after the backup there is, but that's in the wee small hours)
And secondary to that, why are logs 41, 46, and 50 only partially filled?
Thanks in advance and appreciate your collective patience with the 101 questions.
Cheers
Mark
------------------------------
Mark Clayton
------------------------------