Informix Log Analysis

We all know that informix requires logs. However, we will discuss the usage and significance of each log as follows:

The first thing to note is that there are two types of informix logs: Physical logs used to store the front image of data, and logical logs used to store the operation process of all things.

In the initial configuration, physical logs and logical logs are not stored in the root disk space. The default size is 2 MB for physical logs and 6 logical logs, 2 MB for each log file. However, in the actual production environment, these two parameters usually need to be adjusted.

From the suggestion of informix, the size of logical logs is generally the business volume per day. The logical logs are rolled around and the physical logs/logical logs are = 1/3. However, it is impossible for some business systems with a large data volume to make proper adjustments.

The number of physical log files is only 1, the minimum number of logical log files is 3, and the maximum is 32767. You can use the onparams command to change the physical and logical logs.

C: \ Informix> onparams --
Usage: onparams-a-d [-S ] [-I] |
-D-l [-Y] |
-P-s [-D ] [-Y]
-A-Add a logical log file
-I-Insert after current log
-D-Drop a logical log file
-P-Change physical log size and location
-Y-Automatically responds "yes" to all prompts

The above is the onparams help file. Next we will first change the location and size of the physical log:

C: \ Informix> onparams-p-s 40000-d phydbs-y
Shutting down, please wait...
Initializing, please wait...
Recovering, please wait...

You can use phybegin onstat-l to view the chunk that the physical log currently exists. Physize to view the current physical log file. The unit is page. Before that, we created phydbs and specified its size. We specify the size of the physical log file after-s and the location of the physical log file after-d. Next we will change the location and size of the logical log:

C: \ Informix> onparams-a-d logdbs-s 30000-I
Logical log successfully added.

Then, use onstat-l to view the logic log of the new addition:
C: \ Informix> onstat-l
IBM Informix Dynamic Server Version 9.40.TC2E1 -- Quiescent -- Up 00:08:10 -- 25728 Kbytes
Physical Logging
Buffer bufused bufsize numpages numwrits pages/io
P-1 0 8 8 7 1.14
Phybegin physize phypos phyused % used
3: 53 10000 12 0 0.00
Logical Logging
Buffer bufused bufsize numrecs numpages numwrits recs/pages/io
L-3 0 8 37 14 2.6 1.0
Subsystem numrecs Log Space used
OLDRSAM 37, 2628
Address number flags uniqid begin size used % used
0CB37CA8 1 U-B ---- 1 1: 763 500 500
0CB37CE8 2 U-B ---- 2 1: 1263 500 500
0CB37D28 3 U-B ---- 3 1: 1763 500 500
0CB37D68 4 U-B ---- 4 1: 2263 500 500
0CB37DA8 5 U-B ---- 5 1: 2763 500 284
0CB37DE8 6 U---C-L 6 1: 3263 500 315
0CED8B98 12 A ------ 0 2: 37553 7500 0 0.00
0CED8B58 11 A ------ 0 7500 0 0.00
0CED8B18 10 A ------ 0 7500 0 0.00
0CED8AD8 9 A ------ 0 7500 0 0.00
0CED8A98 8 A ------ 0 2: 7553 7500 0 0.00
0CED8A58 7 A ------ 0 7500 0 0.00
12 active, 12 total

We can find that the status of the newly added logical log is A. Back up ontape-s-L 0 and use onstat-l to find that the flag bit of all logs has changed to F. Then, use onmode-l to switch the logic log to the newly added logic log, and use onmode-c to force the checkpoint operation. Finally, use onparams-d-l log_file_num-y to delete the original logical log file. This completes the migration of informix logs.

In onstat-l, the flag bit indicates the status of the logical log,

A Indicates that logs that are not available are newly added.
F indicates idle logs that can be used. Generally, logs are in this status only after 0 backups.
Utable shows the used logical logs
L indicates that the current log file contains a checkpoint
C indicates that the current log file is being used.
B Indicates the log file that has been backed up

Generally, a backup of level 0 is required after a log file is added or deleted.

In the onconfig file, LOGFILES specifies the number of IDS logical logs, LOGSIZE specifies the size of logical logs, PHYSDBS specifies the location of physical logs, and PHYSFILE specifies the size of physical logs. LTAPEDEV specifies the location of logical log backup, LTAPEBLK specifies the size of each block, and LTAPESIZE specifies the size of the backup file.

Next we will discuss the log mode of the database:
No log
No buffer log
Buffer logs
Ansi Mode

We can use ontape to change the log mode.

When no log is used, no logs are written for all DML statements. That is to say, the database does not support transactions. When the database recovers the system backup, the database without logs cannot be completely recovered. Because only the status at the time of backup is recorded in the backup, the database changes after the backup must be restored from the logical log, so these changes cannot be recovered.

Buffer log: All DML statements are written to the log buffer. When the log buffer is full, it is written to the disk. This greatly reduces disk I/O and improves database performance. However, when the system recovers from a problem, data in the buffer zone will be lost. This data cannot be recovered.

No buffer log mode: All DML statements are written to the buffer when they occur, but the transactions are immediately written back to the disk after the commit, in this way, when a system problem occurs, the minimum data loss is guaranteed, but the disk I/O is increased, so the database performance will be affected.

Ansi mode: This mode has the same log buffer processing method as the non-buffered log mode, but this mode is irreversible.

In addition, the processing of BLOB logs is very special. It does not need physical logs and does not need to write the pre-image. BLOB pages are directly written to Disks without the processing of shared memory. Changes to any BLOB idle image will be recorded in the logic log, and all blob spaces data will be refreshed to the hard disk with the backup of the logic log.