Re: Too-frequent checkpoints; which parameters to check?

Bob Cunningham wrote: I've got a situation with On-Line 7.22 (SPARC, Solaris 2.5.1) where we get flurries of checkpoints at short intervals, during some update periods that look like: 23:35:07 Checkpoint Completed: duration was 2 seconds. 23:35:14 Checkpoint Completed: duration was 1 seconds. 23:35:22 Checkpoint Completed: duration was 1 seconds. 23:35:30 Checkpoint Completed: duration was 1 seconds. 23:35:38 Checkpoint Completed: duration was 1 seconds. 23:35:46 Checkpoint Completed: duration was 2 seconds. 23:35:53 Checkpoint Completed: duration was 1 seconds. ... 23:45:06 Checkpoint Completed: duration was 1 seconds. 23:45:14 Checkpoint Completed: duration was 1 seconds. Needless to say, queries slow to a crawl when there's a checkpoint every few seconds. Log files aren't a problem, but I'm not sure exactly what is... Which tuning parameters should I be looking to change: PHYSFILE, the LRU* ones, or ??? Bob: Without supporting configuration parameters I'm going to take a stab at your problem. I'm assuming (theres that word) that your checkpoint interval is set to the default of 5 minutes. So you problem is your physical log is set to small. The only thing that can trigger a checkpoint earlier then the setting of checkpoint interval is the physical log exceeding 75% full. Your log is becoming full probably becase you have an unexpected flurry of updates to single records on a data page. The physical log holds before and after images of modified pages, it also writes in complete pages. So if you update 10 records on 10 different pages it becomes 10 seperate entries in the physical log, as opposed to updating 10 records all located on a single page which becomes 1 physical log entry. You might want to look at the amount of records per page on your table to see if you have low numbers, or try to cluster your data better. Refer to the P&T manual chapter 2 for proper physical log size, or vol 1 of the DBA guide chapter 24 for physical log management. I hope this helps David Henseler