CHK REQ times suddenly getting very long

11.70 FC4GE (Yes I know ;-) I have quite a large instance (as in storage) of the above primarily doing timeseries inserts using a mixture or putelem and bulkload. It has been running for years with pretty good performance as the number of data soruces grew. It is now at about 400k devices with 1-5 different metrics being recorded evey 30 minutes, as well as a few 10k realtime devices recording every minute. Checkpoint with timeseries heavy on inserts always took a few (low single digit) seconds. Over the past few weeks whilst the actual checkpoint time is still low it sits in CKPT REQ for up to 100 seconds. I don't think I ever knew what causes or could delay the move from CKPT REQ to CKPT INP. The PHYS LOG %used never gets above about 7. Does anyone have ideas where I can start to look for the issue? I hesitate to say nothing has changed recently because from the above it clearly has but I can't think of anything that would cause this. Any help or thought wil be gratefully received. ------------------------------ Clive Eisen ------------------------------

Clive: You should do an IIUG TechTalk about managing that many TS devices in Informix! That aside, here are the mechanics of what's happening, maybe it will help you figure out what changed and how to manage that: When a checkpoint is triggered the engine locks a mutex latch that prevents any session from entering a "critical section" of engine code because it needs exclusive and consistent access to certain data structures. This triggers the CHPT REQ flag. If there are any active sessions that are already in a critical section the checkpoint has to wait for them all to exit their critical section code and release the associated resources. This wait is your 100 seconds before the CHPT INP flag. You can see the number of sessions causing the wait, the average wait to clear critical session and the longest wait (note that this will typically be less than the total wait time because some sessions will themselves be waiting for other resources like locked data and index pages held by other critical section sessions), in the three columns under "wait" in the onstat -g ckp report. Once the checkpoint has cleared all other critical section sessions it can begin the checkpoint proper. Usually, an unusually long wait of this type is caused by an unusual number of active sessions that are contending for resources and object locks. Art ------------------------------ Art S. Kagel, President and Principal Consultant ASK Database Management Corp. www.askdbmgt.com ------------------------------

Art, Thank you very much for the explanation. Specifically that its waiting fo ran exit from a critical section. I'll examine onstat -g ckp and if nothing specific jumps out I will start by putting my own mutex around the TS row as as far as I can think that must be the resource that there is contention for. As always, many thanks Art. ------------------------------ Clive Eisen ------------------------------

Hi Clive, At a guess as you mention the large storage footprint, something related to: IC98458 AN INCREASING NUMBER OF CHUNKS CAN INCREASE THE DURATION OF AN ONMODE -C BLOCK This was reported by me way back in 2013 and there is some sort of fix in 11.70.xC9. Given it was thirteen years ago I have lost all details on this now but it was to do with reading and writing of chunk headers, which happened/happens sequentially. I am sure you wouldn't encounter this with a normal checkpoint however. If it is something like this you would not necessarily see any active threads holding up the checkpoint and would have to look for I/O happening, e.g. 'onstat -g iof'. A thread in critical section is quite likely as well. If you have as long as 100 seconds to identify the issue, look for any activity in this period. Taking a diff of the output of two 'onstat -g ppf' commands a few seconds apart can be a good start. Ben. ------------------------------ Benjamin Thompson ------------------------------