gaps in checkpoint vst mean anything?

[danishgambit]

Afternoon 'talkers,

while having a look at some vst's today, noticed that the checkpoint vst (_checkpoint) has a start time and length field - both of which contain date time stamps and I'm curious about the length of time between the end of one checkpoint and the start of the next.

e.g.
checkpoint 1: start = Thu Sep 23 14:22:16 2010
Length= Thu Sep 23 14:37:13 2010

checkpoint 2: start = Thu Sep 23 14:37:53 2010
Length = Thu Sep 23 14:50:12 2010

This implies an interval of 40 secs between 1 checkpoint finishing and the next starting. I imagine during this time the DB engine is writing the contents of the BI cluster to the Database. Just wondering if this sort of interval is common or would it bear further investigation ?

Regards,

DG

 

[RealHeavyDude]

40 seconds checkpoint interval is kinda short.

Mainly of interest with checkpoints is the interval between two of them. When it is to short then you will most probably experience database freezes which come buffers flushed during the checkpoint. Therefore the interval should not be too short. 2 - 5 minutes is the rule of a thumb.

A checkpoint occurs whenever a BI cluster fills. At that time the checkpoint basically syncs the contents of the buffers in the buffer pool with the files on disk. That means it flushes buffer that are marked as changed to disk. As this is a synchronous operation which halts all other activity you don't want it to occur.

For that, if the checkpoint interval is too short, you can increase the cluster size of the BI and, if you have an enterprise database license, you should make sure that the background writers ( APW, BIW, AIW ) are running. If that's the case they will take care of flushing buffers to disk in the background as long as there's enough time for them to do their job - and you will be happy.


Heavy Regards, RealHeavyDude.