Hi Mark,

did you check what's going on with the Checkpoints? 
I suggest checking the online status line - maybe CHKP Blocked and to monitor the dirty page checkpoint write back with "onstat -R|grep dirty ".

Regards
Henri

------------------------------
Henri Cujass
leolo IT, CTO
Germany
IBM Champion 2021 2022 2023
IIUG Board Member
henri.cujass@leolo.com
------------------------------

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

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

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

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

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

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.

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

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

Hi Mark,

You have already received lots of advice here, but I'd like to add something else.  During one of these "freezes", run onstat -g bth and onstat -g BTH to see if there are any blocking threads. They normally take a while to run, but may help you identify what is preventing other things from running.  Also onstat -g wmx and onstat -g lmx to see if there are any locked or waiting mutexes.

If the checkpoint is blocked, then you will start to see sessions with the first flag of "C" in onstat -u.  You may find that some people are unaffected because they are just doing reads, but writes may be paused, while they wait for that checkpoint to complete.

Good luck!

Mike

Hi Mark,

It would be useful to know the version of Informix deployed.

A possible cause of long delays which eventually free up is the db engine searching for free space to insert a row in a large table where there are variable length columns. The bitmap page may only say the pages are partially full which results in an inspection of these pages. It is possible for the engine to scan a large portion of a table before it finds suitable space.

Some older defects related to this: IC98053, IC98529, IT00781, IT02028. I think all are addressed in the 12.10 code line and above.

If this is happening obtaining multiple onstat -g ppf outputs and comparing them to spot activity can pinpoint the tables/fragments/indices involved. Probably better to get the perfstat.sh script from technical support which you can run during an incident to collect many different onstat outputs repeatedly. -g ath, -g lmx, -X, -g con would also be useful.

Ben.

Hi,

When this happens what is the header from onstat?

Is it "(CKPT REQ)" or "(CKPT INP)"?

When the instance appears to freeze capture an "onstat -g stk all" that will allow us to tell what it is waiting on.

As Ben said, which version is this?

NOTE:  Version 14.10.xC9 has an enhancment

IT40986 Enhancement for checkpoint processing

this reduces the number of writes to reserved pages during the checkpoint by a factor of over 100!

If you have slow storage then this helps a lot!

Also assume you have the scheduled task to populate it then in  sysadmin database table mon_checkpoint what is the long wait time for the checkpoint at the time of the issue?

Regards,
David.