I am trying to tune checkpoint time for an Informix 9.21 instance on a solaris box... and no matter what I do I always get long checkpoints because it appears that I only have chunk writes ... never LRU writes ...

Here are the relevant bits of the onconfig:

BUFFERS 768000
LRUS 8
CLEANERS 8
LRU_MAX_DIRTY = 2
LRU_MIN_DIRTY = 1
CKPTINTVL = 300

The weird thing is that onstat -F shows only chunk writes (no LRU writes or foreground writes). onstat -R shows the actual dirty buffers is way past the number that should trigger LRU writes ... but they never happen.

I will be increasing the LRU/CLEANERS up to 128 since that makes it easier to hit the dirty ratio (so says onstat -R in my test env anyway since the % is a ratio of LRUS to buffers) but I am worried that ALL the writing is happening as a CHUNK write during a checkpoint which is dragging the system down ...

Any help would be appreciated.

Hi,

The chunks write is the best operation. The problem is if exist foreground write.
Now, if checkpoint time are big, then please send me output of onstat -d, onstat -m, onstat -F, onstat -R and ONCONFIG file.

Thanks

Gustavo.

Based upon the number of buffers/LRU's you would need 1920 dirty pages before the flush would start. Since your checkpoint interval is 300 seconds you might try timing a "onstat -R" just before the checkpoint would naturally occure.

I think most people would disagree with the previous post - you DO NOT want Chunk Writes.

There's several causes for a long checkpoint. Obviously, the number of pages to be flushed is one cause. Q: How many pages does the checkpoint message say it flushed when it occures?

Also keep in mind that the Physical log plays a very important role in the checkpoint process. The placement (and number) of affected db spaces also has a big influence.

How are these db spaces (including physical and logical logs) positioned?
How many dbspaces are actually affected by the checkpoint. If it's a very few dbspaces you will have little or no parrallel activity.

Before I considered a dramatic increase of LRU's (from 8 to 128) I'd consider lowering the dirty threshholds to 1 and 0.

Setting the LRU cleaning parameters to 1 and 0 is a solution. But in heavy load systems the 0 setting culd be a problem. The problem could be several things, maybe tou can monitor the database state during a checkpoint. Is it (CKPT REQ) then the cleaning itself is not the problem, but stopping all critical sections is (quite difficult to solve), is it (CKPT) then the real clening is the problem.

Especially for the cleaning problem IDS 9.40 has a new 'feature'. LRU max and MIN dirty can now be percentages (like 0.0002) this features is very usefull if you have a relatively large bufferpool.

Greetings,

Rob Prop