Hi, thanks for that.. noted...
Original Message:
Sent: Wed November 22, 2023 06:57 AM
From: Andreas Legner
Subject: Informix processing "freezes"
Brief warning: onstat -g ckp only shows completed checkpoints, so if an ongoing/pending checkpoint is part of the problem, it won't show up in this onstat's body (it would in its header) if captured while the problem is still ongoing.
-> be sure this gets captured shortly after such incident - or resort to sysadmin:mon_checkpoint.
------------------------------
Andreas Legner
Original Message:
Sent: Tue November 21, 2023 03:01 PM
From: Mark Clayton
Subject: Informix processing "freezes"
Hi Andreas, thanks for the reply and advice. Will add the -g command you suggest to a script so we can quickly execute during freezes. Hopefully we get a better capture of data the next time and can identify the culprit. Appreciate the advice. Cheers. mark
------------------------------
Mark Clayton
Original Message:
Sent: Tue November 21, 2023 05:51 AM
From: Andreas Legner
Subject: Informix processing "freezes"
Was this 745 seconds checkpoint a blocking checkpoint?
You'd see this immediately from an asterisk next to Trigger column in 'onstat -g ckp', but only if you captured such output not more than 20 checkpoints after the incident.
To look further into the past, there's sysadmin:mon_checkpoint which should have (at least) all the checkpoints since last restart.
If it was 'Blocking', then what was the trigger/caller? And then, of course, slow disk i/o combined with volume of dirty pages, logical and physical log buffers to flush would have been the primary reason for the duration and, since blocking, the freeze.
If it was not blocking, then it still could've been some session in "critical section" for a very long time, blocking the checkpoint from even starting ... and everyone else from entering into new "critical sections", i.e. from doing any modifying/transactional work. The culprit would then be that first session now buried in the past.
Without further details, we can only speculate ...
Andreas
------------------------------
Andreas Legner
Original Message:
Sent: Mon November 20, 2023 11:05 PM
From: Mark Clayton
Subject: Informix processing "freezes"
Hi all, wanted to ask some advice around some recent Informix "freezes" we've experienced lately. on two occasions we've had cases where Informix has frozen for several minutes resulting in the business application also freezing. the situation has resolved itself after a few mins, but has caused frustration, understandably, for our users. These issues have occurred during normal trading hours, during busier times.
On the latest occasion investigations indicate that a database checkpoint took a long time to complete (Total Time = 745) with very low Avg/Sec for Physical and Logical Logs (70 and 62, respectively). We also see very little other DB activity during this period consistent with the DB freeze (log rolls take almost twice as long as other log rolls around that time), and CPU activity drops during the 'freeze' (perhaps indicating no other non DB activities causing CPU maxing etc.).
Assumption is that informix has halted other DB activity to complete the checkpoint, or perhaps perform a rollback?
We're running a bunch of onstat commands to get a baseline, e.g. -x, -k, -p, -g ses, -u, but hard to see anything that sticks out.
There has to be an underlying cause, but any suggestions as to where to dig deeper / review?
Many thanks. Mark
------------------------------
Mark Clayton
------------------------------